[GIS] Postgis Difference with Geometry union

Question

I've got two GIS tables, let say A and B. I would like to do the difference of A using B. While it's pretty straightforward to do it with QGIS (using geoprocessing->difference), I've got much more troubles to do it with PostGis.

Below a visual example :

1/ My two layers (in yellow and orange)

2/ The difference (in pink) between my yellow layer and my orange layer (using QGIS geoprocessing->difference). Only the part of orange that does not intersect my yellow layer are kept

This is my SQL query :

SELECT  A.id, 
    ST_Union(
        COALESCE(
            ST_Difference(
                A.the_geom, 
                B.the_geom 
            ), 
            A.the_geom
        )
    ) As the_geom 
FROM A 
LEFT JOIN b ON ST_Intersects(A.the_geom, B.the_geom)
GROUP BY A.id;

This approach almost work, the only limit is when an object A intersects more than one B object. In this case, the difference function does not work at all.

As you can see in the picture below (difference in purple) the big shape in the middle remains the same instead of being cut by the two yellow geometries.

So my idea was to do a geometry union of the geometry from B :

SELECT  A.id, 
    ST_Union(
        COALESCE(
            ST_Difference(
                A.the_geom, 
                ST_Union(B.the_geom) 
            ), 
            A.the_geom
        )
    ) As the_geom 
FROM A 
LEFT JOIN B ON ST_Intersects(A.the_geom, B.the_geom)
GROUP BY A.id;

But Postgresql complains about the fact that aggregate functions cannot be nested.

Do you have any idea how I could do the same QGIS difference process but with PostGIS?

Answer 1

I think your problem is related to using ST_Union. Because, if, for the same A.id, you end up with overlapping geometries then ST_Union will merge them together, which is not what you want. Try using ST_Collect in place of ST_Union.