Get single part polygon by their intersection in PostGIS

Question

I have a table in PostGIS that has polygons overlapping each other. (see below an example of 4 overlapping polygons )

Someone could give me an idea, what PostGIS function I can use that give me single part polygons based on their intersection, like the following:

PS: Actually what I want in the end is the individual polygons with no overlap, but the count of polygons overlapping it (counting itself). For example, in the second figure, the count of the polygon 2, 8, 12, 13 will be, respectively, 1, 2, 3, 4

Answer 1

Try this:

Download the PostGIS Addons from this link: https://github.com/pedrogit/postgisaddons

Install by running the postgis_addons.sql file to get the ST_SplitAgg() function.

Test by running the postgis_addons_test.sql file.

Here is a self contained example of a problem similar to your one:

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)

Should work with thousands of polygons and when there are more than two overlaps.

Answer 2

You should be able to get what you want using the Intersection (ST_Intersection) together with Symmetric Difference (ST_SymDifference - apply ST_Dump to it in order to have the individual polygons)