[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