# Find polygons that has no points in PostGIS

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

• LEFT OUTER JOIN is required > gis.stackexchange.com/questions/313517/…
– Mapperz
Oct 9, 2020 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. Oct 9, 2020 at 15:02

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)
``````
• Since I have another issue using JOINs, I choose the 2nd solution, thanks! Oct 9, 2020 at 15:40
• The cross join in solution 3's first query might be somewhat inefficient …
– CL.
Oct 12, 2020 at 11:47

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

• +1 This may have a better query plan in certain circumstances than the (otherwise equal) `LEFT OUTER JOIN` exclusion and would be my choice. Oct 9, 2020 at 14:51
• @geozelot, now that OP has made its choice, I can answer you, I appreciate your choice...🙂 Oct 9, 2020 at 17:44
• ;-) you could add a `LIMIT 1` in the `NOT EXISTS` subquery, to potentially speed up the check. Oct 21, 2020 at 17:52
• 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. Oct 21, 2020 at 18:12
• @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... Oct 21, 2020 at 18:12