I have an issue with my new (CentOS) postGIS setup not transforming WGS84 to BNG via OSTN15 correctly. It all worked beautifully in postGIS2 with proj4.8. As recommended, the relevant entry is in the PostGIS spatial_ref_sys file, with a custom srid 27789 :
"+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +units=m +no_defs +nadgrids=/usr/share/proj/OSTN15_NTv2_OSGBtoETRS.gsb"
ST_Transform(geom,4326,27789) and vice versa both worked as expected, picking up the gsb file..
However, With my latest PostGIS3 and proj7.2 build, the transform no longer works. It gives the standard transform rather than the OSTN15 one, as if it is no longer using the gsb file. gsb file permissions are identical between my builds, and the gsb file location has not changed.
Entering the custom proj string into the ST_Transform command directly works OK. For some reason it is not picked up from spatial ref sys anymore. Any ideas why please?
i.e. running the following code:
postGIS2 : "coords_expected" and "coords_via_OSTN15_spatialrefsys" are the same.
postGIS3 : "coords_expected" and "coords_via_OSTN15_spatialrefsys" are different -they ignore the OSTN15 transform.
postGIS3 : "coords_expected" and "coords_via_OSTN15_manual" are the same, so postGIS can read the gsb files.
--This to verify whether the OSTN15 transform is working or not...
select uprn,
x_coordinate::text || '_' || y_coordinate::text as coords_expected,
round(ST_X(geomBNG_OSTN15_srs))::text || '_' || round(ST_Y(geomBNG_OSTN15_srs))::text as coords_via_OSTN15_spatialrefsys,
round(ST_X(geomBNG_OSTN15_manual))::text || '_' || round(ST_Y(geomBNG_OSTN15_manual))::text as coords_via_OSTN15_manual
from
( --do a OSTN15 transform on the latlon - one via spatial ref sys (srs) - other via 4326/OSTN15 params from spatial ref sys (manual)
select t1.*,
ST_Transform(geom4326, 27789) as geomBNG_OSTN15_srs,
ST_Transform(geom4326, '+proj=longlat +datum=WGS84 +no_defs', '+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +units=m +no_defs +nadgrids=/usr/share/proj/OSTN15_NTv2_OSGBtoETRS.gsb') as geomBNG_OSTN15_manual
from
(
--coords from OS ABP... make latlon geom
select '100090925668' as uprn,
620537 as x_coordinate, 308016 as y_coordinate,
52.6251053 as latitude, 1.2569831 as longitude,
cast(ST_SetSRID(ST_MakePoint(1.2569831, 52.6251053),4326) as geometry(Point,4326)) as geom4326
) t1
) t2
;
--output showing the OSTN15 spatialrefsys transform is being ignored
"uprn" "coords_expected" "coords_via_ostn15_spatialrefsys" "coords_via_ostn15_manual"
"100090925668" "620537_308016" "620538_308019" "620537_308016"
Other details :
--public.spatial_ref_sys entry (excluding srtext field for conciseness)
select srid, auth_name, auth_srid from public.spatial_ref_sys where srid in (4326, 27789);
"srid" "auth_name" "auth_srid" "proj4text"
4326 "EPSG" 4326 "+proj=longlat +datum=WGS84 +no_defs "
27789 "EPSG" 27700 "+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +units=m +no_defs +nadgrids=/usr/share/proj/OSTN15_NTv2_OSGBtoETRS.gsb"
--postgis build details
select postgis_full_version();
POSTGIS="3.0.3 6660953" [EXTENSION] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" PROJ="7.2.0" GDAL="GDAL 3.2.0, released 2020/10/26" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" WAGYU="0.4.3 (Internal)" RASTER
There are no errors reported, just an inaccurate answer (i.e. worst case scenario).
