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.

  • look into st_clusterdbscan
    – ziggy
    Aug 14, 2019 at 15:08

2 Answers 2


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.


This is a classic problem in polygon overlay analysis. Your first query creates all the resultant polygons from the overlay. Each resultant polygon is the child of one or more parent polygons. To find the parents for the children, use ST_PointOnSurface to generate interior points, and then find all the original polygons which are intersected by each point.

This question has a query to do this (in Step 3).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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