HeavenCore | IT Solutions Built on Open Source Software
View Thread
 Print Thread
Function to Calculate Distance Between a pair of Long and Latitudes
admin
Function to Calculate Distance Between a pair of Long and Latitudes


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:         <unkown>
-- Description:      <Returns distance between 2 sets of long/lat in miles>
--
-- Helpful Shizzle:   http://en.wikipedia.org/wiki/Great_circle_distance - master reference for implementation.
--               http://www.movable-type.co.uk/scripts/LatLong.html - JavaScript utility to compare results with.
--               http://mathforum.org/library/drmath/view/51879.html
--               http://www.tbs-sct.gc.ca/rpm-gbi/guides/Latlong_e.asp - definition and description of lat and long, with discussion on precision/accuracy.
-- =============================================
CREATE FUNCTION [dbo].[udf_CalculateDistanceKilometers]
    (
      @decLat1Deg DECIMAL(9, 6) , -- PointA latitude in degrees.
      @decLon1Deg DECIMAL(9, 6) , -- PointA longitude in degrees.
      @decLat2Deg DECIMAL(9, 6) , -- PointB latitude in degrees.
      @decLon2Deg DECIMAL(9, 6) , -- PointB longitude in degrees.
      @MilesOrKilometres CHAR(2)  -- 'mi' or 'km'
    )
RETURNS DECIMAL(9, 4)
AS
    BEGIN

        DECLARE @decEarthRadius DECIMAL(9, 4) ;

        SET @decEarthRadius = CASE @MilesOrKilometres
                                  WHEN 'mi' THEN 3959.871 -- Store the average great-circle radius of the Earth in Miles.
                                  WHEN 'km' THEN 6372.795 --Store the average great-circle radius of the Earth in Kilometers.
                                  ELSE 3959.871
                                END

        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 ) )) * @decEarthRadius
        RETURN @r

    END

GO

Jordon Pilling | Heavencore Administrator
 
Jump to Forum