Mysql 根据经纬度计算距离

 

方式1：st_distance_sphere

st_distance_sphere函数返回以米为单位

SELECT *,
st_distance_sphere(point(lng,lat),point(116.3424590000,40.0497810000)) as juli 
FROM table ORDER BY juli ASC 

 

 

方式2：st_distance

 

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

 

SELECT
    *,st_distance (
    point ( longitudes, latitudes ),
    point ( 113.264435, 23.129163 )) AS juli 
FROM
    zxh_distance 
ORDER BY
    juli ASC;

 

 

方式3 直接计算

 

SELECT
    *,ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
            POW( SIN(( 23.129163 * PI()/ 180-latitudes * PI()/ 180 )/ 2 ), 2 )+ COS( 23.129163 * PI()/ 180 )* COS( latitudes * PI()/ 180 )* POW( SIN(( 113.264435 * PI()/ 180-longitudes * PI()/ 180 )/ 2 ), 2 )))* 1000 
    ) AS juli 
FROM
    zxh_distance 
ORDER BY
    juli ASC