2

I have a PostGIS table with some overlapping polygon and need a PostGIS view where no polygon is overlapping and the data is aggregated (like in the picture).

enter image description here

With a Union of ST_Intersection() and ST_Difference() I get the upper 3 polygons, but then I'm missing the Polygons with no overlap?

SELECT ST_Intersection(a.geometry, b.geometry), a.color||','||b.color as color
FROM mytable a, mytable b 
WHERE a.fid < b.fid AND ST_INTERSECTS(a.geometry, b.geometry)
UNION
SELECT ST_Difference(a.geometry, b.geometry), a.color as color
FROM mytable a, mytable b
WHERE a.fid < b.fid AND ST_INTERSECTS(a.geometry, b.geometry)

How do I include the polygons with no overlap? Is there maybe an easier solution for my problem, like a split/aggregate function?

  • 2
    note that your approach is only working as expected for no more than two polygons overlapping as a pair (a 3rd polygon overlapping any of the two, or both, will lead to partial duplication). – ThingumaBob Jun 20 at 12:41
  • 1
    Is there a way to deal with multiple overlapping polygons? – nepluisse Jun 20 at 14:04
  • the key for handling multiple overlaps, as well as non-intersection, is to pass a union of all intersecting geometries (other than the one to test against) to the function(s); as you might expect, this is computational expensive, and the transition of other column values requires another join, since the union can't keep them. this can also be done recursively, or, possibly best, within a custom (PL/pgSQL) function. – ThingumaBob Jun 20 at 18:08
  • There is support for many overlap cases in the PostGIS Addons github.com/pedrogit/postgisaddons/blob/master/… – Pierre Racine Jun 21 at 12:27
1

You can do a left join on the two tables and keep the rows from A that have not intersections in B

SELECT a.geometry, a.color as color
FROM mytable a
 LEFT JOIN mytable b ON ST_INTERSECTS(a.geometry, b.geometry)
WHERE b.fid IS NULL

Keeping your approach, you would add this query to the list of unions. To make it more efficient, you should not use UNION but UNION ALL instead (it does not try to remove duplicates, so you save a lot of geometry comparisons). At last, you may want to do the left join between A and B in a CTE, and apply the 3 sub-query on the CTE (no need to check 3 times for the intersections)

  • Thanks, that did exactly what I wanted. Plus I do the left join between A and B in a CTE now and its faster, thx for the hint. – nepluisse Jun 20 at 14:00

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.