Wednesday, November 02, 2011

Adding a Google Chart image to a Fusion Tables map

This past weekend, we ran a story in the Austin American-Statesman looking at five years of crime reports for borders along the Texas-Mexico border (Statesman analysis shows that statistics don't back up claims of rampant drug cartel-related crime along border). We felt it important to look at the actual numbers and to compare them with the anecdotal evidence offered in the Texas Border Security report that says the border is a "war zone."

You can read the story for the details of the report ... what I want to talk about here is this interactive graphic Crime rates along the Texas-Mexico border that used numbers from the analysis to put charts in the info windows of a Google map. I figure this might be a useful skill for others to learn, and if I can do it, others can do, and I'm sure it can be improved on.

The map is a combination of two tables in Google Fusion Tables, one for counties (the polygons) and one for cities (the points.) I created and uploaded those tables specifically for this display using data from the Texas Department of Public Safety. We gathered Texas Crime Reports from 2006 to 2010, and then plotted the yearly change for the 14 border counties, for the Sheriff's departments in those counties and for some of the larger city police departments. We looked at these cities and the unincorporated areas so we could compare rural and urban areas to see if there was much difference. You can look at the many, many worksheets created here

For these Fusion Table records, we just wanted the violent, property and total crime rates and percentage of change, so I didn't need all the more detailed numbers. I did add some columns to aid in coloring the polygons and points the way that I wanted to.

But what I really liked was the adding of the chart of the violent crime rate into the pop-up window. I basically used the Google Charts Wizard to make a graphic as an image, then changed the values in the image call to use tin the "Configure info window" in Google Fusion Tables.

The img tag looked something like this:

src="|2006|2007|2008|2009|2010&chxr=0,2006,2010&chxt=x&chs=300x150&cht=lc&chco=76A4FB&chds=0,1000&chd=t:{2006_Viole},{2007_Viole},{2008_Viole},{2009_Viole},{2010_Viole}&chls=1&chma=40,20,20,30&chtt=Violent+Crime" width="300" height="150" alt="Violent Crime"

The parts in curly braces were the variables that would pull in the data for that record.

I'm sure there are better, prettier and more complicated ways of adding charts to info windows, but this was an easy and successful first attempt!


  © Blogger template 'A Click Apart' by 2008

Back to TOP