Calculating Distances Between Latitude and Longitude – T-SQL – Haversine

I’ve got lots of lat/long information for my Silverlight 2 application and I’m now tinkering with getting the elevation view working in my spare time. The problem is that the distances are very small and the haversine formulae I’ve located on the internet are all either miles or kilometers. Not going to work when your next position is only 50 feet away. So I wrote the simple SQL function below based directly on Seth Long’s version here.

Now, since I’m storing positional latitude, longitude and intersection information in my database, it’s simple to just calculate the numbers. The input and output values are of course immutable, so I added the columns to the appropriate table and will update them during the data entry phase of normal operations.

The haversine formula is also known as the ‘great circle formula’ that gives you a distance across the surface of a sphere based on an average curvature value for that sphere. The actual pear shape of the earth is a calculated max diff of only about 20 km so I’m not particularly worried about the error in two points that are literally spitting distance from each other.

By the way, the constant value 20,890,584 is the earth’s average radius in miles (3,956.55) multiplied by 5,280 feet per mile. The wikipedia article on haversine states that the average radius of the earth is 6,367.45 Km and I’m assuming it’s correct. I’m defaulting to feet since my Garmin GPS’ standard output for relative elevation is in feet.

@R is the radius of the earth.

This kind of begs for a discussion of whether it is more appropriate to calculate these values on the client and save the bandwidth or do the work on the server and pass it over. I chose the server because I’m still trying to figure out if I can market this application in the SAAS category. In that light, it seems better to keep as much data instantly available as I can.

Here’s the function:

Create Function [dbo].[fnGetDistance]
      @Lat1 Float(18), 
      @Long1 Float(18),
      @Lat2 Float(18),
      @Long2 Float(18),
      @ReturnType VarChar(10)

Returns Float(18)



      Declare @R Float(8);
      Declare @dLat Float(18);
      Declare @dLon Float(18);
      Declare @a Float(18);
      Declare @c Float(18);
      Declare @d Float(18);

      Set @R = 
            Case @ReturnType 
            When 'Miles' Then 3956.55 
            When 'Kilometers' Then 6367.45
            When 'Feet' Then 20890584
            When 'Meters' Then 6367450
            Else 20890584 -- Default feet (Garmin rel elev)

      Set @dLat = Radians(@lat2 - @lat1);

      Set @dLon = Radians(@long2 - @long1);

      Set @a = Sin(@dLat / 2) 
                 * Sin(@dLat / 2) 
                 + Cos(Radians(@lat1))
                 * Cos(Radians(@lat2)) 
                 * Sin(@dLon / 2) 
                 * Sin(@dLon / 2);
      Set @c = 2 * Asin(Min(Sqrt(@a)));

Set @d = @R * @c;
      Return @d;



  • That does work - however wouldn't it be better to calculate on the Web Server? Database calls are expensive, and Trig functions are very expensive - so using them on the DataBase server probably isn't the most efficient way of doing this calculation.

  • You're right and wrong. It is expensive to calculate anywhere really but I didn't want to change my input application. So what actually happens is that I add a new position to my system and tell it which other positions it is connected to. At that point I add a new intersection record to the database and include this function in that update procedure. Thus the new record includes the distance to each of the associated points. The calculation is only performed one time and the results stored. Unless there is an earthquake I think the positional distances should remain the same. ;)

Comments have been disabled for this content.