Gridshifts: ST_TransformPipeline very slow compared to ST_Transform

[Tobias Moser]

Hi,

I observed that when there is a gridshift with an external file ST_TransformPipeline seems very slow compared to ST_Transform (when trying to transform many points). To me it looks like ST_TransformPipeline keeps opening the shift file for each transformation, while ST_Transform is able cache the shift file. Could this be a bug? Is this a known issue? Is there a way to solve/workaround this? (hope this is the right place to post)

Here are two SQL codes to reproduce the described problem (ch_swisstopo_CHENyx06a.tif can be downloaded here https://cdn.proj.org/):

SELECT ST_Transform(ST_SetSRID(ST_MakePoint(RANDOM() * 352048 + 485071, RANDOM() * 225680 + 74261), 21781), 2056) AS geom FROM generate_series(1, 10000);
Time: 33.302 ms

SELECT ST_TransformPipeline(ST_SetSRID(ST_MakePoint(RANDOM() * 352048 + 485071, RANDOM() * 225680 + 74261), 21781), '+proj=pipeline +step +inv +proj=somerc +lat_0=46.9524055555556 +lon_0=7.43958333333333 +k_0=1 +x_0=600000 +y_0=200000 +ellps=bessel +step +proj=hgridshift +grids=ch_swisstopo_CHENyx06a.tif +step +proj=somerc +lat_0=46.9524055555556 +lon_0=7.43958333333333 +k_0=1 +x_0=2600000 +y_0=1200000 +ellps=bessel') AS geom FROM generate_series(1, 10000);
Time: 19578.107 ms (00:19.578)

I obtained the pipeline from the command "projinfo -s EPSG:21781 -t EPSG:2056 -o PROJ", which i believe should include the same steps ST_Transform does. There is no difference between the execution times of cct with the specified pipeline and the times of cs2cs. Thats why I think the problem is not caused by proj (but I'm not sure how postgis and proj interact).

PostgreSQL 16.9 (Debian 16.9-1.pgdg130+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

POSTGIS="3.5.3 aab5f55" [EXTENSION] PGSQL="160" GEOS="3.13.1-CAPI-1.19.2" PROJ="9.6.0 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.6.0) LIBXML="2.9.14" LIBJSON="0.18" LIBPROTOBUF="1.5.1" WAGYU="0.5.0 (Internal)"


Appreciate any comments and help on this.

Kind regards
Tobias Moser
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20250604/17682391/attachment.htm>