Location lookup query

Question

Location lookup query

fawwad

SSC Eights!

Points: 965
More actions
October 18, 2011 at 1:29 pm

#249673

Hi Experts,
I am working on one GPS based application,need your assistance here. my requirement is as under.
i have a table that has latitude, longitude against a place name. its a static table we have to use it as a reference table.
i will get information from GPS for my device. now i have to bring out location exact match or closest matched.
i have formula to calculate distance in case if its not a perfect match but how to choose closest match !!!
e.g
my point 67.23451 23.12341
location table
67.23421 23.12311
67.32121 23.45333
67.23452 23.12344
any solution for me...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

Since you're using SQL Server 2008 (or later), I would have a look at the spatial datatypes, more specifically geography. This datatype with its methods is created for this kind of stuff.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

fawwad SSC Eights! Points: 965 More actions · Answer 2

can you please put some light, what function i can use to make a lookup or find the closest match for lat, long to my reference table.

my production database exist on sql server 2005. I am doing a practice on sql 2008. so, spatial datatype isnt our solution.

okbangas SSChampion Points: 11773 More actions · Answer 3

Then the post is in wrong group, 2008 instead of 2005. There is no built in function for distance/coordinates in SQL Server 2005.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Here is a MySQL article about this very topic. http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

You can take a look at that and see if it helps. Otherwise You are probably going to increase your google-foo. There are tons of hits for "calculate closet gps location".

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

As I read that you have a number of fixed points and one variable point and you want to get the closest of the fixed points to the variable point. And you are doing this in SQL 2005. Agreed in 2008 the geography datatype would be perfect for this but that isn't an option. You have the formula and if I remember its pretty involved when I last looked at it. A good reference is at http://www.movable-type.co.uk/scripts/latlong.html.

I would probably go about this using a trick, given a table containing the fixed points that you want to find with an Id and two columns, one for latitude and one for longitude in highly precise decimal values. Basically we want to limit conversions.

DECLARE @ClosestId int

SELECT @ClosestId = Id, Distance = Formula to calculate distance

FROM dbo.TableWithFixedPoints

ORDER BY Distance DESC

When it finishes @ClosestId will contain the Id of the closest fixed point.

You would definitely want to performance test this and there might be additional logic that could limit the set it is working with, I could see this being a performance hog if it had to run through 10's of thousands of records each time. But you might be able to say that if just the latitude is over 2 apart you know that it would never use that one and this could be used to limit the working set. A lot depends on how many fixed points you have. If its a couple hundred this could work.

CEWII

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Sean Lange (10/18/2011)
Here is a MySQL article about this very topic. http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
You can take a look at that and see if it helps. Otherwise You are probably going to increase your google-foo. There are tons of hits for "calculate closet gps location".

And that is a nice article.. I'm gonna stash that away..