如何从存储在BigQuery的linestring大型数据集中查找所有道路交叉口

核心问题说明 #

原PostGIS方案无法直接迁移的核心原因是：BigQuery不支持对GEOGRAPHY类型字段直接执行GROUP BY分组操作，我们需要将交叉口的地理结果转换为可分组的标量类型再做聚合。

可用实现代码 #

WITH road_intersections AS (
  SELECT
    -- 计算两条道路的交点
    ST_Intersection(a.geom, b.geom) AS intersection_point,
    -- 转成WKT文本用于后续分组，也可以用ST_Hash生成更短的哈希值降低计算开销
    ST_AsText(ST_Intersection(a.geom, b.geom)) AS intersection_wkt,
    a.gid AS a_gid,
    b.gid AS b_gid
  FROM
    `你的项目.你的数据集.道路表` a
  CROSS JOIN
    `你的项目.你的数据集.道路表` b
  WHERE
    -- 过滤只有相接的道路对，避免全表笛卡尔积的巨大开销
    ST_Touches(a.geom, b.geom)
    -- 用<代替!=可以避免重复计算(a,b)和(b,a)两组相同的道路对，性能提升一倍
    AND a.gid < b.gid
)
SELECT
  -- 最终返回地理类型的交点
  ANY_VALUE(intersection_point) AS intersection_geo,
  -- 统计交汇的道路数量
  COUNT(DISTINCT a_gid) + COUNT(DISTINCT b_gid) AS road_count
FROM road_intersections
-- 过滤掉异常的非点交点（比如两条路部分重叠产生的线结果，根据需求可选）
WHERE ST_GeometryType(intersection_point) = 'POINT'
GROUP BY intersection_wkt

优化建议 #

• 数据量较大时可以先给道路表做空间索引，或者添加ST_DWithin(a.geom, b.geom, 0)过滤条件，进一步降低计算开销
• 如果不需要保留完整WKT做校验，可以用ST_Hash(intersection_point)代替ST_AsText，计算和分组的性能会更高
• 若要排除立交桥等非平面交叉口，可以额外添加高程判断逻辑

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