PostgreSQL计算多城市经纬度距离并生成指定结果表

前提假设 #

基于问题描述，先明确已知结构的合理假设（如需调整可根据实际表/函数修改）：

• public."Coor"表结构：

  CREATE TABLE public."Coor" (
      city_name VARCHAR(50) PRIMARY KEY,
      latitude NUMERIC(9,6),
      longitude NUMERIC(9,6)
  );
  

• calculate_distance函数：接受起点纬度、起点经度、终点纬度、终点经度四个参数，返回英里单位的距离（若函数返回公里，后续转换逻辑反向调整即可）。
• public."distance"表结构（用于存储结果）：

  CREATE TABLE public."distance" (
      city1 VARCHAR(50),
      city2 VARCHAR(50),
      distance_miles NUMERIC(8,2),
      distance_km NUMERIC(8,2),
      PRIMARY KEY (city1, city2)
  );
  

1. 计算两两城市距离并写入目标表 #

通过自连接获取三个城市的无重复两两组合，调用函数计算距离并完成单位转换，最终插入public."distance"表：

INSERT INTO public."distance" (city1, city2, distance_miles, distance_km)
SELECT
    c1.city_name AS city1,
    c2.city_name AS city2,
    calculate_distance(c1.latitude, c1.longitude, c2.latitude, c2.longitude) AS distance_miles,
    ROUND(calculate_distance(c1.latitude, c1.longitude, c2.latitude, c2.longitude) * 1.60934, 2) AS distance_km
FROM public."Coor" c1
JOIN public."Coor" c2 ON c1.city_name < c2.city_name
WHERE c1.city_name IN ('Chennai', 'Kochi', 'Mysore')
  AND c2.city_name IN ('Chennai', 'Kochi', 'Mysore');

• 说明：c1.city_name < c2.city_name确保只生成无序对（如仅保留Chennai-Kochi，避免重复计算Kochi-Chennai），若需要双向组合，移除该条件即可；ROUND(...,2)用于保留两位小数，优化结果可读性。

2. 输出带双单位的格式化结果 #

通过字符串拼接生成友好格式的输出：

SELECT
    CONCAT(city1, ' ↔ ', city2, ': ', distance_miles, ' miles (', distance_km, ' km)') AS distance_info
FROM public."distance";

输出示例：

Chennai ↔ Kochi: 303.45 miles (488.40 km)
Chennai ↔ Mysore: 245.12 miles (394.40 km)
Kochi ↔ Mysore: 192.78 miles (310.25 km)

补充：覆盖已有记录的Upsert逻辑 #

如果需要更新已存在的城市对距离数据，在INSERT语句末尾追加冲突处理：

INSERT INTO public."distance" (city1, city2, distance_miles, distance_km)
SELECT
    c1.city_name AS city1,
    c2.city_name AS city2,
    calculate_distance(c1.latitude, c1.longitude, c2.latitude, c2.longitude) AS distance_miles,
    ROUND(calculate_distance(c1.latitude, c1.longitude, c2.latitude, c2.longitude) * 1.60934, 2) AS distance_km
FROM public."Coor" c1
JOIN public."Coor" c2 ON c1.city_name < c2.city_name
WHERE c1.city_name IN ('Chennai', 'Kochi', 'Mysore')
  AND c2.city_name IN ('Chennai', 'Kochi', 'Mysore')
ON CONFLICT (city1, city2) DO UPDATE 
SET distance_miles = EXCLUDED.distance_miles, 
    distance_km = EXCLUDED.distance_km;

内容的提问来源于stack exchange，提问作者KALYANA ALLAM