PostGIS中ST_3DDifference处理多面体表面耗时过长的优化咨询

问题背景 #

我需要用PostGIS计算山谷中大坝的3D体积：

• dam_polysurf：沿大坝轴线拉伸垂直三角形多边形生成的POLYHEDRALSURFACE
• valley_polysurf：通过以下语句创建：

SELECT ST_Delaunaytriangles (ST_Collect ( ARRAY ( SELECT ( (ST_Dumppoints ( ST_Intersection (mcs.geom, st_buffer(da.geom, 100)) ) ).geom ) from "10m_contours_simp2" mcs, dam_axis da  ) ),  0.0, 2  ) geom INTO test_tin2

SELECT ST_Extrude(tt.geom, 0, 0, -100) geom INTO valley_polysurf FROM test_tin2 tt

当前问题：ST_3DDifference耗时过长 #

执行体积计算语句：

EXPLAIN ANALYZE SELECT ST_3ddifference(dp.geom, vp.geom) FROM dam_polysurf dp, valley_polysurf vp

执行计划显示：

Nested Loop (cost=0.00..485570.60 rows=1849600 width=32) (actual time=579640.077..579640.083 rows=1 loops=1)
-> Seq Scan on dam_polysurf dp (cost=0.00..23.60 rows=1360 width=32) (actual time=0.025..0.025 rows=1 loops=1)
-> Materialize (cost=0.00..30.40 rows=1360 width=32) (actual time=0.030..0.035 rows=1 loops=1)
-> Seq Scan on valley_polysurf vp (cost=0.00..23.60 rows=1360 width=32) (actual time=0.025..0.028 rows=1 loops=1)
Planning Time: 0.560 ms
Execution Time: 579640.261 ms

几何对象细节 #

• valley_polysurf包含767个POLYGONZ（通过SELECT (st_dump(geom)).geom FROM valley_polysurf查询）
• dam_polysurf包含5个POLYGONZ（通过SELECT (st_dump(geom)).geom FROM dam_polysurf查询）

已尝试的无效优化 #

• 添加gist_geometry_ops_nd索引：因每张表仅1行，无性能提升
• 裁剪valley_polysurf到固定Z值：虽减少面数到544，但裁剪本身耗时超过ST_3DDifference节省的时间

环境版本 #

POSTGIS="3.5.2 3.5.2" [EXTENSION] PGSQL="150" GEOS="3.13.0-CAPI-1.19.0" SFCGAL="SFCGAL 1.5.2, CGAL 5.6.1, BOOST 1.84.0" PROJ="8.2.1

优化建议 #

• 简化山谷TIN精度：
  生成test_tin2时，增大ST_Delaunaytriangles的容差参数（原参数为0.0），比如设为1.0或5.0，在满足地形精度需求的前提下，减少生成的三角形数量，降低后续运算复杂度。

• 分步计算替代直接差集：
  先计算大坝体积，再计算大坝与山谷的3D交集体积，最终用大坝体积减去交集体积得到目标值。这种方式避免了生成完整的差集几何，减少运算量：

  -- 计算大坝体积
  SELECT ST_Volume(dp.geom) INTO dam_volume FROM dam_polysurf dp;
  -- 计算大坝与山谷的交集体积
  SELECT ST_Volume(ST_3DIntersection(dp.geom, vp.geom)) INTO intersect_volume FROM dam_polysurf dp, valley_polysurf vp;
  -- 得到最终体积
  SELECT dam_volume - intersect_volume AS final_volume;
  

• 优化山谷多面体生成逻辑：
  生成test_tin2时，除了用st_buffer(da.geom, 100)筛选范围，还可以结合大坝的Z值范围，只保留高于大坝底部的等高线点，减少后续拉伸的面数。

• 升级SFCGAL版本：
  当前使用的SFCGAL 1.5.2版本较旧，新版本（如1.6+）对3D几何运算的性能有针对性优化，尤其是复杂多面体的差集、交集计算，升级后可能显著提升速度。

• 拆分多面体后批量处理：
  将两个多面体拆分为单个POLYGONZ面，先通过ST_3DIntersects过滤出真正相交的面对，再对这些对进行差集运算，最后合并结果：

  -- 拆分大坝和山谷的面
  SELECT dp.id, (ST_Dump(dp.geom)).geom AS face INTO dam_faces FROM dam_polysurf dp;
  SELECT vp.id, (ST_Dump(vp.geom)).geom AS face INTO valley_faces FROM valley_polysurf vp;
  -- 只处理相交的面对并合并结果
  SELECT ST_Collect(geom) INTO final_diff FROM (
      SELECT ST_3DDifference(df.face, vf.face) AS geom
      FROM dam_faces df, valley_faces vf
      WHERE ST_3DIntersects(df.face, vf.face)
  ) t;
  

  这种方式避免了算法处理所有面的两两组合，只运算真正相交的部分，大幅减少计算量。

• 简化大坝几何：
  如果大坝的细节对体积计算影响不大，可使用ST_ApproximateMedialAxis简化大坝轴线，再重新生成dam_polysurf，减少大坝的面数，降低运算复杂度。

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