How to create thematic polygon data maps with Google Fusion Tables

Tutorial goal: Upload and merge two tables (spreadsheet data and geographic boundaries), create an interactive thematic polygon map with an automatic legend, and embed it in a WordPress page or post, like this:

Percent minority students in Hartford-area school districts, 2009-10
click map to view underlying data   Sources: CT Dept of Ed, MAGIC UConn Libraries

Background: The simplest way for my undergraduate students and community partners to create thematic data maps (geographic areas shaded by data values) is to use Google Fusion Tables (GFT), a freely accessible tool for managing, merging, and visualizing data on the web. GFT requires a free Google Drive account (use a regular Google username; avoid limited-access Google Apps accounts). For general information, see Google documentation “About Fusion Tables“ and also the GFT Help Page. This tutorial was updated in Fall 2013 to incorporate new GFT features, such as automatic map legends. See also video tutorial at the bottom of the page.

See related tutorial: How to geocode and create placemark data maps with Google Fusion Tables

Acknowledgement: Tutorial adapted from Create A Map, kh-samples, Google Code.

Download and understand your data
Before starting to map, closely examine your data files to understand their meaning, sources of origin, and limitations.

1) Click on the sample spreadsheet data to download to your desktop, open the file, and examine it.

spreadsheet data (in Excel .xls format) Racial composition of Hartford-area school districts, 2009-10, from the Connecticut Department of Education, CEDaR data site

2) Click on the sample map boundary data to download to your desktop, open the file (using the free Google Earth application), and examine it.

boundary data (.kml format) Connecticut town boundaries, Census 2010, from MAGIC UConn Libraries, boundary data page

Advanced tip: This file is in .kml format (Keyhole Markup Language) and will automatically open in Google Earth for preview. If you download other boundary files from UConn MAGIC or related sites, they will initially appear in a compressed kml format (labeled as .kmz), and must be converted (to .kml) for use in GFT. To convert, open the .kmz file in Google Earth, right-click on desired layer in the Places window, then Save Places As in .kml format, and upload to GFT.

Upload spreadsheet and boundary data to Google Fusion Tables (GFT)
1) Sign in to your Google Drive account and go to Create > Fusion Table (experimental).

If Fusion Tables is not listed, go to Create > Connect More Apps, search for “Fusion” and Connect the Fusion Tables app to your Google Drive account, as shown below:

GFT_AddFusionAppMarked

2) In GFT, import the spreadsheet table you downloaded above. Select “From this computer” > Browse and navigate to the file. Click Next to confirm data, click Next again to add any source info (such as “CT Dept of Ed” or “UConn MAGIC”, then click Finish.

3) Repeat the steps above to upload the .kml boundary data. (The first town name may appear as “County subdivisions not defined,” which you can ignore for this exercise.)

Advanced tip: Any columns highlighted in yellow means that GFT is attempting to geocode location data by placing a point on a map. For this exercise, geocoding is unnecessary because our KML boundary file already contains location data. To turn off the yellow highlighting, in the GFT column drop-down menu, select Change, and select “Text” instead of “Location” for the Description, as shown below:

GFT_ChangeColumn

The “Fusion” part: Merge the spreadsheet table with the boundary table 
4) In your web browser, go to the GFT spreadsheet tab (which should already be open; if not, click on it in your Google Drive tab). In the menu, select File > Merge:

5) Select the table you wish to merge with your spreadsheet table. (In this example, choose the geographic bound

6) Before you merge, confirm the source of the match, which means to select the correct data columns that are common to both tables. In this exercise, go to the right-hand CT Town Boundaries table and select “Name” to match it up with the “District” column of the CT School District spreadsheet data, as shown below. (Match the TYPE of data, meaning Districts and Town names, even if they do not appear in the same order.)

7) Click Next, merge all columns, and View the newly merged table, as shown below:

GFT_MergedTableCreated

Create and Style a Thematic Map with Info Windows
8) In your new Merged table, select the Map of Geometry tab > Change map styles.

9) To modify the thematic map, select Polygons > Fill Color, and experiment with Buckets and Gradients to modify the thematic map shading. Reminder: if you choose to map a percentage column (rather than raw numbers), adjust the range (from 0-100 to an appropriate range, such as 0 – 1.0).

GFTChangeMapFeatureStyles2

Advanced tip: See Mark Monmonier, How to Lie with Maps, Second Edition (University of Chicago Press, 1996), pp. 39-42 excerpt on how same data can be spatially represented in very different ways, by modifying thematic map categories and cut-offs, in the Buckets or Gradients section of GFT. See also ColorBrewer for advice on selecting appropriate map colors and categories. (*To do: look for ColorBrewer with HTML color output.)

10) Select Automatic Legend. Check the box to show a legend, choose a title and position, and include a link to the source data.

GFT_AutomaticLegend

11) To modify how data appears in the map pop-up windows, go to Map of Geometry > Change Info Window Layout. Select boxes in the Automatic tab and/or modify code in the Custom tab.

Advanced tip: To limit the data that appears in your map, go to the Merged Table and select Filter to exclude selected rows, as shown below:

GFTFilter

Embed the interactive map in a WordPress post (or any web page)
12) In the GFT Merged Table, click the Share button in upper-right corner to change the visibility settings of the default (Private) to Anyone with the Link, or Public:

13) Modify the map zoom level and position, then select Map of Geometry > Publish.

14) Modify the map width and height to fit the space allowed by the WordPress theme. (For the Trinity Ed 308: Cities Suburbs & Schools seminar, 600 x 400 pixels looks best.) Copy the long string of code code from the “Paste HTML to embed” field.

GFT_PublishHTML

15) In the Trinity Commons WordPress v3.5 site for this seminar, I have already activated a special plugin (“iframe”) to correctly process the HMTL embedded map code for students with regular authoring privileges. (If you have administrative privileges or your own self-hosted WordPress.org site, this step may not be necessary. WordPress.com sites may be different.)

16) Go to WordPress and create a new post. In the editor, switch from the Visual to the Text tab, which allows users to insert and modify HMTL code. Paste the long string that you copied from the step above. Add square brackets at beginning and end, and edit a few characters to match this format (called a “shortcode”), then publish to view your post.

GFT_iFrameEmbedCode


The interactive Google Fusion Table map should appear in your post, similar to the one at the top of this web page. If not, check all steps above and/or view this video screencast:

7 Responses to How to create thematic polygon data maps with Google Fusion Tables

  1. Pingback: Géolocalisation des personnes ressources pour le CS ENR | E-Entraînement et E-Formation

  2. Carlos Alberto says:

    Dear Jack Dougherty,

    Sorry for my bad English. I’m from Brazil and I’m trying to do something similar to your example. I have xls files and KML, but I can not aggregate them into a list of districts (in Brazil called “counties”), as you did (green area). Could you help me? I could send the KML file for you to take a look?

    Best regards

    Carlos

  3. Pingback: Google Fusion Tables | Bradford Observatory

  4. Pingback: Merging Google Maps and Tables of Data | Gordon F. M. Rakita

  5. Pingback: AHA 2014: Getting Started in Digital History Spatial History & hGIS Breakout Session | Kalani Craig

  6. Pingback: Insérer sa cartographie personnalisée (avec Google Table de fusion et Google Maps) dans WordPress | E-Entraînement et E-Formation

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>