The company I work for (ABR, Inc.) pays it’s employees to use alternative transportation—bicycle, walk, run, ski, etc.—to get to work. It’s not a lot of money, but human behavior being what it is, even a small incentive really works. I’ve been riding my bicycle to work three or four times a week, carrying a hand-held GPS (Garmin eTrex Vista Cx) along with me and logging the data into spatially aware databases (PostGIS and spatialite). You can see a map of last week’s data on my GIS page.
One problem with my GPS is that I get really inaccurate elevation data, and as anyone who has ridden a bicycle knows, elevation changes are really important to how fast you can ride. I’ve noticed that I ride much faster going home than on my way to work, and had concluded (until examining the data…) that it must be an uphill ride to work.
But how to fix the elevation data? One approach would be to download the new global digital elevation model (DEM) that’s based on data from Japan’s ASTER satellite and use this to find the corrected elevation for GPS trackpoint. I’ll probably try this at some point just to see how well the DEM matches my data. But the approach I wound up using is to generate an “average” track based on all my trips to and from work.
There’s probably a way to do this with a single SQL statement, but I couldn’t figure out how, so I did the point iteration in Python. The first SQL query takes an existing trip and segments the trip into a series of points. I chose a maximum interval of 50 meters because that was approximately how often the GPS reports data at the speeds I’m normally travelling. Here’s the PostGIS SQL:
SELECT ST_AsText(ST_PointN( segpoints.points, generate_series(1, ST_NPoints(segpoints.points))) ) FROM ( SELECT ST_GeometryN( ST_Segmentize(track_utm, 50), generate_series(1, ST_NumGeometries(ST_Segmentize(track_utm, 50))) ) AS points FROM tracks WHERE tid=67 ) AS segpoints;
This yields a series of points (in WKT format) along the track.
My Python script takes each of these points and finds the average X, Y, and Z (elevation) coordinates of all points within 50 meters of the point. Here’s the query:
SELECT count(*), avg(lat), avg(lon), avg(ele)*3.2808399 AS avg_ft FROM points WHERE ST_Within( ST_SetSRID(ST_GeomFromText(POINT_WKT), 32606), ST_Buffer(point_utm, 50) );
And the results:
The red squares show the elevations along my route, and the green markers show the speed I was traveling at that point. In this case, I’m riding to work, so home is on the left and ABR is on the right. The big hill on the left is Miller Hill Road. In general, you can see that a ride to work involves going over Miller Hill, then a gradual climb along Sheep Creek Road to the intersection with Ester Dome Road (the little hitch in this section is the hairpin curve before Anne’s Greenhouses where so many people go off the road in winter…), followed by a steeper descent to ABR. My speed tracks the elevation changes fairly closely. I’ll be interested in seeing how these plots change over time as my legs and cardiovascular system strengthens. Will the green points rise uniformly, or will I be able to see improvements in individual aspects of my performance such as hill climbing?
One final point: the elevation at home and ABR are essentially the same. My conclusion after all this is that elevation can’t account for why my rides home are so much faster (1.28 mph, on average). Wind? Cold mornings? Excitement to get home?
For reference, here's my ride home on the same day: