How to Generate a GPX File of OS Grid Squares

Posted

Background

During the 2020 lockdown, whilst not being able to go further afield, I’ve enjoyed exploring the residential streets of my town. I’d like to continue that spirit of “local exploration” by selecting an area of the Peak District near my house, and trying to visit every 1km grid square in that area at least once.

I wanted some kind of diagram to track my progress. The simple solution is to print out the map and colour in the squares. But I didn’t do that.

My finished challenge tracker.

Instead, I decided to use a Mapyx Quo project file to track my progress. When I’ve visited a new area, I will import my route, and keep track of my progress by changing the colour of the grid squares that have been “ticked off”.

It sounds simple, but it ended up being quite difficult. This article explains how I did it.

Execution

Generate list of grid squares

First you need to generate a list of grid squares. I used the OS National Grid format with 4 figures (eg. SK0996) giving 1×1km squares. Mapping software will interpret the coordinate as the south-west (bottom left) corner of the grid square.

I obtained my corner co-ordinates by finding them on the map, right clicking on them and copying the coordinate to my clipboard. Add on at least 1 extra grid square on the north and east sides, as they will be lost later in this process.

Obtaining corner co-ordinates.
My corner points were:
  • SD 98 03 (NW corner)
  • SE 31 03 (NE corner)
  • SK 31 76 (SE corner)
  • SJ 98 76 (SW corner)

Then you need to generate a list of every grid square within those corner points. To do this, I created an Excel spreadsheet with eastings along the top and northings down the side. In addition to the eastings and northings, we also need the relevant sheet reference (the two letters at the beginning).

Note: If I was doing this again, I would convert my “letter and number” grid references to purely numeric ones, as it would make the next step a bit easier. But the method described here works with the letter and number format.

I identified which of the cells would be on each map sheet and made a note. I’ve added this to the diagram to show you what I mean, but you don’t actually add the sheet numbers to the spreadsheet.

Eastings along the top, northings down the side.
(click for full-size image)

Enter a formula into the top-left cell to generate a grid co-ordinate from the sheet letters, easting and northing. Some of the numbers start as single digits, but we need them to be padded with leading 0s, so I concatenated a 0 onto the start of it, then retrieved the last 2 characters using the RIGHT function. This converts 2 to 02 but leaves 20 as 20.

Formula to generate grid references.

Drag the formula across and down to fill that coloured block. All those cells will have the same sheet letters. Then drag a single cell across to the next coloured block, update the sheet letter in the formula, then fill that formula across the rest of the coloured block. Repeat until all the cells are populated. Yes, there are cleverer ways of doing this, but my method is quick and it works!

Completed table of grid references.
(click for full-size image)

Copy and paste the table of grid references into Notepad++ (or another basic text editor). Find and replace the tab characters with line breaks.

Find and replace tabs with line breaks.
(click for full-size image)
Completed list of grid squares.
(click for full-size image)

Now your list of grid squares is ready. Here’s the top 10 rows of mine, which I will continue to use as examples throughout this article. My real list has 952 squares in total!

SD9800
SD9801
SD9802
SD9803
SD9900
SD9901
SD9902
SD9903
SE0000
SE0001

Convert grid coordinates to lat and long

At first I thought I would need to do this manually, with some help from the Ordnance Survey coordinate tools spreadsheets, but thankfully some helpful person has created a batch conversion tool that makes this super easy.

Paste in your list of grid references, set the options to convert from grid references to lat and long, then click convert. The converter will produce a tab-separated output which is ready to be copied and pasted into a new tab (I called mine “LatLong”) on your Excel workbook. I also added a header row to keep things tidy.

Grid Easting Northing Latitude Longitude
SD9800 398000 400000 53.496698 -2.0316167
SD9801 398000 401000 53.505686 -2.0316235
SD9802 398000 402000 53.514674 -2.0316302
SD9803 398000 403000 53.523663 -2.031637
SD9900 399000 400000 53.496701 -2.0165428
SD9901 399000 401000 53.505689 -2.0165463
SD9902 399000 402000 53.514677 -2.0165499
SD9903 399000 403000 53.523666 -2.0165534
SE0000 400000 400000 53.496702 -2.0014688
SE0001 400000 401000 53.50569 -2.0014692

Get grid refs for other corners

Now we have the south-west corner of each grid square as a latitude and longitude, but in order to draw an area for each grid square, we need the other corners as well. The north-east corner of each square is the south-west corner of the square that is 1km to the north, and 1km to the east of the starting square. We will use this concept to get the grid references for each corner of each square. The south-west corner is our “origin”, then we can add 1 to the row number to get the north-west corner, add 1 to the column number to get the south-east corner, and add 1 to the column and row numbers to get the north-east corner.

Methodology for obtaining coordinates for other corners.

