Listing all overlapping polygons using PostGIS?

Question

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.

Pierre Racine · Answer 1 · 2019-03-15T15:25:25.980

1

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.

edited Mar 15 '19 at 15:25

answered Mar 15 '19 at 15:20

Pierre Racine

2,578
15
16

dr_jts · Answer 2 · 2021-06-11T18:05:42.860

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

Listing all overlapping polygons using PostGIS?

2 Answers2