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条回答 默认 最新

  • dongyi5425 2014-07-14 14:01
    关注

    You could use something like

    $iDistance = 20;
    $iRadius = 6371; // earth radius in km
    $iRadius = 3958; // earth radius in miles
    $fLat = x.y; // Your position latitude
    $fLon = x.y; // Your position longitude
    
    $strQuery = "
    SELECT 
      *, 
      $iRadius * 2 * ASIN(SQRT(POWER(SIN(( $fLat - abs(pos.lat)) * pi() / 180 / 2),2) +
    COS( $fLat * pi()/180) * COS(abs(pos.lat) * pi() / 180) * POWER(SIN(( $fLon - pos.lon) *
    pi() / 180 / 2), 2) )) AS distance
    FROM user_zip_codes pos
    HAVING distance < $iDistance 
    ORDER BY distance";
    

    where you have to fetch your lat/lon value before using the SQL. This works for me

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥20 关于php录入完成后,批量更新数据库
  • ¥15 请教往复密封润滑问题
  • ¥15 cocos creator发布ios包
  • ¥15 comsol压电材料数据
  • ¥35 用python实现除法算法中goldschmidt算法
  • ¥15 汇编代码转换成C代码
  • ¥15 除法算法中的归一化具体是怎么变的?
  • ¥20 集成电路的逻辑电路和晶体管简化图
  • ¥15 下载windows builder后的问题
  • ¥15 端口连接数为什么会有限制