通过SQL语句计算两地的经纬度距离

一、SQL案例

select se.EnterpriseId,se.Code,se.Type,se.Name,se.UnifiedCode,se.Latitude,se.Longitude
	,se.Address,se.RedBlackList,se.StatusOfBiz,se.IsQualifyM,se.IsQualify4S,se.IsChainStore
	,se.IsMainStore,se.MainStoreName,se.ContactsPhone,se.BizCategory,se.GoodCount
	,se.EnterpriseRegistStatus
	#通过经纬度计算距离
    ,ACOS(
         SIN( #{enterprise.latitude} * PI() / 180 ) * SIN(
         cast(
              if((se.Latitude is null or ltrim(rtrim(se.Latitude)) = ''), 0.0, se.Latitude) 
              AS DECIMAL ( 12, 8 )) * PI() / 180
             ) + COS(#{enterprise.latitude}  * PI() / 180 ) * COS(
                     cast(
                     	if((se.Latitude is null or ltrim(rtrim(se.Latitude)) = ''), 0.0, 
                     		se.Latitude) AS DECIMAL ( 12, 8 )) * PI() / 180
                      	) * COS(
                   	 #{enterprise.longitude}  * PI() / 180 - cast(
                   	if((se.Longitude is null or ltrim(rtrim(se.Longitude)) = ''), 0.0, 
                   		se.Longitude) AS DECIMAL ( 12, 8 )) * PI() / 180
                   )
                ) * 6378.14 * 1000 as Distance
	from sys_enterprise as se
	where se.IsEnabled = 1
	and se.IsAbolish = 0
	and se.Type = 0
	and se.EnterpriseId = #{enterprise.enterpriseId}

二、SQL参数说明

#通过经纬度计算距离
    ,ACOS(
         SIN( #{enterprise.latitude} * PI() / 180 ) * SIN(
         cast(
              if((se.Latitude is null or ltrim(rtrim(se.Latitude)) = ''), 0.0, se.Latitude) 
              AS DECIMAL ( 12, 8 )) * PI() / 180
             ) + COS(#{enterprise.latitude}  * PI() / 180 ) * COS(
                     cast(
                     	if((se.Latitude is null or ltrim(rtrim(se.Latitude)) = ''), 0.0, 
                     		se.Latitude) AS DECIMAL ( 12, 8 )) * PI() / 180
                      	) * COS(
                   	 #{enterprise.longitude}  * PI() / 180 - cast(
                   	if((se.Longitude is null or ltrim(rtrim(se.Longitude)) = ''), 0.0, 
                   		se.Longitude) AS DECIMAL ( 12, 8 )) * PI() / 180
                   )
                ) * 6378.14 * 1000 as Distance

这一块代码用来计算两地的经纬度距离！
参数说明：
(1)：#{enterprise.latitude} = 纬度
(2)：#{enterprise.longitude} =经度

成功案例演示(对比腾讯地图)：

代码案例

#查询当前定位附近维修企业距离(当前定位经纬度：22.53332,113.93041)
select se.Code,se.Name,se.Latitude as '企业纬度',se.Longitude as '企业经度',ACOS(
         SIN(22.53332 * PI() / 180 ) * SIN(
         cast(
              if((se.Latitude is null or ltrim(rtrim(se.Latitude)) = ''), 0.0, se.Latitude) 
              AS DECIMAL ( 12, 8 )) * PI() / 180
             ) + COS(22.53332  * PI() / 180 ) * COS(
                     cast(
                     	if((se.Latitude is null or ltrim(rtrim(se.Latitude)) = ''), 0.0, 
                     		se.Latitude) AS DECIMAL ( 12, 8 )) * PI() / 180
                      	) * COS(
                   	 113.93041  * PI() / 180 - cast(
                   	if((se.Longitude is null or ltrim(rtrim(se.Longitude)) = ''), 0.0, 
                   		se.Longitude) AS DECIMAL ( 12, 8 )) * PI() / 180
                   )
                ) * 6378.14 * 1000 as Distance
	
from sys_enterprise as se
order by Distance asc

执行结果：

Distance就是计算经纬度后的距离，返回给后台后可以自己保留二位小数！
取当前定位和数据库第一条数据测试(腾讯地图接口返回)

测试结果，基本一致！