[PostGIS] #5735: ST_TRANSFORM uses Helmert transformation instead of NTV2 grid (SRID's : 3812 <->31370)

[PostGIS]

#5735: ST_TRANSFORM uses Helmert transformation instead of NTV2 grid (SRID's :
3812 <->31370)
-------------------------+---------------------------------------
  Reporter:  martinvdb   |      Owner:  rouault
      Type:  defect      |     Status:  closed
  Priority:  medium      |  Milestone:  PostGIS 3.4.4
 Component:  postgis     |    Version:  3.4.x
Resolution:  worksforme  |   Keywords:  ST_TRANSFORM Belgium NTV2
-------------------------+---------------------------------------
Changes (by robe):

 * resolution:   => worksforme
 * status:  new => closed

Comment:

 @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.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5735#comment:5>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.