SQL实现经纬度匹配以及距离排序代码 1、SQL计算经纬度距离的自定义函数 CREATE FUNCTION dbo.GetDistance ( @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 @SqlStr='select *, dbo.GetDistance('+@latbegin+','+@lngbegin+',纬度字段,经度字段) as distance from [存储商家的数据表名] where (纬度字段<>'' and 经度字段<>'') and dbo.GetDistance('+@latbegin+','+@lngbegin+',纬度字段,经度字段)<'+Convert(nvarchar(10),@distance)+' '+@WhereStr+' order by distance asc'