Exporting GeoRecorder database to GPX

I’ve been using GeoRecorder for logging GPS data for some time now and wanted to get it exported to GPX. GeoRecorder has GPX export feature, but only lets export specific tracks, not dump the whole database to GPX. Time for a hack!

GeoRecorder stores its data in /mnt/sdcard/ideamatters/georecorder/locationdata on device. Copied the files to PC, quick peek inside–these are SQLite databases, sweet!

Select latitude, longitude and timestamp converted to datetime:

select latitude, longitude, datetime(time / 1000, 'unixepoch') from logs

Run the query from shell, use commas as separators:

sqlite3 -separator , some_sqlite_file "select latitude, longitude, datetime(time / 1000, 'unixepoch') from logs;"

GeoRecorder creates several database files. I think it’s based on file size, once size of current database reaches a certain limit, it starts a new file. Thus, we loop through a list of files, plus output a header row:

    echo "Latitude,Longitude,Time"
    for FILENAME in $FILES; do
    sqlite3 -separator , $FILENAME "select latitude, longitude, datetime(time / 1000, 'unixepoch') from logs;"

    done;

This produces data like:

Latitude,Longitude,Time
57.6839464902878,25.2477461099625,2011-02-23 09:08:47
57.6839250326157,25.2479124069214,2011-02-23 09:08:55
57.6839250326157,25.2479016780853,2011-02-23 09:08:56
....

Next, convert this to GPX with gpsbabel:

gpsbabel -i unicsv -f - -x transform,trk=wpt,del -o gpx -F -
  • -i unicsv specifies source format: CSV file with header row
  • -f - tells gpsbabel to read data from stdin
  • -x transform,trk=wpt,del tells gpsbabel to create track points from waypoints, and to omit wayopoints in output
  • -o gpx: output format is GPX
  • -F -: write result to stdout

Now we just need to pipe CSV data from for loop into gpsbabel. In order to pipe the whole for loop into gpsbabel, I put it in a function. Here’s the complete script:

#!/bin/bash

if [ ! -n "$1" ]
then
  echo "Usage: `basename $0` data1 data2 etc."
  exit 1
fi 

print_csv ()
{
    echo "Latitude,Longitude,Time"
    for FILENAME in $FILES; do
    sqlite3 -separator , $FILENAME "select latitude, longitude, datetime(time / 1000, 'unixepoch') from logs;"

    done;
}


FILES=$@
print_csv | gpsbabel -i unicsv -f - -x transform,trk=wpt,del -o gpx -F -

And use it like this:

./georecorder-to-gpx.sh data* > rez.gpx
Advertisements

One thought on “Exporting GeoRecorder database to GPX

Comments are closed.