QGIS – SQL Data Offset

by David Crowther

Question:

When opening my SQL Spatial Layer into QGIS, the features appear offset/shifted. However, when shown as spatial data in my webGIS or via GeoServer the features are in the correct location – what is causing this shift?

Answer:

This issue could be related to using a newer version of QGIS, where it is not quite recognising the CRS of the SQL Table and so applies an incorrect or older Transformation.

In QGIS, there are a number of options to edit the default Projection (CRS) Settings for QGIS – Settings > Options > CRS:

Here you can define the CRS for:

• New Projects
• Layers

Working in the UK, we would recommend that these are set to EPSG:27700/BNG.

However, even with these settings chosen, if you add a Table from SQL, QGIS will implement a Transformation to render this onto the map. There are a number of Transformation options for EPSG:27700 with more accurate Transformations added each year.

So, even if your source SQL Table has a SRID applied:

when this is opened in older versions of QGIS e.g. QGIS 2.x, the layer renders as expected in the correct location.

However, the exact same layer may be offset/shifted when opened into QGIS 3.16.

In order to correct this Transformation issue, simply open the Layer Properties > and in the Source Tab change the Assigned Coordinate Reference System (CRS) to override the Layers CRS and apply the correct CRS as required.

Here we can see that the Layer has a current assigned CRS of OSGB 1936/BNG – but we want to change that to a newer and more accurate Transformation of EPSG:27700 – OSGB 1936/BNG.

Once you Apply that change, the layers transformation in the map will be correctly applied and the offset/shift is resolved.

If you save your QGIS Project > the Transformation for that SQL Table will be applied and the layer will open in the correct location.