How to determine how far railway stations are from your house ("as the crow flies")

I've been thinking about a new quest for 2018, and inspired by Vicki Pipe and Geoff Marshall (@allthestations / www.allthestations.co.uk), I've been considering National Rail stations, but all 2563 stations is a bit much. They've made me realise that I don't know much about many of my most local stations. For most of them, I've only been through them on a non-stopping train. But what constitutes local? I needed to work out "as the crow flies" what are my closest stations? Now that I've figured out a method, I've decided to share it so that you can make use of it yourself. Why not endeavour to visit all the stations within X kilometres of your house? If you're a Londoner, the closest stations that you can't use your Oyster for? What radius would you consider close?

I live in Southampton and I'm considering a radius big enough to cover all stations in Hampshire, about 63 kilometres, which would also encompass some stations in Wiltshire, the Isle of Wight, Dorset, West Sussex, Berkshire and Surrey.

How to determine how far railway stations are from your house ("as the crow flies")

A good source for the location of every railway station is the ORR statistics of passenger usage which include the Ordnance Survey co-ordinates of every station. So go to http://orr.gov.uk/statistics/published-stats/station-usage-estimates, then download the Excel spreadsheet from the link "Station usage 2016-17 data". The main page of all the statistics is "Estimates of Station Usage". There you will find 2560 stations, one per row. Columns G & H contain the OS co-ordinates. Now, three stations have opened recently (Low Moor, Ilkeston and Cambridge North) so they aren't included in the statistics. Also, Kenilworth was due to have opened, hasn't yet, but should shortly. You'll probably want to add these four stations to the list.

Name in Column C	Easting in Column G	Northing in Column H
Low Moor	416361	428191
Ilkeston	447337	342718
Cambridge North	547371	260586
Kenilworth	429150	271650

Now, you'll need to know the OS co-ordinates of your house. You can measure these from an Ordnance Survey map, of course. Digitally, a good way is to use streetmap.co.uk. Put in your postcode or address and it should show your location on a map. In small text under the map it should say something like "Location is at 528953, 179679" - those numbers are the easting (528953) and the northing (179679). This is to the nearest metre by the way, so more than accurate enough.

Now you can add a formula to the spreadsheet that will calculate the distance. If you add it to the end of the first station (Abbey Wood, row 2), you can then copy it down through all the other rows. So in cell AA2 enter the formula =SQRT(POWER(G2-528953,2)+POWER(H2-179679,2))/1000, replacing 528953 with your easting and 179679 with your northing. This cell will then tell you the distance, in kilometres, from your house to Abbey Wood, the future terminus of Crossrail. The formula is simply an application of Pythagoras' theorem. Select cell AA2, then hold down a Shift key as you scroll down to row 2561 (or row 2565 if you've added the four new stations). Press Ctrl+D and the formula will be copied down all the rows, with the row references changed. Column AA now shows the distance of each station from your house. You should label cell AA1 "Distance".

If you want a more flexible spreadsheet that you can change your "home" location for, put the easting (528953) in cell AB1 and the northing (179679) in cell AC1, then make the formula in cell AA2 be =SQRT(POWER(G2-$AB$1,2)+POWER(H2-$AC$1,2))/1000. You can copy this formula down as described above. In this way you can change the values in AB1 and AC1 to get distances from a different point.

Now you can use Excel's Sort command, which you will find on the Data tab in recent versions. Select that you want to Sort by "Distance", if you've added the label, and make sure that "My data has headers" is ticked. Select "Smallest to Largest" order to see which stations are closest.

With the example co-ordinates of 528953, 179679, which are for Buckingham Palace, as you might expect it tells you that Victoria is closest at 681m, followed by Charing Cross at 1.49km and Waterloo at 2.12km.

As an exercise, can you find the station closest to the centre of the network? That is, the one with the smallest radius that would cover every other station in the country. Stations that are no more than 500km from all other stations in the country would be good. The best I've found is just under 486km from all other stations - I'm not certain that's the best of all.