ST_TRANSFORM uses Helmert transformation instead of NTV2 grid (SRID's : 3812 <->31370)

[martinvdb]

When I want to transform coordinates of objects stored in Lambert72 (SRID : 31370) to Lambert2008 SRID : 3812), the ST_TRANSFORM function uses the Helmert transformation. However there is a more precise trasformation using the NTV2 grid. I managed to create that transformation using the new ST_TRANSFORMPIPELINE. However, when I try this transformation in PROJ, the default transformation is the most precise one using the NTV2 grid. My questions are :

Why ST_TRANSFORM still use this old unprecise method? Can I change the behavior of the ST_TRANSFORM to perform the transformation using the NTV2 grid?

[rouault]

@martinvdb Can you post an example (using for example just a point) of the ST_Transform() invokation you tried, what you got and what you expected as result?

[martinvdb]

@rouault

Sorry it took me so long to reply. Yes of course, here is an example of the problem : We have our reference dataset which is : Coordinates in EPSG 31370 : X = 156681.404 ; Y = 238403.825 Coordinates IS EPSG 3812 : X = 656673.068 ; Y = 738404.595

When I use "ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(156681.404,238403.825), 31370), 3812));" I get, on postgreSQL/postGIS : POINT(656672.8903371155 738404.4813479027)

When I use "echo 156681.404 238403.825 | cs2cs epsg:31370 +to epsg:3812 -d 3 —only-best=yes" I get on PROJ : 656673.067 738404.595 Proj_info returns that the best transformation is the one using the NTV2 grid

[robe]

@martinvdb,

Can you send over your

SELECT version(), postgis_full_version();

I tested:

SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(156681.404,238403.825), 31370), 3812));

on:

---

PostgreSQL 17.2 (Ubuntu 17.2-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit POSTGIS="3.5.0 d2c3ca4" [EXTENSION] PGSQL="170" GEOS="3.12.1-CAPI-1.18.1" PROJ="9.4.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.12.1) LIBXML="2.9.14" LIBJSON="0.17" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)"

---

I get the below which is slightly different from your PostGIS answer, though close

POINT(656672.8903371155 738404.4813479035)

When I test on my windows system (both 16 and 17 3.5.0), I get the same answer as yours:

SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(156681.404,238403.825), 31370), 3812));

POINT(656672.8903371155 738404.4813479027)

---

 PostgreSQL 16.0, compiled by Visual C++ build 1935, 64-bit POSTGIS="3.5.0 3.5.0" [EXTENSION] PGSQL="160" GEOS="3.13.0-CAPI-1.19.0" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRI
TABLE_DIRECTORY=C:\Users\Administrator\AppData\Local/proj DATABASE_PATH=C:\Program Files\PostgreSQL\16\share\contrib\postgis-3.5\proj\proj.db" (compiled against PROJ 8.13.0) GDAL="GDAL 3.9.2, released 2024/
08/13" LIBXML="2.12.5" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
(1 row)

Also tested with PG17 windows

However when I unremark out the network = on line in my C:\Program Files\PostgreSQL\16\share\contrib\postgis-3.5\proj\proj.ini

Then do a new PG session connection and run the above query, I get a different answer of below which is close to the cs2cs answer you received.

POINT(656673.0670463315 738404.5954985798)

When you are testing with cs2cs you are perhaps using the one shipped with QGIS which is probably a different version and has more grid files? I'm thinking issue is just missing grid files given I get answer similar to cs2cs when I enable network support.

I know for example on windows I don't download the full set of grid files as they take up too much space and network enabled is also turned off on my windows distribution. If you are on windows, all you need to do is look in the folder shown in your postgis_full_version() output (new for 3.5.0 sorry so for older you'll need to guess a bit if you have more than one postgresql install.

This rule of turning on network support should work on any system unless they compiled proj without curl support. I think most distros compile it with curl support though.