dongzhi4239 2014-07-14 11:52
浏览 76
已采纳

按距离排序邮政编码邻近搜索(php / mysql)

I have a table (user_zip_codes) with the users' zip_code, latitude and longitude. I have found a function here on stackoverflow that finds zip codes within a specific radius:

function zipcodeRadius($lat, $lon, $radius) {
    global $mysqli;
    $zipcodeList = array();
    $radius = ($radius ? $radius : 20);
    $sql = "SELECT userID,city,zip_code,country FROM user_zip_codes  WHERE (3958*3.1415926*sqrt((lat-$lat)*(lat-$lat) + cos(lat/57.29578)*cos($lat/57.29578)*(lon-$lon)*(lon-$lon))/180) <= $radius GROUP BY zip_code";
    if($stmt = $mysqli->prepare($sql)) {
        $stmt->execute();
        $stmt->bind_result($userID,$city,$zip_code,$country);
        while($stmt->fetch()) {
            $zipcodeList[] = array('userID'=>$userID,'city'=>$city,'zip_code'=>$zip_code,'country'=>$country);
        }
    }
    return $zipcodeList;
}

It works perfectly. However, I would like the function to sort the array by distance (either by ASC og DESC). How should I adjust my query in order for this to happen?

Thanks in advance.

UPDATE: The word 'distance' might appear ambiguous (thanks to Jorge). I simply wish to sort the zip_codes by distance meant as the distance between two points.

  • 写回答

3条回答

      报告相同问题?

      相关推荐 更多相似问题

      悬赏问题

      • ¥15 使用DWY100k数据集对UEA进行测试,出现报错:IndexError: index 125000 is out of bounds for axis 0 with size 95500
      • ¥15 前端vue实现根据图片url生成pdf文件
      • ¥15 RfidReader资源Q个
      • ¥20 user-agent是否是唯一的,有没有可能相同
      • ¥15 关于#开会#的问题,如何解决?(语言-c++)
      • ¥15 关于#二十四点问题#的问题
      • ¥15 运行kitex的demon出错(求大家解决)
      • ¥15 开发一个类似 typora 这样的软件需要多少钱?
      • ¥15 clearcase7.0安装包
      • ¥15 断点回归模型月度核密度检验不连续