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
  • 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. – Brynne Dec 20 '17 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 '17 at 16:14
  • See my edit, sorry if that wasn't clear. – Anne Dec 21 '17 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 '17 at 8:21

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.