Using OSTN02 NTv2 projection with PostGIS in Postgres on RDS - can't add nadgrids file OSTN02_NTv2.gsb


I'm trying to convert coordinates in and out of the OSGB 1936 / 27700 / "British National Grid" projection, but I'm getting different results to our old Postgres 9.x local DB. For example, for this query:

SELECT st_asewkt(st_transform(geomfromEWKT('SRID=4326;POINT(-0.077731 54.116851 86.778)'), 927700)) AS a;

On Postgres 9.6 with PostGIS 2.3, where we have the OSTN02 NTv2 projection set up in spatial_ref_sys and using the OSTN02_NTv2.gsb file, I get this result:

SRID=927700;POINT(525745.680454343 470703.162768112 86.778)

However, on our Aurora Postgres 14.5 database using PostGIS 3.2 I get:

SRID=927700;POINT(525744.779056573 470705.4231788935 86.778)

Notice that the eastings and particularly northings values are different - the latter out by a couple of metres.

Both have USE_GEOS=1 USE_PROJ=1 USE_STATS=1 returned by select postgis_version();

Setting up to use OSTN02 NTv2 involves adding the OSTN02_NTv2.gsb file from Ordnance Survey, and referring to it from spatial_ref_sys - for e.g. this spatial_ref_sys entry:

srid      | 927700
auth_name | EPSG
auth_srid | 27700
srtext    | PROJCS["OSGB 1936 / British National Grid",GEOGCS["OSGB 1936",DATUM["OSGB_1936",SPHEROID["Airy 1830",6377563.396,299.3249646,AUTHORITY["EPSG","7001"]],AUTHORITY["EPSG","6277"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4277"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",49],PARAMETER["central_meridian",-2],PARAMETER["scale_factor",0.9996012717],PARAMETER["false_easting",400000],PARAMETER["false_northing",-100000],AUTHORITY["EPSG","27700"],AXIS["Easting",EAST],AXIS["Northing",NORTH]]
proj4text | +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +units=m +no_defs +nadgrids=OSTN02_NTv2.gsb

Note in particular the reference to +nadgrids=OSTN02_NTv2.gsb there at the end of the proj4text column.

I'm at a loss as to how to proceed - I don't think I can install the required file, as, as far as I know, I can't get access to the filesystem of the Aurora database instance hosting the DB, but I think it's not using OSTN02 NTv2 and falling back to the built-in OSTN02 transformation?

this blog post suggests "The NTv2-derived northing differs from that of the built-in transformation by nearly 2m. " which is pretty much exactly what I'm seeing.

Is there any way I can get the OSTN02_NTv2.gsb file onto the DB instance, or any other way to enable OSTN02 NTv2 support?

For reference, that file is found on our Postgres 9.6 (with PostGIS 2.3) local server at /usr/pgsql-9.6/share/contrib/postgis-2.3/proj/OSTN02_NTv2.gsb

Full version information from our Aurora cluster:

postgres=> select aurora_version(), version(), postgis_full_version();
-[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
aurora_version       | 14.5.2
version              | PostgreSQL 14.5 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit
postgis_full_version | POSTGIS="3.2.3 0" [EXTENSION] PGSQL="140" GEOS="3.10.3-CAPI-1.16.1" PROJ="6.2.1" LIBXML="2.9.9" LIBJSON="0.12.99" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)"

This question seems somewhat similar to QUUPbqeryfR5KzFgYxBLx7jg but sadly that's from over a year ago with no answers at all.

asked a year ago316 views
1 Answer

Answering my own question, for the benefit of anyone else trying to find the answer to this - I raised a support ticket, and was told that it's not currently possible, but adding it is on the internal team's roadmap:

Currently the "OSTN02_NTv2.gsb" file which is required for "spatial_ref_sys" is not present on Aurora PostgreSQL cluster, hence you are facing the issue. You cannot directly upload this file in the cluster due to the RDS being a managed service and due to the limited access to the underlying file system of the RDS Aurora. However, I have tried my best to work with the internal team to get any possible workaround so that we can help you but unfortunately, there is none at the present moment, please be assure that our team has tried their best to check every possible workaround and they even replicated the same scenario at their end but we cannot get the working workaround at the moment. I hope you can understand.

Moving ahead, to fix this issue permanently, the required file needs to be packaged and shipped in Aurora PostgreSQL cluster which can be done by our internal team and they will definitely do in the upcoming releases as they have added this in their roadmap.

So, at present, there is no way to do this (use the Ordnance Survey's OSTN02 NTv2 transformation), but it may be possible in future.

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions