/ DATABASE, DATA VISUALIZATION

A use-case for Google Fusion Tables

I’ve been traveling somewhat during the last few years, and I wanted to check on a map which countries I already visited. There’s one requirement: I want the whole area of countries I’ve visited to be highlighted. There are a couple of solutions for that:

The Elastic stack

Data can be stored in ElasticSearch, and there’s one world map visualization available for the Kibana dashboard. This works, but:

  1. The visualization handles geo-location, but no highlighting an entire country
  2. It requires some setup, and I’m a developer - read that I’m lazy.
Dedicated app

To be honest, I didn’t search but I assume "there is an app for that". But by using a dedicated app, you’re not the owner of your own data anymore and that doesn’t suit me.

Google Maps

Google Maps allows to add layers, and dedicated data. This solution would be a great fit, if it was possible to easily highlight countries. And still, there’s still a lot of JavaScript to write.

Fusion Tables is an experimental service offered by Google.

Creating the table

While it’s possible to input the data directly into Fusion Tables, it’s easier to do that in a simple Google Spreadsheet. The following is sample data:

Date Country Place Type Event

2017/06

Denmark

Copenhagen

Conference

JDK.io

2017/06

Sweden

Malmö

Conference

JKD.io

2017/05

Ukraine

Kiev

Conference

JEEConf

2017/05

Greece

Athens

Conference

Voxxed Days

Such a spreadsheet can easily be imported into Fusion Tables.

  1. Connect Fusion Tables on Google Drive. From the Drive’s homepage, go on My Drive  More  Connect More Apps. Search for "fusion". Click on Connect.

    Connect Google Fusion Table on Drive

  2. Create a new Fusion Tables document. From the Drive’s homepage, click on New  More  Google Fusion Tables. Then select Google Spreadsheet.

    Import Google Spreadsheet into Fusion Tables

  3. Select the desired spreadsheet and click Select.

    Import new table

  4. Name the table accordingly and click Finish. It yields something akin to the following:

    Import new table

Out-of-the-box, there’s a Map of Country tab that displays each data line on a world map. Unfortunately, it’s a simple dot at the center of the country. It doesn’t fulfil the initial requirement of highlighting the entire country area.

Default world map view

Changing the Location field to "Place" instead of "Country" will place dots at the correct location instead of the country center, but still no highlighting.

Merging multiple Fusion Tables

Fusion Tables support geometries that can be defined using the Keyhole Markup Language format. That can fulfil the highlighting requirement. Yet, that would mean defining the geometry of each country visited manually; it requires an effort I’m not prepared to make. Fortunately, it’s possible to "join" multiple tables - it’s called merging. Merging creates a new table, with both tables associated in it. Even better, if any of the initial table data changes, it’s reflected in the merged table.

Good news: there’s an existing publicly accessible table defining all country geographies. Let’s merge the existing table with it in File  Merge. In the Or paste a web address here field, paste the URL from the world countries above. Click Next. The opening pop-up requires to define the "join" columns of the tables.

Default world map view

Click Next. In the opening pop-up, tick the checkboxes of columns that will be part of the merged table. Click Merge. Wait for the merge to happen. Click View table.

Now, on the world map tab, changing the Location field to "geometry" yields the expected result.

Highlighted world map view

At this point, the requirement is fulfilled. Further refinements would be to access the data via its REST API.

Conclusion

Fusion Tables is a no-fluff, just-stuff cloud service that allows to easily display data in various ways. With its ability to join on other tables, it’s easy to re-use existing tabular data.

Nicolas Fränkel

Nicolas Fränkel

Developer Advocate with 15+ years experience consulting for many different customers, in a wide range of contexts (such as telecoms, banking, insurances, large retail and public sector). Usually working on Java/Java EE and Spring technologies, but with focused interests like Rich Internet Applications, Testing, CI/CD and DevOps. Also double as a trainer and triples as a book author.

Read More
A use-case for Google Fusion Tables
Share this