Storing and Querying GPS Coordinates Effectively

Question

I want to create a large database of GPS coordinates that can be queried by saying "Return all coordinates that are within 'n' metres of [this coordinate]".

I need it to be as efficient as possible so looping through all the coordinates in the database and calculating whether a coordinate is within 'n' metres wouldn't be a desired solution.

Is there an easier solution?

Thanks

Answer 1

I typically do this sort of query using lat/lon. Using spherical geometry, you can put a bounding box around a specific point. For example, say you have a point (X,Y) that you want all coordinates within 1 mile (conversion to meters I'll leave as an exercise for the reader). You can determine a bounding box of (X-1,Y-1),(X+1,Y+1). Then you query your points database using the BETWEEN operator (SELECT foo FROM bar WHERE LAT BETWEEN X-1 AND X+1 AND LON BETWEEN Y-1 AND Y+1). Then you do your detail distance calculation to "round the corners" of your bounding box.

The caveat is that longitude lines are closer together at the top of the sphere, so you'll get skewed results the further away you are from the equator. But it still serves to quickly filter down your results sets.

Google "Great Circle Distance" for the calculations.

EDIT: There are 0.167469 degrees of longitude per mile (it actually ranges from 0.167469 to 0.014564), and 0.014483 degrees of latitude per mile. So your bounding box is (lat - (miles * 0.014483), lon - (miles * 0.167469)), (lat + (miles * 0.014483), lon + (miles * 0.167469))

Answer 2

There is support in SQL Server 2008 for storing spatial data. I've never worked with it myself but I do know you can create queries of the type you want.