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 单位:米
当然你也可以自定义距离函数:
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
(原创)
应用场景:
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
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
(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