如何高效合并多个3D多边形并写入新表（PostGIS处理CityGML场景）

优化方案 #

一、SQL逻辑优化（核心提升，可减少50%以上IO开销） #

你当前的写法多创建了一次临时表、冗余读取了大量不需要的字段，可直接合并为单步查询，同时过滤无效几何减少计算量：

• 去掉不必要的临时表，避免两次全表扫描建筑表、两次写入中间数据
• 仅读取surface_geometry中需要的root_id和geometry字段，不要用SELECT *拉取全表字段
• 可选：如果你的建筑足迹仅需要地面表面，可先过滤surface_geometry表的objectclass_id对应地面表面的取值（3DCityDB中GroundSurface的objectclass_id通常为33），大幅减少需要做2D转换和合并的几何数量
• 可选：如果不需要数据崩溃恢复能力，创建表时加UNLOGGED关键字，写入速度可提升数倍

优化后SQL示例：

DROP TABLE IF EXISTS citydb.building_geom;
-- 不需要临时表直接单步生成结果
CREATE TABLE citydb.building_geom AS
SELECT a.*, ST_Union(ST_Force2D(b.geometry)) AS geom
FROM citydb.building a
LEFT JOIN citydb.surface_geometry b
ON a.lod2_solid_id = b.root_id
-- 可选：只保留地面表面，过滤其他墙面、屋顶面，不需要合并所有表面投影可保留这行
-- WHERE b.objectclass_id = 33
GROUP BY a.id;

二、索引优化 #

提前建好关联字段的索引，避免关联时全表扫描：

• 若surface_geometry的root_id没有索引，提前创建：CREATE INDEX idx_surface_geom_root_id ON citydb.surface_geometry(root_id);
• 若building的lod2_solid_id没有索引，提前创建：CREATE INDEX idx_building_lod2_solid_id ON citydb.building(lod2_solid_id);
• 处理完成后给最终表的geom列建空间索引，方便后续分析查询：CREATE INDEX idx_building_geom_geom ON citydb.building_geom USING GIST(geom);

三、数据库参数调优（适配Win10虚拟机环境） #

编辑PostgreSQL安装目录下的postgresql.conf，调整以下参数后重启服务生效：

• work_mem：设置为64MB~128MB（根据虚拟机总内存调整，ST_Union这类聚合操作非常吃内存，足够的内存可避免磁盘排序，速度提升非常明显）
• maintenance_work_mem：设置为1GB~2GB，建表、建索引时速度更快
• shared_buffers：设置为虚拟机总内存的25%，比如虚拟机给了8G内存就设为2GB
• effective_cache_size：设置为虚拟机总内存的50%~75%，优化器会更倾向于走索引而不是全表扫
• 大数据处理期间可临时关闭autovacuum，处理完成后再开启，避免后台进程占用资源

四、额外优化建议 #

如果数据量超过千万级，可按建筑id范围分批处理，避免单个事务过大占满临时磁盘空间；如果是定期重复执行该任务，可考虑用触发器增量更新建筑足迹，不用每次全量重算。

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