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

Should the result be a polygon coverage with no overlaps - only one polygon in any place? No need to know the ids of the parent polygons or anything like that? — user30184, Oct 31 '18 at 11:04
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 — sibams, Oct 31 '18 at 11:10
Are you missing a "not" in that sentence? What you want is not the individual polygons, but the overlap count? — j08lue, Oct 31 '18 at 12:22
I'd say make a function for counting the number of features in your table overlapping one given feature. And then apply that function to all features. — j08lue, Oct 31 '18 at 12:27
I'd like to have the polygons like in the second figure, having the overlap count in their attribute table. — sibams, Oct 31 '18 at 12:31
I see - so you actually want both. How about running ST_Intersection for every possible combination of two polygons in your table, giving you the intersections as polygons. Then intersect all those with each other, too. That should give you the small parts you want - some of them several times, which is the count you want. — j08lue, Oct 31 '18 at 13:46

score 3 · Accepted Answer · answered Nov 05 '18 at 19:44

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.

Matt_Geo · Answer 2 · 2018-11-02T19:16:04.780

1

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)

edited Nov 02 '18 at 19:16

answered Nov 02 '18 at 19:08

Matt_Geo

1,050
1
10
26

Get single part polygon by their intersection in PostGIS

2 Answers2

Linked