coordinate-distance.md

Calc distance

SQL Server function to calc distance between two coordinates

create FUNCTION [dbo].[fncCalcula_Distancia_Coordenada](
                                                   @decLat1Deg DECIMAL(9,6), -- First latitude in degrees.
                                                   @decLon1Deg DECIMAL(9,6), -- First longitude in degrees.
                                                   @decLat2Deg DECIMAL(9,6), -- Second latitude in degrees.
                                                   @decLon2Deg DECIMAL(9,6)  -- Second longitude in degrees.
) RETURNS DECIMAL(9,4) AS
BEGIN
DECLARE @decEarthRadiusKM DECIMAL(9,4); SET @decEarthRadiusKM = 6372.795 -- Store the average great-circle radius of the Earth in Kilometers.
DECLARE @decLat1Rad DECIMAL(9,6)       -- Store first latitude as radians.
DECLARE @decLon1Rad DECIMAL(9,6)       -- Store first longitude as radians.
DECLARE @decLat2Rad DECIMAL(9,6)       -- Store second latitude as radians.
DECLARE @decLon2Rad DECIMAL(9,6)       -- Store second longitude as radians.
DECLARE @decLonDiffRad DECIMAL(9,6)    -- Store the difference betwen the two longitude (radians) values.
DECLARE @decCosLonDiffRad DECIMAL(9,6) -- Store the cosine of the difference betwen the two longitude (radians) values in radians.
DECLARE @decCosLat1Rad DECIMAL(9,6)    -- Store the cosine of the first latitude (radians) values in radians.
DECLARE @decCosLat2Rad DECIMAL(9,6)    -- Store the cosine of the second latitude (radians) values in radians.
DECLARE @decSinLat1Rad DECIMAL(9,6)    -- Store the sine of the first latitude (radians) values in radians.
DECLARE @decSinLat2Rad DECIMAL(9,6)    -- Store the sine of the second latitude (radians) values in radians.
DECLARE @r DECIMAL(9,4) -- Result.
   SELECT
      @decLat1Rad = RADIANS(@decLat1Deg), -- Convert latitudes and longitudes from degrees to radians.
      @decLon1Rad = RADIANS(@decLon1Deg),
      @decLat2Rad = RADIANS(@decLat2Deg),
      @decLon2Rad = RADIANS(@decLon2Deg)
   SELECT
      @decLonDiffRad = @decLon2Rad - @decLon1Rad
   SELECT
      @decCosLonDiffRad = COS(@decLonDiffRad), -- only compute trig functions once for those values that are used more than once.
      @decCosLat1Rad = COS(@decLat1Rad),
      @decCosLat2Rad = COS(@decLat2Rad),
      @decSinLat1Rad = SIN(@decLat1Rad),
      @decSinLat2Rad = SIN(@decLat2Rad)
   SET @r = ATN2(
                 SQRT( POWER(@decCosLat2Rad * SIN(@decLonDiffRad), 2) + POWER((@decCosLat1Rad * @decSinLat2Rad) - (@decSinLat1Rad * @decCosLat2Rad * @decCosLonDiffRad), 2) ) ,
                 ((@decSinLat1Rad * @decSinLat2Rad) + (@decCosLat1Rad * @decCosLat2Rad * @decCosLonDiffRad))
                ) * @decEarthRadiusKM
   RETURN @r
END
GO