Changes between Version 6 and Version 7 of UsersWikiExamplesOverlayTables

Timestamp:

Jun 11, 2021, 12:33:12 PM (3 years ago)

Author:

mdavis

Comment:

—

UsersWikiExamplesOverlayTables

@@ -106 +106 @@
 [[Image(overlay2.png)]]
 
+== Overlay Function ==
+
 '''Chris Hodgson adds:'''
 
@@ -141 +143 @@
 
 The function takes the name of two tables, and the names of a column from each of them (the id/primary key is the expected use) and the new table name to be created. I believe this approach will be memory limited because all of the linework, and the resulting polygons, are in memory at once and stored in a single (potentially very large) multi-geometry.
+
+Note that the result set will include resultants for areas which are in holes but which do not overlap any parent polygon.  
+If not desired these can be filtered out by keeping only records for which at least one id is non-NULL.
+
+== Worked Example ==
+
+This is the SQL from the above function, with example data:
+
+{{{
+WITH poly_a(id, geom) AS (VALUES
+    ( 'a1', 'POLYGON ((10 40, 30 40, 30 10, 10 10, 10 40))'::geometry ),
+    ( 'a2', 'POLYGON ((70 10, 30 10, 30 90, 70 90, 70 10), (40 40, 60 40, 60 20, 40 20, 40 40), (40 80, 60 80, 60 60, 40 60, 40 80))'::geometry ),
+    ( 'a3', 'POLYGON ((40 40, 60 40, 60 20, 40 20, 40 40))'::geometry )
+)
+,poly_b(id, geom) AS (VALUES
+    ( 'b1', 'POLYGON ((90 70, 90 50, 50 50, 50 70, 90 70))'::geometry ),
+    ( 'b2', 'POLYGON ((90 30, 50 30, 50 50, 90 50, 90 30))'::geometry ),
+    ( 'b2', 'POLYGON ((90 10, 70 10, 70 30, 90 30, 90 10))'::geometry )
+)
+,lines AS ( 
+  SELECT ST_Boundary(geom) AS geom FROM poly_a
+  UNION ALL
+  SELECT ST_Boundary(geom) AS geom FROM poly_b
+)
+,noded_lines AS ( SELECT St_Union(geom) AS geom FROM lines ) 
+,resultants AS (  
+  SELECT geom, ST_PointOnSurface(geom) AS pip 
+    FROM St_Dump(
+           ( SELECT ST_Polygonize(geom) AS geom FROM noded_lines ))   
+)
+SELECT a.id AS ida, b.id AS idb, r.geom
+  FROM resultants r
+  LEFT JOIN poly_a a ON St_Within(r.pip, a.geom) 
+  LEFT JOIN poly_b b ON St_Within(r.pip, b.geom)
+  WHERE a.id IS NOT NULL OR b.id IS NOT NULL;
+}}}