The C# code published here is not very accurate as also mentioned by tanoshimi. I think this has to do with the lack of 'approximate Helmurt transformation'. Just curious is anyone aware of some accurate C# code or is it even possible to achieve using SQL Server's spatial functionalities?
[GIS] convert ordnance survey easting and northing data to WGS84 latitude longitude values
cnetsql serversql-server-spatial
Related Solutions
Edit - Update with OSGB36 specific information.
Well, the good news is that the target projection is OSGB36, which is a well known and standardized projection. The bad news is that GIS programming is still really difficult :)
There are a set of parameters that need to be defined, and mathematical operations applied to those parameters, to succesfully project lat/long to grids. Fortunately, there are databases of parameters for the former, and libraries for the latter. proj.4
, which I mentioned earlier, is one of the standard libraries. There are also ports of proj.4
to other languages, including JavaScript, which is hopefully close enough to jScript for your purposes. The port is proj4js.
The other thing you need to know is the EPSG
number for your coordinate systems. OSGB36 is EPSG:6277. The EPSG is an oil-industry (I think) created body that collected lots of locally defined projections and documented them. The database is now openly maintained, and GIS libraries can use their definitions.
Previous info
Your easting/northing numbers may be in the British national grid system.
I gave an answer to another GIS related question that includes links to proj.4
, which is one of the standard libraries for converting between lat/long and easting/northing (the forward calculation is called projection
in the GIS world). I've never used it for the conversion you're looking for, but the software is well-documented and comes with conversion definitions for lots of projections.
There seems to be a lack of a clear overview on the OGR Virtual Format help page. The phrase it can "provide an anchor file for access to non-file oriented datasources" seems to imply any connection type can be used, but I'm not sure this is the case.
Its original function was to allow spatial data to be created from non-spatial sources, for example by combining an X and Y column in a database table to create point features.
However there is no reason why a spatial database can't also be used for this purpose, but you should then use a standard ODBC connection rather than the MSSQL driver.
To do this create a DSN (Data Source Name) in Windows. Then use the ODBC connection and the DSN in the virtual layer configuration. So if your DSN is named OSVectorMap:
<OGRVRTDataSource>
<OGRVRTLayer name="AASQLlayer">
<SrcDataSource>ODBC:username/password@OSVectorMap</SrcDataSource>
<SrcSQL>SELECT geom27700.STAsText() AS geomWKT FROM TG11_Settlement_Area</SrcSQL>
<GeometryField encoding="WKT" field="geomWKT"/>
<LayerSRS>EPSG:4326</LayerSRS>
<GeometryType>wkbPolygon</GeometryType>
</OGRVRTLayer>
</OGRVRTDataSource>
There are a few more details on creation of Virtual Layers in the MapServer wiki and Virtual Spatial Data page.
Great blog post by the way. I wouldn't worry too much about using Python 3.x - nearly all the useful GIS libraries are still in 2.x And the SQL Server Geometry and Geography types are great. The only downsides are that SQL Server unfortunately seems a second class citizen in the opensource world in terms of blog posts, and integration with other tools.
Best Answer
All formulas you might find on the web do the conversion from OSGB36 grid to lat/lon on the Airy ellipsoid, but there is no exact tranformation from that to WGS84. Simply because OSGB36 has grown over decades, including surveying errors done by our ancestors, and terrestrial moves.
The only exact way to do the last step is to use the official OSGB36 datum shift ntv2 grid file, as published by Ordnance survey on https://www.ordnancesurvey.co.uk/business-and-government/help-and-support/navigation-technology/os-net/ostn02-ntv2-format.html.
Proj.4 is a very simple tool to use that datum shift grid (and the conversion you do with the C# code as well). GDAL and QGIS are also able to use it.