[postgis-users] ST_3DIntersection slow/hanging with moderately complex shapes

[Rubio Vaughan]

Hi all,

I've noticed that ST_3DIntersection becomes quite slow when feeding it 
somewhat complex shapes.

SELECT PostGIS_Full_Version() returns:
"POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" 
PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.0.1, released 2015/09/15" 
LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY (topology procs from "2.1.4 
r12966" need upgrade) RASTER (sfcgal procs from "2.2.0 r14208" need 
upgrade)"

The query below takes a polygon with 97 vertices and extrudes it 
vertically by 5 meters. It then performs an ST_3DIntersection with a 
simple rectangle, which is also extruded by 5 meters. This takes about 
15-20 seconds to run on our system, during which the postgres process 
appears to hang until the query completes. The query can not be 
cancelled or terminated, only a kill -9 will end the process.

The polygon in the query below is an extract of a larger polygon with 
2451 vertices. Trying to perform the same query with the large polygon 
results in a hanging postgres process.

I would think that the 3D intersection is a relatively simple operation 
which shouldn't be taking so long, but perhaps I'm expecting too much?

Best regards
Rubio Vaughan

Example query below:

WITH
shape1 AS (
  SELECT ST_GeomFromText('POLYGON((121800 487784.532276641,
  121800 487967.485626712,121804.579 487964.71,121804.77 487965.08,
  121815.025 487958.935,121875.85 488060.361,121845.279 488078.9,
  121838.669 488082.91,121834.079 488085.67,121827.787 488089.481,
  121841.234 488111.68,121843.325 488111.306,121845.608 488110.874,
  121847.888 488110.418,121850.162 488109.939,121852.431 488109.436,
  121854.695 488108.91,121856.953 488108.361,121859.206 488107.788,
  121861.452 488107.192,121863.692 488106.573,121865.926 488105.931,
  121868.153 488105.266,121870.373 488104.578,121872.586 488103.867,
  121874.791 488103.133,121876.988 488102.376,121879.178 488101.597,
  121881.359 488100.795,121883.533 488099.971,121885.697 488099.124,
  121887.853 488098.255,121889.999 488097.364,121892.136 488096.45,
  121894.264 488095.515,121896.382 488094.558,121898.49 488093.579,
  121901.641 488098.177,121904.879 488102.859,121908.136 488107.527,
  121911.413 488112.181,121914.709 488116.821,121918.025 488121.447,
  121921.36 488126.059,121924.715 488130.657,121928.089 488135.241,
  121931.483 488139.81,121934.895 488144.366,121938.327 488148.906,
  121941.778 488153.433,121945.248 488157.944,121948.737 488162.441,
  121952.245 488166.924,121980.002239962 488200,122032.222894779 488200,
  121983.281 488141.68,121982.997 488141.319,121979.545 488136.9,
  121976.113 488132.467,121972.699 488128.019,121969.305 488123.557,
  121965.929 488119.08,121962.573 488114.589,121959.236 488110.083,
  121955.918 488105.563,121952.62 488101.029,121949.341 488096.481,
  121946.081 488091.919,121942.842 488087.343,121939.621 488082.753,
  121936.421 488078.15,121933.24 488073.532,121943.262 488065.666,
  121946.55 488062.983,121949.786 488060.239,121952.971 488057.434,
  121956.101 488054.57,121959.178 488051.647,121962.198 488048.667,
  121965.162 488045.631,121968.068 488042.539,121970.916 488039.393,
  121980.701 488028.158,121966.434 488005.12,121934.299 488024.816,
  121873.573 487923.555,121879.31 487920.09,121879.079 487919.72,
  121884.85 487916.23,121885.06 487916.57,121895.39 487910.32,
  121894.649 487909.09,121898.989 487906.46,121919.445 487894.064,
  121882.984 487833.706,121843.375 487814.553,121800 487784.532276641))',
  28992) geom
),
shape2 AS (
  SELECT ST_MakeEnvelope(121820, 487790, 121900, 487870, 28992) geom
),
volume1 AS (
  SELECT ST_Extrude(ST_Force3D(geom), 0, 0, 5.0) vol
  FROM shape1
),
volume2 AS (
  SELECT ST_Extrude(ST_Force3D(geom), 0, 0, 5.0) vol
  FROM shape2
)
SELECT ST_3DIntersection(v1.vol, v2.vol) vol
FROM volume1 v1, volume2 v2