BigQuery批量计算多点位周边缓冲区域空间统计数据实现方法

适用场景 #

一次性完成全量图书馆点位周边1km半径范围内的社会人口数据统计，替代原有单点位逐次查询的低效逻辑。

依赖数据表 #

• cis-sdhis.de.biblioteca（图书馆点位表）：存储GEOGRAPHY类型的点位空间数据，包含图书馆名称title字段
• cis-sdhis.inegi.resageburb（人口统计表）：存储P_60YMAS（60岁以上人口）等社会人口指标，以及GEOGRAPHY类型的行政多边形空间字段GEOMETRY

实现逻辑说明 #

原单点位查询通过子查询硬编码单个图书馆id生成缓冲区做过滤，批量场景不需要用窗口函数，直接通过空间关联+分组聚合即可实现：

1. 遍历图书馆表，为每一个图书馆点位生成1000米（1km）半径的缓冲区
2. 通过空间相交判断，匹配所有和对应图书馆缓冲区存在重叠的人口统计多边形
3. 按图书馆名称分组，聚合计算缓冲区内的60岁以上人口总和，同时将匹配到的多边形合并为多面几何对象

注意：原有单点位代码使用ST_WITHIN仅会统计完全落在缓冲区内的多边形，会漏掉跨缓冲区边界的部分，存在统计偏差，批量实现默认使用ST_INTERSECTS匹配所有存在重叠的多边形，若业务明确要求仅统计完全包含的多边形，可将关联条件替换回ST_WITHIN

可直接运行的SQL代码 #

WITH library_with_buffer AS (
    -- 批量生成所有图书馆的1km缓冲区
    SELECT
        title,
        ST_BUFFER(geography, 1000) AS buffer_geom
    FROM `cis-sdhis.de.biblioteca`
)
SELECT
    lwb.title,
    SUM(rb.P_60YMAS) AS buffer_p_60ymas,
    ST_UNION_AGG(rb.GEOMETRY) AS buffer_intersect_geom
FROM library_with_buffer lwb
LEFT JOIN `cis-sdhis.inegi.resageburb` rb
    ON ST_INTERSECTS(rb.GEOMETRY, lwb.buffer_geom)
GROUP BY lwb.title

可选优化（精准统计） #

上述代码直接对匹配到的多边形人口字段求和，会把跨缓冲区边界的多边形整个人口值计入结果，若需要按实际落在缓冲区内的面积占比分摊人口，提升统计精度，可将SUM部分替换为加权计算逻辑：

SUM(
    rb.P_60YMAS * 
    ST_AREA(ST_INTERSECTION(rb.GEOMETRY, lwb.buffer_geom)) / ST_AREA(rb.GEOMETRY)
) AS buffer_p_60ymas_weighted

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