Recently, I had a request to calculate the distance between 2 GPS coordinates. Now, the system I am working with is SQL Server 2005. SQL Server 2008 gives us the geography data type, which is great and has a lot of powerful features. But, if you’re using SQL Server 2000 or 2005, you won’t have access to any spatial data types.
Now, the table i was working with had the city name along with the Latitude and Longitude information. So, I went over to wikipedia and got the formula for calculating distance using great circles. I won’t go into the details of converting the formula to SQL, but the conversion is pretty straightforward.  Check out the script below:

/*
* Function fn_Distance_Calculator
* The function fn_Distance_Calculator calculates the distance between 2 GPS coordinates
* Uses the formula from Wikipedia:
* http://en.wikipedia.org/wiki/Great-circle_distance
*
* Rudy Rodarte 2004 Original version
* 2010-05-14 Converted the code to a function.
*/
ALTER function [dbo].[fn_Distance_Calculator] (
/* Declare Parameters */
@latitudeA float
/* The latitude of the first point */
,@longitudeA float
/* The longitude of the first point */
,@latitudeB float
/* The latitude of the second point */
,@longitudeB float
/* The longitude of the second point */
)
RETURNS FLOAT
AS
BEGIN
-- Declare the variable which will hold
-- the distance
DECLARE @Distance float

-- Declare the Radian versions of the coordinates
DECLARE @latA float
,@lonA float
,@latB float
,@lonB float

-- Convert the degrees to radians.
select @latA = radians(@latitudeA )
select @lonA = radians(@longitudeA)

select @latB = radians(@latitudeB)
select @lonB = radians(@longitudeB)


-- Execute the calculation
select @distance =
atan(
sqrt(
power(
( cos(@latB) * sin( @lonB-@lonA) )
,2) +
power(
( cos(@latA) * sin(@latB) )
-
( sin(@latA)*cos(@latB)* cos( @lonB-@lonA) )
,2)
)
/
(
( sin(@latA) * sin(@latB) )
+
( cos(@latA) * cos(@latB) * cos( @lonB-@lonA) )
)
) * 6372.795 -- Earth's circumfrence in miles

-- Return the number
RETURN @Distance

END


With the coordinates for two cities in hand, the function will give you the distance “as the crow flies.” The function will give you the great circle distance between two points, even if there may be a shorter, more direct route between the two points.