Solved

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

Posted on 2012-03-22
4,433 Views
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?
0
Question by:PatrickK_W
• 2
• 2

LVL 1

Author Comment

ID: 37752543
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
0

LVL 16

Accepted Solution

DcpKing earned 500 total points
ID: 37752588
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?
0

LVL 1

Author Comment

ID: 37752612
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)
0

LVL 16

Expert Comment

ID: 37752643
Maybe you found a bug in the documentation? :)
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.