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