daily pastebin goal
57%
SHARE
TWEET

Untitled

a guest Mar 20th, 2019 43 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH overlapping AS (SELECT
  2.   a.sk_region AS sk_region_1,
  3.   b.sk_region AS sk_region_2,
  4.   round(ST_Area(a.the_geom)) AS region_1_area,
  5.   round(ST_Area(b.the_geom)) AS region_2_area,
  6.   round(ST_Perimeter(a.the_geom)) AS region_1_perim,
  7.   round(ST_Perimeter(b.the_geom)) AS region_2_perim,
  8.   ST_CollectionExtract(
  9.     ST_Intersection(a.the_geom, b.the_geom), 3
  10.   ) AS the_geom,
  11.   ST_CollectionExtract(
  12.     ST_Intersection(a.the_geom, b.the_geom), 3
  13.   ) AS the_geom,
  14.   round(ST_Area(
  15.     ST_CollectionExtract(
  16.       ST_Intersection(a.the_geom, b.the_geom),
  17.   3))) AS overlap_area,
  18.   round(ST_Perimeter(
  19.     ST_CollectionExtract(
  20.       ST_Intersection(a.the_geom, b.the_geom),
  21.   3))) AS overlap_perimeter
  22. FROM qa_subregions a
  23. JOIN qa_subregions b ON ST_Intersects(a.the_geom, b.the_geom)
  24. WHERE a.sk_region != b.sk_region
  25. AND ST_Dimension(
  26.   ST_CollectionExtract(
  27.     ST_Intersection(a.the_geom, b.the_geom),
  28.   3)) = 2
  29. ORDER BY 4 DESC),
  30. -- to get only the first instance of A-overlap-B, not B-overlap-A
  31. distinct_overlapping AS (
  32. SELECT DISTINCT
  33.   CASE WHEN sk_region_1 > sk_region_2 THEN sk_region_2 ELSE sk_region_1 END AS sk_region_1,
  34.   CASE WHEN sk_region_1 < sk_region_2 THEN sk_region_2 ELSE sk_region_1 END AS sk_region_2
  35. FROM overlapping
  36. )
  37. SELECT
  38.   o.*,
  39.   o.overlap_area / o.overlap_perimeter as overlap_ratio,
  40.   ST_MinimumClearance(o.the_geom) as min_clearance,
  41.   ROW_NUMBER() OVER(ORDER BY o.sk_region_1) AS cartodb_id
  42. FROM distinct_overlapping d
  43. JOIN overlapping o ON d.sk_region_1 = o.sk_region_1 AND d.sk_region_2 = o.sk_region_2
  44. WHERE o.overlap_perimeter > 0
  45. ORDER BY overlap_ratio DESC
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top