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?