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