Calculate the distance between two latitude/longitude coordinates in SQL Server

Create an SQL function that calculates the distance between latitude/longitude coordinates in either miles or kilometers

Context

Calculating the distance between two places can be quite tricky, there are some good articles out there, however this page will only go into the code.

For more information on how it distances are calculated on a sphere, have a look at Haversine Formula on Wikipedia, it's a bit complex, hence us not wanting to duplicate the content.

We found some of this code online and have adapted it to an SQL Function, with the ability to ask for miles or kilometres.

This only calculates "as the crow flies", however it was tested as part of an app we'd built on an IPhone and the distances were spot on.

SQL Server

CREATE FUNCTION CoordinateDistanceMiles(@Latitude1 float,@Longitude1 float,@Latitude2 float,@Longitude2 float,@Distance NVARCHAR(10))RETURNS FLOATAS BEGIN-- CONSTANTSDECLARE @EarthRadiusInMiles FLOAT=(CASE @Distance WHEN 'Miles' THEN 3959 WHEN 'Kilometers' THEN 6371 ELSE 0 END);DECLARE @PI FLOAT=PI();DECLARE @lat1Radians FLOAT=@Latitude1 * @PI / 180DECLARE @long1Radians FLOAT=@Longitude1 * @PI / 180;DECLARE @lat2Radians FLOAT=@Latitude2 * @PI / 180;DECLARE @long2Radians FLOAT=@Longitude2 * @PI / 180;RETURN Acos(Cos(@lat1Radians)*Cos(@long1Radians)*Cos(@lat2Radians)*Cos(@long2Radians)+Cos(@lat1Radians)*Sin(@long1Radians)*Cos(@lat2Radians)*Sin(@long2Radians)+Sin(@lat1Radians)*Sin(@lat2Radians)) * @EarthRadiusInMiles;END