Yesterday I attended the Hyperlocal West Midlands Govcamp in Wallsall (Addictomatic linky here). One of the sessions I attended/chipped in on was the “Blue Peter Open Data” session hosted by Will Perrin. There was some very interesting laymans term open-data stuff coming out of it; not much I didn’t already know, but I do like sessions of that kind!

Part way through, Will showed how he’d used Google’s Fusion tables to analyse data sets created or hosted in Google Docs. A pretty cool tool in itself, it also comes with the ability to analyse and map GeoData. This is great if you have a set of street names, for example, that you want to map. One of the attendees, Lucie Kerley, was there from a company called MACE (Media Archive for Central England), looking for home videos from around the Midlands. She asked the question of whether something like this could be used to show pictures and video next to the points – I suggested that, knowing the infinite magics employed by Google (pixies and brownies and the like – we all know that’s their secret) that it should, in theory, be possible.

So, today, I thought I’d give it a crack. I set up a very simple three column spreadsheet in my Google Docs. This contained a location (street names in Lincoln), a link to a video, and a comment. Importing the video into Fusion Tables, I hit the Visualise > Map button and was presented with a map containing my points. Snazzy. But, clicking on the points didn’t automatically show the video. I tried replacing the link with the YouTube embed code, however Fusion Maps didn’t really like this either, and stripped out the tags. Where to go from here?

I did some digging and found that you could customise the HTML within the popup bubbles in Fusion maps. Maybe this was the key. I tried something…

The one thing that is constant, and identifies videos on YouTube, is an ID string which appears in the videos URL. For example, this URL:, identifies the video FL2Bb6-vD1E. The first thing I did was reduce the video column down to just these ID’s. No other embed code or URL information. I then dug into the templating.

I created a custom template which contained very simple HTML. What was that HTML? The standard YouTube embed code. For the video given above, that’s:

The custom HTML can refer to columns in the Fusion table using a {} notation. In my case, the video column was {Video}. So, I replaced the video ID’s in the HTML with {Video}. This gave me:

Did it work? Did it ever! The map was now enabled with points which, when clicked, showed a video in the popup. This is a real proof of concept – of course none of the videos refer to the roads they’ve been slapped on. But it works. Which is, quite frankly, awesome.

There is absolutely no reason that, with a bit of thought, this couldn’t be extended to include playlists, Picasa (maybe Flickr?) galleries and other comments. It’s a brilliant way to create an interactive multimedia map! The only thing I’ve not tested is whether the Fusion Table updates with the Docs Spreadsheet.


You can find the original spreadsheet here.

Here’s the spreadsheet imported into Fusion Tables.

Here’s the map.