Saving data from Android: sqlite to gpx

During my last trip to Australia (the first trip for my Android phone), I have been using the ‘mytracks’ application from google to record all the places where I was going. Mostly for two reasons: sending the data to open street map and geotagging my photos.

Everything went well and I exported most of the tracks without any problem to the SD card (saving into gpx files). However, for one particular track, it didn’t work: when trying to backup the track, the phone would just go on for hours using 100% of the CPU. Even when just trying to display the track, it would just hog the CPU without end (well, until the battery dies which happens quite fast in those conditions).

After looking deeper into the problem it appears that it was the longest track of the trip. It was indeed pretty long: 10h45 of continuous recording (if you wonder how the battery can last that long, think car trip with car charger).

So it is pretty frustrating, my precious data are here, the application doesn’t want to take them out, and everytime I launch the MyTracks application (trying to load the faulty track by default), the application hangs until the battery dies.

The good stuff about Android (well, and the reason why I got this phone) is that it is open source. It is easy to find information on the web… and to save my data. Here is how I proceeded.

Using the SDK for android, you can easily connect to the phone:

adb shell

once there, you can look around. In /data/data/com.google.android.maps.mytracks/databases there is one particularly valuable file: mytracks.db (note to be able to read that, you need to be root). Copy the file to your computer:

adb pull /data/data/com.google.android.maps.mytracks/\
databases/mytracks.db .

Now the data are safe, we can make a backup copy before playing with it. This file is simply the sqlite database containing all the tracks recorded. To manipulate it, we are going to use the python module sqlite3.

Here is the connection to the database:

import sqlite3
conn = sqlite3.connect("mytracks.db")
cursor = conn.cursor()

First, we need to find out what the tables are:

cursor.execute('select * from sqlite_master')
l = cursor.fetchall()
print l

or to get a better view:

map(lambda x: x[1],l)
[u'android_metadata', u'trackpoints', u'sqlite_sequence',
u'tracks', u'waypoints']

Seems good. Let’s have a look at tracks:

cursor.execute("PRAGMA table_info(tracks)")
l = cursor.fetchall()
map(lambda x: x[1],l)
[u'_id', u'name', u'description', u'category', u'startid',
u'stopid', u'starttime', u'stoptime', u'numpoints',
u'totaldistance', u'totaltime', u'movingtime', u'minlat',
u'maxlat', u'minlon', u'maxlon', u'avgspeed',
u'avgmovingspeed', u'maxspeed', u'minelevation',
u'maxelevation', u'elevationgain', u'mingrade', u'maxgrade',
u'mapid']

With this knowledge, we can have a look at what’s inside:

cursor = connection.execute("SELECT * FROM tracks;")
l = cursor.fetchall()
map(lambda x: (x[0], x[1], x[8]) ,l)

this allows me to find out the id (23) of the track I want to export (the one with 12182 points).

Now, it’s time to have a look at the trackpoints:

cursor.execute("PRAGMA table_info(trackpoints)")
l = cursor.fetchall()
map(lambda x: x[1],l)
[u'_id', u'trackid', u'longitude', u'latitude', u'time',
u'elevation', u'accuracy', u'speed', u'bearing']

Good, so we can find out if all my points are here:

cursor.execute("SELECT * FROM trackpoints WHERE trackid=23;")
l = cursor.fetchall()
print len(l)

This reports 12335 points instead of the 12182 reported in tracks (duplicates?) but it is probably not the cause of the error as others tracks have slightly more points here also.

The encoding of the data is quite straightforward:

print lines[0]
#(26233, 23, 116731125, -34976456, 1243647937000L,
-12.0, 128.0, None, None)

For the latitude and longitude, it is simply the decimal value *1000000 to store it as an integer.

For the time, as we celebrated the unix time 123456789 recently (slashdot), the coding for the time is quite obvious: epoch time * 1000. A quick check confirms that:

import time
time.gmtime(1243647937000/1000.)
(2009, 5, 30, 1, 45, 37, 5, 150, 0)

And as I was certainly not driving at 1am, it is stored in GMT.

Now that we understand the structure and know how to retrieve the data, we need to convert them to a more useful format: gpx.

For each point, we will have something like:

<trkpt lat="-34.977534" lon="116.734827">
<time>2009-05-30T01:01:40Z</time>
<ele>8.0</ele>
</trkpt>

So, we just need to go through the data and output the gpx file. This simple script will do:

import sqlite3, time
conn = sqlite3.connect("mytracks.db")
cursor = conn.cursor()
cursor.execute("SELECT * FROM trackpoints WHERE trackid=23 \
ORDER BY time;")
lines= cursor.fetchall()
f = open('output.gpx','w')

f.write('<?xml version="1.0" encoding="ISO-8859-1"
       standalone="yes"?>\n')
f.write('<gpx version="1.0" creator="melaneum script">\n')
f.write('<trk>\n')
f.write('<name><![CDATA[TrackExtract]]></name>\n')
f.write('<desc><![CDATA[]]></desc>\n')
f.write('<number>23</number>\n')
f.write('<trkseg>\n')
for line in lines:
  lon=line[2]/1000000.
  lat=line[3]/1000000.
  ptTimeStruct = time.gmtime(line[4]/1000.)
  ptTime=time.strftime('%Y-%m-%dT%H:%M:%SZ',ptTimeStruct)
  elev=line[5]
  f.write('<trkpt lat="%.6f" lon="%.6f">\n' % (lat,lon))
  f.write('<time>%s</time>\n' % ptTime)
  f.write('<ele>%.1f</ele>\n' % elev)
  f.write('</trkpt>\n')

f.write('</trkseg>\n')
f.write('</trk>\n')
f.write('</gpx>\n')
f.close()

After opening the gpx file in google earth, it appears all correct and well. Data are saved! It is worth noting that it won’t have been possible to do that so easily if the data were kept in some obscure format and if the phone system was a closed one.

7 thoughts on “Saving data from Android: sqlite to gpx

  1. Hi,
    what should I do, if I’m not allowed to access my phone as a superuser. Any ideas how I could access databases (at least copy them).

    Thank you,
    Johannes.

  2. Hi,
    The superuser is not needed to use the adb pull command. You just have to know where the file is (or retrieve the whole /data/data). The command didn’t appear clearly in the post, this is fixed now.

  3. Melaneum – are you sure? This would be sooooo useful, but I still can’t get the mytracks database without root.

    I’m reluctant to root because the device needs to go back to tmob for a repair – precisely the reason I want to back up some memorable tracks (and while you can export, you can’t import).

    Any help would be v. appreciated, shame mytracks isn’t open source!

  4. Hi,
    No, I’m not 100% sure (I don’t have a non root device to try). I was able to use the ‘adb pull’ without using root explicitly. But it’s true that when you use ‘adb shell’, you can’t go in the folder without root. At what step do you encounter a problem?

  5. Thanks to some of the tips here, I was able to rescue most of an important gps-track from a broken MyTracks-database today. Kudos!

  6. Pingback: My tracks không thể export tracklog | Tan's Blog

Leave a Reply

Your email address will not be published. Required fields are marked *