How to split polygons with overlapping polygons AND count overlaps in PostgreSQL?

Question

I am trying to create an SQL query (PosgreSQL + PostGIS) on a single table that contains 1000’s of overlapping polygons (multi-part, some with holes) that will: . cut polygons into non-overlapping pieces, and for each piece . count how many times the overlap occurred.

I have found a few examples googling but none shows the full answer. For example, here is one that cuts polygon (based on: PostGIS equivalent of ArcMap Union ) but doesn’t cut properly if polygons have holes. And I can’t figure out how to add a column with counted overlaps:

WITH forest AS (
    SELECT *
    FROM (VALUES
        (1,ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0) ,(1 1, 1 3, 3 3, 3 1, 1 1))',0))
        ,(2,ST_GeomFromText('POLYGON((10 0,20 0,20 10,10 10,10 0))',0))
        ,(3,ST_GeomFromText('POLYGON((2 2,4 2,4 4,2 4,2 2))',0)) /* hole in first */
        ,(4,ST_GeomFromText('POLYGON((8 5,12 5,12 9,8 9,8 5))',0)) /* overlapping */
        ,(5,ST_GeomFromText('POLYGON((12 2,14 2,14 4,12 4,12 2))',0)) /* hole in second */
        ) forest(id, geom)
    )
SELECT 
  (dump).path[1] id,
  (dump).geom
FROM
(
  -- c) Polygonize the unioned rings (returns a GEOMETRYCOLLECTION)
  --    Dump them to return individual geometries
  SELECT
    ST_Dump(ST_Polygonize(geom)) dump
  FROM
  (
    -- b) Union all rings in one big geometry
    SELECT
       ST_Union(geom) geom
    FROM
    (
      -- a) First get the exterior ring from all geoms
      SELECT
        ST_ExteriorRing(geom) geom
      FROM
        forest
    ) a
  ) b 
) c

Input:

Result:

The answer referenced here requires custom extension: https://gis.stackexchange.com/questions/300800/get-single-part-polygon-by-their-intersection-in-postgis and although include correct counting of overlaps and handling holes, the result contains also overlapping polygons.

WITH geomtable AS (
  SELECT 1 id, ST_GeomFromText('POLYGON((0 1, 3 2, 3 0, 0 1), (1.5 1.333, 2 1.333, 2 0.666, 1.5 0.666, 1.5 1.333))') geom
  UNION ALL
  SELECT 2 id, ST_GeomFromText('POLYGON((1 1, 3.8 2, 4 0, 1 1))') geom
  UNION ALL
  SELECT 3 id, ST_GeomFromText('POLYGON((2 1, 4.6 2, 5 0, 2 1))') geom
  UNION ALL
  SELECT 4 id, ST_GeomFromText('POLYGON((3 1, 5.4 2, 6 0, 3 1))') geom
  UNION ALL
  SELECT 5 id, ST_GeomFromText('POLYGON((3 1, 5.4 2, 6 0, 3 1))') geom
  UNION ALL
  SELECT 6 id, ST_GeomFromText('POLYGON((1.75 1, 1 2, 2 2, 1.75 1))') geom
), parts AS (
  SELECT a.id, unnest(ST_SplitAgg(a.geom, b.geom, 0.00001)) geom
  FROM geomtable a,
       geomtable b
  WHERE ST_Equals(a.geom, b.geom) OR
        ST_Contains(a.geom, b.geom) OR
        ST_Contains(b.geom, a.geom) OR
        ST_Overlaps(a.geom, b.geom)
  GROUP BY a.id, ST_AsEWKB(a.geom)
)
SELECT count(*) nb, ST_Union(geom) geom
FROM parts
GROUP BY ST_Centroid(geom)

Result:

I am hoping that there is a way to make it working for all types of polygons with a few tweaks...

Answer 1

So, I am answering my own question once again...

Here is what I came up with, hopefully it will help others with similar problem:

. this code works with polygons with holes (at least for cases I tested);

. there are two parts: the first splits multiple overlapping polygons into individual parts, the second checks (for a point in each part) how many input polygons it intersects with;

. this code will handle 'a few' overlapping polygons but is untested on large tables - performance can be improved if input data is properly indexed.

WITH forest AS (
    SELECT *
    FROM (VALUES
        (1,ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(1 1, 1 3, 3 3, 3 1, 1 1))',0))
        ,(2,ST_GeomFromText('POLYGON((10 0,20 0,20 10,10 10,10 0))',0))
        ,(3,ST_GeomFromText('POLYGON((2 2,4 2,4 4,2 4,2 2))',0)) /* hole in first */
        ,(4,ST_GeomFromText('POLYGON((8 5,12 5,12 9,8 9,8 5))',0)) /* overlapping */
        ,(5,ST_GeomFromText('POLYGON((12 2,14 2,14 4,12 4,12 2))',0)) /* hole in second */
        ) forest(id, geom)
    ), parts AS(
SELECT path[1] id, geom FROM ST_Dump((
        SELECT ST_Polygonize(the_geom) AS the_geom FROM (
        SELECT ST_Union(the_geom) AS the_geom FROM (
            SELECT ST_Boundary(geom) AS the_geom FROM forest) AS lines
        ) AS noded_lines
    )
))
SELECT a.geom, count(*) nb
FROM parts a , forest b
WHERE ST_Within(ST_PointOnSurface(a.geom),b.geom)
GROUP BY a.id, a.geom