[postgis-users] clip a polygon layer with another polygon later

[Puneet Kishor]

On Jun 8, 2012, at 10:30 PM, Brent Wood wrote:

> Without looking too much into it, I figure you can do this using a case when statement to identify & return the appropriate geometry for the different cases, with perhaps a coalesce to handle nulls??



I realized that my query was goofy. So, I tried the following


	UPDATE 	polys
	SET 	the_geom = COALESCE(t.clipped_geom, null)
	FROM 	(
		SELECT 	id, 
			ST_Intersection(o.the_geom, p.the_geom) AS clipped_geom 
		FROM 	polys p JOIN 
			outline o ON ST_Intersects(o.the_geom, p.the_geom)
		) AS t
	WHERE	polys.id = t.id;

but no joy. No errors, but the actual features are not clipped.

Interestingly, QGIS has a geoprocessing plugin to "clip" one layer using another layer. I tried that, and that worked perfectly creating a shape file with the right features. I want to replicate that in code.


> 
> 
> Brent Wood
> 
> 
> --- On Sat, 6/9/12, Puneet Kishor <punk.kish at gmail.com> wrote:
> 
> From: Puneet Kishor <punk.kish at gmail.com>
> Subject: [postgis-users] clip a polygon layer with another polygon later
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Date: Saturday, June 9, 2012, 2:50 PM
> 
> Given
> 
>     CREATE TABLE outline (
>         id,
>         the_geom
>     );
> 
> and
> 
>     CREATE TABLE polys (
>         id,
>         the_geom
>     );
> 
> I want to update polys.the_geom with only those that are clipped by "outline". That is, all the "polys" that are completely within the ouline.the_geom rows should remain intact, all those that are intersected should only have there overlapping portion retained, and all that are outside outline.the_geom should be set to null.
> 
> I am unable to figure out how to do this in one query. The following doesn't work
> 
>     UPDATE     polys
>     SET     the_geom = ST_Intersection(o.the_geom, p.the_geom)
>     FROM     outline o JOIN 
>         polys p ON ST_Intersects(o.the_geom, p.the_geom)
>     WHERE    ST_Intersects(o.the_geom, p.the_geom) = 'true';
> 
> 
> 
> --
> Puneet Kishor