Fun with FusionTables

Monitoring the State of the World's Oceans

There are thousands of monitoring platforms that gather information about the state of the world's oceans.  The NOAA Observing System Monitoring Center and its partners have an on-line tool for looking at the state of the observing system.  The actual scientific data collection produced by the observing systems (platforms like volunteer observing ships, moored and drifting buoys, and floats and gliders) is much more extensive and complex, but we are focused on understanding the state of the systems making the observations.  The final demo described below is available for you to play with if you'd like.

Fusion Tables

We are always looking for new tools and so we decided to take a look at Google Fusion Tables (or is it FusionTables?).  A Fusion Table as the name implies is a cross between a spreadsheet (data are organized in rows and columns) and a database (you can make queries into the table using an SQL-like syntax).  And they are so much more than both of these things because of the mapping tools and web-based user interface clients available for manipulating the data in a table.

We extracted a tiny sub-sample (parts of May and June 2011 in the Gulf of Mexico and along US Atlantic coast) of the OSMC data collection and put it into a table.  In this instance, we extracted data for drifting platforms and pulled out one sample per day for each platform.  From this we can plot the final location and a "tail" to show where the drifter has been using the Google Maps API v3 and queries into the Fusion Table.

Preparing the Table

You can upload a batch of data into a table from a Google Doc spreadsheet, an Excel spreadsheet or an CSV text file.  To map a Fusion Table one or more columns must contain location information.  The information can be an address or place name that Google can geo-code, a hunk of KML or latitude and longitude values.  We have latitude and longitude values which we use to location each platform at each time.

In order to make a location column and to make up the columns we needed in order to be able to display the data as we wanted with drifter locations and tails, I loaded up into a OpenOffice calc applied some spreadsheet-fu (TM) to the existing columns.

The first thing I did was pull the latitude and longitude into a single column called location.  The fu required to do this is =(C2 & "," & D2).  Apparently the "&" is how you concatenate strings in a spreadsheet.  Who knew?

Turns out, you don't have to do this.  The FusionTables interface allows you to tell use a "two column location" by choosing Edit - > Modify Columns

And then associating the two columns that make up the location together in the dialog that appears.

With the raw location data identified to the Fusion Table you can easily plot every location at ever time, which of course makes a mess.

We knew we wanted to show the tail as a line and the final location as a dot.  Reading up on the documentation we found that you can define geometries in a table using fragments of KML.  So to represent the tail, we decided to create a single line segment that pointed from the current row in the table back to the previous row (and the final position just pointed to itself).  So with the table sorted by platform id and then newest to oldest, the spreadsheet-fu necessary to build the fragment of KML looks like this:

=IF (A2=A3,("" & D3 & "," & C3 &" "& D2 & "," & C2 & ""),"" & D2 & "," & C2 &" "& D2 & "," & C2 & "")

which says essentially, if the platform id of the current row is equal to the platform id of the previous row, then the line segment goes from the location in the current row (C3, D3) to the location in the previous row (C2, D2) and if not it's a new platform so it goes from the current row to itself.  And the very first data row, I filled in my hand with the segment going from itself to itself.

=("" & D2 & "," & C2 &" "& D2 & "," & C2 & "")

Now you can plot the KML fragments as the location.  This too makes a mess, but less of one.

Why are the tails, such ugly random colors?  I'm glad you asked.  You can color geometries by using a color column in you table of the form #RRGGBB.  [Enhancement suggestion: let us use 4 columns of numbers 0-255 one each for alpha, r, g and b.]  So in an attempt make the tails easier to tell apart I applied some spread more spreadsheat-fu to try to come up with a random r,g,b colors for each tail (applied only to that tail) and then applied more spreadsheat-fu to convert it to the necessary #RRGGBB.

I tried several things to come up with good colors, but I eventually lost patience and we're stuck with these muddy purples for now.

Using the Maps API and the FusionTableLayer

