Maps Calculations SQL

/****** Object:  UserDefinedFunction [dbo].[Map_CalculateDistance]    Script Date: 06/15/2011 13:13:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE FUNCTION [dbo].[Map_CalculateDistance]
(
    @Latitude1 float,
    @Longitude1 float,
    @Latitude2 float,
    @Longitude2 float,
    @Units NVARCHAR(10)
)
RETURNS float
AS
BEGIN
    SET @Latitude1 = RADIANS(@Latitude1)
    SET @Longitude1 = RADIANS(@Longitude1)
    SET @Latitude2 = RADIANS(@Latitude2)
    SET @Longitude2 = RADIANS(@Longitude2)
    DECLARE @R float, @dlon float, @dlat float, @a float, @c float

    SELECT @R = CASE
        WHEN UPPER(LEFT(@Units, 1)) = 'M' THEN 3956.3
        WHEN UPPER(LEFT(@Units, 1)) = 'N' THEN 3437.7
        WHEN UPPER(LEFT(@Units, 1)) = 'K' THEN 6367.0
    END

    SET @dlon = @Longitude2 - @Longitude1
    SET @dlat = @Latitude2 - @Latitude1
    SET @a = POWER(SIN(@dlat / 2.0), 2.0) +
        COS(@Latitude1) * COS(@Latitude2) * POWER(SIN(@dlon / 2.0), 2.0)
    SET @c = 2.0 * ATN2(SQRT(@a), SQRT(1.0 - @a))

    RETURN (@R * @c)

END


GO

/****** Object:  UserDefinedFunction [dbo].[Map_DistanceBetweenPoints]    Script Date: 06/15/2011 13:13:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE function [dbo].[Map_DistanceBetweenPoints]
(
    @Latitude1 float,
    @Longitude1 float,
    @Latitude2 float,
    @Longitude2 float,
    @Units NVARCHAR(10)
)
RETURNS float
AS
BEGIN
    SET @Latitude1 = RADIANS(@Latitude1)
    SET @Longitude1 = RADIANS(@Longitude1)
    SET @Latitude2 = RADIANS(@Latitude2)
    SET @Longitude2 = RADIANS(@Longitude2)
    DECLARE @R float, @dlon float, @dlat float, @a float, @c float

    SELECT @R = CASE
        WHEN UPPER(LEFT(@Units, 1)) = 'M' THEN 3956.3
        WHEN UPPER(LEFT(@Units, 1)) = 'N' THEN 3437.7
        WHEN UPPER(LEFT(@Units, 1)) = 'K' THEN 6367.0
    END

    SET @dlon = @Longitude2 - @Longitude1
    SET @dlat = @Latitude2 - @Latitude1
    SET @a = POWER(SIN(@dlat / 2.0), 2.0) +
        COS(@Latitude1) * COS(@Latitude2) * POWER(SIN(@dlon / 2.0), 2.0)
    SET @c = 2.0 * ATN2(SQRT(@a), SQRT(1.0 - @a))

    RETURN (@R * @c)

END

GO

/****** Object:  UserDefinedFunction [dbo].[Map_LatFromDistanceBearing]    Script Date: 06/15/2011 13:13:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE function [dbo].[Map_LatFromDistanceBearing](@lat1 float, @lon1 float, @bearing float, @distance float, @type nvarchar(10))
returns float
AS
BEGIN
declare @d float
declare @lat2 float
declare @lon2 float
declare @brng float
declare @R float

set @d = @distance
set @brng= @bearing

set @lat1 = @lat1 * pi() /180
set @lon1 = @lon1 * pi() / 180
set @brng = @brng * pi() / 180

set @R = 6371.0
if LEFT(UPPER(@type),1)='M'
    set @R = 3956.0

set @d = @d/@R

set @lat2 = @lat1 + @d*cos(@brng)

declare @dPhi float
declare @q float
set @dPhi = log(tan(@lat2/2+pi()/4)/tan(@lat1/2+pi()/4))
if @dPhi != 0
    set @q = (@lat2-@lat1)/@dPhi

--IS FINITE??
if (isnumeric(@q)=0)
    set @q = cos(@lat1)
declare @dLon float
set @dLon = @d*sin(@brng)/@q

--BEYOND POLE??
if (abs(@lat2) > pi()/2)
    if @lat2 > 0
        set @lat2 = pi() - @lat2
    else
        set @lat2 = -1*pi() - @lat2
--set @lon2 = (@lon1+@dLon+pi())%(2*pi())-pi()

declare @a float
declare @b float
set @a = (@lon1+@dLon+pi())
set @b = 2*pi()
declare @result int
set @result = @a/@b
set @lon2 = @a-cast(@result as float)*@b
set @lon2 = @lon2-pi()
set @lat2 = 180 *@lat2 / pi()
set @lon2 = 180 *@lon2 / pi()
return @lat2
END

GO

/****** Object:  UserDefinedFunction [dbo].[Map_LonFromDistanceBearing]    Script Date: 06/15/2011 13:13:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE function [dbo].[Map_LonFromDistanceBearing](@lat1 float, @lon1 float, @bearing float, @distance float, @type nvarchar(10))
returns float
AS
BEGIN
declare @d float
declare @lat2 float
declare @lon2 float
declare @brng float
declare @R float

set @d = @distance
set @brng= @bearing

set @lat1 = @lat1 * pi() /180
set @lon1 = @lon1 * pi() / 180
set @brng = @brng * pi() / 180

set @R = 6371.0
if LEFT(UPPER(@type),1)='M'
    set @R = 3956.0

set @d = @d/@R

set @lat2 = @lat1 + @d*cos(@brng)

declare @dPhi float
declare @q float
set @dPhi = log(tan(@lat2/2+pi()/4)/tan(@lat1/2+pi()/4))
if @dPhi != 0
    set @q = (@lat2-@lat1)/@dPhi

--IS FINITE??
if (isnumeric(@q)=0)
    set @q = cos(@lat1)
declare @dLon float
set @dLon = @d*sin(@brng)/@q

--BEYOND POLE??
if (abs(@lat2) > pi()/2)
    if @lat2 > 0
        set @lat2 = pi() - @lat2
    else
        set @lat2 = -1*pi() - @lat2
--set @lon2 = (@lon1+@dLon+pi())%(2*pi())-pi()

declare @a float
declare @b float
set @a = (@lon1+@dLon+pi())
set @b = 2*pi()
declare @result int
set @result = @a/@b
set @lon2 = @a-cast(@result as float)*@b
set @lon2 = @lon2-pi()
set @lat2 = 180 *@lat2 / pi()
set @lon2 = 180 *@lon2 / pi()
return @lon2
END

GO