Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

On our aerial wildlife surveys we typically generate dozens of GPX files, and usually convert most of them into shapefiles for display in a GIS application. GPX files are a terrific way of keeping all (or almost all) of your GPS data in one place – they are xml files that store tracklog, waypoint & route information in one convenient format. They store multiple tracks, even old saved tracks from a Garmin.

I'd like to store all this track data in a spatialite database, in parallel with other tables that have point observational data from the same survey.

ogr2ogr will let me convert the track to a spatialite database:

ogr2ogr -append -f "SQLite" -dsco SPATIALITE=yes # add to table with Spatialite filetype
  -dsco INIT_WITH_EPSG=yes -t_srs epsg:4326 # using WGS84
  SURVEYDATA.sqlite GPXFILE.gpx tracks -nln tracktable; # adding tracks to 'tracktable'

However, this imports each saved track as a line – losing the time / location information for each track point!

Anyone know of any clever ways of building a tracklog database that will preserve that information?

EDIT:

Tracklogs are not just a line - they are collections of sequential points, each with an elevation and time attribute. Each point belongs to a track segment, which in turn belong to a given track, and points within each track segment were taken without interruption and may be assumed to be linked. For example:

<trk><name>ACTIVE LOG</name>
<trkseg>
<trkpt lat="-13.471403" lon="31.382982"><ele>467.818725585938</ele><time>2009-09-09T03:53:38Z</time></trkpt>
<trkpt lat="-13.471403" lon="31.382982"><ele>468.780029296875</ele><time>2009-09-09T03:53:50Z</time></trkpt>
<trkpt lat="-13.471403" lon="31.382982"><ele>465.896118164063</ele><time>2009-09-09T03:54:43Z</time></trkpt>
</trkseg>

Thus, to save a tracklog you need to save this collection of points with their associated data.

One solution might be to load these tracks as points, with additional columns for track name, segment, elevation and time; I don't think ogr will convert the tracks as points, though.

Solution

Thanks to @scruss who pointed out gpx2spatialite, which I installed (OS X 10.9) with sudo easy_install gpx2spatialite. This set of tools is designed for the 'drawinglife' project (and thus has some 'city' information that is probably unnecessary for most of us); the main gpx2spatialite page doesn't explain much, but the drawinglife wiki is better. Still, lots of undocumented features - like removing duplicates!

Create a spatialite database for GPX files which will hold track segments, tracks, trackpoints and waypoints. It also has 'users' set up (which is odd, since sqlite is not a user-access system) designed to label the various tracks:

gpx2spatialite_create_db MyGPXArchive.db

Load a whole folder of GPX files, including all subfolders, checking and removing duplicates along the way:

gpx2spatialite -s -d MyGPXArchive.db -u SL25 Selous2013/ 

Options:

  • -s= don't check if the trackpoints are in the city database (speeds it up a LOT)
  • -d MyGPXArchive.db = database to use;
  • -u SL25 = add the user SL25 (just a label I used for the set of tracks collected);
  • Selous2013 = the folder (with subfolders) that has the tracks I'm interested in.

Bugs:

  • If you have saved tracks on an older Garmin that discards time stamp info, it'll crash the import (remove those saved tracks using a utility like GPSU).
share|improve this question
    
We use FME safe.com/fme/fme-technology/fme-desktop/features and it maintains all the attribution you want into an Oracle Database –  Mapperz Aug 31 '12 at 16:51
1  
@Mapperz - does FME have a specific method for saving GPX files? I'm really after an FOSS solution though. –  Simbamangu Sep 1 '12 at 6:12

5 Answers 5

up vote 2 down vote accepted

ptrv/gpx2spatialite does this remarkably well, saving timestamps for all points and deriving speed and length data for tracks. It also won't import duplicate tracks, so you can feed it a huge pile for GPX files and it will munge them appropriately.

Update: usage examples, as requested:

Initialize new database:

gpx2spatialite_create_db db.sqlite

Add a single gpx file:

gpx2spatialite -d db.sqlite -u user file.gpx

(If username user does not exist in the database, will query if you wish to create it. The database is built with tracks/waypoints/routes assigned to one or more users.)

Add all of the gpx files in folder/:

gpx2spatialite -d db.sqlite -u user folder/

Usage: it's a spatial database, with table tracklines holding the tracks as polylines, trackpoints containing the individual points (with time, elevation, etc), and waypoints for any defined waypoints. There are also numerous indices, some world city definitions and possibly also embedded SLD styling information I haven't yet explored. As a trivial example, here's a map of how my hometown (Toronto) is defined through ~7 years of track logs:

Toronto GPS logs

By using lines at 75% transparency, the most frequently-travelled routes become darker.

share|improve this answer
    
Interesting. Could you add some examples of how you actually use it? There are no comprehensive examples I can find online. –  Simbamangu Aug 30 at 13:12
    
updated, as requested! –  scruss Aug 30 at 21:11
    
Aha, hadn't realised that easy_install gpx2spatialite would actually install it as a script accessible from the command line! The user option was also confusing, they don't explain it on the main page, and spatialite is a non-access-restricted system. –  Simbamangu Sep 1 at 4:58
    
The user option makes sense for the Drawinglife project, as it allows multiple participants to add their tracks to the same database. Thanks for the hints about excluding the City tagging, and for finding the reasons for the crashes. If you have GPX files without timestamps, you can synthesize the time data using GPSBabel's faketime option. –  scruss Sep 1 at 17:28

There are some ideas here for creating your own program to extract gpx data in python or perl. From here it's not too much of a stretch to put the data into a spatialite database. You will need to think about a suitable schema for storing in spatialite - what attributes do you want to keep?).

share|improve this answer

To further your comment - "does FME have a specific method for saving GPX files?"

Yes you have full control on the routes, waypoints, tracks and metadata enter image description here

FME is a very worthy and powerful tool to manipulate gis data and non-spatial data together.

see for more details http://evangelism.safe.com/fmeevangelist26/

share|improve this answer
    
This looks like a pretty amazing database system – will give it a try. –  Simbamangu Sep 6 '12 at 5:35

If you want to go the OGR route, the best approach is to export to Shapefile first, which will export multiple shapefiles representing the waypoint/trackpoint/way/track distinction and the associated metadata, and then merge each set of files into your main SQLite database.

share|improve this answer

Try adding

-lco GPS_USE_EXTENSIONS=YES

to your ogr2ogr command

share|improve this answer
    
I tried this on a new table - with no effect on the resulting fields or data. What are you expecting it to do? –  Simbamangu Sep 1 '12 at 6:33
    
I was expecting to get the extended attributes. Rereading your question, I don't think there's anyway to get each individual track point times other than exporting the track as points themselves. –  Micha Sep 1 '12 at 14:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.