如何在BigQuery中基于经纬度计算两点距离并新增distance列？

BigQuery提供了地理空间函数可直接计算两点间的球面距离，以下是针对你场景的具体操作方案：

一、临时查询验证计算结果 #

如果只是想先查看计算结果，无需永久修改表，可使用以下SQL：

SELECT
  start_lat,
  end_lat,
  start_lng,
  end_lng,
  -- 计算两点距离（单位：米）
  ST_DISTANCE(
    ST_GEOGPOINT(start_lng, start_lat),  -- 起点经纬度转地理点（注意顺序：经度在前，纬度在后）
    ST_GEOGPOINT(end_lng, end_lat)       -- 终点经纬度转地理点
  ) AS distance,
  -- 可选：转为公里单位
  ST_DISTANCE(ST_GEOGPOINT(start_lng, start_lat), ST_GEOGPOINT(end_lng, end_lat)) / 1000 AS distance_km
FROM `your-project.your-dataset.your-table`;

二、永久新增distance列到表中 #

如果需要把计算结果永久存储到表中，分两种场景操作：

1. 自动计算的生成列（推荐） #

新增一个生成列，后续插入或更新数据时，distance会自动计算，无需手动维护：

ALTER TABLE `your-project.your-dataset.your-table`
ADD COLUMN distance FLOAT64
GENERATED ALWAYS AS (
  ST_DISTANCE(
    ST_GEOGPOINT(start_lng, start_lat),
    ST_GEOGPOINT(end_lng, end_lat)
  )
) STORED;

• STORED表示计算结果会存储在表中，而非每次查询时临时计算

2. 一次性计算现有数据 #

如果不需要自动更新，仅为现有数据计算距离：
首先新增空列：

ALTER TABLE `your-project.your-dataset.your-table`
ADD COLUMN distance FLOAT64;

然后更新所有行的distance值：

UPDATE `your-project.your-dataset.your-table`
SET distance = ST_DISTANCE(
  ST_GEOGPOINT(start_lng, start_lat),
  ST_GEOGPOINT(end_lng, end_lat)
)
WHERE TRUE; -- 匹配所有行

关键注意事项 #

• 经纬度顺序：ST_GEOGPOINT的参数是经度（lng）在前，纬度（lat）在后，切勿颠倒，否则计算结果完全错误
• 单位说明：ST_DISTANCE默认返回单位为米，如需公里，除以1000即可
• 权限要求：执行ALTER TABLE和UPDATE语句需要数据表的编辑权限

用你的示例数据测试，第一行的计算结果约为3975米（或3.98公里），符合实际地理距离。

内容的提问来源于stack exchange，提问作者Sandro Lélis