CSV to GPX

[Tim Hall]

I've recently started hunting bench marks and other OS mapping related stuff.  There's a website bench-marks.org.uk thathas an incomplete list of same. The OS maintain a fuller, searchable list which spits stuff out as a CSV file.  Piucking a random example , or two, of CSV to GPX converters  II hoped to produce a file that I could read with OSMAnd, thus displaying little dots on my phone to cycle towards. But they don't work. The first example put the points thousands of miles away, whilst the second didn't work at all.

The whole GPS thing is new ground to me, so a simple explanation would be great.  extract of the CSV file is below:

Code: [Select]

"Square","Easting","Northing","Mark type","Description","Height","Order","Datum","Verified year","Levelling year","Metres above ground"
"TQ","2803","4395","CUT MARK","NBM EL SUB STA NW SIDE JUNC HORLEY ROW YEWTREE CLOSE S FACE 2.0M SW ANG","55.629","3","N","1971","0","0.4"
And a bit of the GPX:

Code: [Select]

<gpx xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd" version="1.1" creator="GPS Data Team ( http://www.gps-data-team.com )"><wpt lon="0.0000000" lat="0.0000000"><name>Square Easting Northing Mark type Description Height Order Datum Mark type Description Height  Order Datum</name></wpt><wpt lon="0" lat="0"><name>TQ 2803 4395 CUT MARK NBM EL SUB STA NW SIDE JUNC HORLEY ROW YEWTREE CLOSE S FACE 2.0M SW ANG 55.629 3 N CUT MARK NBM EL SUB STA NW SIDE JUNC HORLEY ROW YEWTREE CLOSE S FACE 2.0M SW ANG 55.629  3 N</name>


[Kim]

Starter for 10:  Your CSV contains OS grid coordinates, not WGS84 latitude/longitude as you'd expect to find in a GPX file.

Incompatible coordinate systems aside, I assume the converter is looking for a heading in the first row that identifies the latitude and longitude columns, and may be not be expecting the words "easting" and "northing" (which I've only ever encountered being used in the context of the British grid, but that may just be my ignorance).

[fuaran]

You could try GPSBabel, using the universal CSV format.  https://www.gpsbabel.org/htmldoc-development/fmt_unicsv.html
It does support OS grid references, and will convert them to latitude/longitude as required for GPX. Though yes, you do need the headings to have the correct names etc.

[T42]

Starter for 10:  Your CSV contains OS grid coordinates, not WGS84 latitude/longitude as you'd expect to find in a GPX file.

The French are similarly annoying, using a system devised by one Lambert, the origin of which is a point in the Atlantic such that all coordinates in mainland France are positive. Simply transposing them to Greenwich-relative coords obviously doesn't work, spherical trig oblige.   

[frankly frankie]

http://www.gpsvisualizer.com/tutorials/waypoints.html
is a useful read.  Though NB that some of that information is specific to that website, and not general GPX.  And
http://www.gpsvisualizer.com/convert_input
can convert CSV to GPX, but you would need the coordinates in decimal degrees format I think.  And when using that form or any other on that excellent website, be sure to check out the 'advanced options'.

[Kim]

Starter for 10:  Your CSV contains OS grid coordinates, not WGS84 latitude/longitude as you'd expect to find in a GPX file.

The French are similarly annoying, using a system devised by one Lambert, the origin of which is a point in the Atlantic such that all coordinates in mainland France are positive. Simply transposing them to Greenwich-relative coords obviously doesn't work, spherical trig oblige.   

I've not really found it to be a problem in practice.  Proper GPS receivers (eg. Garmin) can be set to use all sorts of coordinates and datums, so in the field you can just work to the format of your paper map.  OS grid *is* convenient for their paper maps.

One-off coordinates can be dealt with the same way, or by feeding them into a suitable online mapping tool.

I've not yet had to deal with it programmatically, as per the OP's problem.  Cooking the CSV headers and feeding it to GPSBabel sounds like a reasonable approach.  No doubt there are standard libraries for this sort of thing if you're writing software that's going to make a habit of it.

[andrew_s]

OS Grid to WGS84 lat/long converters are likely to require pure X and Y fields, with values in metres.

Tim has 3 fields - square, easting and northing, containing coordinates to 10 m precision.
UK OS grid coordinates have X values with 6 digits, and Y values with 7 digits (Shetland is > 1000 km N of the Lizard).

Tim will have to add a zero to the end of his easting and northing fields, then get the first digit or two from the 100 km square reference by a lookup table, probably.

The square letters are based on a 5 x 5 matrix, omitting "I", so the first row is ABCDE, the second row is FGHJK etc
The second of the two letters identifies a 100 km square within a 500 km x 500km square, the first letter identifies the 500 km square. The National Grid 0, 0 point is at the SW corner of the "S"  500 km square, and the 500 km square letter will be one of S (SW England & Wales), T (SE England), N (Scotland and England N of Windermere) or H (Shetland and most of Orkney).
There's also a little bit of beach just S of Ravenscar that's in the "O" 500 km square.

Tim's TQ 2803 4395 will have a numeric grid reference of T=(500000E,0N) + Q=(0E, 100000N) + 28030E,43950N, ==> 528030, 143950


The other thing is that conversions frequently aren't all that accurate.
If I put a GPX track into ArcMap at work, the default conversion, based solely on the two geoids and the OS grid projection, comes out something like 110m out (from memory). I've got to select a further "fiddle transformation" to get within a metre or two, and there's a whole list of these fiddle transformations for use in different parts of the country.

The OS page at https://www.ordnancesurvey.co.uk/business-and-government/help-and-support/navigation-technology/os-net/coordinate-spreadsheet.html contains a conversion spreadsheet and a PDF about coordinate conversions and why you won't get accurate results

[Greenbank]

The converter here: http://www.movable-type.co.uk/scripts/latlong-os-gridref.html was almost spot on (based on the description) of the sample point provided above.

[frankly frankie]

What a great thread this is turning into.  I thought the OS-provided PDF referred to in Andrew's post was fascinating and instructive, if understandably a bit Brit-centric.