Google Maps is ideal for vehicular navigation, but less so for exploring the great outdoors on foot. It’s justly popular as a Geographical Information System (GIS) with which you can publish maps with places of interest superimposed as pushpins, but the setup isn’t as flexible as it might be.
If you want to display multiple points using geographic data from a file, as opposed to adding each point manually, it’s a tricky programming exercise. And if your locations are National Grid References (NGRs), conversion to latitude and longitude is required. This is either a laborious manual exercise using an online utility or one involving difficult programming. More significantly, the base maps can only be those provided by Google Maps. OpenSpace Mapbuilder instead uses Ordnance Survey maps.
But what if you had a geological project and need a geological survey map, an archaeological project that needs a historical map, or maybe you just prefer Harvey’s Maps? Our solution is to use Microsoft Excel, plotting your data as an XY scatter plot and using any map as the background. You can obtain the maps electronically or scan them in, but consider copyrights if you plan to publish them online.
Here, we’ll use a familiar Ordnance Survey Landranger (1:50,000) map that you can download for free. We’re using only a segment of this map, so use a photo editor to crop in on the most north-westerly 5x5km section and save it as a Jpeg file.
To exactly follow our workshop, you’ll need to use our data for the locations of blackbirds spotted on a hypothetical ornithology field trip. The NGRs are SX831955, SX830960, SX843981, SX845985 and SX839985. Later in our workshop, we also add the locations at which swallows were seen (SX821954, SX820958, SX828959 and SX805993).
You can use any version of Excel; we’re using Excel 2007.
Pinpoint a route
Step 1. Create an Excel workbook and rename two worksheets as ‘Main’ and ‘AtoZ’. Put a title in cell A1, and the headings ‘Description’, ‘Grid Ref’, ‘Easting’ and ‘Northing’ in A3 to D3 on the ‘Main’ worksheet. Enter a text description such as the time below and the NGRs for blackbirds (provided in the introduction).
Step 2. In the ‘AtoZ’ worksheet, type the column headings ‘Letter’, ‘Easting’ and ‘Northing’ in cells A1 to C1. Type the letters A to Z, with I omitted, down column A and starting in A2. Type 0, 1, 2, 3, 4, 0, 1, 2, 3, 4 and so on into B2 to B26, and 4, 4, 4, 4, 4, 3, 3, 3, 3, 3 and so on into C2 to C26, ending with 0 in C26.