
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 9: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



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 (nonspatial) 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



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

