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

[Michael Treglia]

Paul, thanks so much for the quick response.

This is making sense - unfortunately I'm hitting a topology error that I'll
try to deal with at a better hour... It's helpful to see your code for this
though as I try to get a better sense of how to work most efficiently with
PostGIS/sql.

Best,
mike

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

> 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
>>>
>>
>>
>
>
>
>
> _______________________________________________
> 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/20160721/42ab0c56/attachment.html>