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
4,741 Views
Last Modified: 2012-08-16
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
Comment
Question by:PatrickK_W
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 1

Author Comment

by:PatrickK_W
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

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

by:PatrickK_W
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

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question