Introduction to Data Mapping with Fusion Tables

In this tutorial, we cover Google Fusion Tables, a database service that can handle significantly larger datasets than Google Spreadsheets and be used to power data-heavy web applications.

However, we only care about FT's ready-to-go interactive mapping functionality: FT is not the best database software nor the best mapping software, but what it lacks in flexibility it makes up for convenience, especially if you need to quickly produce an interactive map and publish it to the Web.

Fusion Tables is very much like a spreadsheet, though not really designed for quick data entry or fixes. In other words, you want to do most of your data-wrangling and editing in a spreadsheet before you import it into Fusion Tables.

Download this dataset as a CSV: SFPD crime incident reports from January to March 2014

Importing data

Go to Fusion Tables in your Google Drive (you may first have to Connect it as an App).

In the Import New Table dialog, Choose File from your computer. Select the CSV that you just downloaded then hit the Next button. The next dialog box you'll see is a preview of the table. If it looks fine, hit Next again. And you can hit Finish on the next dialog after customizing your table info.

Video

It'll take a minute or so to load, and then it will look like this:

Mapping the data

I'll skip over the table-functionality for now, let's get right to making a map.

The first thing we have to do is tell Fusion Tables which column to use. If we give it a column that contains text, such as "100 Broadway, San Francisco CA", Fusion Tables will geocode that address and place a marker on the map.

The SFPD data already has geocoordinates in the x and y columns. So we just need to tell Fusion Tables to use those columns.

Go to Edit > Change Column

Select the y column and set its Type to Location

Then click the Two column location and set Latitude to y and Longitude to x

Then click the Save button.

It'll take a few moments for Fusion Tables to update the schema, i.e. "interpret" the x and y columns as geocoordinates.

Video

Add a Map tab

Mapping the data is now as easy as creating a Map view, which will show up in the FT interface as a tab. Click the red + sign on the menubar and select Add map.

In the Configure map sidemenu, select y as the column for Location

Video

When you're done, the map of San Francisco will be covered in red dots, each one representing a crime report.

Basic filtering

As in every kind of storytelling, getting to the point is important in map making. There's far too many dots on this map, so let's filter out the less important ones using FT's built-in filtering capability.

1. In the Map tab, click on the blue Filter button. This will bring up a dropdown menu. Choose the category field.
2. A sidebar will appear on the left side. Check the boxes of crime categories you'd like to see on the map. You can see the map dynamically update with each choice. In my example, I choose the ASSAULT category, which still leaves 2,437 points on the map.
3. Click the Filter button again and choose the descript field: this will allow you to filter by subcategories of crime.
4. Clicking the little 3-bar icon in each panel's titlebar will reveal another dropdown, allowing you to filter, well, the filtering choices. For example, selecting"Values match pattern with % wildcards" and then doing a Find for "AGGR%" will quickly filter the reports to just the aggravated assaults. You don't even have to manually check each of those boxes. This filters the list of assaults to 596.

Video

Formulaic work

I mentioned earlier that you'll want to do all of your data cleaning and manipulation in a spreadsheet before you move it to Fusion Tables, because FT simply is less suited for such work.

It is inconvenient to import/export data back and forth. But as I've said before, there's just no do-it-all data tool. With Fusion Tables, we get some quick-and-easy interactive maps, and the price we pay is less flexibility.

In this section, we'll go through the motions, which are necessary to create a map with differentiated icons.

Making something notable

The problem with our aggravated assaults map, which we've filtered to 596 records, is that every dot looks the same. But we might believe that some incidents are more…notable…than others, and it's worth highlighting them in the map.

The most direct way to do this is to specify different icons for certain records. But first things first, what constitutes a more notable aggravated assault? Look at the data again. There's a resolution field, which mentions what the police found or did in response to the call, including making an arrest.

While we're in the Map view with the filters menu open, let's just add the resolution field as another filter so we can get a quick fix of the various ways aggravated assaults were resolved:

It looks like the possible resolutions are split between ARREST, BOOKED (244 cases) and NONE (325 cases), with a smattering of other resolutions (e.g. COMPLAINANT REFUSES TO PROSECUTE and PSYCHOPATHIC CASE).

