如何在BigQuery中计算多组经纬度数据点的质心？

问题背景 #

有包含多组经纬度的数据集，需要按ID分组计算每组的质心（球面坐标的平均位置）。注意：直接对经纬度取平均值会导致误差，因为经纬度是球面坐标系的角度值，正确做法是先转换为三维笛卡尔坐标计算平均，再转回经纬度。

示例数据 #

解决方案：BigQuery SQL查询 #

WITH cartesian_coords AS (
  SELECT
    ID,
    COS(RADIANS(LAT)) * COS(RADIANS(LONG)) AS x,
    COS(RADIANS(LAT)) * SIN(RADIANS(LONG)) AS y,
    SIN(RADIANS(LAT)) AS z
  FROM
    `your-project.your-dataset.your-table`
)
SELECT
  ID,
  DEGREES(ATAN2(AVG(y), AVG(x))) AS CENTROID_LONG,
  DEGREES(ACOS(AVG(z) / SQRT(POWER(AVG(x), 2) + POWER(AVG(y), 2) + POWER(AVG(z), 2)))) AS CENTROID_LAT
FROM
  cartesian_coords
GROUP BY
  ID
ORDER BY
  ID;

代码说明 #

1. 笛卡尔坐标转换：将经纬度转为弧度后，计算每个点的三维笛卡尔坐标(x,y,z)，适配球面坐标系的计算规则。
2. 聚合计算：按ID分组，对x、y、z坐标分别取平均值，得到组内的平均三维坐标。
3. 转回经纬度：用ATAN2推导经度，ACOS推导纬度，再通过DEGREES将弧度转回角度值，得到最终质心坐标。

正确计算后的示例输出 #

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