1

I'm working on OpenStreetMap data on Postgis. I have in a table "roads_polygons_label" containing all the polygons and roads in a given area and for each of them a label saying what it is (e.g. building, residential, grassland, according to OSM database). The problem is that some polygons are nested. For instance polygon A labeled as "residential" contains polygons B and C both labeled as "building". I want to separate A from B and C to get three records:

  • polygon A\(BuC) labeled as "residential"

  • polygon B labeled as "building"

  • polygon C labeled as "building"

I managed to get a table "overlapping" where I join "roads_polygons_label" with itself on an ST_Intersects query to have for each larger polygon a row for each polygon it contains. So in my basic example it would give two records for polygon A:

  • A - B

  • A - C

Then I wanted to use ST_Union to make a big multipolygon made of all polygons nested in one larger polygon, so here a polygon made of BuC. But I would need to loop on each larger polygon and I don't know how to do that. I can create a table "large_polys" listing all polygons containing smaller polygons, but then I don't know how to join "overlapping" with "large-polys" in the loop. I have read the following posts but they did not provide a solution for me problem:

Separate polygons based on intersection using PostGIS

SQL Server - Select all polygons nested within a larger polygon

Edit : illustration of what I mean: nested polygons Maybe "nested" is not the right term. A, B and C have different osm ids. I also have several polygons with the same id and different area, but that's another issue.

Improve this question
4
  • 1
    Something that isn't clear to me is what you mean by 'nested' polygons. The example you give sounds like A, B, and C are currently a multipolygon, but somehow each polygon part has different attributes. This isn't something I've encountered in PostGIS before and I'm having trouble finding any documentation on how that would work.
    – Emily
    Dec 20, 2017 at 15:28
  • 1
    I am not understanding what you mean by "some polygons are nested. For instance polygon A labeled as "residential" contains polygons B and C both labeled as "building". I want to separate A from B and C to get three records", show us a picture or provide some geojson so for this example so we can play with it
    – ziggy
    Dec 20, 2017 at 16:14
  • See my edit, sorry if that wasn't clear.
    – Anne
    Dec 21, 2017 at 7:00
  • I think I, solved it with a simple groupby on my larger polygons to use ST_Union on the contained polygons, and then ST_Difference on the larger polygon and the union.
    – Anne
    Dec 21, 2017 at 8:21

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.