5

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) enter image description here

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 enter image description here

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.

enter image description here

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?

4
  • 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
2

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.

3
  • Unfortunately by using a ST_Union I get this error : "aggregate functions cannot be nested" – Arnaud Geotribu Dec 22 '14 at 12:35
  • I meant: use your first query (the one with just one ST_Union) and replace ST_Union by ST_Collect. – erilem Dec 22 '14 at 13:00
  • Hi Erilem, by removing the first ST_Union and by only using it inside the difference, the query works now ! Thanks for your help. You gave me a good tip :) – Arnaud Geotribu Dec 23 '14 at 5:35

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.