3

I have access to a remote DB with the following specifications:

PostgreSQL 11.12

POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.0-CAPI-1.11.0 673b9939" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.3.1, released 2018/06/22" LIBXML="2.9.1" LIBJSON="0.12.1" LIBPROTOBUF="1.3.0" TOPOLOGY RASTER

I also have the following polygon in British National Grid (27700):

select st_geometryfromtext('POLYGON ((624172 308463.88, 624168.25 308465.95, 624172.25 308473.05, 624169.3 308474.62, 624161 308459.58, 624166.68 308456.55, 624172 308463.88))', 27700)

When I transform the polygon to 4326, it is completely misaligned with the base map layer. Here I use ESRI Topo layer because it seems to be better aligned with UK data.

select st_transform(st_geometryfromtext('POLYGON ((624172 308463.88, 624168.25 308465.95, 624172.25 308473.05, 624169.3 308474.62, 624161 308459.58, 624166.68 308456.55, 624172 308463.88))', 27700), 4326)

enter image description here

When I run this query, I have no choice of picking a separate transformation so I assume the default transformation between 27700 and 4326 is being used.

However, if I load the same polygon to QGIS I am given the choice to pick between various transformations: enter image description here

Both OSTN02 & OSTN15 transformations were downloaded from Ordnance Survey and installed the .gsb files locally. Since OSTN15 is considered the most accurate, I used it to transform the polygon and the result aligns better with the base map:

enter image description here

This is how the 2 transformations compare to each other: enter image description here

Since the database is remote, I cannot install a .gsb file with the correct transformation nor add any extension.

My question is: Is there a way to use the correct transformation within my query?

If I look at the projections in the DB:

SELECT * FROM spatial_ref_sys WHERE srid = 27700

I get this projection:

+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +datum=OSGB36 +units=m +no_defs

Is there another, more accurate, proj4text I could use?

Improve this question
1
  • if you can't install the gsb file I doubt you can write to the spatial_ref table either. Just ask the remote operator to install the gsb file, it will help all their GB users
    – Ian Turton ♦
    Mar 1, 2022 at 17:28

1 Answer 1

Reset to default
0

You have to go through a couple of transformations to achieve an accurate representation of your data (as well as using the OSTN file) Try this:

ST_Transform(ST_Transform(ST_SETSRID(geom,27700),4277),4326) as geom_4326_WGS84

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.