Building a GPX Spatialite database

Every aerial survey generates dozens of tracklogs and waypoints – and these tracks are a very important part of the data for the survey. We can use tracklog data to confirm ground speed, actual coverage of the survey, off-track measurements and other metadata about the transects flown.

I’m playing with storing all the survey data in a Spatialite database – RSO and FSO data, photographs, and the spatial files for the planning and reporting. While it’s straightforward to import a shapefile directly into Spatialite, the shapefiles I have in a QGIS project have usually been edited, and it would be good to keep the original tracks straight from the GPS for complete records.

GPX to Spatialite

Luckily, OGR (command line) can work with both GPX and Spatialite databases. A great post from ‘Scratching Surfaces’ gave me the initial code for this:

ogr2ogr -append -f "SQLite" -dsco SPATIALITE=yes
    -dsco INIT_WITH_EPSG=yes -t_srs epsg:4326
    gpx.db GPXFile.gpx tracks -nln TrackTable
ogr2ogr command line for ogr utilities; on OSX, installed with the GDAL complete package from Kyngchaos.
-append Add data to the the table
-f “SQlite” -dsco SPATIALITE=yes Use SQLite as the base format (the basis of Spatialite) with Spatialite extensions loaded.
 -dsco INIT_WITH_EPSG=yes Initialise table with CRS? Yes!
-t_srs epsg:4326 … and use 4326 (WGS84) as the CRS.
gpx.db The Spatialite database
GPXFile.gpx tracks The GPX file and the geometry to import – just tracks in this case.
-nln TrackTable ‘New layer name’ and the name of the table (TrackTable). Because of ‘append’, this will be ignored if there’s already data.

 Loading a directory

It would be nice to keep track of the original filename for each track that gets imported. I added a new column to the table after first importing:

ALTER TABLE TrackTable ADD COLUMN trackname TEXT;

A bash script can then iterate through all the GPX files in a directory:

#!/bin/bash

DB=$"spatial_db2.sqlite"; # Database name

T=$"TrackTable"; # Table name in SQLite

for file in $(ls *.gpx); do  # Loop through all the GPX files in the directory
ROW=$(spatialite $DB "SELECT COUNT(ROWID) FROM $T"); # What was the last row?
ogr2ogr -append -f "SQLite" -dsco SPATIALITE=yes # add to table with Spatialite filetype
    -dsco INIT_WITH_EPSG=yes -t_srs epsg:4326 # using WGS84
    $DB "$file" tracks -nln $T; # adding tracks
spatialite $DB "UPDATE $T SET trackname='$file' WHERE ROWID > $ROW"; # update trackname for all the new rows
done

This now gives you a Spatialite table with all the tracks – including saved tracks, not just the ‘ACTIVE LOG’ – as rows in the database. What else do we need?

  • It would be good to do the same thing for all the waypoints (as a separate table);
  • Perhaps save the GPX file itself as a BLOB.