we've recently migrated from the EnterpriseDB packaged PostgreSQL 9.5/PostGIS 2.3 to the BigSQL verision (9.6/2.3), PostGIS was installed via PGC. Platform is Windows 7 64bit and data migration was via a database dump.
Unfortunately this has resulted in the PostGIS ST_Transform queries no longer functioning. Previously this would work -
"UPDATE secure.lp_long_mx SET nztm_geom = ST_Transform(nzmg_geom, 2193) WHERE lp_long_mx.cc_x >=2000000 AND lp_long_mx.nztm_geom IS NULL;"
Now it throws the error of -
NOTICE: PostGIS was unable to transform the point because either no grid shift files were found, or the point does not lie within the range for which the grid shift is defined. Refer to the ST_Transform() section of the PostGIS manual for details on how to config
ERROR: transform: couldn't project point (2.67867e+006 6.45903e+006 0): failed to load datum shift file (-38)
If I run the same query on a Linux box where I dumped the same database it runs no problem.
"SELECT PostGIS_Full_Version();" results are these
Windows 7 64bit -
"POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.0.3, released 2016/07/01" LIBXML="2.9.4" RASTER"
Ubuntu 16.04 64bit -
"POSTGIS="2.3.2 r15302" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" RASTER"
Windows spatial_ref_sys on database also seems fine when I run
"select * from spatial_ref_sys where srid in (2193,27200);"
I had a hunt around for other similar problems and saw some references to file locations and checked PostGIS has correctly set the Windows GDAL_DATA Environment Variable (C:\PostgreSQL\gdal-data).
Anyone have any ideas on how to resolve or better diagnose - I'm more a GIS than database persons so am clueless on the debugging side. I'm also stuck on Windows 7 so the Linux box isn't a solution. Seems (naively) to me an issue with the BigSQL PostGIS installation.