<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body style="overflow-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;"><br id="lineBreakAtBeginningOfMessage"><div><br><blockquote type="cite"><div>On Jun 4, 2025, at 9:10 AM, Tobias Moser <tobias.moser@wsl.ch> wrote:</div><br class="Apple-interchange-newline"><div>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<div>
<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></div></div></div></blockquote><div>Kind of known. There is a caching mechanism for projection objects in PostGIS, but the cache is keyed on the SRID of the projection. Pipelines don’t have a simple integer key, so they and other non-SRID based forms of reprojection are not cached. Since they are not used that often, we don’t get a lot of complaints. </div><div><br></div><div>A workaround would be to stuff things into collections so that each function call addresses a large number of vertices. </div><div><br></div><div>A fix would be a major re-work of the proj cache to use text keys, which might have other knock-on performance implications (each cache check costs a string cmp, rather than an integer cmp.</div><div><br></div><div>ATB,</div><div><br></div><div>P</div><div><br></div><br><blockquote type="cite"><div><div><div class="moz-text-html" lang="x-unicode"><p>
</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>
</div>
</div></blockquote></div><br></body></html>