Google Fusion Tables! How could I forget? I remember trying out Google’s first attempt at thematic mapping when it first came out but I hadn’t given it a second thought since then; probably because it makes maps like this (percentage of state population on Facebook):

and like this (capacity of ethanol facilities across the US):

The Good:

1. This data was publicly available and easily searchable.
2. I made each of these maps in about 2 minutes.

The Bad:

1. Wrong projection for choropleth map
2. On the proportional / colored symbol map, a single variable is being redundantly encoded in both size and color.
3. Single color scheme
4. I can only set the extent by typing the name of a country in the text box

I understand that for many people, this tool gives them mapping ability that they didn’t have before. But if you care about having attractive and correct maps, it doesn’t go nearly far enough.

I’m going to do a quick reproduction of each of these maps in indiemapper, and in the process, show you how you can take advantage of the great data in Google Fusion Tables and still make great maps using indiemapper.

Google Fusion Table Basics

Google Fusion Tables are dead simple to use, so I won’t spend too much time on this. First, head over to http://tables.googlelabs.com/ to see what’s there.

• You can upload your own data by clicking “new table”
• You can browse a ton of publicly available data by clicking “public tables”
• You can search for a specific dataset by typing into the search box

Like I said: dead simple. To get a more in-depth tour, visit Google’s own overview here -> http://tables.googlelabs.com/public/tour/tour1.html

US Facebook Participation

The first table I found was Percent of state population on Facebook (2009). To get the data into indiemapper, I’m going to have to combine the tables. Fusion tables do have some geographic export abilities, but it will only export points and I want to attach this data to state polygons. I can export the data by going to File->Export and my CSV will automatically begin downloading. For my geography, I grabbed a states file that also had some census data (in case I wanted to do some bivariate maps).

I did the standard tabular combine process in OpenOffice as described in this post. A few things to note:

• My Facebook data didn’t contain any values for the District of Columbia. I had to add an extra row of zeroes to make sure the data would match up.
• I had to do a find / replace to remove all the commas and %’s in the data so indiemapper would recognize the values as numeric instead of text.

You can download the final combined shapefile here -> http://indiemapper.s3.amazonaws.com/data/facebook_states.zip

I loaded the file into indiemapper and made a simple choropleth of percent of population that is registered for Facebook. I projected the map to Albers Equal Area Conic centered on 100°W. Then I created a choropleth map of the percentage values, just like in Fusion Tables. Finally, I added a label layer and did some quick adjustments to label placement and rotation. After about 3 minutes of work, it looked like this:

Download the IMP

Since I had the census data attached to my shapefile, I thought I would make a bivariate choropleth, just for kicks. Since Facebook used to be the domain of college students alone, I thought I would compare the number of Facebook users to the rate of 18 – 21 year olds in each state. I added the bivariate cartogram layer and adjusted some of the overlapping states to come up with:

Download the IMP

Ethanol Facilities

Google Fusion Tables include a fairly robust geocoding feature, so I wanted to test it on data that was made of discrete points. This dataset shows the location of ethanol facilities along with their capacities. From the table, if I go to Visualize -> Maps, there is a link to export the KML.

I thought I would be able to take this exported KML and use it directly in indiemapper, but unfortunately, I was wrong. The exported KML handled the geography perfectly, however, it did not maintain any numerical data for me to use. I won’t go into too much detail other than to say, the KML data looked like this:

<description>
   <![CDATA[
      <b>Longitude</b>: -103.334403992
      <b>Latitude</b>: 34.1861915588
      <b>Company</b>: Abengoa Bioenergy Corp.
      <b>Address</b>:
      <b>City</b>: Portales
      <b>State</b>: NM
      <b>zipcode</b>:
      <b>Company Website</b>:
      <b>Feedstock</b>: milo
      <b>Status</b>: Not Producing
      <b>Capacity [Mg/y]</b>: 30
      <b>State Date</b>: July 2005
   ]]>
</description>

when it should look like this, as described in the KML reference:

<ExtendedData>
   <Data name="longitude">
      <displayName><![CDATA[Longitude]]></displayName>
      <value><![CDATA[-103.334403992]]></value>
   </Data>
   <Data name="latitude">
      <displayName><![CDATA[Latitude]]></displayName>
      <value><![CDATA[34.1861915588]]></value>
   </Data>
   <Data name="company">
      <displayName><![CDATA[Company]]></displayName>
      <value><![CDATA[Abengoa Bioenergy Corp.]]></value>
   </Data>
   <Data name="address">
      <displayName><![CDATA[Address]]></displayName>
      <value><![CDATA[ ]]></value>
   </Data>
   <Data name="feedstock">
      <displayName><![CDATA[Feedstock]]></displayName>
      <value><![CDATA[milo]]></value>
   </Data>
   <Data name="status">
      <displayName><![CDATA[Status]]></displayName>
      <value><![CDATA[Not Producing]]></value>
   </Data>
   <Data name="capacity">
      <displayName><![CDATA[Capacity (Mg/y)]]></displayName>
      <value><![CDATA[30]]></value>
   </Data>
</ExtendedData>

Since I was only looking for a single numerical value (capacity), I did a quick find / replace to only get the single value. You can download the edited KML file here.

Once I was finally in indiemapper, I wanted to make a quick map. First, I added the Facebook shapefile from the original example. It will be a good basemap showing states and maybe I’ll want that census data in there too. Then, I added the KML data and created a proportional symbol map from the “Capacity” variable.

The map looks good but I think there is more to the story than just this. I will use that census data to create a quick choropleth map of crop acreage and add that under the proportional symbols.

Download the IMP

Now we have a better picture of not only where the ethanol facilities are in the US, but also one possible explanation of why they’re there.

Conclusion

When I started this post, I had hoped that Google Fusion Tables were going to play nicer with indiemapper. While I think the redesigns are a massive improvement over the map visualization built into Fusion Tables, the inability to export usable KML is a major problem. While you still may want to take a look if you’re looking for publicly available data, if it is going onto a map, I would check Geocommons first to see if it is in a usable form.

This entry was posted on Tuesday, June 8th, 2010 at 10:02 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.