Stranded Strava ride#

Mon, 10 Jun 2013 13:50:29 +0000

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 pull doesn'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> ^D
  

  Yes, you read that right, there really is a column called latiude in that table. Note that the ride_id of 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.