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)
AS
Begin
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)
End
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;
End