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
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.
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
y columns. So we just need to tell Fusion Tables to use those columns.
Go to Edit > Change Column
y column and set its Type to Location
Then click the Two column location and set Latitude to
y and Longitude to
Then click the Save button.
It'll take a few moments for Fusion Tables to update the schema, i.e. "interpret" the
y columns as geocoordinates.
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
When you're done, the map of San Francisco will be covered in red dots, each one representing a crime report.
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.
- In the Map tab, click on the blue Filter button. This will bring up a dropdown menu. Choose the
- 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
ASSAULTcategory, which still leaves 2,437 points on the map.
- Click the Filter button again and choose the
descriptfield: this will allow you to filter by subcategories of crime.
- 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.
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
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
- 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
- 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.
- 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
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:
- 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.
- Open Google Spreadsheets and re-import the newly downloaded CSV.
- Create a new column named
We will now build a semi-complicated logical formula. First, let's think about what we want: We want a
iconcolumn that, when the corresponding
NONE, has a name of one type of icon, e.g.
small_red. And for all other
resolutionvalues, they should have an
Now think about this in terms of basic computer functionality:
If the value in the
resolutioncolumn equals the text string of
NONE, then insert the text string of
iconcolumn. Else, insert
This calls for the logical
IFfunction. 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")
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
ycolumns 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.
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.
- If you're still looking at your Fusion Tables assaults map, select Tools > Publish from the menubar.
- 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.
- Back in the Publish dialog box, customize the width and height of the map to your liking.
Go to your Github Pages repository, e.g.
- 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).
- Commit the file.
- Visit the page at:
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.