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