PostgreSQL中如何统计现有缓冲区内的人口并写入Operation表新字段?
问题修正与实现方案
原有SQL错误点
- 子查询语法错误,
SELECT pop_per_sq count(*)不符合SQL语法规范,未指定正确的聚合逻辑 - 需求是统计人口总和,不应使用计数函数
count(*),需使用求和函数SUM()对人口字段ew做聚合 - 空间关系判断逻辑不符合需求:
ST_Contains(o.buffer,s.geom)仅会统计完全落在缓冲区范围内的人口多边形,部分和缓冲区重叠的多边形不会被纳入统计,通常这类场景使用ST_Intersects判断重叠关系即可,若需要按重叠面积比例折算人口可参考进阶方案
基础实现(统计所有和缓冲区有重叠的人口多边形的总人口)
-- 新增人口字段 ALTER TABLE public."Operation" ADD COLUMN population int; -- 更新人口统计值 UPDATE public."Operation" o SET population = COALESCE(( SELECT SUM(s.ew) FROM public."Restaurants" s WHERE ST_Intersects(o.buffer, s.geom) ), 0);
说明:使用
COALESCE函数是为了处理缓冲区没有覆盖任何人口多边形的情况,避免返回NULL值,会自动赋值为0。
进阶实现(按重叠面积比例折算人口,精度更高)
如果人口多边形面积较大,缓冲区只覆盖了多边形的一部分,直接统计全量人口会有误差,可以按重叠面积占比折算人口:
ALTER TABLE public."Operation" ADD COLUMN population numeric; UPDATE public."Operation" o SET population = COALESCE(( SELECT SUM(s.ew * ST_Area(ST_Intersection(o.buffer, s.geom)) / ST_Area(s.geom)) FROM public."Restaurants" s WHERE ST_Intersects(o.buffer, s.geom) ), 0);
说明:这里字段类型改用
numeric存储折算后的小数人口值,如果需要取整可在外层包裹ROUND()函数。
内容的提问来源于stack exchange,提问作者Aravinth
相关产品推荐
相关产品推荐

