:page-liquid: :icons: font :experimental: :imagesdir: /assets/resources/use-case-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.

Come https://support.google.com/fusiontables/answer/2571232[Google Fusion Tables^].

WARNING: 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:

[width=”80%”,cols=”5*“,options=”header”,align=”center”] |=== h|Date h|Country h|Place h|Type h|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 menu:My Drive[More > Connect More Apps]. Search for “fusion”. Click on btn:[Connect]. + image::connect-fusion.png[Connect Google Fusion Table on Drive,737,675,align=”center”] +
  2. Create a new Fusion Tables document. From the Drive’s homepage, click on menu:New[More > Google Fusion Tables]. Then select btn:[Google Spreadsheet]. + image::import-spreadsheet.png[Import Google Spreadsheet into Fusion Tables,831,547,align=”center”] +
  3. Select the desired spreadsheet and click btn:[Select]. + image::import-new-table.png[Import new table,786,401,align=”center”] +
  4. Name the table accordingly and click btn:[Finish]. It yields something akin to the following: + image::fusion-table.png[Import new table,596,284,align=”center”]

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.

image::default-map.png[Default world map view,613,452,align=”center”]

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 https://en.wikipedia.org/wiki/Keyhole_Markup_Language[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 https://support.google.com/fusiontables/answer/171254[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 https://fusiontables.google.com/data?docid=1N2LBk4JHwWpOY4d9fobIn27lfnZ5MDy-NoqqRpk&pli=1#rows:id=1[table^] defining all country geographies. Let’s merge the existing table with it in menu:File[Merge]. In the Or paste a web address here field, paste the URL from the world countries above. Click btn:[Next]. The opening pop-up requires to define the “join” columns of the tables.

image::merge-column.png[Default world map view,478,470,align=”center”]

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

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

image::highlighted-map.png[Highlighted world map view,616,457,align=”center”]

At this point, the requirement is fulfilled. Further refinements would be to access the data via its https://developers.google.com/fusiontables/[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.