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

enter image description here

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:

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.


Install the PostGIS Addons and give a try to ST_GeoTableSummary(). There are examples in the .sql file. Something like:

CREATE TABLE test_geotable_summary AS
SELECT * FROM ST_GeoTableSummary('public', 'test_geotable', 'geom', 'id1');

The Addons comprise others functions to remove overlaps as well.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.