April 14, 2011

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:

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.

Upload image, step 1

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.

Upload image, step 2

In the following window, click “Next” to import all data columns. If you’d like to omit columns, uncheck them, then click “Next.”

Import image, step 3

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.”

Merge the data

Go to the shapefile tab and copy the URL from the address bar.

Copy shapefile URL

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.

Map data column to shapefile column

Name the new merged table and click “Merged tables.”

Name merged table

Once the tables have finished merging, create the map by selecting Visualize >> Map.

Create 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.

Initial info window

To clean it up, select the “Configure info window” link above the menu. Uncheck the columns you want to hide.

Configure info window

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.

New info window

To further change the look of the map, click the “Configure styles” link above the map.

Configure map styles

In the pop-up window, go to Polygons >> Fill color and select the “Gradient” tab to change the map shading.

Change 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.

New color scheme for map

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. Copy this table ID.

In FusionTablesLayer Builder, paste that number into “Your table ID.”

Paste 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.”

Check that the map 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:

And if you have a project you’d like the Fusion Tables team to know about, email 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.

Support high-integrity, independent journalism that serves democracy. Make a gift to Poynter today. The Poynter Institute is a nonpartisan, nonprofit organization, and your gift helps us make good journalism better.
Chrys Wu is a journalist, strategist, coder and cook. When she’s not advising clients on user engagement and community building, she launches and organizes Hacks/Hackers…
More by Chrys Wu

More News

Back to News


Comments are closed.