Question about ST_Difference across two layers with many multipolygons

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Question about ST_Difference across two layers with many multipolygons

mtreg
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 


ATB,
P


On Wed, Jul 20, 2016 at 7:34 PM, Michael Treglia <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 Wed, Jul 20, 2016 at 8:33 PM, Paul Ramsey <[hidden email]> 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 


ATB,
P


On Wed, Jul 20, 2016 at 7:34 PM, Michael Treglia <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users






_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about ST_Difference across two layers with many multipolygons

mtreg
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 <[hidden email]> 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 Wed, Jul 20, 2016 at 8:33 PM, Paul Ramsey <[hidden email]> 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 


ATB,
P


On Wed, Jul 20, 2016 at 7:34 PM, Michael Treglia <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users






_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users