Online journalists are well aware of how important data can be to stories. But how do we give visual context to raw information without an army of developers at our disposal?
If the data has been normalized and saved as an Excel file, .ods, .csv or .kml, Google Fusion Tables can help. Fusion Tables manages large collections of data so you can query, map, timegraph, chart, and add interaction — including user comments — to them.
News outlets have used Fusion Tables most often for mapping data. Take a look:
- The poorest and richest places in England (heat map from The Guardian)
- Worldwide map of nuclear power stations and earthquake zones (heat map from maptd)
To make a heat map in Fusion Tables, follow this example tutorial, which is adapted from a workshop Google Developer Programs Engineer Kathryn Hurley recently led for Hacks/Hackers NYC.
Start with with a spreadsheet of the data to map, saved as an Excel or OpenOffice file, or in .csv format. To show boundaries on the map, you’ll need shapefiles (saved as .kml files) as well.
For each file you’d like to upload, select: New table >> Import table.
In the Import New table window, choose the data source in the left-hand column and click “Choose file”, then click “Next” to begin the import.
In the following window, click “Next” to import all data columns. If you’d like to omit columns, uncheck them, then click “Next.”
In the next window, fill as many or as few of the boxes as you need. Click “Finish.”
If your shapefile isn’t in .kml format, try Shpescape, made by Josh Livni. Shpescape can convert .prj, .shp, .shx and .dbf to .kml.
Now that both the data and the shapefile have been uploaded as individual tables, they need to be merged:
Open each table in a separate browser tab. In the data file window, select “Merge.”
Go to the shapefile tab and copy the URL from the address bar.
Return to the data file window and past the URL in Box 2, “Merge with.” Then click “Get.”
In the two columns below, you’ll have the column headers from each table. Click the radio button corresponding to the column you’d like the data to be mapped to. In this case, region to state name.
Name the new merged table and click “Merged tables.”
Once the tables have finished merging, create the map by selecting Visualize >> Map.
To show the shapefile outline, be sure Location is set to “geometry.”
The resulting map will work, but it doesn’t look very interesting. And the info bubble for each individual state has extraneous text in it.
To clean it up, select the “Configure info window” link above the menu. Uncheck the columns you want to hide.
To further change what’s in the information window, click the “Custom” tab and edit the HTML. Click “Save.”
The map should now reflect your changes.
To further change the look of the map, click the “Configure styles” link above the map.
In the pop-up window, go to Polygons >> Fill color and select the “Gradient” tab to change the map shading.
In the drop-down menu, choose the column that will be assigned the gradient, pick colors, and give the colors a range. You may need to experiment a little to see what works best for your map.
Click “Save.” The map will update to the new color scheme.
To make changes to the map itself — and add a search box — use FusionTablesLayer Builder.
The Layer Builder is still in an experimental phase, so there may be hiccups. Google Code has documentation that explains its use in detail.
To try it out yourself, make sure your map is public by clicking on the “Share” button in the right-hand corner above the map and setting the Visibility option to “Public.”
Then copy the table ID, which are the numbers immediately after “dsrcid=” in the URL. 
In FusionTablesLayer Builder, paste that number into “Your table ID.”
When you’re ready to show your new map to the public, grab the embed code. First, make sure your Fusion Table is public by clicking on the “Share” button in right-hand corner above the map and checking that the Visibility option is “Public.”
Then click on the “Get embeddable link” link above your map and copy the resulting code. Paste the code into an HTML document, save the file and open the file in a browser. You’re done.
Don’t let this long post fool you: If your data has been scrubbed and properly formatted, it is relatively quick to get a map, chart or SIMILE timeline started with Google Fusion Tables.
To learn more, see these tutorials:
- Google’s Fusion Tables tutorial presented at NICAR 2011
- Additional Google Fusion Tables tutorials
- A quick video tutorial from Circle of Blue, which reports on water issues worldwide.
And if you have a project you’d like the Fusion Tables team to know about, email googletables-feedback@google.com or contact them on Twitter @GoogleFT.
Chrys Wu is a journalist, strategist, coder and cook. When she’s not advising clients on user engagement and community building, she organizes Hacks/Hackers meetups and events to bring journalists, developers and designers together to reboot news. She’s on Twitter @MacDiva.
Comments