Windows 7 BigSQL packaged PostGIS ST_Transformation queries not working

Question

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.

Answer 1

geometry does not use GDAL so has nothing to do with that setting. There is a proj folder containing the nad shift files. I have to check if BigSQL is distributing those. They might not be. I think there is some hard-coding in PostGIS code to try to look for those in share/contrib/postgis-2.3.

I haven't checked to see how that plays with BigSQL. You could try copying the files from:

http://postgis.net/windows_downloads/ (look in the winnie section, vor your PostgreSQL version/ PostGIS version.

Try to copy the files in share/contrib/postgis-2.3/proj to your share\postgresql\contrib\postgis-2.3\proj

of your bigsql install.

If that doesn't work, put in a ticket in our tracker: http://postgis.net/support/

The paths of BigSQL are a little different, so might have to make some changes to accommodate or let them know what variable they need to set.

Also provide a specific geometry so I can test it.

Answer 2

Thanks for helping out on this issue Regina!

Ronald, we will include the proj folder in our next release. We have recently begun to support PostGIS in our distributions in an effort to help GISers across all platforms. Thanks for helping us make our distribution better - and feel free to provide feedback to https://www.bigsql.org/forum if you find any other issues or have suggestions for improvements.

Best, Holly Orr Senior Technical Manager OpenSCG

Answer 3

Hi Ronald and Regina -

Proj datum shift files have been included in the new binaries postgis23-pg96-2.3.3-1 across all the platforms. They are located in bigsql/pg96/share/postgresql/contrib/postgis-2.3/proj.

Look at this blog post for instructions for installing the BigSQL distribution and/or updating to the latest release: https://www.openscg.com/2017/07/postgis-2-3-3-is-now-available/

Please tell us if you have any further issues or questions!