[PostGIS] ST_3DIntersection query hangs (PostGIS 3.4, PostgreSQL 15.12, SFCGAL 1.5.0)

[Regina Obe]

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