In the previous Fusion Tables tutorial, we saw how to add data points to a map, using latitude and longitude coordinates.
In this tutorial, we'll be adding polygons to a map. We'll also use a few other of Fusion Tables's mapping options, which let us use "buckets" of numerical data to determine the color of a shape (you can use buckets for points too, to determine which icon is used).
We'll also be practicing a bit of data-organization using Spreadsheets. Unlike the previous tutorial, we'll be doing this before we go into Fusion Tables.
At the end of this tutorial, we'll create a map of the United States showing the results of the 2012 presidential election:
KML stands for Keyhole Markup Language and is one of the shapefile formats that Fusion Tables understands. It contains a series of latitude and longitude coordinate pairs, each representing a part of the overall shape or boundary.
Here's what the KML for Washington DC looks like:
<Polygon> <outerBoundaryIs> <LinearRing> <coordinates> -77.007931,38.966667,0.0 -76.910905,38.8901,0.0 -77.045147,38.788234,0.0 -77.034947,38.814029,0.0 -77.044888,38.829478,0.0 -77.040104,38.838526,0.0 -77.038777,38.862543,0.0 -77.067586,38.886213,0.0 -77.078649,38.915711,0.0 -77.122328,38.932171,0.0 -77.042088,38.993541,0.0 -77.007931,38.966667,0.0 </coordinates> </LinearRing> </outerBoundaryIs> </Polygon>
If you're wondering, how do I make such shapes myself? The answer is: it's kind of complicated. The shape data can come from any number of official sources (such as the U.S. Census). Then it's a bit of work to get it into a KML file, nevermind into a simplified spreadsheet. For our purposes, this KML data is just stuffed into a field called "
Geometry". We leave it to Fusion Tables to parse that data and draw it on an interactive map.
I downloaded the KML data from this public Fusion Table, though I had to vastly simplify the coordinates of Alaska to make it compatible with Google Spreadsheets (there is a limit to how many characters you can stuff into a single cell: 50,000 characters).
So this is not a lesson about finding geodata, it's merely a demonstration of how to incorporate it into Fusion Tables.
Gathering and cleaning via spreadsheet
The first steps involve working inside of the spreadsheet to prepare the data for Fusion Tables.
Election results data
Since we only care about Obama and Romney, we can cut out all the third-party candidate columns. We can also remove the Electoral method column and subsequently, all rows that aren't "Winner Take All (WTA)". If we sort by state name, we can basically align this table with the KML table found here.
Rather than making you go through that copy-and-paste operation, you can just copy the resulting spreadsheet here.
Deriving color columns
If you recall from the previous Fusion Tables tutorial, FT let us define icons for every point using a designated column. For shapes, we can designate a column to be used for the border and fill color.
Let's just do that now while we're working with the spreadsheet:
We are interested in the difference between Obama's percentage of the vote and Romney's percentage of the vote. So create a column named
diff pctand fill it with the formula
Econtains Obama's vote pct and Romney's vote pct respectively). Some of the columns will have a negative percentage, and that corresponds to states in which Romney beat out Obama:
Let's create a second column derived from this
diff pctand name it simply
diff pctis positive, then
victory_colorwill have a value of
#0000FF(blue). Else, the value is
This is the formula:
=IF(J2 > 0, "#0000FF", "#FF0000")
OK, we're done with working in this spreadsheet. Go ahead and export it as CSV so that you can re-import it into a new Fusion Table. The takeaway here is that we're preparing the data, i.e. reshaping it, so that it meets FT's requirements for mapping and coloring.
Mapping shapes with Fusion Tables
If you've skipped all the steps above and just want to see the Fusion Table, here it is
FT will use the
Polygon column, in which we've stored the KML data, as the default column to map.
We will now be working inside the Change map feature styles menu. In the previous tutorial), we configured the Points > Marker icon setting. Now, we will work with Lines and Polygons.
- Click on the Map 1 tab. You should see the shapes and borders of the states drawn already.
- Click the dropdown menu of the tab and select Change Map, which should open up the Configure map left-panel if it isn't already open.
- Under Feature map, click the Change feature styles…
- Select Lines > Line color
- Select the Column tab and the Use color specified in a column
- In that dropdown menu, select
victory_color(or whatever you named the column)
- Hit Save
The borders of each state should now be colored according to the candidate who received the most votes. If your state shapes are all filled in with red, you can go back to the Change map feature styles and, under Polygons > Fill color, change the fill color to gray to make the borders more evident.
Watch me make the borders colorful:
Changing the fill of the state shapes can be done as easily as we changed the lines. However, this time, we'll use the Buckets tab. This allows us to define color based on the numerical value of a column.
- Re-open the Change map feature styles menu
- Select Polygons > Fill color
- Select the Buckets tab
- Select Divide into Custom buckets
- Change the data column to
diff pct(or whatever you named it). This should give you the range of values (
-0.48 - 0.836). Select
use this range
Configure a couple buckets, making negative numbers correspond to red, and positive numbers correspond to blue:
Hit Save and check out the map:
Watch me make the states red and blue:
Thinking about bucket size
There's a certain art and methodology to defining the "buckets". In the example above, we defined only two buckets: red for Romney states and blue for Obama states.
diff pct column contains more than just a binary value, it contains margin of victory. And by adding more buckets, our map can illustrate the intensity of the popular vote victories by state.
Here's a sample configuration for five color buckets:
Here's the resulting map:
Watch me refine the political-color map by adding more buckets:
Play around with the values. Add more buckets, change the ranges, etc. One thing that is hopefully evident is how easy it is to change the message of the map by altering the bucket size and intensity of the color. Note how the map is subtly different if you decide that
+/-10% margin of victory is the cutoff between pink/red and light-blue/darkblue, versus changing that cutoff to
+/-20 margin of victory.
In the Change map feature styles menu, you can also define the intensity by using the Gradient option, which will vary the color proportionate to the numerical values, using a smooth gradient for the continuous scale of values, rather than you defining the buckets by hand.
This is more "scientific", perhaps, but not always "better." In a given race, a 10% margin may be quite significant and worth highlighting, depending on how tight that race was believed to be. In the 2012 election data we have at hand, the margin of Obama victory goes up to 83%, which skews the continuum, but that margin of victory only occurs in the District of Columbia.
This concept of thresholds is a nuanced one. Read Steven Romalewski's critique of a heat map related to NYC's stop-and-frisks to see how changing the thresholds (i.e. the buckets) can drastically impact the message of a map.