Sam Booth

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.

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.

• 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.

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.

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!

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

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.

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.

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:

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.

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.

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.

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!

Downloads

• OS_Grid_GPX_Excel.xlsx
• OS_Grid_GPX_Import_Mapyx_Quo.gpx