As far as I know, in the FusionTables UI (the source of the previous map images) you can only map one "location" at a time, so you can not simultaneously show the KML fragments that describe the tail and the dots that show the sample locations.  And besides even if you could it would be a really big mess with the dots all over the line segments.  What we want to present is the drifter's current location and its tail.  I used some spreadsheet-fu to create a column which was blank everywhere except the final location of the platform.  You know, =IF(A3=A4,"",I3) if it's the same as the next one, blank, if the next one is different mark the final position.  But, when I plot that column in the UI I only get a few of the final locations.  Something about the blank rows is confusing the Fusion Tables mapping engine.  Or something.

No, worries.  More spreadsheet-fu and you can extract a new table which has no blank rows and only shows the final locations.

So now we have all (or almost all) of the raw materials we need to create the map we want.  Using the SQL API you can request data from your table and using the V3 Maps API you can get Google to create and deliver map tiles with plots of the data that overlay on the map.  And not only that, the layers are active and accept clicks which will pop up an info window.  And not only that you can register a click listener on the layer which gets the location clicked, the table row at that location and an info window object you can populate with HTML.

To build our little demo, we created two such layers:


var tailstable = 1128869;

var tailsbase = "SELECT tail FROM " + tailstable;

var linelayer;

linelayer = new google.maps.FusionTablesLayer(tailstable);
linelayer.setQuery(tailsbase);
linelayer.setMap(map)

and I added the exactly the analogous query from the table that contains only the final locations.  And to each of these layers we added a click listener.

google.maps.event

.addListener(

linelayer,

'click',

function(e) {

e.infoWindowHtml = "<b>plat_id:</b>"

+ e.row['plat_id'].value

+ "<br>"

+ "<b>country:</b>"

+ e.row['country'].value

+ "<br>"

+ "<b>latitude:</b>"

+ e.row['latitude'].value

+ "<br>"

+ "<b>longitude:</b>"

+ e.row['longitude'].value

+ "<br>"

+ "<b>date:</b>"

+ e.row['date'].value

+ "<br>"

+ "<b>type:</b>"

+ e.row['type'].value

+ "<br>"

+ "<b>count:</b>"

+ e.row['count'].value

+ "<br>"

+ "<b>value:</b>"

+ e.row['value'].value

+ "<br>"

+ "<b>location:</b>"

+ e.row['location'].value

+ "<br>"

+ "<input type="button" value="hide" onclick="changeMap('"

+ e.row['plat_id'].value

+ "');" />"

+ "<input type="button" value="all" onclick="linelayer.setQuery(tailsbase);redlayer.setQuery(redfinalbase);bluelayer.setQuery(bluefinalbase);" />";

Ok, sorry.  It's a mess.  Suffice it to say that the first n-2 lines are just setting up the HTML text of the info window with the contents of the row at the location where you clicked on the layer.  The event object has a has with the row values in it which can be referenced by the column names (just like in a query).  So e.row['country'].value gets the value in the 'country' column in the table at that location.

The last two lines set up some buttons in the info window that will hide or show all of the other tails in the plot.  Since the lines make a mess, we decided to create a button to hide the locations.  To do this we just update the query in the linelayer to extract only the rows pertaining to a particular platform id.

function changeMap(desc) {
    linelayer.setQuery("SELECT 'tail' FROM " + tailstable+ " WHERE 'plat_id' = '" + desc + "'");
    bluelayer.setQuery("SELECT 'location' FROM " + bluefinaltable+ " WHERE 'plat_id' = '" + desc + "'");
}

The query in red takes the platform id from the call on the button click and changes the layer query to only get rows on the platform id.  The second query grabs data from yet a third table that is same as the other final position table, but is configured on the server to use blue dots instead of red.  This is a total hack, but I we wanted to leave all the other final positions visible when you hide the tails so you can see what's going on nearby, but we wanted the final position of the selected drifter to be distinct.  You can see it all in action on my test page.