Assume a table of overlapping polygons where the number of polygons overlapping a single area varies from 1 to n. I am trying to find a PostGIS/SQL query that will return a list of all of the polygons that contribute to a particular overlap.
I am currently using a two pass approach that is 1/2 in SQL and 1/2 in Python. I believe that this approach could be significantly improved, but am unsure exactly how.
SQL: This query returns a table of polygons that represent all of the overlaps between all of the input polygons.
SELECT ST_AsEWKB(geom) AS geom FROM ST_Dump(( SELECT ST_Polygonize(the_geom) AS the_geom FROM ( SELECT ST_Union(the_geom) AS the_geom FROM ( SELECT ST_ExteriorRing(footprint).geom) AS the_geom FROM study) AS lines ) AS noded_lines ) )
This picture might help to describe what is occurring above. The polygons 'contributing' to the intersection are noted in red. The above query computes the polygons, but does not result in an array of 'contributing members'.
Python: The second pass is to take the original input layer and intersect it with the results of the above SQL query.
Question: Does a one pass solution in pure SQL exist for this spatial problem? The posted SQL gets the polygon boundaries, but I am unsure how to identify which input polygons created that boundary in a way that does not presume the number (or max number) of contributing polygons.
This question is similar to: Find and list all polygons that overlap with another polygon except answers along the lines of:
SELECT * FROM table as a, table as b WHERE ST_Intersects(a.Geometry, b.Geometry) AND a.id > b.id
result in a selection of pairwise overlaps.