Create a new tab on your Excel workbook called “Squares”. Add a column heading saying “SW Grid” and paste your original list of grid references underneath. This column is the grid reference for the south-west corner of the square. Add columns “NW Grid”, “NE Grid” and “SE Grid” which will be populated with grid references for the other corners.

To make these formulas easier to read, I added a named range called “grid” that contained the grid references on the coloured cells on my “Grid” worksheet.

Add “Column” and “Row” columns after the “SW Grid” column and use these formulas to populate them with the column and row numbers of grid reference for the SW corner.

Column: =SUMPRODUCT(--(grid=A2)*COLUMN(grid))
Row:    =SUMPRODUCT(--(grid=A2)*ROW(grid))

Then use the ADDRESS and INDIRECT formulas to retrieve the grid references for the NW, NE and SE corners.

NW Grid: =INDIRECT(ADDRESS($C2-1,$B2,1,1,"grid"))
NE Grid: =INDIRECT(ADDRESS($C2-1,$B2+1,1,1,"grid"))
SE Grid: =INDIRECT(ADDRESS($C2,$B2+1,1,1,"grid"))

This is the bit where we lose the squares at the north and east edge of our area. You will notice that some of the grid reference columns are now populated with the headings from around your grid reference table. We need to get rid of these. I added a column called “Include” and entered a formula that would return “No” if any of the three grid references did not begin with the letter S (as all my valid grid references begin with S).

=IF(OR(LEFT(D2)<>"S", LEFT(E2)<>"S", LEFT(F2)<>"S"), "No", "Yes")

Then I sorted the spreadsheet using this column, deleted all the rows saying “No”, and re-sorted my spreadsheet alphabetically by SW Grid reference. I copied the whole sheet and use “Paste values only” on a new Worksheet (“Squares2”) to remove the formulas. I deleted the “Column”, “Row” and “Include” columns to be left with the following:

SW Grid NW Grid NE Grid SE Grid
SD9800 SD9801 SD9901 SD9900
SD9801 SD9802 SD9902 SD9901
SD9802 SD9803 SD9903 SD9902
SD9900 SD9901 SE0001 SE0000
SD9901 SD9902 SE0002 SE0001
SD9902 SD9903 SE0003 SE0002
SE0000 SE0001 SE0101 SE0100
SE0001 SE0002 SE0102 SE0101
SE0002 SE0003 SE0103 SE0102
SE0100 SE0101 SE0201 SE0200

Now we have the grid references for each corner of each grid square.

Get lat and long for other corners

On my “Squares2” Worksheet I added 8 new columns. One column for lat and long for each of the four corners (SW, NW, NE and SE). The formulas are easier to drag across later if you place all the latitude columns in a block, followed by all of the longitude columns. I used a simple VLOOKUP formula to retrieve the values from the “LatLong” sheet.

SW Grid NW Grid NE Grid SE Grid SW Lat NW Lat NE Lat SE Lat SW Long NW Long NE Long SE Long
SD9800 SD9801 SD9901 SD9900 53.496698 53.505686 53.505689 53.496701 -2.0316167 -2.0316235 -2.0165463 -2.0165428
SD9801 SD9802 SD9902 SD9901 53.505686 53.514674 53.514677 53.505689 -2.0316235 -2.0316302 -2.0165499 -2.0165463
SD9802 SD9803 SD9903 SD9902 53.514674 53.523663 53.523666 53.514677 -2.0316302 -2.031637 -2.0165534 -2.0165499
SD9900 SD9901 SE0001 SE0000 53.496701 53.505689 53.50569 53.496702 -2.0165428 -2.0165463 -2.0014692 -2.0014688
SD9901 SD9902 SE0002 SE0001 53.505689 53.514677 53.514679 53.50569 -2.0165463 -2.0165499 -2.0014696 -2.0014692
SD9902 SD9903 SE0003 SE0002 53.514677 53.523666 53.523667 53.514679 -2.0165499 -2.0165534 -2.0014699 -2.0014696
SE0000 SE0001 SE0101 SE0100 53.496702 53.50569 53.505689 53.496701 -2.0014688 -2.0014692 -1.9863921 -1.9863949
SE0001 SE0002 SE0102 SE0101 53.50569 53.514679 53.514678 53.505689 -2.0014692 -2.0014696 -1.9863892 -1.9863921
SE0002 SE0003 SE0103 SE0102 53.514679 53.523667 53.523666 53.514678 -2.0014696 -2.0014699 -1.9863864 -1.9863892
SE0100 SE0101 SE0201 SE0200 53.496701 53.505689 53.505687 53.496698 -1.9863949 -1.9863921 -1.9713149 -1.9713209

Generate GPX file

