1

This is my first venture into GIS datasets. I've decided to start a little project which will eventually hook into the Google Maps api to overlay/plot/pin from data in my database.

I've started loading different datasets from various sources in a MySQL database. In particlar, I'm working with Ordnance Survey data which appears to be accurate enough for what I'd like to achieve. However, the X/Y co-ordinates in my dataset are Ordnance Survey (GB) Easting and Northing.

Ordnance Survey provide a very useful conversion spreadsheet (https://www.ordnancesurvey.co.uk/docs/support/projection-and-transformation-calculations.xls) to convert between different datums (is that the correct term? or it Projections?) and I've attempted to reverse engineer the formulae in the spreadsheet so I can manually do these calculations. I totally failed at this bit.

In an ideal world, I'd like a simple calculation which can be dumped straight into a (My)SQL statement which will run through the dataset held in my table, populating additionals column for both latitude and longtitude with the converted values (in WGS84) of both easting and northing co-ordinates.

Can this be achieved using pure SQL? (i.e. What is the underlying maths etc that can be transposed into a SQL statement).

Note: This question seems to have been asked a couple of times but the solution seems to be to employ the use of an external coding language such as ASP, PHP, JS etc. I don't really have much front end coding experience - I'm a database administrator so I'm more familiar with SQL. I'm also pre-empting that someone will ask what front end language I will eventually be using for my project as surely I must know at least a little bit in one or most of the web dev languages. To answer that: I'll be developing the application in "Oracle APEX" (using ODBC link from Oracle to MySQL) which is essentially pure PL/SQL, no web coding experience required.

2

However, the X/Y co-ordinates in my dataset are Ordnance Survey (GB) Easting and Northing. Ordnance Survey provide a very useful conversion spreadsheet (https://www.ordnancesurvey.co.uk/docs/support/projection-and-transformation-calculations.xls) to convert between different datums (is that the correct term? or it Projections?) and I've attempted to reverse engineer the formulae in the spreadsheet so I can manually do these calculations.

As you can probably tell from your linked spreadsheet, this will not be simple. You've said your data is in Easting and Northing, which are flat, Cartesian coordinates. Your first order of business is to convert those into latitude/longitude, which are angular measurements along an ellipsoid, so that means quite a lot of maths.

If you remain undeterred and want to do this yourself, you'll need to determine if your horizontal datum is ETRS89 or OSGB36, since both are used by the Ordnance Survey group and each use a different ellipsoid definition. See this link for more information on the two datums. After that, you can find guidance and formulas for converting your Transverse Mercator XYs (British National Grid projection) into lat/longs on the helpful IOGP Guidance Note 7-2 PDF, starting on page 45.

Once that is done, if your horizontal datum was OSGB36, you'll need to transform the OSGB36 lat/longs into WGS84. There are several ways to transform those coordinates, each with their own ease of implementation, area of use, and accuracy. However, if your datum was ETRS89, you can just assume those lat/longs are WGS84, as they are coincident to one meter. Unless you require better accuracy than that, in which case, forget everything I've said. ;)

In short, the third party tools exist out there for a reason, because converting coordinates is not a trivial task.

  • Thanks Mintx. I think I need to brush up on my Maths before delving any deeper into the world of datum conversion! As the data is static and I only really need to run a conversion once (for the time being), I've decided to complete this task via visual basic from within the OS spreadhseet itself, looping through each set of E/N co-ordinates and populating a new row for lat/long. This should prove much easier than trying to figure out what all those mathematical symbols mean! – Huskie69 Jun 17 '16 at 8:11
  • 1
    Consider Grid Inquest the OS application which you can batch process coordinates.I think trying to do this manually is going down a very long dark road... – Hornbydd Apr 21 '18 at 7:09

Your Answer

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

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