How do I store spatial coordinates in SQL Server 2005

Question

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?

Answer 1

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

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

Answer 2

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.