How to make a non-Flash intensity map in Fusion Tables

I’ve recently become very, very intimately acquainted with the advanced innards of Google Fusion Tables in a very unhealthy way. You think I’m joking, but I’m not. One of the coolest visualizations Fusion Tables (I’ll say FT from this point forward) is capable of would be its Intensity Map option. Automatically pull in the shapes of various countries or states, match up a certain number (population, incidents of something, etc.) with a range of colors, and color in those shapes according to that number. A light green Washington state has less incidents of something than a dark green New Jersey, for example. This is cool, but renders in Flash. There’s a time and a place for that technology. Problem is, it doesn’t play well with other FT maps.

In the world of computer-assisted reporting, we’ve talked about the value of merging datasets for a long time. Overlay crime incidents with average income. Is there more crime in poorer areas? And for both analysis and visualization, wouldn’t it be better if we could visually overlay specific points on an intensity map, not just looking at straight text. To make this happen, we have to roll our own intensity map, since we can’t combine the FT-generated Flash ones with FT points visualized as a layer, which is JavaScript-based.

To demonstrate how this might work, we’re going to throw together a nation intensity map divided by state. County is also possible, and I just did it for my final project at PBS. But to keep this example reasonable, we’ll stick to the states. I’ll just be mapping population of states, as a base layer for other data. It comes from the July 2009 count from the Census Bureau. The raw data is here, and my cleaned data is here: Download it to your computer, so we can have a consistent example to work from.

Find a sample completed map here, in my example Fusion Table: http://www.google.com/fusiontables/DataSource?snapid=S242838rYvt

1. Take a look at the Fusion Table Google provides here: http://www.google.com/fusiontables/DataSource?dsrcid=227275. Polygons are stored by Google in the KML (Keyhole Markup Language) format. You just need to put this special type of file into a column in your Fusion Table, and tell the map this is the geography you want to use. The Census Bureau has uploaded a lot of its data in KML format, which you can find by Google searching “kml us counties” or some such. If you go to the visualize map option, you’ll even see all the state’s outlined. Note the ID number at the end of the URL, in this case: “227275″.

2. Prepare your own data. Make sure you have column names, and that your states or counties are formatted like the Fusion Table with geography you were just looking at. For example, their states use full name format, “Alabama”, not “AL”, so you must do the same.

3. When your data is in shape, create a new Fusion Table and upload your data.

4. Go to the Merge menu on the Fusion Table you created. In the upper right, there is a place to enter a table ID. Enter that ID you copied at the end of step 1, in this case, “227275″. Click Get.

5. It will bring in this table’s column as a list to the right side of this selection window. Select “name” as the column you want to join with your table. Select “state” on the left, which is a column from your table that you wish to join. Both of these columns in separate tables should match, i.e. “Alabama” –> “Alabama”. On the right side, click add subset of columns , and uncheck all boxes that aren’t geo. We don’t want to be adding extra columns to our table haphazardly, we just need that geographic info.

6. At the bottom of this form, select a name for a new table, which will combine the data you brought in with the data Google provides. Click “Merge Tables”, and give it a minute or two.

7. Click on visualize Map. You should see a lot of red shapes, that correspond to various states. If this doesn’t work, just above the map is a drop-down menu labeled “Location.” Make sure “geo” is selected, it may be trying to use your state names, unsuccessfully, as opposed to the geographic data we worked so hard to import.

8. Last step is to place an actual intensity in the map. We do this by clicking on “Configure styles” just above the map. Click on “Fill Color” under “Polygon”. You’ll see it defaults to a general red fill color that is under the “Fixed” tab. But we want color variety, so we’re more interested in the other options. To mimic FT’s typical Intensity map function, go to Gradient, and select the bubble that says “Show a gradient.” It defaults to that green, but you can change the color. Adjust the maximum number from “100″ to just above the maximum value you have. With the populations, I choose 35000000 (don’t use commas, even if the number is large). Under select column, choose what number column the map should use to assign colors. Here, we’ll use “population.”

Another option is to choose the buckets tab. Instead of a gradient of one color, we divide the numbers into equal buckets. If we have 5 buckets (the default), the lowest 20% of numbers get the first color, next 20% get the second, etc. You can select the colors of the different buckets, how many buckets you want, and again, you must set an accurate maximum number.

9. Now, you can embed this more flexible intensity map, just by grabbing a link the way you would with any FT map. If you want to overlay with points, create the points as a map in a separate table, and follow my previous tutorial on creating an embeddable map with multiple layers.