performance - 如何提高计算haversine公式的查询性能？

Question

给定一张包含纬度和经度的位置表，其中哪些位置最接近给定位置？

当然，求地球表面的距离，就是用大圆距离，用半正弦公式，也叫球面余弦公式算出来。

我有以下代码:

 SELECT zip, latitude, longitude, distance
 FROM (
  SELECT z.zip,
         z.latitude, z.longitude,
         p.radius,
         p.distance_unit
             * DEGREES(ACOS(COS(RADIANS(p.latpoint))
             * COS(RADIANS(z.latitude))
             * COS(RADIANS(p.longpoint - z.longitude))
             + SIN(RADIANS(p.latpoint))
             * SIN(RADIANS(z.latitude)))) AS distance
  FROM zip AS z
   JOIN (   /* these are the query parameters */
    SELECT  42.81 AS latpoint, -70.81 AS longpoint,
            50.0  AS radius,  111.045 AS distance_unit
        ) AS p ON 1=1
  WHERE z.latitude
    BETWEEN p.latpoint  - (p.radius / p.distance_unit)
        AND p.latpoint  + (p.radius / p.distance_unit)
   AND z.longitude
    BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
        AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
) AS d
WHERE distance <= radius

有什么方法可以提高这个查询的性能吗？

是否有必要使用 PostGIS 来改进它，或者它只是我的半正弦公式的包装器？

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 上建立索引，这将允许数据库服务器避免检查数据库中的许多行。