SQL - distance between two coordinates
Haversine Formula in SQL (as approximate Miles between coordinates) - note the cast to decimal(8,5) and then back to real is required or you get math errors using trigonometric functions on high-precision reals.
-- =============================================
CREATE FUNCTION [dbo].[udf_DistanceByHaversine]
(
@FromLat decimal(8,5)
,@FromLon decimal(8,5)
,@ToLat decimal(8,5)
,@ToLon decimal(8,5)
-- can not use real or float; causes rounding errors leading to
-- exception at acos(1.000...01)
)
RETURNS real AS
BEGIN
RETURN
( 3960 * acos( cos( radians( @FromLat ) ) *
cos( radians( @ToLat ) ) * cos( radians( @ToLon ) - radians( @FromLon ) ) +
sin( radians( @FromLat ) ) * sin( radians( @ToLat ) ) ) )
END
GO
Created By: amos 4/2/2015 5:12:05 PM
Updated: 4/2/2015 5:12:51 PM
|
|