Calculate Distance in M/KM using Latitude, Longitude


You will get the bunch of functions to calculate the distance between two points. Some functions will give you approximate accuracy some may not. I wrote the simple SQL function below which return the approx distance for given lat-long.

CREATE FUNCTION [dbo].[LatLonRadiusDistance]
(
      @lat1Degrees      decimal(15,12),
      @lon1Degrees      decimal(15,12),
      @lat2Degrees      decimal(15,12),
      @lon2Degrees      decimal(15,12),
      @MilesOrKMFlag    CHAR(1) = 'M'           --M:Miles | K:KiloMiles
)
RETURNS decimal(9,4)
AS
BEGIN

      DECLARE @earthSphereRadiusKilometers as decimal(10,6)
      DECLARE @kilometerConversionToMilesFactor as decimal(7,6)

      SELECT @earthSphereRadiusKilometers = 6366.707019
      SELECT @kilometerConversionToMilesFactor = .621371

      -- convert degrees to radians
      DECLARE @lat1Radians decimal(15,12)
      DECLARE @lon1Radians decimal(15,12)
      DECLARE @lat2Radians decimal(15,12)
      DECLARE @lon2Radians decimal(15,12)

      SELECT @lat1Radians = (@lat1Degrees / 180) * PI()
      SELECT @lon1Radians = (@lon1Degrees / 180) * PI()
      SELECT @lat2Radians = (@lat2Degrees / 180) * PI()
      SELECT @lon2Radians = (@lon2Degrees / 180) * PI()

      -- formula for distance from [lat1,lon1] to [lat2,lon2]
      RETURN
      ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2)
      + COS(@lat1Radians) * COS(@lat2Radians)
      * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2)))
      * (@earthSphereRadiusKilometers
      * CASE WHEN @MilesOrKMFlag = 'M' THEN @kilometerConversionToMilesFactor ELSE 1 END), 4)

END
GO
Advertisements

One thought on “Calculate Distance in M/KM using Latitude, Longitude

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s