# Postgis Difference with Geometry union

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?

• Please clarify what you are attempting to achieve. Your queries lack join rules, resulting in polynomial expansion, which would be expected to take a long time (N x M rows). If you want the difference of A1 with B1 and A2 with B2,... you need to add `WHERE b.keycol = a.keycol` to the first attempt. – Vince Dec 19 '14 at 11:08
• Hi Vince, what I would like to achieve it's the difference between the geometry objects of my layer A and all the geometry objects that intersects of my layer B. It's equivalent to the difference geoprocessing function in QGIS. – Arnaud Geotribu Dec 19 '14 at 11:14
• The difference between all features will include NxM shapes. Maybe you want all shapes that overlap? With key columns from each table? Please update the question to assume that not everyone who knows SQL geometry knows QGIS. Graphical examples would not be inappropriate. – Vince Dec 19 '14 at 11:38
• Hi Vince, I've added some graphical examples. Is it more clearer what I would like to achieve ? Thanks – Arnaud Geotribu Dec 19 '14 at 12:08

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