SQL Server 2008 spatial data - fastest way to find if a given lat/long point inside an area & help with polygons

Hi. We’re playing with spatial data in SQL Server 2008 with a view to building an interface.

Basically we have a number of records in SQL Server. Each record has 2 lat long coordinates to denote a 500mx500m grid. This overlays the UK. This data has been provided to us and we don’t need to verify it.
Ie each records had min_lat, max_lat, min_long, max_lond, some_data columns

We want to pass a lat long point to sql server (ie the user clicks on a google map) and find a row in sql server where the point is inside that rows grid and  then display the relevant data for that row.

For example the following 4 lat/longs will build a grid around ben nevis:
TL - 56.798937031503 -5.00620317132339
TR - 56.798937031503 -4.99838730303897
BR- 56.7942545768597 -4.99838730303897
BL - 56.7942545768597 -5.00620317132339
 
And if we select a point – say 56.796268 -5.0034890, we want to know that it is inside the grid (which it is). Easy enough.
Is it actually faster to just do a floating point comparison on the lat/long of the passed point – ie passed lat>= min lat and passed lat <=max lat and passed long >=min long and passed long <= max long

Or should we use the SQL Server spatial geography type, build a polygon for each row and see if the point is inside the area of the row?
We where initially given the centre point of each grid and where using a proximity search to return the closest centre point to where you clicked. This worked really quickly – having fabricated a few duplicate entried to swell the data to over 3 million records, by using this code you could get the records within 1000m of the point passed in under 1 second

declare @point geography
set @point = geography::Point(53.2108154296875,-3.05141019821167, 4326)

SELECT  postcode, latlong.STDistance(@point)
FROM postcodes
where latlong.STDistance(@point)<=1000
order by latlong.STDistance(@point)

we where looking at loading a polygon for each row of data (this data is NEVER going to change, so it’s not a problem to store the polygon). Is this correct for the above lat/longs – ie a polygone with 5 points (the 5th is the start position) adding the points as below in lat long

declare @area as geography
set @area = geography::STGeomFromText('POLYGON((
56.798937031503 -5.00620317132339,
56.798937031503  -4.99838730303897,
56.7942545768597 -4.99838730303897,
56.7942545768597 -5.00620317132339,
56.798937031503 -5.00620317132339
))', 4326)

This happily accepts the polygon, but if you try to see if the point 56.796268 -4.99920317132339 is inside the polygon it returns 0 with the below

declare @point geography
set @point =geography::Point(56.796268, -5.0034890,4326)


declare @area as geography
set @area = geography::STGeomFromText('POLYGON((
56.798937031503 -5.00620317132339,
56.798937031503  -4.99838730303897,
56.7942545768597 -4.99838730303897,
56.7942545768597 -5.00620317132339,
56.798937031503 -5.00620317132339
))', 4326)

select @point.STIntersects(@area)
but if you create the point with long, then lat (ie the wrong way around) it gives 1 to show it’s inside the polygon

declare @point geography
set @point =geography::Point(-5.0034890, 56.796268, 4326)


declare @area as geography
set @area = geography::STGeomFromText('POLYGON((
56.798937031503 -5.00620317132339,
56.798937031503  -4.99838730303897,
56.7942545768597 -4.99838730303897,
56.7942545768597 -5.00620317132339,
56.798937031503 -5.00620317132339
))', 4326)

select @point.STIntersects(@area)

so, I assumed that the polygone needed to have th e lat/long values reversed as I knew the point was created correctly in lat long order. but if you try to create a polygon with

declare @area as geography
set @area = geography::STGeomFromText('POLYGON((
-5.00620317132339 56.798937031503,
-4.99838730303897 56.798937031503,
-4.99838730303897 56.7942545768597,
-5.00620317132339 56.7942545768597,
-5.00620317132339 56.798937031503
))', 4326)

It says this is not a bvalid geography instance.

So what am I doing wrong here.

Even with a spacial index, would trying to identify if a point is within a polygon in any of 3million plus records be any faster than just checking the float values against each other fot the lat and long values?
LVL 1
PatrickK_WAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatrickK_WAuthor Commented:
with regard tothe polygone - i was thinking that if i've change the lat longs around it might be intersetings, so change teh point order to top left,  bottom left, bottom right, top right, top left

this accepts the polygon withte reversed lat long as below. a point created correclty with lat long now shows as being iside or outside the area correctly with the below. the spatial result also looks correct on teh area now with lat going up and long going accross.... is this actually how it's supposed to be done?

declare @point geography
set @point =geography::Point(-5.0034890, 56.796268, 4326)


declare @area as geography
set @area = geography::STGeomFromText('POLYGON((
-5.00620317132339 56.798937031503,
-4.99838730303897 56.798937031503,
-4.99838730303897 56.7942545768597,
-5.00620317132339 56.7942545768597,
-5.00620317132339 56.798937031503
))', 4326)

select @point.STIntersects(@area)
select @area
DcpKingCommented:
Assuming you have indexed the lat and long columns then doing the comparisons will probably be quicker - atomic ops like < and > are usually faster than working with a structure created on top (like spatial objects).

You could speed things up more, maybe, by deciding the desired resolution before issuing the queries. For example, do yoo really need to ask the db if your point is (-5.0034890, 56.796268)? I suppose you do if you want to know that you're right at the tippy-top of Ben Nevis, as any deviation will probably indicate a very rapid fall-off in altitude :).  Normally, though, something like (-5.0034, 56.7962) might get you results faster, by getting you the 100 metre square rather than the 1 metre square you're in.

BTW, you say that STIntersects fails for your area using (lat,long), but gets it when using (long,lat). If you move the values to fall outside your polygon do the returned values swap?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatrickK_WAuthor Commented:
BTW, you say that STIntersects fails for your area using (lat,long), but gets it when using (long,lat). If you move the values to fall outside your polygon do the returned values swap?


yes this is exaclty right, see below: point returns 0, pointbakwards returns 1.... 1 being correct. i've hit loads of problems with the polygons.... not really sure what i'm going. most examples i have seen to seem to specify long then lat for each coordinate.


declare @point geography
set @point =geography::Point(56.796268, -5.0034890,4326)

declare @pointbackwards geography
set @pointbackwards =geography::Point(-5.0034890, 56.796268,4326)

declare @area as geography
set @area = geography::STGeomFromText('POLYGON((
56.798937031503 -5.00620317132339,
56.798937031503  -4.99838730303897,
56.7942545768597 -4.99838730303897,
56.7942545768597 -5.00620317132339,
56.798937031503 -5.00620317132339
))', 4326)

select @point.STIntersects(@area)
select @pointbackwards.STIntersects(@area)
DcpKingCommented:
Maybe you found a bug in the documentation? :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.