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.k...@gmail.com> wrote:
>
> From: Puneet Kishor <punk.k...@gmail.com>
> Subject: [postgis-users] clip a polygon layer with another polygon later
> To: "PostGIS Users Discussion" <postgis-users@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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
