Chris Essig

Walkthroughs, tips and tricks from a data journalist in eastern Iowa

Map Mania

with one comment

Here are a few maps I’ve worked on in the last couple of months:

1. City breakdown of property taxes in Iowa

– This map (shown above) includes property tax rates for every city in Iowa (900+) dating back to 2010 and color-codes them based on their rate. I won’t go into too much detail on how I went about doing it; instead, I’ll refer you to this great Poynter tutorial on how to make a heat map using Google Fusion Tables (which is what the above map is based off of). When you’re done with that, check out their more recent post on mapping data by county, cities, etc. using what is known as shapefiles (Don’t worry. I didn’t know what they were either until I read the post).

For this map, I first found tax rates for every city in Iowa on the Iowa Department of Management’s website. These rates were contained in several spreadsheets (City Tax Rates FY12, City Consolidated Tax Rates FY12, FY 11, etc.).For each spreadsheet I used (six in all: three for city tax rates dating back to 2010 and three for consolidated tax rates dating back to 2010), I basically went in and chopped out all the information in the spreadsheets I didn’t need (Example spreadsheet), which would up being most of it. I then merged the six spreadsheets by city name using Google Fusion Tables.

I then found the shapefile that maps every city in Iowa on the Census site, which contained the geographical data that Google uses when it maps out the cities. I then merged that spreadsheet with the one containing all the tax rates and pulled it into Google Fusion Tables.

Google did the heavy lifting so to speak and mapped out all the cities on the map. I then told FT to map the cities based on their tax rate. The final product is cities with higher tax rates are red; cities with lower tax rates are yellow. The legend is HTML/CSS, which I copied heavily off of the Chicago Tribune News App and their wonderful map walkthrough. When you get the hang of Fusion Tables, check it out. Their maps are much prettier than mine.

The property tax map is on my (barren) Github account. Please copy anything you find helpful.

2. 2008 Flood Buyouts

Compared to the above map, the flood buyout map was a piece of cake to make. We got a spreadsheet from the city of Cedar Falls that listed every home that was offered a buyout from the federal government and their address. Besides mapping shapefiles (like above), Google can also map simple addresses (obviously). So Google can map a spreadsheet of addresses as well.

3. Road to the Dome: A look at the prep teams in the semifinals

I think high school football is popular everywhere. The Cedar Valley is certainly no exception. In Iowa, we have six football classes based on size of the schools. Each year, the semifinals and final rounds of each class are played at the UNI Dome in Cedar Falls. This package breakdowns each of the 24 teams in the semifinals and maps them based on their school address. Readers can then click on each pointer on the map and read more about the team.

From a technical standpoint, this is the first map I’ve put together where a info box DOESN”T open up when a reader clicks on a point on the map. Instead, the information on the teams opens in a table format to the right of the map. This uses Google’s API; I borrowed heavily off of this map + chart example provided by Google. The main difference being is I called a table visualization instead of a chart visualization in the example.

The table below the map (the one where you can select a class and see the team’s playoff schedule) was borrowed heavily off this Google table example.

Here’s the table I ended up with on Google Fusion Tables. If you’re looking for the map source, go to this webpage and click view source. This is where the map is housed.

My Google-powered Javascript is pasted below for anyone who is curious:

google.load('visualization', '1', {'packages':['table']});

function initialize() { 
 var map = new google.maps.Map(document.getElementById('map_canvas'), {
 center: new google.maps.LatLng(42.3, -453.4),
 zoom: 7,
 minZoom: 5,
 maxZoom: 11,
 mapTypeControl: false,
 streetViewControl: false,
 mapTypeId: google.maps.MapTypeId.TERRAIN

 var layer = new google.maps.FusionTablesLayer({
 query: {
 select: 'mappable_address',
 from: 2102557
 suppressInfoWindows: true

 // Add a listener to the layer that constructs a chart from
 // the data returned on click
 google.maps.event.addListener(layer, 'click', function(e) {

 var data = new google.visualization.DataTable();
 data.addColumn('string', 'About:');
 data.addColumn('string', e.row['School'].value);
 ['Class', e.row['Class'].value],
 ['Mascot', e.row['Mascot'].value],
 ['Address', e.row['Address'].value],
 ['League', e.row['League'].value],
 ['League Record', e.row['League Record'].value],
 ['Regular Season Record', e.row['Season Record'].value],
 ['Playoffs - Round 1', e.row['Round 1'].value],
 ['Playoffs - Round 2', e.row['Round 2'].value],
 ['Quarterfinals', e.row['Q-Finals'].value],
 ['Semifinals', e.row['Semifinals'].value]

 var chart = new google.visualization.Table(document.getElementById('chart'));
 var options = {
 'title': e.row['School'].value + ' ',
 chart.draw(data, options);

function changeData(team) {
 var whereClause = "";
 if(team) {
 whereClause = " WHERE 'Class' = '" + team + "'";
 var queryText = encodeURIComponent("SELECT 'School', 'Round 1', 'Round 2', 'Q-Finals', 'Semifinals' FROM 2102557" + whereClause);
 var query = new google.visualization.Query('' + queryText);


function getData(response) {
 var table = new google.visualization.Table(document.getElementById('visualization'));

Written by csessig

November 10, 2011 at 10:20 pm

One Response

Subscribe to comments with RSS.

  1. […] used this table in the past on a map for prep high school football teams. Check this past blog post for more […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: