mysql自带函数st_distance查询两个经纬度点的距离

1、mysql的写法

SELECT
   s.id,s.name,s.lng,s.lat, 
   (st_distance (point (lng, lat),point(115.067,34.76) ) / 0.0111) AS distance
FROM road_servicearea s
HAVING distance<30
ORDER BY distance

2、mybatis的写法

  SELECT uuid, uuid storeId, storeName, storeType, picture storePicture, phone, geographicalPosition address,
concat(DATE_FORMAT(openTime, '%H:%i'),'-',DATE_FORMAT(closeTime, '%H:%i')) businessPeriod, labelId, null,
(st_distance(point(longitude, latitude), point(#{longitude}, #{latitude}))*111195 ) AS storeDistance
FROM store
 WHERE themeId = #{themeId}
AND  status = 1
AND verifyStatus = 3
ORDER BY storeDistance ASC
LIMIT #{pageIndex},#{pageSize}

3、备注

st_distance 为mysql5.6+版本中开始自带的经纬度计算函数,将直接返回 2个经纬度

st_distance (POINT (latitude1,longitude1),POINT(latitude2,longitude2)) / 0.0111