如何通过经纬度计算距离来优化SQL查询？

Question

我有一个结构类似于这样的表格：

table name: shop

id_shop      int(10)
name         varchar(200)
latitude     double
longitude    double

我想计算给定坐标与保存在数据库中的坐标之间的距离。

我的当前查询：

SELECT *
  FROM `shop` AS `s`
 WHERE
      (
        ( 6371
        * ACOS(
            SIN( RADIANS( latitude ) )
          * SIN( RADIANS( 53.5353010379 ) )
          + COS( RADIANS( latitude ) )
          * COS( RADIANS( 53.5353010379 ) )
          * COS( RADIANS( 14.7984442616 ) - RADIANS( longitude ) )
          )
        )
        <= 25
      )

加上一些数据的左连接。有没有优化这个查询的方法？带有连接，大约需要13毫秒。我还需要在这里加入一些限制和总商店数量的计数（用于分页）。

- hsz

---

你希望它比13毫秒快多少？ - Abe Miessler

可能实际上并没有比13毫秒更快，但在我的系统中，会有许多类似的查询，针对不同的给定坐标+分页。我正在寻找一些处理这个问题的最佳方法。 - hsz

这些给定的坐标是来自另一个表格，还是来自在谷歌地图上的点击或移动设备的坐标？ - Matthew Dresser

我使用来自谷歌地图的地理位置脚本获取它们。 - hsz

Answer 1

以下是一些想法，其中一些可能根据您的具体情况不适用。

1. 您可以将纬度和经度的转换为弧度并存储在行中。这将节省计算成本（实际上，当存储数据时会产生成本）。
2. 如果您的表非常大，您可以使用简单的线性距离计算而不是Haversince公式来限制应用Haversince公式的结果。
3. 如果您的表中有其他数据可以作为很好的第一个过滤器（国家/地区等），您可以首先应用它。
4. 您可以重新排列连接的顺序，使其在距离筛选之后应用，以便您不会在不符合条件的数据上产生连接成本。

- tvanfosson

---

1. 不错的想法 - 我应该考虑一下；
2. 是的，表很大。线性计算应该是什么样子的？
3. 我没有那些数据；
4. 它没有改变任何东西。

- hsz

基本上，你可以使用勾股定理来计算距离——这可以“足够好”地工作，取决于你的数据和你正在进行的工作，它对于在地球上相对有限的区域内寻找“最近的东西”而不是实际需要准确的距离是可以的。你也可以像使用勾股定理一样进行粗略计算来缩小数据集，然后在缩小后的较小数据集上进行更准确、更昂贵的 Haversine 计算。 - Matt Gibson

Answer 2

首先，您可以在存储纬度和经度时将预先计算的内容存储在数据库中。例如，如果您将纬度和经度预先存储为弧度，则只需要在每个位置存储时计算一次RADIANS（latitude）和RADIANS（longitude），而不是每次需要进行距离计算时都进行计算（假设您需要进行多次计算）。

也许您还可以通过在首次填充行时存储SIN（RADIANS（latitude））和COS（RADIANS（latitude））来进一步减少计算量...

我猜您随着时间的推移会进行很多“最接近X”的计算——这通常是人们面对这种计算时所做的事情——预先计算可能是尝试的第一件事。

- Matt Gibson