Point in Polygon

Dec 3, 2010 at 8:47 PM

Hi, I would like to do a simple point in polygon lookup.  I have a table of points, and a table of polygons.  I would like to know which polygon the points fall in.

I have done this before in PostGIS, but cannot figure out which function does this with Sql Server 2008 R2.  

Any help appreciated.

Thanks,

mike

 

Developer
Dec 3, 2010 at 8:57 PM

Hi Mike -

It's STIntersects().  Here is a trivial example:

DECLARE @point GEOMETRY = 'Point(1 1)'
DECLARE @poly GEOMETRY = 'Polygon((0 0, 2 0, 2 2, 0 2, 0 0))'

SELECT NULL WHERE @point.STIntersects(@poly)=1

Note that the boolean expression STIntersects()=1 will not use a spatial index if written as 1=STIntersects().

Please let me know if you have further questions.

Thanks,

- Ed

Dec 5, 2010 at 2:37 PM

Ed, Thanks for your prompt reply.   I took your example and made it into

SELECT a.ID, b.ID

From Points a, Poly b

WHERE  a.County = b.County

and a.geom.STIntersects(b.geom)=1

 

My question is now one of performance.  I am running this on a fairly serious server (8 CPUs, 64 GB of ram (or more, don't remember the specs exactly), and running it on a single point takes 2 seconds.  I would like to be able to do this for millions of points.  Both the point layer and poly layer (which has 170,000 features) have a spatial index.  I have also added a WHERE clause on county (non-spatial) in the hopes of limiting the spatial search.

Is there anything else I can do to increase the performance of this search?

Thanks again for your help,

Mike

Jan 7, 2011 at 9:01 PM

Mike,

Just a thought: do you also have indexes on the COUNTY fields in the two tables?  Depending upon how the query optimizer chooses to perform this query, this could make a difference.

-- TAD