Sql中根据经纬度计算两点的距离

首先需要在SqlServer中创建一个计算距离的function，代码如下：

CREATE FUNCTION [dbo].[fnGetDistance] 

(@LngBegin REAL, @LatBegin REAL, @LngEnd REAL, @LatEnd 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 

       RETURN @Distance

END

调用刚才创建的function计算距离：

select StartTime,StartAddress,EndAddress,dbo.fnGetDistance(StartLng,StartLat,EndLng,EndLat)as Distance from Wind_Order

查询结果截图如下：