2

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

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: img_after

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
2

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.

| improve this answer | |
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)

Post GIS tools

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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