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

Question

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](https://www.ordnancesurvey.co.uk/business-government/tools-support/os-net/format) 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](http://blog.mackerron.com/2012/07/03/ostn02-for-postgis/) 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](https://repost.aws/questions/QUUPbqeryfR5KzFgYxBLx7jg) but sadly that's from over a year ago with no answers at all.

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.

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

Relevant content