So let's divide up the map markers into two things: NONE, and everything else (most of which involve arrests), with the opinion that no resolution implies a possible unsettling scenario, such as a stabbing suspect who hasn't been apprehended.

Specifying different map markers

1. To customize the map markers, we need to Change map, an action that can be found by clicking the dropdown arrow of the Map tab. This pop opens the panel titled, Configure map
2. In that panel, click Change feature styles. This will open a new popup window titled, Change map feature styles. In the side-list of actions, click Points > Marker icon.
3. By default, the map is set to the Fixed option, which allows you to choose just one icon for all of the points. Click the Column tab, which reveals the option to Use icon specified in a column

Problem is, we don't have a column in the way that Fusion Tables wants it. Yes, we know we want to ultimately differentiate icons by resolution, but FT wants a column in which the values are things like large_red or f_blue.

In other words, we need to create a new column dedicated to naming the icon we want to use.

IF only we had a column for icons

This is where we need to import/export from FT to Spreadsheets and back to FT again:

1. While in the Map view with the filtered aggravated assaults, select File > Download from the menubar. Choose the options Filtered rows and CSV – in other words, we don't want to export all the 32,000 original rows, just the 600 assaults we've filtered for.
2. Open Google Spreadsheets and re-import the newly downloaded CSV.
3. Create a new column named icon.

4. We will now build a semi-complicated logical formula. First, let's think about what we want: We want a icon column that, when the corresponding resolution value is NONE, has a name of one type of icon, e.g. small_red. And for all other resolution values, they should have an icon value of small_yellow

5. Now think about this in terms of basic computer functionality:

   If the value in the resolution column equals the text string of NONE, then insert the text string of small_red into the icon column. Else, insert small_yellow.

6. This calls for the logical IF function. Here's its documentation:

    IF(logical_expression, value_if_true, value_if_false)
   
    Example:
    IF(A2 = "foo", "A2 is foo", "A2 is not foo")
   

   And this is what we want for our scenario:

      =IF(F2 = "NONE", "small_red", "small_yellow")
   

   

7. We have what we need to make differentiated icons on our Fusion Tables map. So now export the data out of Spreadsheets and import back into Fusion Tables (it's probably easier to create a brand new table). Yes, you'll have to redo that step of specifying the x and y columns as being of the Location type. This is why it's better to this data transformation before you ever go into Fusion Tables, but now you've had a hands-on experience with the more pedantic work in data visualization.

8. Finally, follow the steps previously outlined above in the Specifying different map markers section. Here's the video of those steps, once the re-importing has been done:

Publishing to the web

Have you created a Github Pages repository yet? If not, follow this tutorial and get back here.

One of Fusion Tables's most convenient features is that your work not only lives on the web (inside of the FT app hosted on Google), but it can be easily embedded as an interactive into your a webpage of your choice.

1. If you're still looking at your Fusion Tables assaults map, select Tools > Publish from the menubar.
2. The first thing to do is make the table publicly viewable. FT will prompt you to Change visibility. Click on that link and set your table's visibility settings so that anyone on the Web can view your table.
3. Back in the Publish dialog box, customize the width and height of the map to your liking.
4. Select the Get HTML and JavaScript dropdown, then select all the code and Copy it to your clipboard.

5. Go to your Github Pages repository, e.g.

   https://github.com/USERNAME/USERNAME.github.io

6. Create a new file, name it something like assaults-map.html, and then Paste your clipboard into the file. If you know a little HTML, you can edit the file as you like (between the <body> tags at the very bottom of the pasted code).
7. Commit the file.
8. Visit the page at: http://USERNAME.github.io/assaults-map

Video

Conclusion

Fusion Tables is a great way for creating interactive web maps from large (but relatively simple) data tables. Check out the tutorial on creating intensity maps in Fusion Tables to see other ways of linking data to geography inside of FT.

More info

• Create with Fusion Tables tutorial
• Intro to Data Mashing and Mapping with Google Fusion Tables