Tables can be a boring way to show data

When it is a travel story, mapping the data makes more sense

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

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:

  • Airport
  • 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.

The spreadsheet

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 MyMaps

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

 

Fusion Tables is an experimental data visualization web application to gather, visualize, and share data tables. It can also be used to search for public Fusion Tables and public 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.

Tableau

Tableau was originally designed to commercialize Stanford University’s Department of Computer Science research.  The software queries relational databases, OLAP cubes, cloud databases, and spreadsheets and then generates a number of graph types. Tableau’s mapping functionality is able to plot latitude and longitude coordinates.

 

There are a few different versions of Tableau, which can be summarised into free or chargeable. The chargeable systems, which offer more functionality, are Desktop, Server and Online. Prices start from $35 a month.

 

The free software is Reader and Public. The Tableau map in this blog was created and shared via Tableau Public.

 

The finished map is shown below:

 

Tableau: Conclusion

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

Datawrapper is a quick and easy way to create Web charts. Data is uploaded by either copying and pasting from a spreadsheet or uploading a CSV file.

 

This is an open-source project was created by ABZV, a German journalism-training institute (it closed down in September 2016), using PHP and JavaScript. As it is open-source, the code is freely available on GitHub.

 

Downloaded code can be reworked to remove logos, change colours and host the data on your own servers.

 

Datawrapper charts, maps, and the uploaded data are hosted on Amazon Web Services.

 

It is very easy to use, probably due to the lack of options. It did, however, fail my spreadsheet requirement. You might find this problem when inspecting the map below.

 

Datawrapper: Conclusion

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.

 

Others

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.

Bing Maps

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.

Maps4News

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.

This tool offers the possibility to export different file formats that can be used for both print and digital publications. It uses multiple data sources such as HERE, OpenStreetMap, Andes and Natural Earth.

 

An example of a map can be found here

 

While experimenting with Maps4News I found that the email support was excellent. But all the quick email responses showed was that this system cannot easily generate a map similar to the ones above.

 

One suggestion from support was to add all the ToolTip data a one long string (sentence), which isn’t really practical.

 

Even though the finished map could be branded, Maps4News couldn’t easily generate the map I wanted, and ultimately, it isn’t free anyway, so it gets 0/6.

 

Openstreetmap

 

OpenStreetMap (OSM) is a collaborative project motivated by restrictions on use or availability of map information across much of the world, to create a free editable map of the world.

 

The data generated by the OpenStreetMap project is considered to be its primary output. The data is then available for use to replace Google Maps.

 

Map data is collected from scratch by volunteers, the data is then entered into the OpenStreetMap database.

 

Map editing of maps can be done using the default web browser editor called iD, the Flash-based application Potlatch for  intermediate-level users, and JOSM and Merkaartor for advanced users.

 

iD cannot import locations from a spreadsheet, so my airport data would have to be added individually. This defeats the purpose of this exercise.

 

Polatch could be used to upload locations, but only via a GPX or GEOJSON file or url. JSOM and Merkaartor are too advanced, and I could probasbly upload dozens of maps using My Maps, Fusion, Tableau and Datawrapper before learning how to create maps with these two editors.

 

0/6

 

Conclusion

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.