Search code examples
sqlsql-serversqlgeography

Filter list of points using list of Polygons

Given a list of points and a list of polygons. How do you return a list of points (subset of original list of points) that is in any of the polygons on the list

I've removed other columns in the sample tables to simplify things

Points Table:

| Longitude| Latitude  |
|----------|-----------|
| 7.07491  | 51.28725  |
| 3.674765 | 51.40205  |
| 6.049105 | 51.86624  |

LocationPolygons Table:

|     LineString       |
|----------------------|
| CURVEPOLYGON (COMPOUNDCURVE (CIRCULARSTRING (-122.20 47.45, -122.81 47.0, -122.942505 46.687131 ...  |
| MULTIPOLYGON (((-110.3086 24.2154, -110.30842 24.2185966, -110.3127...

If I had row from the LocationPolygons table I could do something like

DECLARE @homeLocation geography;  
SET @homeLocation = (select top 1 GEOGRAPHY::STGeomFromText(LineString, 4326)
FROM LocationPolygon where LocationPolygonId = '123abc')

select Id, Longitude, Latitude, @homeLocation.STContains(geography::Point(Latitude, Longitude, 4326)) 
as IsInLocation from Points PointId in (1, 2, 3,)

which would return what I want in a format like the below. However this is only true for just one location on the list

| Id | Longitude| Latitude  | IsInLocation |
|----|----------|-----------|--------------|
| 1  | 7.07491  | 51.28725  | 0            |
| 2  | 3.674765 | 51.40205  | 1            |
| 3  | 6.049105 | 51.86624  | 0            |

How do I handle the scenario with multiple rows of the LocationPolygon table?

I'd like to know

  1. if any of the points are in any of the locationPolygons?
  2. what specific location polygon they are in? or if they are in more than one polygon.

Question 2 is more of an extra. Can someone help?

Update #1 In response to @Ben-Thul answer.

Unfortunately I don't have access/permission to make changes to the original tables, I can request access but not certain it'll be given. So not certain I'll be able to add the columns or create the index. Although I can create temp tables in a stored proc, I might be able to use test your solution that way

I stumbled on an answer like the below, but slightly worried about performance implications of using a cross join.

WITH cte AS (     
      select *, (GEOGRAPHY::STGeomFromText(LineString, 4326)).STContains(geography::Point(Latitude, Longitude, 4326)) as IsInALocation  from 
(     
    select Longitude, Latitude from Points nolock  
) a cross join (
    select LineString FROM LocationPolygons nolock 
) b
)

select * from cte where IsInALocation = 1

Obviously, it's better to look at a query plan but is the solution I stumbled upon essentially the same as yours? Are there any potential issues that I missed. Apologies for this but my sql isn't very good.

Solution

  • Question 1 shouldn't be too bad. First, some set up:

    alter table dbo.Points add Point as (GEOGRAPHY::Point(Latitude, Longitude, 4326));
create spatial index IX_Point on dbo.Points (Point) with (online = on);

alter table dbo.LocationPolygon add Polygon as (GEOGRAPHY::STGeomFromText(LineString, 4326));
create spatial index IX_Polygon on dbo.LocationPolygon (Polygon) with (online = on);

    This will create a computed column on each of your tables that is of type geography that has a spatial index on it.

    From there, you should be able to do something like this:

    select pt.ID, 
   pt.Longitude,
   pt.Latitude,
   coalesce(pg.IsInLocation, 0) as IsInLocation
from Points as pt
outer apply (
   select top(1) 1 as IsInLocation 
   from dbo.LocationPolygon as pg
   where pg.Polygon.STContains(p.Point) = 1
) as pg;

    Here, you're selecting every row from the Points table and using outer apply to see if any polygons contain that point. If one does (it doesn't matter which one), that query will return a 1 in the result set and bubble that back up to the driving select.

    To extend this to Question 2, you can remove the top() from the outer apply and have it return either the IDs from the Polygon table or whatever you want. Note though that it'll return one row per polygon that contains the point, potentially changing the cardinality of your result set!