How do I find points that are NOT within a list of polygons using MS SQL?

Question

I have a table of points, and and a table of polygons. I have managed to build queries (MS SQL) to get the containing polygon for a given point, as well as listing all the points within a specified polygon, but I cannot work out how to find all points that are not within any polygon.

The following query lists all points with the containing polygon. My initial thought was to just look points that are not contained (i.e. STContains() <> 1), but this returns all points too, seem there are always polygons that do not contain a given point.

    SELECT points.name, polygons.name
    FROM points CROSS JOIN
    polygons
    WHERE  (polygons.shape.STContains(points.shape) = 1) 

How do I find those points that are not within any polygons?

Answer 1

At least in PostgreSQL it is much faster to do a left or right join on st_intersects instead and use

"where id is null"

to find those that "don't have a friend"

edit:

Ok, here comes an example tested in SQL Server 2014 express:

There is 2 polygons and 4 points. 2 of them is inside the one of the polygons and oine point on the border of the other polygon. By doing a right join we get back all points, but only those inside a polygon gets a polygon id. So we filter away all points that gets a polygon id and left is the points outside all polygons. In this case only one, point(0 3)

This is, at least in PostGIS a fast way of doing it because the spatial index is used on the intersects operation.

with polygons as
(
    select 'p1' id , geometry::STGeomFromText('polygon ((1 1, 1 5, 5 5, 1 1))', 0) poly
    union all
    select 'p2' id , geometry::STGeomFromText('polygon ((0 0, 1 1, 0 1, 0 0))', 0) poly

)
,
points as
(
    select 1 id, geometry::STGeomFromText('point (0 0)', 0) P
    union all
    select 2 id, geometry::STGeomFromText('point (3 4)', 0) P
    union all
    select 3 id, geometry::STGeomFromText('point (2 3)', 0) P
    union all
    select 4 id, geometry::STGeomFromText('point (0 3)', 0) P
)
select points.id, points.p.STAsText() 
from polygons 
right join 
points 
on polygons.poly.STIntersects(points.p) = 1
where polygons.id is NULL;

Answer 2

My eventual solution after much trial and error was the following simplified (hopefully not oversimplified!) code. This lists all points from allpoints that do not have matches in containedpoints (i.e. those points that are in at least one polygon).

    SELECT allpoints.id, allpoints.name 
    FROM
    (
        SELECT points.id, points.name
        FROM points CROSS JOIN 
            polygons
        WHERE (polygons.geom.STContains(points.geom) = 1) 
    ) AS containedpoints RIGHT OUTER JOIN
    ( 
        SELECT points.id, points.name
        FROM points 
    ) AS allpoints ON containedpoints.id=allpoints.id
    WHERE containedpoints.id IS NULL

Answer 3

Assuming you can generate an ID for the points you're trying to isolate, you can (as suggested) use a sub-query to generate a list of points that DO intersect the polygons, then use the ID of those points in the where clause NOT IN statement:

select 
pointID
from points
where pointID not in 

    (

    select distinct
    pointID
    from points
    join polygons
    on points.geom.STIntersects(polygons.geom) = 1
    where polygon.id = '941' -- if you have a particular ID from the polygons you're looking to isolate, but optional if your entire polygon dataset is to be analyzed

    )