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

原文 标签 php mysql

Sorting zip code proximity search by distance (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.

Answer

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

翻译

我有一个表(user_zip_codes),其中包含用户的邮政编码,纬度和经度。我在stackoverflow上找到了一个函数,该函数可查找特定半径内的邮政编码:

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;
}


它运作完美。但是,我希望该函数按距离对数组进行排序(通过ASC或DESC)。我应该如何调整查询以使其发生?

提前致谢。

更新:
“距离”一词可能看起来含糊不清(感谢Jorge)。我只是希望按距离(即两点之间的距离)对邮政编码进行排序。
最佳答案
您可以使用类似

$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";


在使用SQL之前,您必须先获取经/纬度值。这对我有用
相关推荐

php - PHP类常量和php函数常量给出警告

php - Symfony DomCrawler状态代码不起作用?

php - 使用foreach循环删除建立SQL查询中的最后一个“ UNION ALL”

php - 基于时间的PHP问候

php - Laravel雄辩的ORM-save()返回未找到的列(MySQL错误)

php - f3框架sql查询联接

php - PHP Regex:匹配字符集或字符串结尾

php - PHP / MySQL平均基于数组的内容

php - 如何使用PHP将图像发送到deviantsart rest API

php - Laravel关系返回null