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,181 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
  • 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Update foreign key reference after insert 9 34
sql server query? 6 26
Database Containment - Benefits 6 25
Test a query 23 15
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now