Now we have our lat and long values, we can use them to generate a GPX file. The easiest way to get started is to export a sample GPX file from the application you will be using. This means that your GPX file will be in the exact format that it’s expecting, increasing your chances of a successful import on the first attempt.

This section will be different depending on which mapping software you are using. I am using Mapyx Quo, which supports “areas” as an extension to the standard GPX file format (alongside the usual routes, tracks and waypoints). Your mapping software may not support areas, or it may support them using a different extension to the GPX file specification, so your experience may vary.

I created a new project in Mapyx Quo and plotted two simple four-sided areas.

Sample areas to export.

I exported them as a GPX file and examined the contents.

<?xml version="1.0"?>
<gpx xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.1" creator="Quo v2" xmlns="http://www.topografix.com/GPX/1/1">
  <metadata>
    <desc>GPX file generated with Quo.</desc>
    <link href="http://www.mapyx.com">
      <text>Mapyx</text>
    </link>
    <time>2020-11-30T20:04:04.9113498+00:00</time>
  </metadata>
  <extensions>
    <area name="Area 1" desc="" cmt="" xmlns="">
      <drawing v="True" pv="True" pw="4" pc="255|255|255" pt="0" lv="True" lw="4" lc="0|128|0" lt="0" ls="0" lcs="3" fc="255|255|255" ft="120" fs="-1" fsc="0|0|0" />
      <point name="WPT 001" desc="" cmt="" sym="" lon="-1.83762693401174" lat="53.4531785691876" />
      <point name="WPT 002" desc="" cmt="" sym="" lon="-1.8230300113031" lat="53.4507308979313" />
      <point name="WPT 003" desc="" cmt="" sym="" lon="-1.82320821009013" lat="53.4440795356634" />
      <point name="WPT 004" desc="" cmt="" sym="" lon="-1.83916337491853" lat="53.4450908360668" />
    </area>
    <area name="Area 2" desc="" cmt="" xmlns="">
      <drawing v="True" pv="True" pw="4" pc="255|255|255" pt="0" lv="True" lw="4" lc="0|128|0" lt="0" ls="0" lcs="3" fc="255|255|255" ft="120" fs="-1" fsc="0|0|0" />
      <point name="WPT 001" desc="" cmt="" sym="" lon="-1.80766161718529" lat="53.4527745424752" />
      <point name="WPT 002" desc="" cmt="" sym="" lon="-1.79380726398683" lat="53.4528413236645" />
      <point name="WPT 003" desc="" cmt="" sym="" lon="-1.79565269792186" lat="53.4448445636948" />
      <point name="WPT 004" desc="" cmt="" sym="" lon="-1.80815023575222" lat="53.4445956254592" />
    </area>
  </extensions>
</gpx>

The structure is fairly basic. Each area is represented by an <area> tag, which contains a single <drawing> tag (with formatting information including path width, path colour, etc.) followed by multiple <point> tags to define the shape of the area. We will be using Excel to generate one <area> tag per table row. I’ve not seen this method used anywhere else. It’s a bit “quick and dirty” but it works perfectly well for generating simple XML syntax.

Back in Excel, I left a blank column and added several new columns: Area open, Drawing tag, SW Point, NW Point, NE Point, SE Point and Area close. I entered formulas to generate the GPX markup as required.

Using Excel to generate GPX markup.

To escape double quotes in Excel formulas you use two double quotes, so as you can see, you end up with lots of quote marks. You may also notice that I’ve added another 5 decimal places to the lat and long coordinates (all zeroes), because Mapyx Quo would not import the points until I included 13 decimal places as included in the export. This is one reason why it’s really useful to start with an export, because when it doesn’t work, there will be very few differences between the known-good export and your import file, so it’s easy to track down problems.

Area open ="<area name="""& A2 & """ desc="""" cmt="""" xmlns="""">"
Drawing tag isn't a formula. It's just reproduced exactly as it appears in the export.
SW Point: ="<point name=""" & A2 & """ desc="""" cmt="""" sym="""" lon=""" & I2 & "00000"" lat="""&E2&"00000"" />"
NW Point: ="<point name=""" & B2 & """ desc="""" cmt="""" sym="""" lon=""" & J2 & "00000"" lat="""&F2&"00000"" />"
NE Point:  ="<point name=""" & B2 & """ desc="""" cmt="""" sym="""" lon=""" & J2 & "00000"" lat="""&F2&"00000"" />"
SE Point: ="<point name=""" & D2 & """ desc="""" cmt="""" sym="""" lon=""" & L2 & "00000"" lat="""&H2&"00000"" />"
Area close: ="</area>"

Then I pasted the GPX from Excel into my GPX file (leading the header and footer intact), saved the file, and imported into Mapyx Quo.

Success!

My grid squares overlaid on the map.

Downloads

Author
Categories Mapping Software Reviews, Technical