计算地图上两坐标点之间的距离

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[Gps](@gps_1 varchar(80),@gps_2 varchar(80))
returns numeric(10,2)--设置返回值，记住是returns 而不是return
AS

BEGIN

    DECLARE @gps1_w float
    DECLARE @gps1_s float
    DECLARE @gps2_w float
    DECLARE @gps2_s float
    DECLARE @a1 float
    DECLARE @b1 float
    DECLARE @a float
    DECLARE @b float
    declare @s float
    DECLARE @return float
IF @gps_1='' and @gps_2=''
    BEGIN
       SET @return =0.0
    END
ELSE
    BEGIN
     
       SET @gps1_w =Convert(float,Substring(@gps_1,1,Patindex('%,%',@gps_1)-1))
       SET @gps1_s =Convert(float,Substring(@gps_1,Patindex('%,%',@gps_1)+1,20))
       SET @gps2_w =Convert(float,Substring(@gps_2,1,Patindex('%,%',@gps_2)-1))
       SET @gps2_s =Convert(float,Substring(@gps_2,Patindex('%,%',@gps_2)+1,20))
       set @a1=(@gps1_s*Pi()/180.00)
       set @b1=(@gps2_s*Pi()/180.00)
       set @a=@a1-@b1
       set @b=(@gps1_w-@gps2_w)*pi()/180.00
       set @s= 2 * Asin(Sqrt(Power(Sin(@a/ 2), 2) +Cos(@a1) * Cos(@b1) * Power(Sin(@b / 2), 2))) * 6378.137
       SET @return =@s
    END
return @return --返回结果

END

 

使用： select dbo.Gps(gps1,gps2)