传递两个经纬度,计算中间的距离,单位km
USE [Witbank_Times]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetDistance] Script Date: 2016/9/9 15:23:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <gaoxing>
-- Create date: <2015-09-22>
-- Description: <计算两个经纬度之间的距离>
-- =============================================
--创建经纬度距离计算函数
ALTER FUNCTION [dbo].[fnGetDistance]
--LatBegin 开始经度
--LngBegin 开始维度
(@LatBegin float, @LngBegin float, @LatEnd float, @LngEnd float) RETURNS FLOAT
AS
BEGIN
--距离(千米)
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 Round(@Distance,2)
END