Colored polygon map with Google Fusion Tables
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 web page, like this:
Click to explore: Percent minority students in Hartford-area school districts, 2009-10. Sources: CT Dept of Ed, MAGIC UConn Libraries
One of the easiest ways 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 for Education 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.
view this video screencast</a>:
https://www.youtube.com/watch?v=ReUAlZsJxP4
To Do **: If you need to display textual data in your legend, rather than numerical data, see this solution: https://github.com/JackDougherty/FusionTable-Map-custom-legend
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
Additional resource: For related data projects, you may download this CT Census Tracts 2010 boundary file (.kml format), from MAGIC UConn Libraries, boundary data page. Also, you can download census tract spreadsheet data to merge with it from Census.gov or SocialExplorer.com (export in CSV format, and multiply 6-digit tract number by 0.01 to add decimals to match).
Advanced tip: These sample boundary files in KML (Keyhole Markup Language) format initially were downloaded from MAGIC in compressed KMZ format, which is not compatible with Google Fusion Tables. To learn how to modify files with free tools, see this tutorial to convert from KMZ to KML in Google Earth and filter results in Google Fusion Tables.
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:
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, see the Map tab and select Change feature 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 info windows, go to Map tab > Feature Map > Change info window. 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:
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 many WordPress pages, 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 my seminars, 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. Currently, iframes do NOT work in most WordPress.com sites.)
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.
Data Visualization for All by Jack Dougherty and contributors is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License. You may freely share and modify this content for non-commercial purposes, with a source credit to http://DataVizBook.org.