Monday, February 11, 2013

Here's some social security data from my buddy Andy.

Thursday, July 14, 2011

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);

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.

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.

Sunday, June 26, 2011

ASAP Lists is on the Market

I said it would be ready in May, but I missed by a month.  Actually, I missed by 13 months, but don't tell anyone.  Please give ASAP Lists a try and please let me know what you think.  I built a site where you can can read the blah, blah about the app and see some tutorials.

To get the app use the Market page for ASAP Lists or scan this QR code
with your Android phone.

I know it's pretty lean, but it should be very smooth and solid.  The feature requests I have so far are:
  1. The ability to change the color of the items in a category.
  2. The ability to move items from category to category and categories from list to list.
  3. The ability to set the order of the list items (in addition to ordering the categories).
The first two will get done.  The third one will require more thought.

Friday, July 16, 2010

An OpenLayers Map Widget

As part of my contract with NOAA I created a reference map widget which we use to allow users to select lat/lon rectanges, lat and lon lines and points.  We have a (unfortunately) long history of building map widgets to allow users to drive our interface.  The first (and best in my opinion) was a Java applet developed by Jonathan Callahan.  It was extremely functional and intuitive.  The issue was browser compatibility.  When we started to rely more and more on JavaScript to drive other parts of the user interface, the job of transferring information back and forth between the JavaScript and the Java applet proved to be a browser compatibility nightmare we never full solved.  And as applets fell out of favor we moved on.

At the same time as the applet was in active use, we built (credit for the good parts goes to Joe Sirott) a server-side map that required two clicks to mark a rectangle.  Joe's implementation had a nice Active X interactive selection, but again we could not use it on a enough browsers to deploy it.  Joe has since built other nice map widgets.  I built the "two-click" version.  It worked.  On all browsers, but as one user put it so eloquently: "It's not draggable, dude".

Next up was a long experiment with Google Maps.  I built a widget that allowed click, hold and drag drawing of rectangles, horizontal and vertical lines using markers on the corners and mid-points.  It worked.  Sort of.  I could get it to work fine, but I was slow and patient.  When users used it the response was so sluggish they the were immediately frustrated.  Another killer was the projection.  We deal mostly in global data on a rectangular equal-area projection.  The Google maps modified Mercator looked terrible on the global view and the selection scaling when dragging a rectangle above 60 north was dramatic and disconcerting.

Enter OpenLayers, the GWT-OpenLayers project and a client that said rightly that the Google Maps implementation was s!@$.  I built yet another map widget.  I think it works well.  Editing the selection is not as slick as was with the Java applet and the strict modal interface is not ideal, but I haven't heard of any browser compatibility issues.  And eliminating those was the goal all along.

The OL Map Widget is available on Google code.  There is an example deployment for JavaScript and a deployment using GWT and a Flash-based tutorial video there along with the code.

No doubt this would be more interesting (slightly less boring?) if I had links to all the previous map implementations, but it seems like too much to figure out how to find or deploy stable examples of each.

Wednesday, June 23, 2010

Layar Point of Interest Service on App Engine

A friend made a pitch to an creative agency about Augmented Reality.  The pitch was well-received and the next step was to deploy a small sample to showcase the potential of the technology.  The friend knew that Layar was a premiere AR platform and he also knew I had some Android experience so he called me.

Sure, I said, no problem I can build you a Layar.  So, I got a developer key from Layar and jumped on the site to get started.  In retrospect I should not have been surprised, but to deploy a layer on Layar you need a Web service to pump your "points of interest" data to Layar servers which in turn deliver it to the handset.  I say in retrospect, because I expected to not only be able to define the layer container at, but the points of interest as well.  Of course.  The concept is to take existing collections of point data and offer via AR.  If you have a collection of hundreds, thousands or maybe millions of data points you're not going to be keying them into a Web form at Layar.

But, suppose you are starting from scratch and you only have a few points.  Well, then you might want to enter them by hand and serve them up to Layar.  There are services that allow you to do just that (buildAR and to name two) and pre-built servers you can deploy and a whole host of developers who will do the job, but what's the fun in that.  The fun is to build your own.

I chose App Engine because, well because it's there, because I been wanting to try it since they announced the Java environment and because for a light loads it's free.

This was my first experience with JDO.  You have to adjust your thinking to keep your focus on you objects and resist the temptation to think about things like insert/select to make a copy of a "row".  If you have trouble you can spend a little time in some JDO forums so the JDO police can sarcastically, flippantly and dismissively point out your old school thinking.  Persisting a copy of an object seems like a perfectly legitimate thing to want to do and of course it is possible, the key for me being the key (set to null) and using the serializable DTO to move the data between the existing object and new one.

I built a simple GWT interface that allows you to login using your Google account, to enter POIs, save them, edit them and copy them.  And once you've added some points of interest, there's a URL you can hit that will pump out the POIs in the appropriate JSON container for use with Layar.

It's built.  It works.  If you load Layar on your device and search "Idea City" you'll find a layer which is being served via this service.  You have to be on 6th Street in Austin (30.271428, -97.752770) to see anything, but if you really want to see the POIs you can set your lat/lon in the settings menu of the Layar app.  Better yet, here's a QR you can read.

Idea City Layar QR Code

Don't forget you put yourself on 6th and Wood in Austin either physically or virtually.

I say it's built, but there are more things to do.  For one the geo-spatial query is not particularly efficient.  In fact, it's positively brute force inefficient, but it works for small collections.  After I steel myself, I'll head back into the JDO forums to see if I can figure out a query that will let me efficiently select on a radius from a fixed lat/lon similar to the SQL in this example (in the Create SQL statement to fetch the POI information section).  There's also the matter of sanity checking on the Web form entries.  Clearly URLs should look like URLs, lats and lons should probably be normalized and maybe I should even ask you if you're serious when you enter a point that's "far away" from other points in the layar just to be sure.  The ultimate would be to add a service that would allow you to ingest an existing service into a UI, point and click the XML or JSON parts of the existing service output to build the crosswalk to the Layar JSON spec, maybe intersperse a few hand entered points and release the whole thing as a new Layar.  Yeah.  Right.

What is built now is called SnapLayar.  If you want to try it, contact me via my profile and all add you to the list of authorized users.  Maybe.  After I get the sanity checking implemented.

Friday, March 26, 2010

The Basics (Updated 4 June 2011)

Weathertop Consulting, LLC is the legal entity through which I write software both as a contractor for the National Oceanic and Atmospheric Administration and for private companies.

For nearly six years the focus of my work has been on development of the Live Access Server (LAS) a Web-application software system for display and analysis of geo-science data sets. The software, which can be downloaded and installed by anyone, gives data providers an easy way to establish services for their on-line data holdings so their users can make plots, create and download sub-sets in a variety of formats, and compare and analyze data.

Weathertop Consulting, LLS also the umbrella under which I develop my own applications for Android smart phones (the first product developed without a partner is scheduled for beta release in May).  I did release it in May.  Well, not May exactly, but June.  And not 2010 exactly, but 2011.  The ASAP Apps for Android site has all the details.  The first product I did (iMap Weather™) was a top-50 winner in the Android Developer Challenge I.  You can search in on the Android Market by the phrase "iMap Weather".