【百度 map】数据库中知道两点坐标，查询出两点间的距离

方法一：

/*****************************************
百度坐标计算两点之间的距离


*********************************************/
CREATE FUNCTION [dbo].[f_GetDistance]
(
@lat1 DECIMAL(18,13),--小值
@lng1 DECIMAL(18,13),--大值
@lat2 DECIMAL(18,13),
@lng2 DECIMAL(18,13)
)
RETURNS decimal(18,2)
BEGIN
DECLARE @num decimal(18,13)
IF @lat1 is null or @lng1 is null
BEGIN
set @num=100000
END
ELSE
BEGIN
DECLARE  @pk DECIMAL(18,10) = 180 / 3.14169  
DECLARE @a1 DECIMAL(18,13);
DECLARE @a2 DECIMAL(18,13);
DECLARE @b1 DECIMAL(18,13);
DECLARE @b2 DECIMAL(18,13);
DECLARE @t1 DECIMAL(18,13);
DECLARE @t2 DECIMAL(18,13);
DECLARE @t3 DECIMAL(18,13);
DECLARE @tt DECIMAL(18,13);
DECLARE @tsum DECIMAL(18,13);

SET @a1 = @lat1 / @pk  
SET @a2 = @lng1 / @pk  
SET @b1 = @lat2 / @pk  
SET @b2 = @lng2 / @pk  
SET @t1 = cos(@a1) * cos(@a2) * cos(@b1) * cos(@b2)  
SET @t2 = cos(@a1) * sin(@a2) * cos(@b1) * sin(@b2)  
SET @t3 = sin(@a1) * sin(@b1) 
SET  @tsum = @t1 + @t2 + @t3
IF (@t1 + @t2 + @t3>1)
BEGIN
SET @tsum=1
END
IF (@t1 + @t2 + @t3<-1)
BEGIN
SET @tsum=-1
end

SET @tt = acos(@tsum)  
return 6366000 * @tt  
END
RETURN @num*1000;
END
GO

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

方法二：经测试有偏差

--查询两点间距离方法

CREATE FUNCTION F_GetDistance(@lng1 float,@lat1 float,@lng2 float,@lat2 float) RETURNS float
BEGIN
DECLARE @num float
set @num=2 * 6378.137 * ASIN(SQRT(POWER(SIN(PI() * (@lat1 - @lat2) / 360), 2) + COS(PI() * @lat1 / 180)* COS(@lat2* PI() / 180) * POWER(SIN(PI() * (@lng1 - @lng2) / 360), 2)));
RETURN @num*1000;
END

 

---------------注意参数顺序

create FUNCTION dbo.fnGetDistance
(
@LatBegin REAL
, @LngBegin REAL
, @LatEnd REAL
, @LngEnd REAL
)

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,10) / 10000

--将千米单位改为米
RETURN @Distance*1000;
 
END

 

----注意参数顺序

CREATE function fn_GoogleMap
(
@lat1 float,
@lng1 float,
@lat2 float,
@lng2 float
)
returns float
AS
begin
declare @radLng1 float
declare @radLng2 float
declare @a float
declare @b float
declare @s float
set @radLng1=@lng1 * PI() / 180.0
set @radLng2=@lng2 * PI() / 180.0
set @a=@radLng1-@radLng2
set @b = (@lat1 - @lat2) * PI() / 180.0
set @s= 2 * Asin(Sqrt(Power(Sin(@a / 2), 2) + Cos(@radLng1) * Cos(@radLng2) * Power(Sin(@b / 2), 2))) * 6378.137
set @s = Round(@s * 10000,4) / 10000;

--将千米单位改为米
return @s
end