Changes between Initial Version and Version 1 of UsersWikiExamplesOverlayTables

Timestamp:

Apr 14, 2009, 9:09:20 AM (15 years ago)

Author:

pierre

Comment:

—

UsersWikiExamplesOverlayTables

@@ +1 @@
+= Examples Overlay Tables =
+
+Bill Thoen [http://postgis.refractions.net/pipermail/postgis-users/2006-September/013091.html asks]:
+
+"Using PopstgreSQL/PostGIS, what are the steps I need to use to split one layer of polygons with another layer and also maintain data attrributes among the polygons that are split? For example, I've got corn and soybean field polygons that need to be split at the borders of overlaying CLU (Common Land Unit) polygons, and if a corn field crosses under a CLU boundary, I want to split the field at the CLU boundary and copy the orignal field id, crop code and other attributes into the two (or more) resulting farm field polygons (and probably assign a new unique polygon id to them all too.) "
+
+Paul Ramsey [http://postgis.refractions.net/pipermail/postgis-users/2006-September/013093.html answers]:
+
+
+{{{
+CREATE TABLE new_fields AS
+SELECT
+   Intersection(f.the_geom, c.the_geom) AS the_geom,
+   f.attr1,
+   f.attr2,
+   c.clu_name
+FROM
+   fields f,
+   clu c
+WHERE
+   f.the_geom && c.the_geom
+AND
+   Intersects(f.the_geom, c.the_geom)
+
+}}}
+
+
+Kevin Neufeld answers:
+
+Given two polygonal datasets, fields and clu boundaries,
+[[http://postgis.refractions.net/support/wiki/overlay1.png]]
+
+One could perform an overlay operation by:
+
+1. Extract the linework from the polygons (you may wish to add any interior rings to this query).
+
+{{{
+CREATE TEMP TABLE all_lines AS
+SELECT St_ExteriorRing(the_geom) AS the_geom
+FROM fields
+UNION ALL
+SELECT St_ExteriorRing(the_geom) AS the_geom
+FROM clu;
+
+}}}
+
+
+2. Node the linework:
+
+{{{
+CREATE TEMP TABLE noded_lines AS
+SELECT St_Union(the_geom) AS the_geom
+FROM all_lines;
+
+}}}
+
+
+3. Re-polygonize all linework:
+
+{{{
+CREATE TEMP TABLE new_polys (id serial PRIMARY KEY, the_geom geometry);
+INSERT INTO new_polys (the_geom)
+SELECT geom AS the_geom
+FROM St_Dump((
+ SELECT St_Polygonize(the_geom) AS the_geom
+ FROM noded_lines
+));
+
+}}}
+
+
+4. Transfer attributes from original polygons to newly formed polygons
+  * Create point-in-polygon features.
+
+{{{
+CREATE TEMP TABLE new_polys_pip AS
+SELECT id, ST_PointOnSurface(the_geom) AS the_geom
+FROM new_polys;
+
+}}}
+
+
+  * Overlay points with original polygons, transferring the attributes.
+
+{{{
+CREATE TEMP TABLE pip_with_attributes AS
+SELECT a.id, f.attr1, c.attr2
+FROM new_polys_pip a
+LEFT JOIN fields f ON St_Within(a.the_geom, f.the_geom)
+LEFT JOIN clu c ON St_Within(a.the_geom, c.the_geom);
+
+}}}
+
+
+  * Join the points to the newly formed polygons, transferring the attributes:
+
+{{{
+CREATE TABLE new_fields AS
+SELECT *
+FROM new_polys a LEFT JOIN pip_with_attributes b USING (id);
+
+}}}
+
+
+[[http://postgis.refractions.net/support/wiki/overlay2.png]]