Plotting GPS tracklogs to UK National Grid in AutoCAD

When out on site visits for work (as with other times), I often record where I have gone using the GPS on my phone. It can often be handy to overlay this on a CADdrawing. Until now, converting this has been non-trivial. However, I now have a reasonable, if somewhat convoluted, workflow.

I record tracklogs using MotionX-GPS for the iPhone, which allows you to export the result as a gpx file containing Latitude/Longitude pairs in decimal degrees, with corresponding elevation and timestamp.

Using the free GPS Babel software, this can be converted to a csv comma delimited list of Latitude/Longitude pairs, although still in decimal degrees. However, for UK National Grid, they need to be in easting and northings from the origin off Cornwall.

This is where I had been getting slightly stuck. And I am sure there are various solutions using GIS software, but I wasn’t finding one, and don’t have easy access to GISsoftware anyway.

I was using an Excel spreadsheet developed for geocaching (Waypoint workbench) to convert the co-ordinates from decimal degrees to UK National Grid, but the resulting points were offset from their true location. I suspect this is because the spreadsheet does not take into account some difference in geoid or datum[1]The Wikipedia page on OS National Grid has a section on the differing data for WGS84 used by GPS and OSGB36 used for the UK National Grid, which might be the source of the error, but I’m not sure.

But, there is another piece of software available from the Ordnance Survey called Grid InQuest that you have to email them to get a copy. This appears to correctly convert the latitude/longitude from GPS to Eastings/Northings. However, in this conversion process the elevation is lost, but this is not of importance to me, and is usually rather inaccurate anyway.

A little bit of manipulation is then required in Excel to combine the eastings and northings into one column and remove duplicate points, before plotting in AutoCAD as a polyline.

Summary of Steps Required

  1. Export GPX file from GPS
  2. Convert GPX to CSV in GPS Babel, using standard parameters
  3. In Grid InQuest, File>Convert File(s)
    1. Select delimited by commas, and Geodetic (Lat, Long) coordinate format
    2. Set Latitude, Longitude and Ellipsoidal Height as columns 1, 2, and 3 respectively (although there is no data for the height in the GPX file, it still works).
    3. Set Projected coordinates (Eastings, Northings) as the output format, and append to existing files (or specify a filename).
  4. Open the CSV in Excel, and add the following formulae in the next blank columns =D1&","&E1where D and E are the columns of eastings and northings respectively, and =IF(F1=F2,"",F2) to filter duplicates.
  5. select all the data, press ctrl+L to make a list (the data probably doesn’t contain headings) then, using the drop down arrow on the last column, auto filter then data using the condition “Does not Contain” and leave the box blank. This will give just the individual values.
  6. Select just this last column of data, and copy to the clipboard.
  7. Finally, to plot these into AutoCAD, type pl to start a polyline and press enter, then paste the concatenated co-ordinates and press enter to finish.

There is doubtless an easier way to do all of this, and I would be interested to know if you have any suggestions. But I thought I’d put this up here in case it prevents someone going though the same headaches as me.

Notes

Notes
1 The Wikipedia page on OS National Grid has a section on the differing data for WGS84 used by GPS and OSGB36 used for the UK National Grid, which might be the source of the error