使用经纬度在MySQL中查找最接近的10个城市？

Question

我在网上搜索了很久，想找到解决我的问题的方法，但我仍然无法弄清楚。

我有一个非常简单的数据库，包含id、城市名称、纬度、经度和城市信息。当有人进入一个城市页面时，我想显示10个附近的城市。

如何使用MySQL计算并通过PHP返回呢？

我在这个网站看到了很多建议，但是都没有成功。

我尝试了很多方法却没有成功，没有得到任何结果。

<?php
$slatitude = 43.2141341;
$slongitude = 64.4368684;
$miles = 200;

//connect

$query = "SELECT *, 
( 3959 * acos( cos( radians('$slatitude') ) * 
cos( radians( latitude ) ) * 
cos( radians( longitude ) - 
radians('$slongitude') ) + 
sin( radians('$slatitude') ) * 
sin( radians( latitude ) ) ) ) 
AS distance FROM cities HAVING distance < '$miles' ORDER BY distance ASC LIMIT 0, 10";

$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){

while ($row = mysql_fetch_assoc($query)){
$id = $row['id'];
$cityname = $row['cityname'];
$latitude = $row['latitude'];
$longitude = $row['longitude'];

echo "$cityname<br />";

}}

?>`

- A Kuijk

---

你的数据库中 确实 有200英里内的城市吗？ - BenOfTheNorth

你在没有使用 HAVING 子句的情况下得到了结果吗？还是出现了错误信息？ - G-Nugget

我的数据库中肯定有距离在200英里以内的城市。我完全没有收到任何错误信息。 - A Kuijk

对于短距离（小于200英里为短距离），我建议使用“等经纬投影”而不是“余弦定理”来计算距离。 - TreyA

Answer 1

你不能使用having，因为没有按任何东西进行分组。你需要在where中重复你在select中所做的。

$query = "SELECT *, 
( 3959 * acos( cos( radians('$slatitude') ) * 
cos( radians( latitude ) ) * 
cos( radians( longitude ) - 
radians('$slongitude') ) + 
sin( radians('$slatitude') ) * 
sin( radians( latitude ) ) ) ) 
AS distance FROM cities WHERE ( 3959 * acos( cos( radians('$slatitude') ) * 
cos( radians( latitude ) ) * 
cos( radians( longitude ) - 
radians('$slongitude') ) + 
sin( radians('$slatitude') ) * 
sin( radians( latitude ) ) ) ) < '$miles' ORDER BY distance ASC LIMIT 0, 10";

或者你可以像这样做：

$query = "
SELECT * FROM (
  select *, 
  ( 3959 * acos( cos( radians('$slatitude') ) * 
  cos( radians( latitude ) ) * 
  cos( radians( longitude ) - 
  radians('$slongitude') ) + 
  sin( radians('$slatitude') ) * 
  sin( radians( latitude ) ) )) as distance from cities
) WHERE distance < '$miles' ORDER BY distance ASC LIMIT 0, 10";

- Ian Overton

---

你需要将第二个查询中的 AS distance 移动到子查询内部。 - G-Nugget

我相信查询语句原本使用HAVING子句是可以正常工作的。在MySQL中，别名表达式可以在HAVING子句中使用而不需要GROUP BY，但这并不是标准SQL。 - G-Nugget

我建议安装PHPMyAdmin。它非常用户友好。 - Ian Overton

第二个例子在FROM(...)后面缺少AS...，导致出现“每个派生表必须有自己的别名”的错误。 - user5147563

提示：在大型数据库中，此方法非常缓慢。您可能希望通过纬度/经度进行限制，就像我所做的那样："... WHERE Latitude > ".($slatitude-1.5)." AND Latitude < ".($slatitude+1.5)." AND Longitude > ".($slongitude-1.5)." AND Longitude < ".($slongitude+1.5)." ..."使用此方法，速度比以前快100倍（0.03秒），之前需要3秒。 - user5147563

Answer 2

为了提高查询速度，您可以首先使用以下子选择器来限制要进行计算的结果集（请注意，我还使用了一种不同的方法来计算距离-它对我有效，但可能因人而异）。在我的测试中，使用容差为1的where子句比没有使用where子句的查询快100倍以上。

...
$tol = 1; // limits the search to lat/long within 1 from the given values
$query_args = array($lat,$long,$lat-$tol,$lat+$tol,$long-$tol,$long+$tol);
$query = "
  SELECT *,latitude, longitude, 
    SQRT( POW( 69.1 * ( latitude - %s) , 2 ) 
        + POW( 69.1 * ( %s - longitude ) 
          * COS( latitude / 57.3 ) , 2 ) ) 
    AS distance FROM zipcodes
      WHERE latitude > %d AND latitude < %d 
      AND longitude > %d AND longitude < %d 
  ORDER BY distance ASC limit 10
";
...

- Bryan Gmyrek

Answer 3

以下是我用来获取给定经纬度集合中最接近的邮政编码的查询。表非常简单：

id，zip，lat，lng

此查询假设您的中心点使用“$slatitude”和“$slongitude”。这将返回所有与距离变量“$miles”（即3、2.4、1000）内匹配的结果。在我的脚本中，结果被放入数组中，并按最接近的X个结果进行排序。

$getzip = mysql_query("SELECT zip,((ACOS(SIN(".$slatitude." * PI() / 180) * SIN(lat * PI() / 180) + COS(".$slatitude." * PI() / 180) * COS(lat * PI() / 180) * COS((".$slongitude." - lng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM zips HAVING distance<='$miles' ORDER BY 'distance' ASC", $link2) or die (mysql_error($link2));

希望它有所帮助！

- MelArlo

Answer 4

最简单的解决方案是：

获取城市的坐标并

select ...
order by abs(`Latt`-reqLatt) * abs(`Long`-reqLong)

虽然不是完美的，但你可以从前50个中找到你想要的。

- Teson

---

那大概总的来说可行，但它没有使用投影，因此在半径边缘附近的城市会出现一些错误，以及距离相似的城市排序。 "半径"也将是"正方形"。 - G-Nugget

这也没有提供限制在200英里范围内的方法。 - Ian Overton

Answer 5

我使用PDO来完成这个任务，因为mysql_函数已经被弃用了。

<?php
 //Connect to database
$dbh = new PDO("mysql:host=$host;dbname=$database", $username, $password);//Change to suit 
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
    // Prepare statement
    $stmt = $dbh->prepare("SELECT  *, ( 3959 * acos( cos( radians(?) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance FROM cities HAVING distance < ? ORDER BY distance LIMIT 0 , 10");
    // Assign parameters
    $stmt->bindParam(1,$slatitude);
    $stmt->bindParam(2,$slongitude);
    $stmt->bindParam(3,$slatitude);
    $stmt->bindParam(4,$miles);
    //Execute query
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $stmt->execute();
    if ($stmt->rowCount()>0) { 
    // Iterate through the rows
    while($row = $stmt->fetch()) {
        $id = $row['id'];
        $cityname = $row['cityname'];
        $latitude = $row['latitude'];
        $longitude = $row['longitude'];
        echo "$cityname<br />";]);
        }
    }
   else{
        echo "No Records";
       }   
}

catch(PDOException $e) {
    echo "I'm sorry I'm afraid you can't do that.". $e->getMessage() ;// Remove or modify after testing 
    file_put_contents('PDOErrors.txt',date('[Y-m-d H:i:s]').", mapSelect.php, ". $e->getMessage()."\r\n", FILE_APPEND);  
 }
//Close the connection
$dbh = null; 
?>

这个演示使用了这个查询

- david strachan