I have 2 tables Table A with this fields (379 rows in this table) enter image description here

The geom of table A contains this (QGIS only used for better visualization) enter image description here

The table B contains this fields (97 rows in this table) enter image description here

The Field cobertura5 contains a buffer of 20 km enter image description here

I made the difference operation on QGIS first and the result is this (298 rows on QGIS) enter image description here

In PostGIS, I've tried this sentence looking for the same result

Create Table prueba AS
Select ST_Difference(tableA.geom,tableB.cobertura5) From tableA, tableB

And get this on QGIS (35162 rows) (Exactly the same as tableA) enter image description here

I get stuck there with no ideas of what to do maybe a WHERE or maybe include another operation on the sentences?

  • 2
    The multiple tables in a FROM clause style query fell out of favor nigh on 30 years ago, when JOIN was added to the SQL standard. One of the main issues with the FROM list was forgetting to add a WHERE clause element to enforce a relationship between the tables, resulting in polynomial expansion. Since not all features overlap all other features, you're going to have nearly a hundred copies of every row in A in the result set. I suggest finding a good SQL tutorial, where you can work on the JOIN clause before integrating spatial types and functions into the mix.
    – Vince
    Apr 17 '21 at 0:11

This recent post has a good solution to this problem. For your data the code looks like:

         ST_Difference(a.geom, blade.geom),
       )) AS geom
FROM   tableA AS a
  SELECT ST_Union(b.cobertura5) AS geom
  FROM   tableB AS b
  WHERE  ST_Intersects(a.geom, b.cobertura5) 
) AS blade;
  • Thanks, that works really well and give me the expecting results Apr 20 '21 at 3:41
  • This will work as far as PostGIS is able to union the complete coverage into a single polygon. That's not the case of you have hundreds of thousands of polygons. Apr 20 '21 at 14:17
  • The query only unions the polygons which intersect each polygon from the A table. That should work fine for the dataset shown.
    – dr_jts
    Apr 20 '21 at 18:21
  • @dr_jts With this dataset, no doubts. We need ST_DifferenceAgg() in geos! Apr 21 '21 at 13:28
  • 1
    @PierreRacine handling a massive amount of data in reasonable memory is going to require a new approach to overlay. Hopefully that can get worked on sometime.
    – dr_jts
    Apr 22 '21 at 18:19

In PostGIS each polygon from table B is removed from each polygon from table A individually and the resulting difference is not cumulative. i.e. polygon B1 and B2 of table B are removed from two different instances of polygon A1 of table A producing two polygons instead of one.

The trick is to accumulate all the differences from table B for each polygon of table A. Cumulate here means aggregate. Unfortunately there is still no ST_Difference() aggregate function in PostGIS. But one has been developed in the PostGIS Addons, a set of advanced plpgsql functions for PostGIS..

So download the Addons and execute them in your database. This will enable those functions.

Then you can try this query:

SELECT ST_DifferenceAgg(tableA.geom, tableB.geom) geom
FROM tableA, tableB
WHERE ST_Intersects(tableA.geom, tableB.geom)
GROUP BY tableA.geom;
  • Works well, it was easy with the Addons. Thanks Apr 20 '21 at 3:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.