Changes between Version 5 and Version 6 of UsersWikiExamplesOverlayTables

Timestamp:

Oct 14, 2011, 2:56:29 AM (13 years ago)

Author:

chodgson

Comment:

—

UsersWikiExamplesOverlayTables

@@ -105 +105 @@
 
 [[Image(overlay2.png)]]
+
+'''Chris Hodgson adds:'''
+
+Here is a basic SQL function which implements Kevin's multi-step approach in a single query, accessible by one function call:
+
+{{{
+CREATE OR REPLACE FUNCTION my_overlay(table1 varchar, col1 varchar, table2 varchar, col2 varchar, result_table varchar) returns void AS 
+$$ 
+BEGIN
+EXECUTE 'CREATE TABLE ' || result_table || ' AS '
+|| 'WITH all_lines AS ( '
+        || 'SELECT St_ExteriorRing(st_geometryn(the_geom,1)) AS the_geom '
+        || 'FROM ' || table1
+        || ' UNION ALL '
+        || 'SELECT St_ExteriorRing(st_geometryn(the_geom,1)) AS the_geom '
+        || 'FROM ' || table2
+|| '), noded_lines AS ( '
+        || 'SELECT St_Union(the_geom) AS the_geom '
+        || 'FROM all_lines '
+|| '), new_polys AS (  '
+        || 'SELECT geom AS the_geom, ST_PointOnSurface(geom) AS pip '
+        || 'FROM St_Dump(( '
+                || 'SELECT St_Polygonize(the_geom) AS the_geom '
+                || 'FROM noded_lines)) '
+|| ') '
+|| 'SELECT a.' || col1 || ' as ' || table1 || '_' || col1 
+|| ', b.' || col2 || ' as ' || table2 || '_' || col2 
+|| ', p.the_geom as the_geom '
+|| 'FROM new_polys p '
+|| 'LEFT JOIN ' || table1 || ' a ON St_Within(p.pip, a.the_geom) '
+|| 'LEFT JOIN ' || table2 || ' b ON St_Within(p.pip, b.the_geom)';
+END
+$$ LANGUAGE plpgsql;
+}}}
+
+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.