“Do Something Fun with My Data” – A Challenge to the Socrata Team
Chris Metcalf, our Director of Product Development and Developer Evangelism, recently participated in a sailing race that circumnavigates Vancouver Island. Just for the fun of it, he brought along a GPS data logger that used Socrata’s API to publish his position every 10 minutes. Each data point became publicly available on the Socrata platform the moment it was uploaded. A challenge was issued to all employees: “Do something fun with my data.” (View Chris’ Socrata-powered online dataset here)
Challenge accepted. But what to do? A graph of speed and/or bearing? Meh. A chart of uptime vs downtime? Boring. An animated map in processing? Perhaps. The data was (and still is) API accessible, so it would have been cool to make some kind of real-time app that would poll the site periodically for updates. Given my time constraints and the fact that this sailing race would soon come to an end (and, therefore, become a static dataset), I decided it would be more practical to do something after the fact. An animated marker on a map would have been sufficient to show when and where Chris went, but after some Googling I settled on an animated KML track in Google Earth, complete with 3D model. People use these tracks to animate car trips, bike rides, hikes, and even to show the orbits of satellites.
I started with this blog post about “Reliving your hiking and biking adventures.” It’s geared at importing data directly from a GPS unit, but it does include a few excellent sample KMZ files with ready-to-go animated tracks. By inspecting the KML in a text editor (after unzipping them, of course. Remember that KMZ is simply zipped KML), we see two important things: 1) A set of matching and tags, containing our position and time data, respectively. 2) A series of references to a .dae file (3D model) and its associated texture .jpgs.
All that needs to be done is to swap out the time/place data and the 3d model references, replace the reference to the existing model with the new one, and we’ll be able to animate data from Chris’ sailing race!
Tweaking the Data
To accomplish this, we need to do some manipulation in excel. For the date and time fields, first we need to convert to Zulu time by adding 4 hours (for some reason, Chris’ data logger was recording in EDT, or Zulu time minus 4 hours). Excel’s time() function takes care of this. Next, we’ll generate a custom time format that matches the format in the example KML, then add a concatenate() to add the tags.
For the location fields, we simply do a concatenate() of the latitude and longitude fields, adding tags and commas where appropriate, and a zero for each elevation value. Once everything looks good, simply copy and paste these values into the example KML, replacing the existing values. (Be careful, the number of tags and the number of tags must be the same!)
Adding a 3D Model
For the 3d model, I found a sailboat model on Google’s 3D Warehouse, and downloaded the .skp file. Google Sketchup effortlessly exports it as a .dae (model and associated textures), which we will use in our track.
Once the KML is opened in Google Earth, there’s a “Play Tour” button in the sidebar. Click it, and Google Earth will zoom in to the start point, the 3D model will appear, and the tour will start playing.
You can fine tune some settings in Google Earth to get it looking the way you want it to. I moved the camera back considerably and lowered its angle so we could see more scenery. You can also turn on the sun and speed up/slow down the animation. Once it’s ready, use your favorite screen capture software to record the track (Quicktime is great for this on Macs) and your favorite video editor to add music, titles, and transitions. Done!
A copy of the final KMZ file for this animation is available on github it you would like to replay it yourself in Google Earth.
Chris Whong is a Data Solutions Architect for Socrata, helping governments and citizens realize the potential of open data in their communities. He is an active member of the civic tech community in New York City, and is a mapping and dataviz enthusiast.