Where is the Olympic Torch?


The burning question. You can see the route of the Olympic Torch relay here on the official website for the London 2012 Games, but I wanted to make my own map. The route of the Olympic Torch relay is published by LOCOG and available as a file here, courtesy of Oliver O’Brien at the Centre for Advanced Spatial Analysis, who’s also made his own map of the torch relay route. The data is in JSON (JavaScript Object Notation) format, so needs to be converted before Excel or Fusion Tables will be able to read it. Fortunately there’s a JSON to CSV converter online. The file has longitude and latitude data for each calling point on the route, so setting up markers for each stop on the relay is easy. The tricky part is plotting the route itself. A Fusion Tables map can draw a line between two points, but this means creating a new column with a multigeometry element, which is quite easy to do manually if you only have a few points to connect together but very time consuming if you have over 1000. Here’s an Excel macro I wrote to do the job automatically. You just need a column with latitude data and a column with longitude data, with data in rows so each row represents a point defined by a latitude and longitude. The latitude column needs to be to the left of the longitude column. Create a blank column to the left of both, and run the macro in any empty cell in the blank column. It’ll create a multigeometry element with a line connecting the location of the current row with the location in the row directly above. 1000 clicks later, and you have your very own Olympic Torch route map.