PostgreSQL中基于勾股定理的地理位置临近度排序优化

在许多应用场景中，我们需要根据地理坐标（经纬度）来查找某个特定点附近的其他点，并按距离远近进行排序。对于小范围内的近似计算，我们可以采用勾股定理来计算两点之间的平面距离。由于经纬度单位与实际距离的换算并非线性，通常会引入一个近似的转换系数（例如，在赤道附近，1度经度或纬度大约对应111139米）。

假设我们有一个名为 point 的表，其中包含 lat (纬度) 和 lng (经度) 列，我们希望查找距离 (myPointLat, myPointLng) 在 metres 范围内的所有点，并按距离从小到大排序。

距离计算公式

我们使用的距离平方公式为： ((abs(l.lat * 111139 - myPointLat * 111139)^2) + (abs(l.lng * 111139 - myPointLng * 111139)^2))

这个公式计算的是两点在近似平面坐标系下的距离平方。通过将其与 metres^2 比较，可以筛选出在指定半径内的点。

方法一：使用子查询定义计算列

为了避免在 WHERE 和 ORDER BY 子句中重复复杂的距离计算表达式，我们可以将距离计算封装在一个子查询中，作为一个新的计算列。这种方法提高了SQL的可读性和维护性。

SELECT Column1, Column2, Column3 -- 选择你需要的列
FROM (
    SELECT *,
           (
               (ABS(l.lat * 111139 - myPointLat * 111139)^2) +
               (ABS(l.lng * 111139 - (myPointLng * 111139))^2)
           ) AS proximity_squared -- 计算距离平方并命名为 proximity_squared
    FROM point l
) AS subquery_with_distance
WHERE proximity_squared <= metres^2
ORDER BY proximity_squared;

说明：

• myPointLat 和 myPointLng 是目标点的经纬度参数。
• metres 是指定的半径（以米为单位）。
• 111139 是将经纬度转换为米的大致系数。
• Column1, Column2, Column3 应替换为 point 表中你实际需要的列名。

优点：

硅基智能

基于Web3.0的元宇宙，去中心化的互联网，高质量、沉浸式元宇宙直播平台，用数字化重新定义直播

62

查看详情

• SQL语句更简洁，避免了表达式重复。
• 提高了代码的可读性。

潜在性能考量： PostgreSQL的查询优化器可能会尝试优化子查询，但理论上，它可能先计算所有行的 proximity_squared，然后再进行过滤和排序。对于非常大的数据集，这可能不如直接重复表达式高效。

方法二：直接重复距离表达式（性能优化）

为了最大化性能，尤其是在大数据集上，更推荐直接在 WHERE 和 ORDER BY 子句中重复距离计算表达式。这种方式允许PostgreSQL在计算所有行的排序键之前，先执行过滤操作，从而显著减少需要排序的数据集大小。

SELECT *
FROM point l
WHERE (
          (ABS(l.lat * 111139 - myPointLat * 111139)^2) +
          (ABS(l.lng * 111139 - (myPointLng * 111139))^2)
      ) <= metres^2
ORDER BY (
          (ABS(l.lat * 111139 - myPointLat * 111139)^2) +
          (ABS(l.lng * 111139 - (myPointLng * 111139))^2)
         );

优点：

• 性能更优： PostgreSQL可以先应用 WHERE 子句的过滤条件，只对符合条件的少量数据进行距离计算和排序。这对于大型数据集而言，能够显著减少CPU和I/O开销。
• 避免了创建临时子查询的开销。

缺点：

• SQL语句中重复了复杂的表达式，可读性稍差。

注意事项与最佳实践

1. 坐标系与精度：
   • 本教程采用的勾股定理计算是基于平面坐标系的近似方法。111139 这个系数是一个粗略的平均值，将经纬度转换为米。它在赤道附近比较准确，但随着纬度的增加，经度每度对应的实际距离会减小。
   • 对于需要高精度地理空间计算（例如，跨越较大地理范围或对精度有严格要求）的应用，强烈建议使用 PostGIS 扩展。PostGIS 提供了专门的地理空间数据类型和函数，可以进行更准确的球面距离计算、空间索引等高级操作。
2. 数据类型：
   • 确保 lat 和 lng 列使用浮点数类型，例如 DOUBLE PRECISION，以保证计算精度。
3. 索引：
   • 传统的B-tree索引无法直接优化基于复杂函数计算的 WHERE 或 ORDER BY 子句。
   • 如果使用PostGIS，可以创建空间索引（如GiST索引），这将极大地加速空间查询。
4. 参数化查询：
   • 在实际应用中，myPointLat、myPointLng 和 metres 应该作为参数传递给查询，以防止SQL注入并提高查询性能（通过利用预编译语句）。
5. 性能权衡：
   • 对于数据量较小或查询频率不高的场景，方法一（子查询）的可读性优势可能更重要。
   • 对于数据量大、查询频繁且对性能有较高要求的场景，方法二（重复表达式）通常是更好的选择。在现代PostgreSQL版本中，优化器在某些情况下可能会智能地处理子查询，但直接重复表达式通常更保险地获得最佳性能。

总结

在PostgreSQL中实现地理位置的范围筛选和临近度排序，我们可以选择使用子查询或直接重复表达式。从性能角度考虑，尤其是在处理大量数据时，直接在 WHERE 和 ORDER BY 子句中重复距离计算表达式是更优的选择，因为它允许数据库优化器在排序前先进行高效过滤。然而，对于对精度有更高要求的地理空间应用，引入PostGIS扩展将是更专业和强大的解决方案。理解这两种方法的优缺点，并结合实际需求进行选择，是构建高效数据库查询的关键。

以上就是PostgreSQL中基于勾股定理的地理位置临近度排序优化的详细内容，更多请关注php中文网其它相关文章！