2

I am trying to understand SQL commands in PostGIS.

I am trying to perform an operation which would be completed with the use of the Difference Operator in QGIS but I have large numbers of polygons so I need to manage this in PostGIS instead.

Essentially, I have some circular polygons. Let's call those buffers and I want to cut out some areas from those buffers. Let's call the areas to be cut out "blanks". The output should be the buffers minus any areas where they overlap with the blanks.

I've looked around for some suitable code to re-use and have found the code below. This works, after a fashion, BUT it doesn't give me the buffers which do not intersect with any blanks, which I also need. The problem is, I don't understand how this is working.

Why does JOIN use the expression FROM buffers b JOIN blanks a i.e. why isn't it FROM buffers JOIN blanks?

Why do we need an ST_INTERSECTS as well an ST_DIFFERENCE?

Shouldn't ST_DIFFERENCE on its own be enough?

Why is it so complicated to create what is presumably a regularly used operation?

I am new to SQL and these may be obvious questions. I've worked my way through as many tutorials as I can but they all seem to be quite basic or too advanced for me to follow. The second things progress beyond simple expressions I get lost.

CREATE TABLE buffers_minus_blanks AS
WITH temp AS 
(
  SELECT   b.id, st_union(a.geom) AS geom
  FROM     buffers b JOIN blanks a ON st_intersects(a.geom, b.geom)
  GROUP BY b.id
)
SELECT b.buffer_id, st_difference(b.geom,t.geom) AS newgeom
FROM buffers b LEFT JOIN temp t ON b.id = t.id;
10
  • 1
    I'm sorry but I'm new to all this. How is asking how to do a difference operation between two vector datasets in PostGIS not a GIS question? Lots of other people ask PostGIS questions on here. I'm just a new starter who's asking for guidance. Thx
    – Ddee
    May 18, 2021 at 13:43
  • 1
    Does this answer your question? Using PostGIS to find the overall difference between two (large) polygon datasets
    – JGH
    May 18, 2021 at 13:56
  • 1
    Hi there, yes thank you that does help. But I don't understand it. I'm trying to not just copy solutions but understand what I'm doing so I can learn to do this properly. I have no idea what coalesce does in the solution you identified (thank you) or why an intersect is still needed because there's already a difference.
    – Ddee
    May 18, 2021 at 14:35
  • 4
    Your questions aren't about the ST_Difference, they're about the JOIN constraint, and structuring SQL queries to return the rows you need, and now about Coalesce(). If you want to understand spatial SQL, learning about SQL first is critical.
    – Vince
    May 18, 2021 at 14:39
  • 2
    @JGH ST_Intersection isn't used in this query, ST_Intersects is; the latter can leverage the spatial index, and isn't nearly as expensive as ST_Intersection.
    – Vince
    May 19, 2021 at 2:32

1 Answer 1

2

With the help of @JGH, here is the solution I have. This seems to work nicely. Thank you

SELECT a.field_i_need, ST_Multi(COALESCE(ST_Difference(a.geom, output.geom),a.geom)) AS geom
FROM buffers AS a
CROSS JOIN LATERAL (
  SELECT ST_Union(b.geom) AS geom
  FROM blanks AS b
  WHERE ST_Intersects(a.geom, b.geom) 
) AS output ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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