: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: +
- The visualization handles geo-location, but no highlighting an entire country
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.
- 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”] +
- 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”] +
- Select the desired spreadsheet and click btn:[Select]. + image::import-new-table.png[Import new table,786,401,align=”center”] +
- 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^].
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.
Posts Tagged ‘data visualization’