How to make a Google Fusion table map (ie a zoomable map of addresses) for Sherlock special cinemas

Note: be careful if you are using this mapping tool when dealing with people's addresses - it is easy to make their data public (uploading to web, sharing with others) without meaning to. In this example the data come from already-public cinema addresses. 

I'm looking forward to seeing the Sherlock Special ('The Abominable Bride') at New Year, it's going to be shown on BBC One on New Year's Day but also in cinemas around the world - in Australia, Canada, New Zealand, United States and here in the UK. The Sherlockology website has a meta-list of country pages and more cinemas, and countries are being added (this is the current UK listing).

I thought it would be fun to take the alphabetic list of UK cinemas and put them on a map, that looks like this.

You can zoom in and out of the map and click on any of the red dots to see details of where each cinema is. For the next update I'll add some colour to mark cinemas that have Dolby Atmos sound systems (the Sherlock special is apparently mixed for Dolby Atmos). Most of the cinemas don't have Dolby systems and of those that do most have 3D or 7.1.

There are four stages to this

a) gathering the data

b) using Google Fusion tables to create the map

c) Troubleshooting

d) ongoing updates and improvements

A) Gathering the data

- make a table that has, at minimum, a list of postcodes or addresses - that's all you need to start

The raw data was here, each line consists of a town or city, the name of a cinema, sometimes a web address and a phone number. These data need to be put into a table which can be done in an Excel spreadsheet or a Google Drive spreadsheet (you can use your Gmail account to log in, there's no requirement to download anything you can do it all online). If you don't want to use either of these then you can upload correctly formatted data in a plain text file: .csv (comma-separated variables), .tsv (tab-separated variables), or .txt (text). I used Google Drive spreadsheets for this. 

My current spreadsheet looks like this (click to make it bigger)

I put the cinema name in one column and copied the name of the town / city into another then searched for the cinema's postcode and added those in another column. Postcodes alone are sufficient to create a Google Fusion map (it just takes address data and locates on a map ('geolocated' data)) and anything else you add means that when people click on each point they'll have more information. 

It took me an hour and a half (I was having breakfast and watching Frasier at the time so not rushing) to get all the postcodes and have a ready-to-map table, this is really the longest part of the process. Of course you can spend hours tinkering and improving but once you've got to this stage you're ready to go to the next stage, the rest of this section is about 'finessing' the data.

I've added some extra info about the cinemas' websites in two columns. For example there's a generic Odeon websites address but each Odeon cinema also has its own website. There's a column for the Dolby Sound system that the cinema has and I found the information from Dolby's own website. Some cinemas in London are listed under their local name, eg 'Covent Garden' or 'Greenwich' so I created a second column called 'area' and put those locations in there and changed the city to London.

There's also an 'icons' column which I added after making the map above (note that all the dots are red). In the next version the cinemas with Dolby Atmos will have green map-pointers, those with 3D or 7.1 will be blue and the rest in yellow, bit more colourful. It's a work in progress so there'll be more stuff added or refined.

Pro-tip

When filling in information that's currently missing use the filter icon (looks like a funnel) to show only the rows where your item of interest is currently missing. Clicking the filter icon adds to all the column headers a small downward arrow - if you click on it it will present you with a single copy of everything in that column (eg in the 'City / Town' column London features several times but will show up just once in the filtered list). You can then select, or deselect to show only what you're interested in and the rest will be temporarily hidden. Just click the filter icon again to turn filtering off.

B) Using Google Fusion tables to create the map [About Fusion tables]

- this is the probably the quickest part of the process

Once your raw data is in a tabulated form you can go to Google Fusion tables. You will need to be signed into Google to do this (or you can send your file to a Google-using friend and ask them to make the map but note that if you want to amend it later you'll have to repeat the process).

You are presented with a screen that looks like this

 1. Click on Google Spreadsheets to use a file on Google Drive, or on Browse... to choose a file on your computer. I used Google so the next screen I saw asked me to choose from my spreadsheets.

2. Just click the spreadsheet you want and press 'Select'. Then wait for the sheet to load.

3. This is the point at which you can pick 'Sheet 1' or another sheet within the spreadsheet, depending on where your data is. In most cases you'll probably only have one sheet but be aware if you're uploading a file from your computer that although it will process the data from Sheet 1 (if you choose that) I think it uploads all of them so be careful if you have sensitive data in the other sheets.

4. This is the bit where it asks you to confirm what column it should use to map addresses onto the Google map. In my case it's Postcode but it could equally be something else.

5.  This screen lets you finish importing the table by adding some information. Because the data came from Sherlockology I put their website in the Attribution page link and wrote a more detailed description that what is currently here. You can add more detail later once the map is made. Press Finish to get the data properly formatted in Fusion tables style.

 

6. This is what the final fusion table looks like - there are three tabs each presenting the same data in a different way. In 'Rows' you can see each row as in a spreadsheet, in 'Cards' you can see the data from each row presented as it would look on a business card - this is what's shown when someone clicks on a location pointer (the red dots) in the 'Map of Postcode' in tab 3. 

The map isn't ready though - you need to click on that tab to bring up the 'begin geocoding' options which looks like this, below.

7.  Clicking on the third tab will start the process of geocoding automatically, and once done you will see something like the map below. It's best to zoom out first to see if there are any obviously wrong map points (see the one in the US). You can click on that red dot to see its card which will tell you which row caused a problem - you may need to add in some extra detail. For example there's a Greenwich in London UK as well as one in Connecticut US.

8. Zoomed out to show that while the majority of the UK postcodes are correctly positioned at least one is in the wrong place. On a real map you can click any of the red dots to see what data point it's meant to be.

Making the red icons different colours (depending on properties)

If you've added an Icons column with different coloured icons to reflect differences in your data (in my case I've used colours to denote different types of sound systems) you will need to click on 'Change feature styles' (visible in Picture 7) and then select the Column option (basic red dots = fixed, column lets you select which column you put your icon names in, eg large_green or small_blue etc and buckets is for continuous data that you want to segment, eg Low, Medium, High).

9. Using the map feature styles option and the marker icon options to change their colour. Pick whichever column has your icon names in (you can call the columns anything you like by the way).

And here's the most recent example of the map - and I've also just spotted that yellow map icons don't work well when zoomed in though they're fine at a distance. I'm going to change them back to red! 

10. Green icons show cinemas with Dolby Atmos. Blue icons show those with Dolby Surround 7.1 or Dolby 3D, yellow ones show cinemas with no Dolby system in place (according to the information from Dolby's own website, which could be out of date of course - I need to check!). I've clicked on an example and you can see that data point's 'card' with relevant info. In the next update of the map this data point should also have its own local website too, and a tidy up of the original list text (last line) where the phone numbers have got a bit mangled.

Sharing your map

At the top right of every map (or Google document in fact) there's a bright blue Share button which lets you share a link for people to view only, or for them to edit, or gives you the option of inviting people by their email addresses to view (or, in the case of the originating documents, edit the data).

C) Troubleshooting

After my first map I spotted that one of the Dublin addresses was mistakenly in Poland and the NE28 postcode of the North Shields cinema appeared in the US. Limerick doesn't use postcodes but when searching for the name of the cinema on Google it showed up fine on Google Maps. So for those ones I added 'Ireland' as a location clue, or used the name of the cinema instead. Whatever works.

D) Ongoing updates and improvements

I'll write this section when there are some :)