Roland, Just a clarification here. ST_3DIntersection, just got a renaming to CG_3DIntersection to make it clear it's an SFCGAL function as discussed here - https://trac.osgeo.org/postgis/ticket/5405 so it will still be available in 3.5 and beyond. This is to prevent confusion of what is SFCGAL and what is GEOS -- as we had major issues with ST_3DIntersects (in that the SFCGAL version of it was not accessible because PostGIS GEOS ST_3DIntersects took it's place when we removed the sfcgal backend switch and lots of people complained about ST_3DIntersects being broken cause for example it didn't handle solids anymore.).
So this is just to future proof things That said, I know a number of issues have been addressed since SFCGAL 1.5.0, have you tried upgrading to SFCGAL 1.5.2 https://gitlab.com/sfcgal/SFCGAL/-/releases/v1.5.2 I think the culprit of a lot of these issues is the LHR / RHR orientation which caused a lot of functions such as ST_3DIntersection to fail or hang. So if you can't upgrade to 1.5.2 or higher, try using https://postgis.net/docs/manual-3.4/ST_ForceLHR.html ST_3DIntersection( (SELECT ST_ForceLHR(geom) FROM geo_edge WHERE pk_id = a.EDGE_A), (SELECT ST_ForceLHR(geom) FROM geo_edge WHERE pk_id = a.EDGE_B) ) To see if it fixes your issue. IF that still doesn't please try to isolate some geometries that trigger the issue. > Hi List > > We are encountering a problem when using ST_3DIntersections. > > Context: > A quality-check identifies candidate geometries (error_multipoints). We then > load the corresponding geo_edge.geom values and compute intersections. All > input geometries are valid. > > Query: > WITH id1 AS ( > SELECT > (string_to_array(involved_objects, ';'))[2]::int AS EDGE_A, > (string_to_array(involved_objects, ';'))[4]::int AS EDGE_B > FROM error_multipoints > WHERE user_name = 'test_xxx' > ) > SELECT > a.EDGE_A, > a.EDGE_B, > ST_AsText( > ST_3DIntersection( > (SELECT geom FROM geo_edge WHERE pk_id = a.EDGE_A), > (SELECT geom FROM geo_edge WHERE pk_id = a.EDGE_B) > ) > ) AS INTERSECTIONS_3d > FROM id1 AS a; > > Problem/observation: > - CPU goes to 100% immediately, query never finishes. > - Client never receives a response. > - Process cannot be terminated inside PostgreSQL, only at OS level. > - With limit of 4000 records everything works within seconds. > - With limit of 5000 records the issue always appears → memory leak? > > Setup: > - PostgreSQL 15.12 > - Postgis 3.4 (USE_GEOS=1 USE_PROJ=1 USE_STATS=1) > - SFCGAL 1.5.0 > > Remarks: > - We are aware ST_3DIntersection is deprecated (PostGIS ≥ 3.5), but we still > rely on it in some workflows. > - We are aware that the query could be written more efficiently using JOIN, > but for the sake of reproducibility, we are sticking with this example for the > time being. > > Question: > Has anyone experienced similar hangs with ST_3DIntersection at scale? > Any ideas if this is related to SFCGAL memory handling, or known > limitations/workarounds? > What exactly is happening that prevents me from terminating the process > inside PostgreSQL? > > Thanks for your input and ideas! > Roland
