Gridshifts: ST_TransformPipeline very slow compared to ST_Transform

[Paul Ramsey]

> On Jun 4, 2025, at 9:10 AM, Tobias Moser <tobias.moser at wsl.ch> wrote:
> 
> 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)
> 
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. 

A workaround would be to stuff things into collections so that each function call addresses a large number of vertices. 

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.

ATB,

P


> 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 <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/6330ad6f/attachment.htm>