SQL SERVER formula for calculating distances based on map latitude and longitude
Copy Code code as follows:
Go
--Create latitude and longitude distance calculation function
createfunction [dbo]. [Fngetdistance]
--latbegin begins longitude.
--lngbegin Start Dimension
(@LatBegin Real, @LngBegin Real, @LatEnd Real, @LngEnd Real)
Returnsfloat
As
BEGIN
--distance (km)
DECLARE @Distance Real
DECLARE @EARTH_RADIUS Real
SET @EARTH_RADIUS = 6378.137
DECLARE @RadLatBegin Real,
@RadLatEnd Real,
@RadLatDiff Real,
@RadLngDiff Real
SET @RadLatBegin = @LatBegin *pi ()/180.0
SET @RadLatEnd = @LatEnd *pi ()/180.0
SET @RadLatDiff = @RadLatBegin-@RadLatEnd
SET @RadLngDiff = @LngBegin *pi ()/180.0-@LngEnd *pi ()/180.0
SET @Distance = 2 *asin (
SQRT (
Power (SIN (@RadLatDiff/2), 2) +cos (@RadLatBegin) *cos (@RadLatEnd)
*power (SIN (@RadLngDiff/2), 2)
)
)
SET @Distance = @Distance * @EARTH_RADIUS
--set @Distance = Round (@Distance * 10000)/10000
Return @Distance
End
@Distance units are: km