关于根据距离列表排序sql

//第一种  根据经纬度排序       

        $map = "1=1 ";
        if ($loadCode && $unloadCode) {
            $map .= " AND load_city_id = $loadCode and unload_city_id = $unloadCode";
            
        } elseif($loadCode) {
            $map .= " AND load_city_id = $loadCode";
        } elseif($unloadCode) {
            $map .= " AND unload_city_id = $unloadCode";
        }
        
        if ($status > 1) {
            $map .= " AND driver_id = $driverId AND status = $status";
        } else {
          $map .= " AND pay_type IS NOT NULL";
          if($ismy) {
            $map .= " AND driver_id = $driverId";
          } else {
             $map .= " AND status = 1";  
          }
          
        }
        
        $virtual_open = config('site.virtual_open');
        if($virtual_open) {
            $map .= " AND is_show = 1";
        } else {
            $map .= " AND is_real = 1";
        }
        
        
        $sql = "SELECT *,ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN(({$lat} * PI() / 180 - load_lat * PI() / 180) / 2), 2) + 
            COS({$lat} * PI() / 180) * COS(load_lat * PI() / 180) * 
            POW(SIN(({$lng} * PI() / 180 - load_lng * PI() / 180) / 2), 2))) * 1000) AS distance 
            FROM fa_order WHERE $map ORDER BY distance ASC 
            LIMIT ".$start.",".$pagesize;
        $list = Db::query($sql);        

//第二种  附近商家
    public function getStoreByDiscount(){
        $lat = $this->request->param("lat");
        $lng = $this->request->param("lng");
        $coord = get_coord_by_lat_lng($lat,$lng,5);
        $list = $this->model->field("id,name,logo_image,telephone,location_x,location_y")->where('location_x', '>=', $coord['right-bottom']['lat'])
        ->where('location_x', '<=', $coord['left-top']['lat'])
        ->where('location_y', '>=', $coord['left-top']['lng'])
        ->where('location_y', '<=', $coord['right-bottom']['lng'])->select();
        
        $store = [];
        foreach($list as $key => $val) {
            $list[$key]["distance"] = getDistance($lat,$lng,$val["location_x"],$val["location_y"]);
        }
        $distance = array_column($list,'distance');
        array_multisort($distance,SORT_ASC,$list);
        $this->success("success", $list);
    }

/**
 * 计算两组经纬度坐标 之间的距离
 * params ：lat1 纬度1； lng1 经度1； lat2 纬度2； lng2 经度2； len_type （1:m or 2:km);
 * return m or km
 */
function getDistance($lat1, $lng1, $lat2, $lng2, $len_type = 1, $decimal = 2)
{
    $pi = 3.1415926000000001;
    $er = 6371;
    
    $radLat1 = $lat1 * $pi / 180;
    $radLat2 = $lat2 * $pi / 180;
    $a = $radLat1 - $radLat2;
    $b = $lng1 * $pi / 180 - $lng2 * $pi / 180;
    $s = 2 * asin(sqrt(pow(sin($a / 2), 2) + cos($radLat1) * cos($radLat2) * pow(sin($b / 2), 2)));
    $s = $s * $er;
    $s = round($s * 1000);

    if (1 < $len_type) {
        $s /= 1000;
    }

    return round($s, $decimal);
}

/*根据当前定位获取附近距离的范围*/
function get_coord_by_lat_lng($lat, $lng, $distance = 2)
{
    // 定义地球的半径
    define('EARTH_RADIUS', 6371);
    $new_lng =  2 * asin(sin($distance / (2 * EARTH_RADIUS)) / cos(deg2rad($lat)));
    $new_lng = rad2deg($new_lng);
 
    $new_lat = $distance / EARTH_RADIUS;
    $new_lat = rad2deg($new_lat);
 
     return [
        'left-top' => ['lat' => $lat + $new_lat,'lng' => $lng-$new_lng],
        'right-top' => ['lat' => $lat + $new_lat, 'lng' => $lng + $new_lng],
        'left-bottom' => ['lat' => $lat - $new_lat, 'lng' => $lng - $new_lng],
        'right-bottom' => ['lat' => $lat - $new_lat, 'lng' => $lng + $new_lng]
    ];
}

 

//高德经纬度 逆解析
public function cityLocation($location) {
$key = 'aebd531e9a82cf5626c61079871c970btest'; // 替换为你的高德地图API密钥
//$location = '117.373237,31.877761'; // 示例经纬度，格式为"经度,纬度"
$url = "https://restapi.amap.com/v3/geocode/regeo?key={$key}&location={$location}&extensions=all";

$response = file_get_contents($url);

$data = json_decode($response, true);

$res = [];
if ($data && isset($data['regeocode'])) {


$address = $data['regeocode']['addressComponent']; //省市区
if($address) {

$region = Region::where(['code'=>$address['adcode']])->find();
$city_id = Region::where(['id'=>$region['pid']])->value('code');
$res = [
'province' => $address['province'],
'city' => $address['city'],
'district'=> $address['district'],
'city_id' => $city_id??''
];
}

return $res; // 输出地址信息
} else {
return $res;
}
}