Friday, May 20, 2011

My Fusion Table (used in previous blog)

Never heard of Fusion Tables? That's a shame because I think this might be one of Google's best integrations.

The short story is that Fusion Tables are just database tables that Google hosts online for you.  There's a nice collection of ways to view or chart your data on the site, but you can also use SQL queries to pull your data or subsets of your data to your javascript or other web-friendly application. I'm just beginning to discover Fusion Tables, but let me show you what I know.

Like most things Google, Fusion Tables are free.  They do offer services and enterprise-scaled access for a fee, but for smaller jobs, the free stuff is perfectly adequate.  And if you're worried about data privacy, they have a few settings that allow you to make your data public (listed), private (restricted by google login), or semi-public (anyone you give the url to can view without authentication, but the table is not listed).  The "Share" button in the upper right corner of your screen helps you manage the visibility of your table and lets you invite people to use your data.  A lot of people DO share their data, so there's a growing body of publicly available structured data in Fusion Tables. In another post soon, I will share a list of interesting sites that use Fusion Tables.

If you'll be doing any programmatic importing of this data, for instance with javascript, and you don't want to use OAuth authentication, make sure your table is Public or Unlisted.

You can see from the table image above (or by following the link at the top of this post) that this table is a three rows of data in six columns.  If there were more columns than fit on your screen, you would see forward and/or backward arrows on the right edge for moving to the next page (this is a feature I'm not crazy about - I'd rather have it all on the same page).  You can sort by any of the columns, and if you're the editor or have write access you can edit values right on the screen and Google updates the table with some AJAX code.

There are easy to navigate menus, like File, which lets you create new tables, export this table to CSV, import from CSV, KML files, Microsoft Excel, Open Document spreadsheets, or Google docs.  The Edit menu lets you alter your schema.  The View menu lets you filter or aggregate your data.

The stunner, however, is the Visualize menu, which lets you view your data in a variety of ways, including as Google Maps.  It may be occurring to you now why my Location column has coordinates in it.  In map view, the Location column specifies geo locations - you can use a Google pop-up (looks like the Google Earth icon) dialog to find locations or paste in coordinates as text.  And I color the markers with the Loc_Color column (in Map view the Configure Styles link lets you tell the app which column has marker color info).  The map view has a drop-down list that lets you view geometries on the map as well, and I've put polygons into the table just for this.  I used Configure Styles to associate a column of color values (Geom_Color) with the Geometry text I pasted into that column.  That text consists of KML code that I pulled out of KML files I made - I'll get into that in just a minute - and Fusion Tables detects that it's KML code just as long as you don't have new line characters in it.  I also threw some meaningless numbers into the table (Numeric_Value) so you could view them through the various Chart types.

Unfortunately you can't view Locations and Geometries at the same time.

If you're not impressed yet, I hope I can show you a few things in an upcoming installment that will make Fusion Tables worth your while.

I haven't forgotten that I was going to tell you about making KML files, in particular making geocoded geometries. You might already have some of the tools to draw geolocated shapes - Google Earth, and even Google Sketchup.  If you have neither of these, you can use Google's My Maps and draw right on a Google Maps surface, which is what I used to draw New Mexico and Texas (see photo down below).

Google Earth is probably the most obvious, since it's possibly THE geographic tool of choice and it has a Add Polygon tool right on the top bar.  Just point and click, move the mouse, click again for another point, etc. until you're done and then double click.  Of course you then save your work by selecting an item or a folder of items on the left side and using File/Save.  Google Earth makes a KMZ file which is just a zip file; if you change the file extension you'll have no problem opening it and extracting the doc.kml file, which is a plain text file full of XML tags that are specific to geographical positioning.

The limitation of Google Earth is that it doesn't have very sophisticated drawing tools (I could be wrong about this); if you're familiar with Google Sketchup, though, you'll know that drawing all kinds of insane or intricate shapes is really easy.  Sketchup also has the ability to geolocate your shapes (File/Geolocate/Add location) and export to KML.  One drawback to Sketchup is that the size of the geographic space you can import at one time in order to draw on it is limited (it's about 6500 ft * 6500 ft). You can download more imagery to make a patchwork of tiles, though.

If you won't have any of that installing nonsense, or work on a platform that doesn't support Google Earth or Sketchup, you can just point your browser to and click on My Maps.  You WILL need a Google login to use this feature. In My Maps, while editing any map of yours, you will be given tools to draw lines and polygons and placemarks.  Then, with the map link (Link button in the upper right corner of your map) you can grab the url and tack on &output=kml and Google will send you your work in a KML file.

Here's the map I made in order to draw the geometries for my Fusion Table.

and here I trick Google into sending me the KML file.

Hey, you can do similar things and more at

1 comment:

  1. By the way, if you're looking for a table with all the states filled in with geometries and almanac facts, you'll find a public Fusion Table at