3

I have a layer with points and a layer of polygons. I can find all the polygons that contains at least one point inside itself by:

SELECT polygons.id
FROM polygons, points
WHERE ST_Intersects(polygons.geom, points.geom)

But what if I just want the opposite of this: select all polygons that contains no points?

SELECT polygons.id
FROM polygons, points
WHERE NOT ST_Intersects(polygons.geom, points.geom)

This last query actually returns all polygons since the condition NOT ST_Intersects(polygons.geom, points.geom) can be true for some points/polygons pairs (there is always a point somewhere which is not inside a polygon).

Improve this question
2
  • 1
    LEFT OUTER JOIN is required > gis.stackexchange.com/questions/313517/…
    – Mapperz ♦
    Oct 9 '20 at 14:02
  • thanks, but I have an issue using JOIN because I have to convert geometries for polygons from ways to polygons using ST_Polygonize, and this is not allowed (aggregate functions are not allowed in JOIN conditions), but that's another issue that could be fixed.
    – juminet
    Oct 9 '20 at 15:02
5

Solution 1 using LEFT OUTER JOIN:

SELECT polygons.id
FROM polygons
LEFT OUTER JOIN points 
ON st_within(points.geom, polygons.geom)
WHERE points.id IS NULL

Solution 2 using a subquery:

SELECT polygons.id
FROM polygons
WHERE polygons.id NOT IN (
SELECT polygons.id
FROM polygons, points
WHERE st_within(points.geom, polygons.geom)
)

Solution 3 using EXCEPT:

SELECT polygons.id
FROM polygons, points
EXCEPT
SELECT polygons.id
FROM polygons, points
WHERE st_within(points.geom, polygons.geom)
Improve this answer
2
  • 1
    Since I have another issue using JOINs, I choose the 2nd solution, thanks!
    – juminet
    Oct 9 '20 at 15:40
  • The cross join in solution 3's first query might be somewhat inefficient …
    – CL.
    Oct 12 '20 at 11:47
4

Decision 5

SELECT  a.id, (a.geom) geom FROM polygons a WHERE NOT EXISTS
(SELECT 1 FROM points b WHERE ST_Intersects(a.geom, b.geom))

Choose the one you like best...🙂

Improve this answer
5
  • 1
    +1 This may have a better query plan in certain circumstances than the (otherwise equal) LEFT OUTER JOIN exclusion and would be my choice.
    – geozelot
    Oct 9 '20 at 14:51
  • @geozelot, now that OP has made its choice, I can answer you, I appreciate your choice...🙂
    – Cyril Mikhalchenko
    Oct 9 '20 at 17:44
  • ;-) you could add a LIMIT 1 in the NOT EXISTS subquery, to potentially speed up the check.
    – geozelot
    Oct 21 '20 at 17:52
  • 1
    the LIMIT 1 may have am impact in certain circumstances, but generally PG will terminate the subquery when at least one row can be rerurned. It absolutely makes no sense on primary keys or unique columns, since then always only one row can ever be returned. Try it, but it may not be necessary.
    – geozelot
    Oct 21 '20 at 18:12
  • 1
    @geozelot, the control example on my "machine", does not show a significant reduction in the speed of execution of the request, although I understand that this is also affected by the work of the entire system, I think that while everything remains in place ... in any case, thank you and I will consider it in the future...
    – Cyril Mikhalchenko
    Oct 21 '20 at 18:12

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.