在许多应用场景中，我们需要根据地理坐标（经纬度）来查找某个特定点附近的其他点，并按距离远近进行排序。对于小范围内的近似计算，我们可以采用勾股定理来计算两点之间的平面距离。由于经纬度单位与实际距离的换算并非线性，通常会引入一个近似的转换系数（例如，在赤道附近，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 表中你实际需要的列名。
优点：
- 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语句中重复了复杂的表达式，可读性稍差。
注意事项与最佳实践
-
坐标系与精度：
- 本教程采用的勾股定理计算是基于平面坐标系的近似方法。111139 这个系数是一个粗略的平均值，将经纬度转换为米。它在赤道附近比较准确，但随着纬度的增加，经度每度对应的实际距离会减小。
- 对于需要高精度地理空间计算（例如，跨越较大地理范围或对精度有严格要求）的应用，强烈建议使用 PostGIS 扩展。PostGIS 提供了专门的地理空间数据类型和函数，可以进行更准确的球面距离计算、空间索引等高级操作。
-
数据类型：
- 确保 lat 和 lng 列使用浮点数类型，例如 DOUBLE PRECISION，以保证计算精度。
-
索引：
- 传统的B-tree索引无法直接优化基于复杂函数计算的 WHERE 或 ORDER BY 子句。
- 如果使用PostGIS，可以创建空间索引（如GiST索引），这将极大地加速空间查询。
-
参数化查询：
- 在实际应用中，myPointLat、myPointLng 和 metres 应该作为参数传递给查询，以防止SQL注入并提高查询性能（通过利用预编译语句）。
-
性能权衡：
- 对于数据量较小或查询频率不高的场景，方法一（子查询）的可读性优势可能更重要。
- 对于数据量大、查询频繁且对性能有较高要求的场景，方法二（重复表达式）通常是更好的选择。在现代PostgreSQL版本中，优化器在某些情况下可能会智能地处理子查询，但直接重复表达式通常更保险地获得最佳性能。
总结
在PostgreSQL中实现地理位置的范围筛选和临近度排序，我们可以选择使用子查询或直接重复表达式。从性能角度考虑，尤其是在处理大量数据时，直接在 WHERE 和 ORDER BY 子句中重复距离计算表达式是更优的选择，因为它允许数据库优化器在排序前先进行高效过滤。然而，对于对精度有更高要求的地理空间应用，引入PostGIS扩展将是更专业和强大的解决方案。理解这两种方法的优缺点，并结合实际需求进行选择，是构建高效数据库查询的关键。
