ST_Intersection + ST_Difference + not overlapping Polygons

Question

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).

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?

Answer 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)