10 Jun 2013 dan   » (Master)

Stranded Strava ride

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.db /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.

Syndicated 2013-06-10 12:50:29 from diary at Telent Netowrks

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!