Write the Code. Change the World.

12月 16

mysql 计算距离并按距离远近升序排序

$latitude = 22.531162063479226;
$longitude = 113.954436585508;

$query = Model::query()->select(['latitude', 'longitude', 'address',]);

$query->where('latitude', '>', 0);

$query->addSelect(DB::raw("acos(cos(" . $latitude . "*pi()/180)*cos(latitude*pi()/180)*cos(" . $longitude . "*pi()/180-longitude*pi()/180)+sin(" . $latitude . "*pi()/180)*sin(latitude * pi()/180)) * 6367000 AS distance"));

$res = $query->orderBy('distance', 'asc')->get();

参考:

SELECT
    id,
    latitude,
    longitude,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            23.163292 * PI() / 180 - latitude * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(23.163292 * PI() / 180) * COS(latitude * PI() / 180) * POW(
                    SIN(
                        (
                            113.3114676 * PI() / 180 - longitude * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS distance
FROM
    cw_party
ORDER BY
    distance asc