Fusion Tables map tutorial: How to find and use public KML geographic data

This tutorial was prompted by questions students raised in my online mapping course: some students ran into problems when working with real-world data sets that (a) are not properly formatted and (b) do not come with ready-made KML geographic data.

For purpose of demonstration, this tutorial focuses on how to search public Fusion Tables data for KML data. We will use a data set that has been formatted and uploaded to my Fusion Tables account; if you want to, you can read this tutorial for how to prepare data. Also, we will only show key steps in creating the heat map; if you need details, read this step-by-step guide on creating heat maps.

The data set and the mapping needs

Say I ran into a table that lists wine consumption in some European countries, and I wanted to create an interactive heat map. Below is a screenshot of the table that I uploaded to my Fusion Tables account, notice that the table has four columns (it was not created by me), but I actually only need two columns: Country and Per Capita in L. And notice that the table does not have KML geographic data for each country.

Fusion Tables tutorial

How to search Fusion Tables public data

As is said in this KML tutorial, there are people from around the world sharing all kinds of data via Fusion Tables. So, at the Fusion Tables public data site, I typed “Europe KML” in the search box, and found one file, nov2012_europe_kml_map, that contains KML data for European countries:

Fusion Tables tutorial

This file, as shown below, is in Japanese and has columns of data that are not relevant to what we want to do. What we actually need from this table are just two columns: Id, which lists the countries; geometry, which lists KML geographic information for each country.

Fusion Tables tutorial

How to create a heat map of European wine consumption

Back to my wine consumption table, and following the step-by-step tutorial, I click File>Merge, and copy/paste link to the europe_kml_map table, as shown in the screenshot, then click Next.

Fusion Tables tutorial

In the next window, I instruct Fusion Tables to match the Country column in my table with the Id column in the europe_kml_map table, then click Next.

Fusion Tables tutorial

In the next window, I need to select what columns to keep in the new, merged table. Notice that the two original tables both contain columns that are not relevant to what I need to do, which is to plot per capital wine consumption on a map; so I selected to keep three columns in this step, then clicked Merge.

Notice here that I’m keeping the Country column in my table, not the Id column in the other table, this is because the Id column in the other table has additional countries for which I do not have wine consumption data.

Fusion Tables tutorial

By this step, I have created a new table with three columns: Country, Per Capita in L, and geometry, as is shown in the screen shot below. The heat map tutorial we referenced above has details on how to create a heat map with a table such as this one; embedded below the table is the actual map I created.

Fusion Tables tutorial

Related posts:

About Mu Lin

Dr. Mu Lin is a digital journalism professional and educator in New Jersey, United States. Dr. Lin manages an online marketing company. He also manages MulinBlog Online J-School (www.mulinblog.com/mooc), a free online journalism training program, which offers courses such as Audio Slideshow Storytelling; Introduction to Social Media Marketing; Writing for the Web; Google Mapping for Communicators; Introduction to Data Visualization; Introduction to Web Metrics and Google Analytics.
This entry was posted in Data journalism. Bookmark the permalink.

Leave a Reply