# Mapping on a grand scale: poetry

I had a riot yesterday.  At work.  I was truly in my element.

I was given a task to map the 300+ offices that my client had across the UK.  And from that to identify clusters.  Although they relied on the same underlying data, I treated these two tasks largely in isolation to one another.  After all, identifying clusters from a visual map is rather an inexact science.

The mapping was sublime.  I used a Google Spreadsheet to convert postcodes to latitude and longitude coordinates (using the advice I’d previously documented here), used this to generate a KML file (through some fancy Excel formulae) which I could then import into Google Maps to create a visual representation of the locations.  Awesome.

Then on to the distances.  I’d had a vision the previous evening that I wanted to create one of those triangles that you used to see in the back of road atlases, across which you could trace your finger to figure out the distance from Plymouth to Edinburgh, for example.  So I went about creating one.

I made column headings out of the locations in Excel, transposing the data to duplicate them as row headings, together with their respective latitude and longitude coordinates.  I then used trigonometry (with the earth’s radius as a constant) to find the distance between each pair of locations (as the crow flies rather than driving distance).  A lovely piece of conditional formatting (if row number is greater than column number) allowed me to hide all the lower half of the triangle—a number format of ;;; hides a cell’s contents without affecting the value of the cell.

I was lost in Excel and Google Maps for an entire morning.  The subject matter had me gripped.  The quality of the output was top drawer; and the importance of the messages therein was right up there.  I love days like that.

2 Responses to “Mapping on a grand scale: poetry”

1. Steph Gray on June 30th, 2010 23:11

Sounds awesome. Can you post a non-restricted version of the spreadsheet with formulae etc?

2. Dan on June 30th, 2010 23:12

Will do. Will take a little time to get it into a sharable state but I’ll do so 🙂