如何在mysql中加快Haversine公式? [英] How to speed up the Haversine formula in mysql?

问题描述

作为一个爱好项目，我开始为一家公司创建一个网站.我在为网站编写复杂的代码方面不是很先进，但是我想尝试一下.

As a hobby project I started making a website for a company. I am not very advanced in making complicated codes for website but I wanted to give it a try.

我有一个来自荷兰的邮政编码数据库，其中有471000条记录.我使用Haversine公式查找在13 km半径内的邮政编码，然后从数据库中选择在此结果中具有邮政编码的每个用户.但是该公式需要6秒钟才能加载所有结果.如何加快此过程?

I have a zip code database from the Netherlands with over 471000 records in it. I use the haversine formula to look up which postal codes are within the radius of 13 km and then I select every user from the database that has a postalcode in this result. But the formula takes 6 secs to load all the results. How can I speed this process up?

下面的代码:

$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/>

    '; }

提前谢谢！

推荐答案

此查询永远不会特别快.但是，有一些方法可以改进它.

This query will never be especially fast. However, there are some ways it can be improved.

首先:在这里不必使用Haversine公式.仅当地球曲率是重要因素或非常接近两极时才需要进行校正.这两种情况都不是-需要精确计算的最大距离是12英里，即使在地平线上也差不多.在这样的规模上，地球实际上是平坦的，因此勾股定理足以计算距离.

First: The Haversine formula is not necessary here. The corrections which it applies are only necessary when the curvature of the earth is a significant factor, or very near the poles. Neither of these are the case here -- the largest distance which needs to be calculated accurately is 12 miles, which is barely even over the horizon. On this scale, the earth is effectively flat, so the Pythagorean Theorem is good enough for calculating distances.

一个纬度约为69英里，在52°N(荷兰所在的位置)，经度为cos(52°) x 69 = 42.5英里，因此公式变为:

One degree of latitude is about 69 miles, and at 52°N (around where the Netherlands are), a degree of longitude is cos(52°) x 69 = 42.5 miles, so the formula becomes:

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

第二:我们可以对纬度和经度使用剪刀测试".如果某个点在任何基本方向上都离目标点超过12英里，那么它肯定不在该点的12英里范围内.我们可以利用这一事实对纬度和经度进行快速比较，而完全跳过距离计算.使用上面推导的一个纬度/经度的数字，我们得到:

Second: we can use a "scissor test" for latitude and longitudes. If a point is more than 12 miles in any cardinal direction from your target point, it certainly cannot be within a 12-mile circle of that point. We can use this fact to perform a fast comparison on the latitude and longitude, skipping the distance calculation entirely. Using the figures for one degree of latitude/longitude we derived above, we have:

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上具有索引的情况下，这将使数据库服务器避免检查数据库中的许多行.

Note that this doesn't replace the full distance check! It's simply a first step to quickly throw out points that can't possibly be within the right radius. With an index in place on lat or lng, this will allow the database server to avoid examining many of the rows in the database.

这篇关于如何在mysql中加快Haversine公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！