[postgis-users] Question about ST_Difference across two layers with many multipolygons

[Paul Ramsey]

This would be easier w/ pictures, but
- for each island you want to
- create the set of parks that are contained w/i that island and
- remove that whole set of parks from the one island they refer to
Because: ST_Difference() is a two parameter function: one parameter for the
island, and one parameter for all the things you want removed.
So.

WITH parks_to_remove AS (
  SELECT islands.id,
    ST_Collect(parks.geom) AS geom
  FROM islands
  JOIN parks
  ON ST_Intersects(parks.geom, islands.geom)
)
SELECT islands.id,
  ST_Difference(islands.geom, parks_to_remove.geom) AS geom
FROM islands
JOIN parks_to_remove
ON islands.id = parks_to_remove.id;


ATB,
P


On Wed, Jul 20, 2016 at 7:34 PM, Michael Treglia <mtreglia at gmail.com> wrote:

> Hi All,
>
> Please pardon this fairly general question - I'm struggling with using
> ST_Difference, and think I might be missing something just about the
> intended way ST_Difference functions, as I'm still learning PostGIS... I
> think the answer to this will help me troubleshoot or at least lead me to
> ask better questions down the line.
>
> So, I have a set of large and many multipolygons (islands) within one
> layer, and many smaller sets of multipolygons that are contained within the
> islands  (parks) in another layer. I'm trying to effectively clip out the
> parks from the islands, so basically the end result will be the Islands
> with many holes throughout. The code I was expecting to use was along the
> lines of this:
>
> SELECT ST_Difference(islands.geom, parks.geom) AS newgeom INTO
> test.islands_noparks FROM staging.parks, staging.islands
>
> However, this is producing the entire islands, but with many rows (just
> about as many rows as there are parks). Thus, I think I might be a bit
> confused about how ST_Difference is intended to function. Would anybody be
> able to clarify?
>
> Thanks so much for your time! Best,
> Mike
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160720/f862d131/attachment.html>