CREATE FUNCTION [dbo].[CalculateDistance]
(@Longitude1 DECIMAL(8,5),
@Latitude1 DECIMAL(8,5),
@Longitude2 DECIMAL(8,5),
@Latitude2 DECIMAL(8,5))
RETURNS FLOAT
AS
BEGIN
DECLARE @Temp FLOAT
SET @Temp = SIN(@Latitude1/57.2957795130823) * SIN(@Latitude2/57.2957795130823) +
COS(@Latitude1/57.2957795130823) * COS(@Latitude2/57.2957795130823) * COS(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823)
IF @Temp > 1
SET @Temp = 1
ELSE IF @Temp < -1
SET @Temp = -1
RETURN (3958.75586574 * ACOS(@Temp) )
END
from the blog I mentioned.
>>I think you can replace 4*((4*atn(1/5))-(atn(1/239))) by PI()
>>
>>And if you store the Latitude and Longitude in radians, then it becomes
>>
>>SET XAxis = cos([TABLENAME].Latitude) * cos([TABLENAME].Longitude)
>>
>>
>>likewise
>>SET YAxis = cos([TABLENAME].Latitude) * sin([TABLENAME].Longitude)
>>SET ZAxis = sin([TABLENAME].Latitude)
>>
>>
>
>Thanks
>
>I just want to verify that part. My code to replace those values are as follow:
>
>
>UPDATE test SET XAxis = (cos(((PI())/180)*Latitude)*cos(((PI())/180)*Longitude))
>UPDATE test SET XAxis = (cos(((PI())/180)*Latitude)*sin(((PI())/180)*Longitude))
>UPDATE test SET ZAxis = (sin(((PI())/180)*Latitude))
>
>
>Then, applying the related code would give the image attached:
>
>
>declare @CenterLat float=47.7795
>declare @CenterLon float=-65.7191
>declare @EarthRadius float=6371
>declare @SearchDistance float=10000
>
>declare @CntXAxis float
>declare @CntYAxis float
>declare @CntZAxis float
>
>set @CntXAxis = cos(radians(@CenterLat)) * cos(radians(@CenterLon))
>set @CntYAxis = cos(radians(@CenterLat)) * sin(radians(@CenterLon))
>set @CntZAxis = sin(radians(@CenterLat))
>
>select *, ProxDistance = @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + ZAxis*@CntZAxis)
>from test
>where @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + ZAxis*@CntZAxis) <= @SearchDistance
>order by ProxDistance ASC
>
>
>The code works, but the distance calculated is enormous. There is only about 12 to 16 km between those two coordinates.
If it's not broken, fix it until it is.
My Blog