如何在BigQuery中生成等距外围GEOGPOINT以优化Looker地图展示？

问题根源 #

之前的approx_circle_points函数在高纬度区域生成椭圆，是因为直接采用平面经纬度偏移计算，没考虑地球球面几何特性——经度线在高纬度会收敛，导致横向偏移的实际距离被压缩。要生成准确的球面圆周等距点，必须基于球面投影计算。

优化后的表函数实现 #

以下自定义表函数利用BigQuery原生ST_PROJECT函数（基于球面几何），生成指定中心点、半径、点数的等距圆周点，完全适配全球任意纬度区域：

CREATE OR REPLACE TABLE FUNCTION `your-project.your-dataset.accurate_circle_points`(
  center GEOGRAPHY,
  radius_km FLOAT64,
  num_points INT64
)
RETURNS TABLE (point GEOGRAPHY, azimuth FLOAT64)
AS (
  SELECT
    ST_PROJECT(center, radius_km * 1000, radians(azimuth_deg)) AS point,
    azimuth_deg AS azimuth
  FROM UNNEST(GENERATE_ARRAY(0, 360 - (360 / num_points), 360 / num_points)) AS azimuth_deg
);

函数参数说明

• center: 聚合后的中心点（ST_CENTROID_AGG生成的GEOGRAPHY类型）
• radius_km: 圆周半径（单位：千米，自动转换为米适配ST_PROJECT的距离单位）
• num_points: 圆周上的等距点数量（支持不同城市的自定义需求，如德州6个、佛罗里达7个）

使用示例 #

1. 运行时调用（Looker仪表盘实时计算）

针对德州的聚合中心点生成6个100km半径的圆周点：

SELECT
  city,
  age_bucket,
  cp.point AS circle_point
FROM `your-project.your-dataset.agg_device_data` agg
CROSS JOIN `your-project.your-dataset.accurate_circle_points`(agg.centroid, 100, 6) cp
WHERE agg.city = 'Texas'

2. 预计算（写入聚合表提升性能）

将圆周点预计算到聚合表中，减少Looker运行时计算压力：

CREATE OR REPLACE TABLE `your-project.your-dataset.agg_device_with_circle_points` AS
SELECT
  agg.city,
  agg.age_bucket,
  agg.centroid,
  cp.point AS circle_point,
  cp.azimuth AS point_azimuth
FROM `your-project.your-dataset.agg_device_data` agg
CROSS JOIN `your-project.your-dataset.accurate_circle_points`(
  agg.centroid,
  100,
  CASE agg.city
    WHEN 'Texas' THEN 6
    WHEN 'Florida' THEN 7
    ELSE 10 -- 默认10个点
  END
) cp;

验证准确性 #

可以通过以下SQL验证生成的点是否符合要求：

-- 验证所有点到中心点的距离是否等于指定半径（误差在1米内）
SELECT
  ST_DISTANCE(center, point) / 1000 AS distance_km,
  ABS(ST_DISTANCE(center, point) / 1000 - 100) AS error_km
FROM `your-project.your-dataset.accurate_circle_points`(ST_GEOGPOINT(-122, 37), 100, 10);

-- 验证相邻点之间的球面距离是否相等（误差在1米内）
WITH circle_points AS (
  SELECT point, ROW_NUMBER() OVER() AS rn
  FROM `your-project.your-dataset.accurate_circle_points`(ST_GEOGPOINT(-122, 37), 100, 10)
)
SELECT
  ST_DISTANCE(p1.point, p2.point) / 1000 AS adjacent_distance_km
FROM circle_points p1
JOIN circle_points p2 ON p2.rn = MOD(p1.rn, 10) + 1;

内容的提问来源于stack exchange，提问作者sarath.mec