Examples Overlay Tables

Bill Thoen ​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 ​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,

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);