SQL: detect invalid points outside an overlapping feature

Question

I am using the latest QGIS software. I am working on this script for the basic selection of invalid geometry points beyond the overlapping context, and how would I finish writing it in the WHERE clause using the postGIS commands?

SELECT *
FROM cougar_sample_points, NM_GMU

WHERE (NOT st_intersection(NM_GMU.geometry, cougar_sample_points.geometry))

The current script seems to do the opposite it says, it finds 154 points of 160, those which are within the boundary rather than the 6 outside of it. If I remove the not, I get like over 1000 records, like a crosstab query would.

What do you suggest?

I will want to delete those records after highlighting the selection.

I figured out how to do this in the attributes table just fine, but I want to learn the SQL.

Anyone know where to find good quality SQL documentation on these commands?

Answer 1

The problem is that if a point is within polygon A, it is also outside of polygon B. To overcome this, you can use a where exists clause to ignore points that are within any polygon.

Note that we don't need to compute the intersection (st_intersection) itself, we just need to know if they intersect or not (st_intersects)

SELECT *
FROM cougar_sample_points
WHERE NOT EXISTS (
   SELECT 1 
   FROM NM_GMU
   WHERE st_intersects(NM_GMU.geometry, cougar_sample_points.geometry)
)