Tables can be a boring way to show dataWhen it is a travel story, mapping the data makes more sense
Making boring data interesting
Before I started my MA Online Journalism, I used tables to display data in my blogs. These are very functional, but they are also very dull.
I have now been shown many ways to display data that are both informative and pretty to look at. This blog is about my quest to find a way to present information about 32 airports.
Being a travel story it makes sense to use a map. My goal was to generate a map which:
- Was of the UK
- Would accept a spreadsheet, this could be Google Sheets, .xlsx or .csv formats
- Had a suitable icon to show the airport location
- Had some way of displaying the data when the airport was selected – I wanted this to be shown with a MOUSEOVER type command
- The user could visit the airport’s website from a link in the data box/ToolTip
- This had to be a free service
This blog is divided as follows:
As these websites are free, would I have to compromise on some of my needs?
The spreadsheet data
The data is the extra charges that a passenger may have to pay before they finally get to sit in the aircraft. This was in a spreadsheet format and it contains:
- iconName – used only for Google Maps and Google Fusion to select an airport icon
- Airport Code – IATA three-letter airport code
- Website – airport’s website address
- Latitude/Longitude – needed to place the icon in the correct place
- Airport Development Fee – does the airport charge extra to pay for redevelopment?
- Drop Off – is there a fee to drop off passengers by car at the terminal building
- Pick Up – is there a fee to pick up passengers by car at the terminal building
- Plastic bags for security – clear plastic bags for liquids during the security checks
- Express Security Lane – is there a fast track scheme to ‘jump’ the queues for security
- Luggage Trolleys – is there a fee to use a luggage trolley
The research for this information was carried out via email, telephone calls and by checking the airport websites. I found that some airport websites are more informative than others.
This information (apart from the longitude, latitude and iconName) was to be displayed in a pop-up box, sometimes called a ToolTip Box.
How do others display this information?
The Civil Aviation Authority does have a pdf table that has some of this information for a selection of airports. When I started this the CAA’s version was a couple of years out of date.
It has since been updated in part, with the promise of a further update in June 2017.
Below is the spreadsheet. The idea was to have one version which can be used by all the websites without any alterations, apart from the way it is saved (xlsx, csv, etc.,). I didn’t want to have to compile a new spreadsheet each time I tried another system.
Google Maps is probably the web mapping service that most people can name. It offers satellite imagery, street maps, 360° panoramic views of streets (Street View), real-time traffic conditions (Google Traffic), and route planning for traveling by foot, car, bicycle (in beta), or public transportation.
Many businesses use it to map their office locations and to help prospects to find them via a Google search.
An API is now needed to be able to embed a Google Map. Also, adding a Google Map to a WordPress page isn’t always straightforward, so I needed a plugin. I chose WP Google Maps because of the high number of active installs and the good reviews.
The instructions to generate an API are simple. However, I found the execution difficult as my Google account wasn’t set up for API generation.
I found the problem by using the Admin Console, and the next day I was able to generate an API.
The map is show below:
A functional map
As you can see, the map is a better way to show the spreadsheet data. If I wasn’t looking at various methods of data mapping I may have stopped with this.
However, the airport information is only displayed after the airport is clicked on and I wanted a MOUSEOVER function.
The video below shows how I created this map:
Google My Maps – Conclusion
I do like using Google My Maps, it was my main mapping tool when I was a travel researcher. Back then I didn’t have to embed the maps, I just shared the link with my clients.
I found that setting up my Google account to enable it to generate an API was a pain. The help information wasn’t that clear/beginner-friendly information. Once it was set up, it was easy to embed the map.
I am disappointed about a lack of a MOUSEOVER function, but I was happy with everything else. For this exercise I give Google My Maps 5/6.
Google Fusion Tables
If the data is fluid in nature, any updates to the data stored in the base spreadsheet will automatically the table/map. If a data owner has selected to make their data public, it can be searched for and even merged with another data set.
For example, a public KML boundaries data set can be merged with image data set to make a map more colourful.
In this exercise I actually used Fusion first, this is why the spreadsheet has a column called ‘iconName’. Fusion allows for icon selection automatically via the data set. Each row can have its own icon, but in this case I am only using the ‘Airport’ icon.
Conclusion: Similar to Google My Maps
On the surface, My Maps and Fusion look similar, but they are two very different systems. My Maps is an easy to use (once you have an API) custom mapping software that creates basic maps.
Fusion tables is about displaying data on a scale that My Maps can’t handle. For this exercise it one really isn’t any better than the other. Neither have a MOUSEOVER function, and Fusion doesn’t have a clickable hyperlink to the airport websites from the pop-up data boxes.
So for this exercise I give Google Fusion Tables 4/6.
I have a love/hate relationship with Tableau. Sometimes the drag and drop functionality is so simple to use, at other times I find it a nightmare.
It does offer the MOUSEOVER function I wanted, however, the airport website address isn’t clickable. The icon used is also very dull. I am giving Tableau 4/6.
Datawrapper is very easy to use. It even has the MOUSEOVER function I was after. However, the spreadsheet had to be saved as a CSV file, which automatically added an extra step to the process. But the worse problem was the site’s inability to accept a ‘£’ sign. The pop-up ToolTip Box shows a ‘?’ instead.
To work around this I would have to remove every £ sign in the spreadsheet and then adjust the ToolTip Box to show costs. The icons choice was limited and the urls for the airport websites were not clickable. I give Datawrapper 3/6.
There are other mapping systems, some were interfaces between the user and Google Maps. These ones were ignored. The following systems were investigated as alternatives to the four already mentioned.
There is a Bing Maps Excel add-in, which should, in theory, make it easy to add an Excel spreadsheet to a map. However, it isn’t as straight forward as that. My spreadsheet has separate longitude and latitude columns. The add-in wanted a single column.
This is easy to do via ‘=(F2&”,”&G2)’ to combine this cells to generate a combined long/lat column. I could generate the location pins, however, the tooltip data was the next issue. The data had to be numeric, so no airport name, airport code, website address, etc.
Also, while trying to generate the map Excel kept freezing. These problems were before trying to figure out how to embed the map.
When other free web based systems are available and are easy to learn, it doesn’t make much sense to me to try to map data using Bing Maps, 0/6.
Not a free service once the 10 free maps are used up. Maps4News is a mapping service that enables journalists and newsroom staff to create branded styled maps. It is able to create simple locator maps or more sophisticated enriched, interactive maps.
All the spreadsheet data was obtained from websites, emails and telephone calls. I am comparing 32 UK airports, so there could be many hidden copyright issues as even though the information is in the public domain, some companies do not like third parties using it.
This blog is a comparison or a review of airport costs, I am using the data with the fair dealing defence. This defence is covered on the UK Government website, which says:
“Fair dealing for criticism, review or quotation is allowed for any type of copyright work. Fair dealing with a work for the purpose of reporting current events is allowed for any type of copyright work other than a photograph. In each of these cases, a sufficient acknowledgement will be required.”
Yes, the completed spreadsheet is mine, but the whole point of it was to have information to write a review. So can I enforce copyright if someone was to take my work without permission? Possibly, but it isn’t worth the hassle in this case.
Anyway, using systems such as Datawrapper automatically publishes your data as open source, which will mean anyone can use it without the creator’s permission.
The guidelines for using Google Maps were last updated 17 December 2015. Put simply, if I was using an embedded Google Map for commercial reasons I would have to seek (and probably pay for) permission.
I am using My Maps and the Fusion Map for non-commercial, fair dealing purposes so I do not need written permission.
Sharing your table in Fusion is optional.
Tableau Public is free software that can allow anyone to connect to a spreadsheet or file and create interactive data visualizations for the web. So there are no copyright issues as long as it is used as intended.
Tableau does, however, monitor usage and will remove any data which could cause the organisation legal issues.
I used Tableau in my post.
Datawrapper is an open source platform that is all about sharing, so it is a free to use (dependant on use) service. It is also available to download for free should a user want to use their own servers. Using Datawrapper isn’t an issue, but like Tableau Public, it does publish your data open-source.
Even without any programming knowledge it is easy to generate an interactive map using a spreadsheet. The four systems I have focussed on are fairly self-explanatory and a map can quickly be made with a little bit of trial and error.
As these systems are free (up to a point), I did have to compromise on the final image. However, these compromises are ok as a map can be made in less than 15 minutes.
As well as finding the quickest and easiest method, attention should be paid to copyright issues. Some website owners, such as Ryanair, do get concerned about the use of data mined off their websites.
If you are concerned about others using your data then it probably isn’t a good idea to publish it on the web in the first place. But if this is a concern then keeping your spreadsheet private with Google Fusion is probably the best method. Unless you can load Datawrapper onto your own server.
To get a map that did everything I wanted would be possible with some programming knowledge, but it probably isn’t worth the extra hassle.