What was it again?

Google Fusion Tables has been around for a while, but inspired by the talks at Google I/O 2011 I felt like finally giving it a try. For quite a while I wanted to generate some maps for my family’s winery, so I thought, let’s see how this would work with Google Fusion Tables.

A map with all customers

I wanted to start simple, so my first goal was to get a map showing a dot for every customer. The first thing to do is getting your data into Google Fusion Tables, so you have to prepare your data for the import.

Geocoding your addresses with Google Fusion Tables

To display a customer on a map, all customer addresses have to be geocoded. Google Fusion Table takes care of the geocoding, you just have to make sure that the address is stored in a single column and not in multiple columns. For example the following SQL query generates a valid import table:

select customerId, name1, name2, ...,
(street & ', ' & zipCode & ' ' & city & ', ' & country) as address
from customers

The result table of this query can directly be imported in Google Fusion Tables, and then simply be geocoded using the column “address” (File » Geocode).

“Manually” geocoding

Unfortunately, you can only geocode 2500 addresses per day, so I couldn’t use the built-in geocoder for my customer table. I had to take a different approach: The Mapbender project provides a file with the polygons of all German postal codes (plz.zip). I took this file, imported the data into a PostGIS database and then generated a KML file with the centroids of all postal codes. Then I imported the KML file into Google Fusion Tables and already could display the postal codes on a map.So now I had my customer table (with columns like customerId, name and zipCode) and the postal code table (with column zipCode and geometry). In a relational database you would perform a join to connect the two tables using the zipCode column. In Google Fusion Tables this join is called Merge (actually merge is a left-outer join). When you merge two tables, the data of the two tables is not copied into the new table. A merged table is more like a view. So when you make changes in the original tables, these changes are also visible in the merged table.

Aggregate your data

Ok, now I could display the customers on a map using the merged table. But there was a big drawback: No matter if there was only one customer or 20 customers in a postal code area, on the map you would only see a single point for that postal code. So I generated a new table thats consists of two columns: the postal code and the number of customers within the postal code area.

select zipCode, count(*) as customerCount
from customers
group by zipCode

But I also wanted to take it a step further and group all customers together whose postal code number is the same in the first three digits (the call of function mid returns the first three digits):

select zipCodeGrouped, sum(customerCount) as sumCustomerCount
from (
   select (Mid(zipCode, 1, 3) || '00') as zipCodeGrouped, count(*) as customerCount
   from customers
   group by zipCode)
group by zipCodeGrouped

Imported in Google Fusion Tables, the table looked like this:

For the new postal code groups I had to create a new KML file in which the geometries of postal codes, that have the same first three digits, were merged using the PostGIS function ST_Union.

Configure the map styling

Once your table has a geometry column, you can put your geometries on a map simply by clicking on Visualize » Map. But often you also want to visualize data associated with the geometries, in my example the number of customers in a postal code group. So I changed the style of postal code polygons to use a gradient.

Postal code areas with many customers are using a darker color, while areas with only a few customer use a lighter color. And this is what the final map looked like:

What is cool

• Easy to get started
  Even if you have never heard of GIS, you can easily import your data, geocode your addresses and create a map.
• Just scales
  This is what I really like about Google Fusion Tables! No matter if you have 10 points or 100 000 points, 10 simple polygons or 100 000 very complex polygons – you won’t notice a difference. Instead of rendering the geometries on the client side, the geometries are rendered on image tiles in the Google cloud. So you also don’t have to worry about simplifying your geometries, Google Fusion Tables does that job for you.
• Integrates with the Google Maps API
  Google added a Fusion Table Layer to their Google Maps API, so it got a lot easier to publish your data on your own maps.

What is not so cool (yet?)

• Limited styling
  Especially for points the styling options are limited. For points, you can currently only choose a marker out of a limited set of markers. Proportional symbols, chart symbols and labels would be nice. Google Fusion Tables is still beta, so likely there will be improvements.
• Style of background maps
  The standard background maps provide too much additional information, that you are not interested in. For the above example you don’t need the street network, a few larger cities would be enough for the background map. If you are using your layer with the Google Maps API, you can change the map style. But not directly in Google Fusion Tables.
• No spatial joins/filters/operations
  Queries like “Get me all customers in this area that live not more than 10 km away from the next store.” are not possible in Google Fusion Tables (without using the API).

So would I use Google Fusion Table to generate business reports? Preparing the postal code geometries was a bit tedious, but you only have to do it once. Then you could import your data using the Google Fusion Tables API. In my case I would prefer a solution that integrates with our existing Pentaho BI Platform setup.

Links

• Google Fusion Table
• “Managing and visualizing your geospatial data with Fusion Tables” at Google I/O 2011 (Slides)
• Google Fusion Tables API
• Google Maps API: Fusion Table Layer
• “New Features in Google Geo Fusion Tables, Places API and Custom Street View” at Hamburg JS Meetup 2011