1

Topic: OS Grid reference to longitude / latitude conversions (pond locations)

I don't know if this is the best place for this question.  I've been collecting information about ponds in my local area and I'm trying to set up a Google maps page to display where they are.

How I'm going about it.  Google maps only seems to work with longitude and latitude data so I have converted the location of each pond from the UK grid references  to long. and lat. using a spreadsheet downloaded from the web (the spreadsheet is called convert.xls by Phil Newby from www.haroldstreet.org.uk).  Its recommended on a number of web-sites.

The problem I'm getting.  When I convert my grid references using the spreadsheet it doesn't show any errors and the numbers seem to be about right, but the markers shown on Google maps are varying distances from the actual ponds.  Does anyone have any idea whats going wrong or found a better way of doing this?  I wondered if the coordinate data needs to be corrected in some way, either before or after conversion?  Also, how can I tell if my longitude and latitude values are in WGS-84 or OSGB-36 format and how much of a difference does it make?

Failing that, does anyone have a spreadsheet that will convert a list of grid references correctly?  I've come across a number of web-pages, including the OS maps excel converter, but they only convert one value at a time.

Thanks for any help supplied,

Grant

Re: OS Grid reference to longitude / latitude conversions (pond locations)

Hi Grant,

I haven't done this but it sounds like the convert.xls spreadsheet turns grid refs into OSGB-36 lat/long but that you would need WGS-84 for Google. They are different enough projections to give odd results I think(?)

Have you tried the Phil Brady addin/spreadsheet also available from that website? This one: http://www.haroldstreet.org.uk/osgb/exc … sion-code/ - the osgb.xla is the important bit with the macros in, and then you can see how example of how to use them in the other examples workbook.

Try inserting a new worksheet in the example workbook. You'll want to put your os grid reference in A2 and then in  B2 =longitude(84,A2)  C2 =lattude(84,A2) etc.

Teresa Frost
WeBS National Organiser, BTO. NFBR Council Member.
ex-ALERC/CBDC/KMBRC

Re: OS Grid reference to longitude / latitude conversions (pond locations)

(How I remember searching in vain for my first geocache because the GPS was set in OSGB-36 long/lat instead of WGS-84 - it was a good 100m out or more if I remember correctly!)

Teresa Frost
WeBS National Organiser, BTO. NFBR Council Member.
ex-ALERC/CBDC/KMBRC

Re: OS Grid reference to longitude / latitude conversions (pond locations)

There's a basic utility that I wrote quite a while back at http://www.bnhs.co.uk/focuson/maponge/html/index.htm which will turn a tab-separated text file of records into a Google Earth layer of icons or grid-squares if it's of any help. Just save the example records and styles files as a starting point. There's help if you need it.

Keith

Re: OS Grid reference to longitude / latitude conversions (pond locations)

The earth is not a perfect sphere - it has bulges and depressions in its surface - so creating maps inevitably introduces some distortions.  A number of different mathematical models (or views) can be used to 'unfold' either the whole earth, or regions of particular interest (like Great Britain), into a smooth plane for mapping purposes.  Coordinate systems (ie. point locations) based on the different models are usually confusingly similar (as the projection models differ only slightly), but importantly will only agree up to a certain point.  One consequence that never fails to surprise people is that there is no definitive latitude and longitude for a location - the values differ slightly depending what model you are using.

There are two coordinate systems (ie 2 models) that are commonly used for maps of Great Britain - these are usually referred to as WGS84 (for World Geodetic System 1984) and OSGB36 (for Ordnance Survey great Britain 1936) and use slightly different projections:

    1.     OSGB36 was developed by the Ordnance Survey based on the Airy 1830 ellipsoid.  It uses an elliptical approximation that provides the best local fit for the region containing Great Britain.
    2.    WGS84 is a worldwide standard, and uses a elliptical model that is the best approximation for the whole earth.  It is used by most GPS systems.

The Ordnance Survey give a very good description of the coordinate systems in its report "A Guide to coordinate systems in Great Britain" which can be downloaded from here:

        http://badc.nerc.ac.uk/help/coordinates/OSGB.pdf

Unfortunately (for us in the UK) paper maps - which are based on the OS Grid - are aligned to the OSGB36 model, whilst web mapping services and applications like Google invariably use the WGS84 model.  As you've found, this makes converting OS Grid references to lat/long values suitable for plotting on web a fairly involved process.

Lat/long Values:
To complicate matters further, latitude and longitude values (eg. 52.658N, 1.718W) almost never include any description of which model they refer to, which can lead to slight differences when unknowingly mixing coordinate systems.  There can be up to 0.2km divergence between the same lat/long value in OSGB36 and WGS84 models, so even getting the conversion correct is no guarantee that your latitude and longitude values will agree everyone elses.  The only way of knowing what coordinate system your lat/long values refer to is:

    (1)    by understanding which conversion method you followed (if they were transformed from OS Grid references), or;
    (2)    by asking the dataset provider (if they were generated by someone else).

If you are working to a precision of 10s of metres, it is important to be aware of the different coordinate systems and apply a consistent approach to converting values.

Data Conversions:
Converting OS Grid references to lat/long values (suitable for use in mapping web-pages) can be broken down into four steps:

        OS Grid Reference  ->  Eastings / Northings  ->  OSGB36 Lat/Long  ->  WGS84 Lat/Long

The first step, converting an OS Grid reference to Eastings and Northings (ie. distance from the false origin of the OS Grid) is a relatively simple step; this often tricks people into thinking they can do the conversions themselves.  However successive steps are increasingly complex, and require some expertise to implement correctly in a spreadsheet or application.  The OS guide referenced above contains details of the relevant algorithms.

If you have a small number of pond locations, there are numerous web pages which can be used to convert a single OS grid reference.  The definitive version is probably the downloadable Ordnance Survey spreadsheet (projections-and-transformation-calculations.xls), although this is quite complicated to use and (if I remember correctly) requires Eastings and Northings as inputs.  www.nearby.org.uk  and www.movable-type.co.uk also provide good resources for converting coordinate data values.

I don't know of any spreadsheets that will do a batch-type conversion of OS Grid references -  I think part of the problem is the complex nature of the calculation, which doesn't lend itself easily to a spreadsheet format.  I tried the one you mention and failed to get correct values (although I didn't explore why).

Some (but not all) GIS-related applications will allow you to convert data between various coordinate systems and formats.

FieldNotes recording software (see http://www.FieldNotes.co.uk) allows you to convert batches of coordinates between OS Grid reference / Eastings and Northings / OSGB36 / WGS84 systems (and different input / output formats) simply by pasting your data into a dialog box - see the [Tools | Coordinate Convertor] menu option.  Plus - if you enter your ponds as Sites, the software will create your Google map web-page with markers at each of the relevant locations.  This might save you a bit of legwork - Google change their APIs on a fairly frequent basis.

Good luck with your project!

Simon Skidmore
Green Man Software Ltd

6

Re: OS Grid reference to longitude / latitude conversions (pond locations)

wow!  thank you all very much for posting so much usefull information.  I'll try a few approaches and see what works best for my ponds.

Thanks again,

Grant

Re: OS Grid reference to longitude / latitude conversions (pond locations)

Grant,
  I have picked up a reference to my name and my routines.

If you were to get hold of my excel add-in and install it, you should have all the tools you need to plot your pond data in Google Earth