PostgreSQL中无需ST_Distance查询1英里范围内客户的替代方案

核心思路：手动实现Haversine公式 #

由于不能使用PostGIS的ST_Distance这类空间函数，我们可以用Haversine公式手动计算地球表面两点的大圆距离。该公式基于球面几何，能准确计算经纬度坐标间的距离，单位可通过地球半径参数控制（这里取3956英里，对应地球平均半径的英里值）。

具体查询语句 #

SELECT 
  f.id AS facility_id,
  c.id AS customer_id,
  -- 计算两点间的英里距离
  3956 * 2 * ASIN(
    SQRT(
      POWER(SIN(RADIANS((c.latitude - f.latitude) / 2)), 2) +
      COS(RADIANS(f.latitude)) * COS(RADIANS(c.latitude)) *
      POWER(SIN(RADIANS((c.longitude - f.longitude) / 2)), 2)
    )
  ) AS distance_miles
FROM 
  facility f
JOIN 
  customer c ON 
    -- 先做粗略范围过滤，减少后续复杂计算（可选但推荐，提升性能）
    c.latitude BETWEEN f.latitude - 0.0145 AND f.latitude + 0.0145
    AND c.longitude BETWEEN f.longitude - 0.0145 AND f.longitude + 0.0145
WHERE 
  -- 精确判断距离≤1英里
  3956 * 2 * ASIN(
    SQRT(
      POWER(SIN(RADIANS((c.latitude - f.latitude) / 2)), 2) +
      COS(RADIANS(f.latitude)) * COS(RADIANS(c.latitude)) *
      POWER(SIN(RADIANS((c.longitude - f.longitude) / 2)), 2)
    )
  ) <= 1;

细节说明 #

• 弧度转换：PostgreSQL内置RADIANS()函数，将度数转为弧度（公式要求输入为弧度）。
• 粗略过滤：0.0145度约等于1英里的纬度跨度（经度跨度随纬度变化，这里取近似值缩小计算范围），能大幅减少需要计算Haversine公式的记录数，适合数据量较大的场景。
• 地球半径参数：3956英里是地球平均半径的英里值，如果需要计算公里，替换为6371即可。

可选优化：创建自定义距离函数 #

如果需要多次复用该计算逻辑，可以创建一个自定义函数：

CREATE OR REPLACE FUNCTION calculate_mile_distance(
  lat1 NUMERIC, lon1 NUMERIC,
  lat2 NUMERIC, lon2 NUMERIC
) RETURNS NUMERIC AS $$
BEGIN
  RETURN 3956 * 2 * ASIN(
    SQRT(
      POWER(SIN(RADIANS((lat2 - lat1) / 2)), 2) +
      COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
      POWER(SIN(RADIANS((lon2 - lon1) / 2)), 2)
    )
  );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

之后查询可以简化为：

SELECT 
  f.id AS facility_id,
  c.id AS customer_id,
  calculate_mile_distance(f.latitude, f.longitude, c.latitude, c.longitude) AS distance_miles
FROM 
  facility f
JOIN 
  customer c ON 
    c.latitude BETWEEN f.latitude - 0.0145 AND f.latitude + 0.0145
    AND c.longitude BETWEEN f.longitude - 0.0145 AND f.longitude + 0.0145
WHERE 
  calculate_mile_distance(f.latitude, f.longitude, c.latitude, c.longitude) <= 1;

内容的提问来源于stack exchange，提问作者Praj Patil