Mon, 10 Jun 2013 13:50:29 +0100
This morning, due to some unlikely-ever-to-be diagnosed bug perhaps involving poor network connectivity in our office basement, the Android Strava app started denying all knowledge of my ride into work.
It turned out not to be quite as lost as I feared it would be, but the process of getting it uploaded was still kind of complicated. You will need root on your android, plus a real computer (or a Mac will do at a pinch, if that’s all you have available) with the android stuff installed, sqlite3 and gpsbabel.
- copy the strava database onto the computer. Do this in two steps because
adb pulldoesn’t have the permissions to read the directory where the database lives
$ adb shell shell@android:/ $ su shell@android:/ # cp /data/data/com.strava/databases/strava /sdcard/tmp-strava shell@android:/data/data/com.strava/databases # exit shell@android:/ $ exit $ adb pull /sdcard/tmp-strava 4566 KB/s (1277952 bytes in 0.273s)
- use sqlite3 to get the data points out. This involved a bit of digging around
sqlite> .tables CLUB_ACTIVITY comments related_activites activities facebook_athletes segment_leaderboards android_metadata followers segments athletes followings streams challenge_leaderboards heartrates waypoints challenge_participants kudos zones challenges notifications sqlite> .headers on sqlite> select max(timestamp) from waypoints; max(timestamp) 1370851561000 sqlite> select * from waypoints where timestamp=1370851561000; ride_id|pos|timestamp|latiude|longitude|altitude|h_accuracy|v_accuracy|command|speed|bearing|device_time|filtered|elapsed_time|distance c558df30-01ae-43ab-99c6-9b4649b8d596|1742|1370851561000|51.5209762891755|-0.0867852196097374|107.0|10.0|||3.56535005569458|13.0|1370851559177|0|1752811|10778.9532725066 sqlite> .mode csv sqlite> .output /tmp/track.csv sqlite> select date(timestamp/1000.0,‘unixepoch’) as date,time(timestamp/1000.0,‘unixepoch’) as time,latiude as lat,longitude as lon,altitude as alt from waypoints where ride_id=‘c558df30-01ae-43ab-99c6-9b4649b8d596’; sqlite3> ^DYes, you read that right, there really is a column called
latiudein that table. Note that the
ride_idof my missing ride is not necessarily the same as your missing ride: in this case I looked at the timestamp of the most recent waypoint logged and it corresponded to the time I finished my ride, but you might have to dig further.
- now we have a CSV file, we need only turn it into a format that Strava will recognize, and upload it with the Strava upload button. According to the docs, this GPSBabel invocation should transform a list of waypoints in CSV form into a track in GPX form – according to empirical observation, it merely gives a list of waypoints in GPX format. But Strava seems to accept that as a valid upload anyway
$ gpsbabel -i unicsv -f /tmp/track.csv -o tcx -x transform,trk=wpt -t -F /tmp/track.tcx
- And here it is – though, you might argue, hardly worth the effort of recovering it. Somewhere in the process it seems to have lost track of my local time conventions too, but UTC is the one true timezone, so no big deal.