SQL自定义函数--计算两个坐标点间距离

根据经纬度计算地球上两点之间的距离的Sql Server函数，单位是千米。 

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

      

    RETURN @Distance  

END  

调用：

select base_no,base_name,base_longitude,base_latitude,dbo.fnGetDistance(42.261659,123.826256,base_latitude,base_longitude) as distance from stock_baseinfo where property_right = 1 and dbo.fnGetDistance(42.261659,123.826256,base_latitude,base_longitude) < 500 order by distance