Mysql根据经纬度计算距离并排序

计算公式

纬度：31.182592 store_locy 经度：121.431744 store_locx

按M计算

        SELECT *,
                ROUND(
                6378.138 * 2 * ASIN(
                    SQRT(
                        POW(
                            SIN(
                                (
                                    31.182592 * PI() / 180 - store_locy * PI() / 180
                                ) / 2
                            ),
                            2
                        ) + COS(31.182592 * PI() / 180) * COS(store_locy * PI() / 180) * POW(
                            SIN(
                                (
                                     121.431744 * PI() / 180 -   store_locx * PI() / 180
                                ) / 2
                            ),
                            2
                        )
                    )
                ) * 1000
            ) AS distance
        FROM t_merchant_store
        ORDER BY distance ACS

按KM计算

    SELECT
    *,
    (
    2 * 6378.137 * ASIN(
    SQRT(
    POW( SIN( PI( ) * ( 121.431744- store_locx ) / 360 ), 2 ) + COS( PI( ) * 31.182592 / 180 ) * COS( store_locy * PI( ) / 180 ) * POW( SIN( PI( ) * ( 31.182592- store_locy ) / 360 ), 2 ) 
    ) 
    ) 
    ) AS distance 
FROM
    t_merchant_store 
ORDER BY
      distance ASC

转自: https://blog.csdn.net/lishuaiqi_d/article/details/105428037