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

roland.maron at swisstopo.ch roland.maron at swisstopo.ch
Thu Aug 28 07:41:33 PDT 2025


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


More information about the postgis-users mailing list