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 <mtreg...@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@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users