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

[Paul Ramsey]

I wish I could edit emails after sending, I forgot to group the parks by
their island:

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)
  GROUP BY islands.id
)
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;



On Wed, Jul 20, 2016 at 8:33 PM, Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

> 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/12e54efc/attachment.html>