I'm looking to store GPS co-ordinates in an SQL Server 2005 for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL Server 2008 (& later) along with the geometry and geography field types. I'll need to support older versions of SQL Server as well (at least 2005).

What's the best way of storing GPS co-ordinates and calculating distances between 2 GPS locations in SQL Server 2005?


GPS co-ordinates are just latitude and longitude - if you have to support SQL 2005, then store them as numbers to your required precision.

To calculate the distance, you can implement the Haversine formula

| improve this answer | |
  • Looking at Decimal and Numeric on MSDN and examples of GPS co-ordimnates, decimal(18,12) would be suitable? – SteB Nov 21 '12 at 9:20
  • Numeric is the same as decimal. It depends how precise you need to be, but it would be a good starting point. – podiluska Nov 21 '12 at 9:22

If you store coordinates in decimal, you won't get a spatial-rtree index. My suggestion is simple: upgrade. If not, you'll be reimplmenting all of the spatial functionality as you need it. It'll be a slow, and it's going to be a lot of work.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.