BigQuery无ST_LINE_INTERPOLATE_POINT时，沿LineString均匀生成点的方案

现有某区域内电线杆总数、各道路LineString几何数据，已算出电线杆平均间距，需要将电线杆均匀分布在各道路上。原本计划用以下代码生成插值点，但BigQuery没有ST_LINE_INTERPOLATE_POINT函数，求可行替代方案，同时不确定原方法是否最优：

SELECT road_geometry, ST_LINE_INTERPOLATE_POINT(road_geometry, ratio) AS interpolated_points
FROM road_table, UNNEST(GENERATE_ARRAY(0,1, 1/(road_length/avg_dist_pole))) AS ratio

期望输出每条记录对应的POINT Geography数组。

替代方案：用BigQuery内置的ST_LINE_INTERPOLATE函数 #

BigQuery虽然没有ST_LINE_INTERPOLATE_POINT，但自带的ST_LINE_INTERPOLATE函数可以实现完全相同的逻辑——根据比例值在LineString上生成对应点，直接替换即可满足需求。

具体实现步骤 #

• 先计算每条道路需要的电线杆数量：基于道路长度和平均间距，算出每条路的点数（比如FLOOR(road_length / avg_dist_pole) + 1，+1是为了包含道路起点）；如果有固定总电线杆数要求，需先按道路长度占比分配每条路的配额，再调整间距。
• 生成等间隔比例数组：用GENERATE_ARRAY生成0到1之间的等间隔比例值，间隔为1/(点数-1)（仅当点数大于1时）。
• 生成插值点并聚合：用ST_LINE_INTERPOLATE生成每个比例对应的点，最后聚合为POINT数组。

示例SQL代码 #

WITH road_with_counts AS (
  SELECT
    road_id,
    road_geometry,
    road_length,
    avg_dist_pole,
    -- 计算每条道路的电线杆数量（包含起点）
    CASE 
      WHEN road_length = 0 THEN 0
      ELSE FLOOR(road_length / avg_dist_pole) + 1
    END AS pole_count
  FROM road_table
),
road_with_ratios AS (
  SELECT
    road_id,
    road_geometry,
    pole_count,
    -- 生成等间隔比例数组
    CASE
      WHEN pole_count <= 1 THEN [0.0]
      ELSE GENERATE_ARRAY(0.0, 1.0, 1.0/(pole_count - 1))
    END AS ratios
  FROM road_with_counts
)
SELECT
  road_id,
  road_geometry,
  -- 聚合生成的点为POINT数组
  ARRAY_AGG(ST_LINE_INTERPOLATE(road_geometry, ratio)) AS pole_points
FROM road_with_ratios, UNNEST(ratios) AS ratio
GROUP BY road_id, road_geometry, pole_count

注意事项 #

• 如果需要严格匹配总电线杆数，要先计算所有道路总长度，再按(road_length / total_length) * total_poles分配每条路的配额，避免最终总数与预期偏差。
• 必须处理road_length=0的情况，防止出现除以零的错误。
• ST_LINE_INTERPOLATE的第二个参数是0到1之间的比例值，和你原思路的ratio逻辑完全一致，无需额外调整。

原方法的评估 #

原思路是完全合理的，核心逻辑（按等比例生成间隔点）是实现均匀分布的标准做法，不存在明显的优化空间。唯一的问题是没有适配BigQuery的函数体系，替换成ST_LINE_INTERPOLATE后，原逻辑可以完美迁移。

如果要进一步优化，可以考虑：

• 提前过滤长度为0的道路，减少无效计算。
• 若总电线杆数固定，优先按长度比例分配配额再生成点，避免最后总数不符。

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