• API
• FAQ
• Tools
• Archive
SHARE
TWEET # Untitled a guest Mar 20th, 2019 44 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.

Top