php - 如何加速mysql中的Haversine公式？

Question

作为一个业余项目，我开始为一家公司制作网站。我不太擅长为网站制作复杂的代码，但我想尝试一下。

我有一个来自荷兰的邮政编码数据库，其中包含超过 471000 条记录。我使用 haversine 公式查找 13 公里半径内的邮政编码，然后我从数据库中选择每个在结果中有邮政编码的用户。但是公式需要 6 秒来加载所有结果。我怎样才能加快这个过程？

代码如下:

$finder = $mysqli->query("SELECT lat, lng FROM postcodetabel WHERE postcode = '$s' OR plaats = '$s'");
$finder1 = mysqli_fetch_assoc($finder);
$latitude = $finder1['lat'];
$longitude = $finder1['lng'];
$query = $mysqli->query("SELECT postcode, (
      6371 * acos (
      cos ( radians('$latitude') )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians('$longitude') )
      + sin ( radians('$latitude') )
      * sin( radians( lat ) )
    )
) AS distance
FROM postcodetabel
HAVING distance < 12
ORDER BY distance ASC");

$quertie = $mysqli->query("SELECT bigav, id, naam, email, bedrijfsnaam, telnummer FROM gebruikers WHERE postcode = '$postcode' AND status = 1 AND soort LIKE '%" . $soort . "%'");
while($quertie2 = mysqli_fetch_assoc($quertie)) { echo '
<div class="kapper">
    <div class="kapperfoto"><img src="/vluggeknipt/ondernemer/pagina/uploads/'.$quertie2['bigav'].'" style="width:100px;height:100px;"></div>
    <div class="boektekst">
        <font class="headingkap"><strong><a href="?page=profiel&id='.$quertie2['id'].'">Naar profiel &raquo;</a></strong></font><br/>
    </div>
    <div class="kappertext">
        <font class="headingkap"><a href="?page=profiel&id='.$quertie2['id'].'"><strong>'.$quertie2['naam'].'</strong></a></font><br/>
        <i class="mobhide">'.$quertie2['email'].' - '.$quertie2['telnummer'].'</i><br/>
        <i class="mobhide">'.$quertie2['bedrijfsnaam'].'</i>
    </div></div><br/>

    '; }

提前致谢!

Answer 1

这个查询永远不会特别快。但是，有一些方法可以对其进行改进。

第一:这里不需要Haversine公式。仅本地球曲率是一个重要因素或非常靠近两极时，才需要进行修正。这两种情况都不是这里的情况——需要准确计算的最大距离是 12 英里，几乎不超过地平线。在这个尺度上，地球实际上是平坦的，因此勾股定理足以计算距离。

一个纬度大约是 69 英里，在 52°N(荷兰附近)，一个经度是 cos(52°) x 69 = 42.5 英里，所以公式变为:

sqrt(pow(69*(lat - $latitude), 2) + pow(42.5*(lng - $longitude), 2))

第二:我们可以对纬度和经度使用“剪刀测试”。如果一个点在任何主要方向上距离您的目标点超过 12 英里，则它肯定不能在该点的 12 英里圆内。我们可以利用这个事实对纬度和经度进行快速比较，完全跳过距离计算。使用我们上面推导出的一个纬度/经度的数字，我们有:

WHERE (lat BETWEEN ($latitude  - 12/69.0) AND ($latitude  + 12/69.0))
  AND (lng BETWEEN ($longitude - 12/42.5) AND ($longitude + 12/42.5))

请注意，这并不能取代完整的距离检查!这只是快速排除可能不在正确半径内的点的第一步。通过在 lat 或 lng 上建立索引，这将允许数据库服务器避免检查数据库中的许多行。