<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<div class="moz-text-html" lang="x-unicode">
<p>Hi,</p>
<p>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)<br>
</p>
<p>Here are two SQL codes to reproduce the described problem (ch_swisstopo_CHENyx06a.tif can be downloaded here
<a class="moz-txt-link-freetext" href="https://cdn.proj.org/">https://cdn.proj.org/</a>):</p>
<p><font face="monospace">SELECT ST_Transform(ST_SetSRID(ST_MakePoint(RANDOM() * 352048 + 485071, RANDOM() * 225680 + 74261), 21781), 2056) AS geom FROM generate_series(1, 10000);
<br>
Time: 33.302 ms</font><br>
</p>
<p><font face="monospace">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);<br>
Time: 19578.107 ms (00:19.578)</font></p>
<p>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).<br>
</p>
<p><font face="monospace">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<br>
</font></p>
<p><font face="monospace">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=<a class="moz-txt-link-freetext" href="https://cdn.proj.org">https://cdn.proj.org</a> 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)"<br>
<br>
</font></p>
<p><span _d-id="2277" class="--l --r sentence_highlight">Appreciate any comments and help on this.</span></p>
<span _d-id="2277" class="--l --r sentence_highlight">Kind regards<br>
Tobias Moser</span> </div>
</body>
</html>