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.
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:
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:
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:
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).
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.
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:
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.
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.
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: