Mysql 计算2个坐标点位置的距离
测试结果 ST_DISTANCE_SPHERE 最准确,ASIN结果近似,ST_DISTANCE 不论是乘111195 还是除0.0111 结果都相差有点大。(单位都是米)
ST_DISTANCE_SPHERE 需要mysql5.7 以上
ST_DISTANCE需要mysql5.6 以上
ASIN mysql 5.0以上都可以
一、ST_DISTANCE (乘 111195)
SELECT id,name,work_lng,work_lat,service_city, (round( ST_DISTANCE( POINT(work_lng, work_lat), POINT(116.23303, 39.91124) ) * 111195 )) AS distance FROM `la_coach` order by distance asc;
二、ST_DISTANCE (除 0.0111)
SELECT id,name,work_lng,work_lat,service_city, round((st_distance(point(work_lng, work_lat),point(116.23303, 39.91124)) / 0.0111) * 1000)AS distance FROM `la_coach` order by distance asc;
三、ST_DISTANCE_SPHERE
SELECT id, NAME, work_lng, work_lat, service_city, ( ROUND( ST_DISTANCE_SPHERE( POINT(work_lng, work_lat), POINT(116.23303, 39.91124) ) ) ) AS distance FROM `la_coach` order by distance asc;
四、ASIN
SELECT id,name,work_lng,work_lat,service_city, ROUND(6378.138*2*ASIN(SQRT(POW(SIN((39.91124 *PI()/180-work_lat*PI()/180)/2),2)+ COS(39.91124 *PI()/180)*COS(work_lat*PI()/180)*POW(SIN((116.23303 *PI()/180-work_lng*PI()/180)/2),2)))*1000) AS distance FROM `la_coach` order by distance asc;
在线经纬度距离计算
1.http://www.atoolbox.net/Tool.php?Id=1001
2.https://www.lddgo.net/convert/distance
3.https://tool.box3.cn/lbs.html