mysql - 具有适当缩放级别的附近位置

Question

我有一个包含 13.000 个位置的 mysql 数据库。使用 html 地理定位，我通过定义半径(例如 1 公里)找到用户的位置，我计算一个边界框并使用它来查找该半径内的(sql)位置。输入:地理位置用户，输出:(排序)半径 1 公里内的位置数组。

如果该半径内没有位置，则此方法不起作用。我想要显示附近大约 10 个位置，无论半径如何。这意味着谷歌地图缩放级别应该是动态的，并且 sql 搜索也应该以不同的方式进行。输入:地理位置用户，输出:附近 10 个位置，没有预定义半径，位置应该或多或少在 map 上可见 -> 适当的 map 缩放级别。

我想从 1 公里开始，如果该范围内没有位置，则将半径增加 +100 米并保持循环，直到找到 +/- 10 个位置(排序并将它们放入数组中) 。然后找到从用户位置到数组中最后一个位置的距离(最大距离)，并从那里计算适当的谷歌地图缩放级别。

我有一个问题:如果最近的位置在 20 公里以内怎么办？每次循环增量为 100 m，它将循环计算 200 次!恐怕这会导致等待时间很长。

我应该如何解决这个问题？还有其他方法可以获取附近的位置吗？

这是我的mysql表结构(其中formid代表位置的ID)，总共189.031行。

编辑:我已经尝试过 Ollie Jones 的答案，这是我使用的脚本(我将计数器除以 2，因为我有每个地址的语言重复项):

 $rad = 0.2;  // radius of bounding circle in kilometers

 $R = 6371;  // earths mean radius, km

  // first-cut bounding box (in degrees)
  $maxLat = $_GET['lat'] + rad2deg($rad/$R);
  $minLat = $_GET['lat'] - rad2deg($rad/$R);
  // compensate for degrees longitude getting smaller with increasing latitude
  $maxLon = $_GET['long'] + rad2deg($rad/$R/cos(deg2rad($_GET['lat'])));
  $minLon = $_GET['long'] - rad2deg($rad/$R/cos(deg2rad($_GET['lat'])));

$start = microtime(true);
for ($i = 0; ; $i++) {

  // first-cut bounding box (in degrees)
  $maxLat = $_GET['lat'] + rad2deg($rad/$R);
  $minLat = $_GET['lat'] - rad2deg($rad/$R);
  // compensate for degrees longitude getting smaller with increasing latitude
  $maxLon = $_GET['long'] + rad2deg($rad/$R/cos(deg2rad($_GET['lat'])));
  $minLon = $_GET['long'] - rad2deg($rad/$R/cos(deg2rad($_GET['lat'])));

$sql2 = "SELECT * FROM table WHERE content BETWEEN '".$minLat."' AND '".$maxLat."'";

$result3 = mysqli_query($link, $sql2);  

$counter = 0;
while ($row3 = mysqli_fetch_assoc($result3)) {

$sql3 = "SELECT * FROM table WHERE attribute = 'LON' AND formid = {$row3["formid"]} AND content BETWEEN '".$minLon."' AND '".$maxLon."'";

$result4 = mysqli_query($link, $sql3);  

while ($row4 = mysqli_fetch_assoc($result4)) {
    $counter = $counter + 1;; // or $counter = $counter + 1;
}
}
$total = $counter/2;
echo $total;    
if ($total >= 2 || $rad >= 30) {
$end = microtime(true);
$time = number_format(($end - $start), 2);
echo 'This page loaded in ', $time, ' seconds';
break;  

}
$rad = $rad * sqrt(2);
}
/* close connection */
mysqli_close($link);

Answer 1

注意在OQ发布一些代码后，我发布了这个问题的另一个答案。请看一下。这比这个答案更切题。

您的基本想法似乎不错:也就是说，如果您在第一次尝试中没有获得足够的分数，则增加搜索半径。

您建议每次尝试时将搜索半径增加 100m。这似乎是一种非常不积极的搜索范围扩大策略。

为什么不每次将半径增加当前半径的 41.4% (radius * sqrt(2)) 呢？这样，每次迭代搜索的地理区域就会加倍。您的查询已经返回最近的十个点，因此即使您在一次迭代中突然拉入一千个点，也不会得到疯狂的结果。

请注意，如果您的 13,000 个点是邮政编码/邮政编码质心，则 1km 并不是此搜索的一个很好的起点。除了人口稠密的城市地区外，在任何特定的 1 公里半径内不太可能找到 10 个。您可能想从更大的开始。

编辑感谢您更新您的问题，以包含有关表格结构和查询的信息。这很有帮助。

您提出了一个困难的优化问题。您的纬度和经度值存储在属性表中。据推测，它们存储为文本“-45.12345”而不是 FLOAT 值。优化这些地理查询需要至少在纬度值上使用可顺序扫描的索引。也就是说，您需要能够在 SQL 中说出这样的内容。

SELECT whatever FROM sometable 
 WHERE attribute = 'LAT'
   AND content BETWEEN ?lat-radius AND ?lat+radius

(?lat 是候选点的纬度。)服务器需要能够通过随机访问从 ?-radius 开始的索引来满足该请求然后依次扫描到?+radius。服务器不能这样做:您的查询包含从文本到 FLOAT 的隐式类型转换，如下所示。类型转换击败了索引。

SELECT whatever FROM sometable
 WHERE attribute = 'LAT'
   AND CAST(content AS FLOAT) BETWEEN ?lat-radius AND ?lat+radius

因此，除非您更改架构，使 LAT 和 LONG 可以是带有索引的 FLOAT 值，否则该查询将会很慢，与搜索的半径无关。小半径没有帮助，大半径也没有坏处。

SO:提高性能的最简单方法是执行一次查询。我首先建议的半径的迭代扩展对于您的表结构来说是毫无意义的。使用大半径(50km)，并使用 ORDER BY dist ASC LIMIT n 获取距离候选点最近的点。

这个问题还有另一类解决方案。它涉及使用某种触发器或其他更新方法创建影子纬度/经度表。但这是一项繁重的工作，您可能无法承受。

仅供引用，这里有一篇关于解决地理定位问题的文章。 http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/