mysql 距离范围查询

SELECT
    `hotel`.`id` AS `hotel_id`,
    `hotel`.`name`,
    `hotel`.`province_name`,
    `hotel`.`city_name`,
    `hotel`.`county_name`,
    `hotel`.`address`,
    `hotel`.`start_rating`,
    `hotel`.`latitude`,
    `hotel`.`longitude`,
    `hotel`.`thumbnail`,
    `dida_hotel_lowest_price`.`current_price`,
    6378.138 * 2 * ASIN(
        SQRT(
            POW( SIN( ( '维度' * PI() / 180 - latitude * PI() / 180 ) / 2 ), 2 ) + COS( '维度' * PI() / 180 ) * COS( latitude * PI() / 180 ) * POW( SIN( ( '经度' * PI() / 180 - longitude * PI() / 180 ) / 2 ), 2 ) 
        ) 
    ) AS distance 
FROM
    `dida_hotel_lowest_price`
    INNER JOIN `hotel` ON `hotel`.`dida_hotel_id` = `dida_hotel_lowest_price`.`dida_hotel_id` 
WHERE
    `dida_hotel_lowest_price`.`check_in_date` = '2020-12-25' 
    AND `dida_hotel_lowest_price`.`check_out_date` = '2020-12-26' 
    AND `hotel`.`dida_hotel_id` > '0' 
    AND `hotel`.`city_name` = '上海市' 
    AND 6378.138 * 2 * ASIN(
        SQRT(
            POW( SIN( ( '31.200207' * PI() / 180 - latitude * PI() / 180 ) / 2 ), 2 ) + COS( '31.200207' * PI() / 180 ) * COS( latitude * PI() / 180 ) * POW( SIN( ( '121.26709' * PI() / 180 - longitude * PI() / 180 ) / 2 ), 2 ) 
        ) 
    ) <= '公里'
ORDER BY
    distance asc,
    `hotel`.`create_at` DESC 
    LIMIT 10 OFFSET 0

 

这样范围查询更快

$user = auth()->user();

    $lat = auth()->user()->latitude;
    $lon = auth()->user()->longitude;
    $radius = 3; // km => converted to meter

    $angle_radius = (float)$radius / ( 111 * cos( (float)$lat ) ); // Every lat|lon degree° is ~ 111Km
    $min_lat = (float)$lat - (float)$angle_radius;
    $max_lat = (float)$lat + (float)$angle_radius;
    $min_lon = (float)$lon - (float)$angle_radius;
    $max_lon = (float)$lon + (float)$angle_radius;

    $persons = User::where('status', STATUS::ACTIVE)
                ->where('id', '!=', $user->id)
                ->whereBetween('latitude', [$min_lat, $max_lat])
                ->whereBetween('longitude', [$min_lon, $max_lon])
                ->get();