sql server2008根据经纬度计算两点之间的距离
时间：2021-07-01 10:21:17
帮助过：4人阅读
通过经纬度计算两点之间的距离
create FUNCTION [dbo].
[fnGetDistanceNew]
--LatBegin 开始经度
--LngBegin 开始维度
--29.490295,106.486654,29.615467, 106.581515
(@LatBegin1 varchar(
128),
@LngBegin1 varchar(
128),
@location varchar(
128))
Returns real
AS
BEGIN
--转换location字段,防止字段太长.影响SQL美观
declare @LatBegin REAL
declare @LngBegin REAL
declare @LatEnd REAL
declare @LngEnd REAL
set @LatBegin=Convert(
real,
@LatBegin1)
set @LngBegin=Convert(
real,
@LngBegin1)
set @LatEnd=Convert(
real,
SUBSTRING(
@location,
0,
charindex(
‘&‘,
@location)))
set @LngEnd=Convert(
real,
SUBSTRING(
@location,
charindex(
‘&‘,
@location)
+1,
LEN(
@location)))
--距离(千米)
DECLARE @Distance REAL
DECLARE @EARTH_RADIUS REAL
SET @EARTH_RADIUS = 6371.004
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,
5)
RETURN @Distance
END
sql server2008根据经纬度计算两点之间的距离
标签：
