1

If I load data that's held in EPSG:27700 (British National Grid) on a MSSQL Spatial database (v13.0.5622.0) into an instance of QGIS 3.10 (new project, defaulted to 27700), the CRS does not get recognized, and I am prompted for a transformation between an unknown CRS and EPSG:27700.

The resulting map data is misaligned by about 1 m, as compared with either EPSG:27700 data from a geopackage or a QGIS 3.4 layer definition file of the same MSSQL data table.

There were/are no such problems using QGIS 3.4

The WKT in the loaded layer is:

BOUNDCRS[
    SOURCECRS[
        PROJCRS["OSGB 1936 / British National Grid",
            BASEGEOGCRS["OSGB 1936",
                DATUM["OSGB 1936",
                    ELLIPSOID["Airy 1830",6377563.396,299.3249646,
                        LENGTHUNIT["metre",1
                        ]
                    ]
                ],
                PRIMEM["Greenwich",0,
                    ANGLEUNIT["degree",0.0174532925199433
                    ]
                ],
                ID["EPSG",4277
                ]
            ],
            CONVERSION["unnamed",
                METHOD["Transverse Mercator",
                    ID["EPSG",9807
                    ]
                ],
                PARAMETER["Latitude of natural origin",49,
                    ANGLEUNIT["degree",0.0174532925199433
                    ],
                    ID["EPSG",8801
                    ]
                ],
                PARAMETER["Longitude of natural origin",-2,
                    ANGLEUNIT["degree",0.0174532925199433
                    ],
                    ID["EPSG",8802
                    ]
                ],
                PARAMETER["Scale factor at natural origin",0.9996012717,
                    SCALEUNIT["unity",1
                    ],
                    ID["EPSG",8805
                    ]
                ],
                PARAMETER["False easting",400000,
                    LENGTHUNIT["metre",1
                    ],
                    ID["EPSG",8806
                    ]
                ],
                PARAMETER["False northing",-100000,
                    LENGTHUNIT["metre",1
                    ],
                    ID["EPSG",8807
                    ]
                ]
            ],
            CS[Cartesian,2
            ],
            AXIS["easting",east,
                ORDER[1
                ],
                LENGTHUNIT["metre",1
                ]
            ],
            AXIS["northing",north,
                ORDER[2
                ],
                LENGTHUNIT["metre",1
                ]
            ],
            ID["EPSG",27700
            ]
        ]
    ],
    TARGETCRS[
        GEOGCRS["WGS 84",
            DATUM["World Geodetic System 1984",
                ELLIPSOID["WGS 84",6378137,298.257223563,
                    LENGTHUNIT["metre",1
                    ]
                ]
            ],
            PRIMEM["Greenwich",0,
                ANGLEUNIT["degree",0.0174532925199433
                ]
            ],
            CS[ellipsoidal,2
            ],
            AXIS["latitude",north,
                ORDER[1
                ],
                ANGLEUNIT["degree",0.0174532925199433
                ]
            ],
            AXIS["longitude",east,
                ORDER[2
                ],
                ANGLEUNIT["degree",0.0174532925199433
                ]
            ],
            ID["EPSG",4326
            ]
        ]
    ],
    ABRIDGEDTRANSFORMATION["Transformation from OSGB 1936 to WGS84",
        METHOD["Position Vector transformation (geog2D domain)",
            ID["EPSG",9606
            ]
        ],
        PARAMETER["X-axis translation",446.448,
            ID["EPSG",8605
            ]
        ],
        PARAMETER["Y-axis translation",-125.157,
            ID["EPSG",8606
            ]
        ],
        PARAMETER["Z-axis translation",542.06,
            ID["EPSG",8607
            ]
        ],
        PARAMETER["X-axis rotation",0.15,
            ID["EPSG",8608
            ]
        ],
        PARAMETER["Y-axis rotation",0.247,
            ID["EPSG",8609
            ]
        ],
        PARAMETER["Z-axis rotation",0.842,
            ID["EPSG",8610
            ]
        ],
        PARAMETER["Scale difference",0.999979511,
            ID["EPSG",8611
            ]
        ]
    ]
]

OSTN15 is installed, and is listed at the transformation selection stage when an MSSQL EPSG:27700 layer is added

The text to the left of the little map reads the following when the topmost transformation is selected

Inverse of unnamed + Transformation from OSGB 1936 to WGS84 + Inverse of OSGB 1936 to > WGS 84 (9) + British National Grid

Scope: Accuracy 1m. Remarks: Parameter values taken from OSGB 1936 to ETRS89 (3) (tfm code 7709) assuming > that ETRS89 is coincident with WGS 84 within the accuracy of the tfm. Replaces OSGB > 1936 to WGS 84 (7) (tfm code 5339). Area of use: UK - Britain and UKCS 49°46'N to 61°01'N, 7°33'W to 3°33'E

Identifiers: INVERSE(EPSG):7710, EPSG:19916

+proj=pipeline +step +inv +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 > +y_0=-100000 +ellps=airy +step +proj=push +v_3 +step +proj=cart +ellps=airy +step > > +proj=helmert +x=446.448 +y=-125.157 +z=542.06 +rx=0.15 +ry=0.247 +rz=0.842 +s=-20.489 > +convention=position_vector +step +inv +proj=cart +ellps=WGS84 +step +proj=pop +v_3 > +step +inv +proj=hgridshift +grids=OSTN15_NTv2_OSGBtoETRS.gsb +step +proj=tmerc > +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy

Using the "Assign Projection" processing tool fixes it, as does just editing the spatialrefsys entry in a layer definition file, but that's at the expense of having to create a layer definition file for all the layers on the database so my users don't get confused.

Can anyone recommend any other way forward, other than reverting to 3.4?

  • This might be related to this issue: github.com/qgis/QGIS/issues/34471 It seems starting from 3.10 they changed the way CRS recognition works and there are still some hiccups. – miln40 Mar 17 at 10:06
  • The WKT is as in QGIS layer properties after accepting the default transformation when the layer is added from MSSQL into a project that's using 27700. When I assign a projection, I'm selecting 27700, and the WKT is changed to match – Andrew_S Mar 17 at 11:59
  • @Andrew_S, can you test if the problem persist after saving OSTN15_NTv2_OSGBtoETRS.gsb grid file in your \share\proj folder? – Gabriel De Luca Mar 17 at 16:23
  • OSTN15 is installed, and listed when the transformation selection appears – Andrew_S Mar 18 at 18:27
  • @Andrew_S, if you select and accept that OSTN15 grid daum transformation for the new layer, does the 1 m misalign disappear? – Gabriel De Luca Mar 19 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.