Continuing my theme of recreating spreadsheets I’ve seen on Clearly and Simply I’ve had a go at making an Excel map of UK cities and towns that can be highlighted to find their combined population.
I’ve taken a list of UK cities and towns from this website and selected all towns with a population greater than 30,000 as at the 2011 census (347 towns and cities). I have no idea on the accuracy of this information so if you are using this for analysis you are doing so at your own risk.
This list then needs to be geocoded. Ideally I want this to be in Eastings and Northings so that I can find the distance between two points using a simple Pythagoras calculation rather than having to work out how to use longitude and latitude. For this I used the batch geocoding tool on doogal.co.uk
I needed a base map to plot these over so I found this one on Wikipedia commons, unfortunately it is over 30 years out of date but it’ll do for now. The tricky bit is aligning the map and the chart. Since the map I’m using uses the OSGB36 projection it is split into 100 square Km boxes so I just found a map with these boxes, plotted the corners and lined them up. So I now have something like this:
Which is already far better than I was expecting. Now following the instructions at Clearly and Simply I added the ActiveX control which let me do the circular selection.
To be honest I’ve already got further than I expected to. No to get onto making the towns and cities know that they have been selected using good old Pythagoras. This uses simpler equations than the Clearly and Simply example because of the flat nature of the projection I’m using.
Something has gone wrong here. Looks like I’ve got my eastings and northings mixed up.
That looks a bit better. Now it is just time to make it look pretty by adding a data table and a chart.
It would be interesting to try to get the spreadsheet to vary the size of the selected area after entering the centre of the circle and the radius wanted but I’m not certain where to start for this one. Finding the towns to select is the easy bit, drawing the circle is the tricky bit and I don’t understand the VBA in this spreadsheet enough to do this.
Download my version here Mapping Cities as Points