mysql distance()_mysql距离函数st_distance

st_distance 函数

st_distance 函数是从mysql5.6.1才加入的。

SET @g1 = POINT(1,1), @g2 = POINT(2,2);

select  st_distance (@g1, @g2);

#输出结果：1.4142135623730951  单位是度

SELECT  st_distance (point (1, 1),point(2,2) ) * 111195

//输出结果：157253.47706807632  单位：米

st_distance 计算的结果单位是度，需要乘111195(地球半径6371000*PI/180)是将值转化为米。

当然你也可以自定义距离函数：

CREATE FUNCTION slc (

lat1 DOUBLE,

lon1 DOUBLE,

lat2 DOUBLE,

lon2 DOUBLE

) RETURNS DOUBLE RETURN 6371 * acos(

cos(radians(lat1)) * cos(radians(lat2)) * cos(

radians(lon2) - radians(lon1)

) + sin(radians(lat1)) * sin(radians(lat2))

);

SELECT slc(1,1,2,2) from DUAL

//输出结果：157.22543203804852  km

(原创)

CREATE DEFINER=`root`@`%` FUNCTION `getdistance`(lat1 FLOAT,lng1 FLOAT, lat2 FLOAT, lng2 FLOAT) RETURNS float

DETERMINISTIC

BEGIN

DECLARE s,RAD,EARTH_RADIUS,radLat1,radLat2,radLng1,radLng2 FLOAT;

SET RAD = 3.1415926535897/180;

SET EARTH_RADIUS = 6378137;

SET radLat1 = lat1 * RAD;

SET radLat2 = lat2 * RAD;

SET radLng1 = lng1 * RAD;

SET radLng2 = lng2 * RAD;

SET s = ROUND(ACOS(COS(radLat1)*COS(radLat2)*COS(radLng1-radLng2)+SIN(radLat1)*SIN(radLat2))*EARTH_RADIUS * 10000)/10000;

RETURN s;

end

应用场景:

假设我当时的坐标：109.216756, 19.738124 需要查询我附近20KM内景区，并按照距离由近及远排列

select s.id as poiId,s.name,'sight' as poiCategory ,s.map_lat,s.map_lng, (st_distance(point(s.map_lng,s.map_lat),point(109.216756, 19.738124))*111195) as distance  from travel.poi_sight  s having distance <= 20000  order by distance