PostgreSQL中按勾股定理计算的距离进行高效排序

在处理地理空间数据时，我们经常需要找出某个点附近的其他点，并按距离远近进行排序。例如，给定一个参考点 (mypointlat, mypointlng)，我们希望从 point 表中查询出所有距离该参考点在 metres 范围内的点，并按照它们与参考点的实际距离（或距离的平方）进行升序排列。

最初的查询可能只关注筛选功能，例如使用勾股定理计算距离的平方来判断点是否在指定范围内：

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

这里的 111139 是一个近似的系数，用于将经纬度（度）转换为米，以便进行距离计算。ABS(...) ^ 2 实际上是计算了经度差和纬度差的平方，然后求和，得到的是距离的平方。现在，我们的目标是如何基于这个计算出的距离平方值进行排序。

方法一：使用子查询或CTE封装距离表达式

为了避免在 WHERE 和 ORDER BY 子句中重复复杂的距离计算表达式，我们可以将距离计算封装在一个子查询或公共表表达式（CTE）中，作为一个新的计算列。这样可以提高代码的可读性和维护性。

示例代码（使用子查询）：

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_points
WHERE proximity_squared <= metres^2
ORDER BY proximity_squared;

说明：

• 我们首先在内部子查询中计算了每个点与参考点的 proximity_squared（距离的平方），并将其作为一个新的列。
• 外部查询则基于这个计算出的 proximity_squared 列进行筛选 (WHERE proximity_squared <= metres^2) 和排序 (ORDER BY proximity_squared)。
• Column1, Column2, Column3 应替换为 point 表中你实际需要返回的列名。如果你需要返回所有列，可以使用 SELECT *。

这种方法的优点是代码更加清晰，避免了重复冗长的计算表达式。

方法二：重复表达式以优化性能

尽管子查询方法提高了代码的可读性，但在某些情况下，尤其是在处理大型数据集时，重复距离计算表达式可能会带来更好的性能。这是因为PostgreSQL查询优化器在执行 WHERE 子句时，可以先过滤掉不符合条件的行，从而显著减少需要进行 ORDER BY 操作的数据集大小。

算家云

高效、便捷的人工智能算力服务平台

37

查看详情

当表达式在 WHERE 和 ORDER BY 中重复时，优化器通常能够更好地理解查询意图，并可能在过滤阶段就完成计算，避免对整个表进行不必要的排序。

示例代码（重复表达式）：

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)
);

说明：

• 距离计算表达式在 WHERE 子句和 ORDER BY 子句中都被直接使用。
• PostgreSQL会先执行 WHERE 子句，筛选出符合条件的行。
• 然后，只对这些符合条件的行计算距离并进行排序。
• 对于大型表，这种“先过滤后排序”的策略通常比子查询方法更高效，因为排序操作的开销与待排序的数据量密切相关。

注意事项与最佳实践

1. 单位转换系数： 示例中的 111139 是一个近似值，表示在赤道附近每度纬度或经度（近似）对应的米数。在实际应用中，如果需要更高的精度，特别是在大范围或高纬度地区，应考虑使用更精确的地理空间计算方法，例如PostGIS扩展提供的函数（如 ST_Distance_Sphere 或 ST_DWithin）。然而，对于本教程中基于勾股定理的平面近似距离计算，此系数是常见的做法。
2. 性能权衡： 在选择方法时，需要在代码可读性和查询性能之间进行权衡。对于复杂的、需要频繁修改的距离计算逻辑，子查询或CTE可能更易于管理。但如果性能是首要考虑因素，并且计算表达式相对稳定，重复表达式通常是更优的选择。
3. 索引： 虽然距离计算本身是一个运行时函数，不能直接通过索引加速，但如果 l.lat 和 l.lng 列经常用于其他类型的筛选，为它们创建B-tree索引可能仍然有益。对于地理空间查询，PostGIS的GiST索引通常是更专业的解决方案。
4. 距离平方： 注意，我们排序的是距离的平方 (proximity_squared)，而不是距离本身。这通常是可接受的，因为距离的平方与距离本身具有相同的单调性，即如果A的距离平方小于B的距离平方，那么A的距离也小于B的距离。这样做可以避免开方运算，从而提高计算效率。

总结

在PostgreSQL中，当需要根据计算出的距离（例如使用勾股定理）对结果进行筛选并排序时，主要有两种策略：

• 子查询/CTE方法： 适用于追求代码可读性和避免表达式重复的场景，但可能在性能上略逊一筹。
• 重复表达式方法： 通常能提供更好的查询性能，因为它允许数据库优化器在排序前更有效地进行数据过滤，尤其适用于大型数据集。

选择哪种方法取决于你的具体需求，包括代码维护性要求、数据集大小以及对查询性能的敏感度。在实际部署前，建议对两种方法进行性能测试（使用 EXPLAIN ANALYZE），以确定最适合你应用场景的方案。

以上就是PostgreSQL中按勾股定理计算的距离进行高效排序的详细内容，更多请关注php中文网其它相关文章！