7

I've got 50+ GPX files that I would like to "batch load" into a PostGIS database. All of the track_points data would be loaded into a "track_points" table (with typical GPS fields like lat, long, elevation, time, etc) and the tracks data would be loaded into a similar, appropriately designed "tracks" line geometry table.

I'd prefer to automate this process so that when I get my next 50+, I won't have to hand enter the data into the database. I personally prefer to use Python to script these kinds of processes, but any suggestions are welcome.

My general thought process is to:

  1. Get a list of GPX files to process (easy enough through standard Python tools)
  2. Loop through each GPX file and extract/convert the necessary data to PostGIS format
  3. Insert the GPS data into PostGIS using the psycopg Python library

I think I can manage steps 1 & 3, but am wondering if there is a relatively simple method/library that would convert the data (tracks and track_points) into PostGIS format, or simply the tabular form that I could insert into the already created table.

I've already read "Is there a good GPS track analysis library?", "How to build a geographic database of GPS logs?", and "How to extract .gpx data with python", and have looked into the GDAL/OGR and FWTools Python bindings, but don't want to reinvent the wheel is someone already has a good method for this.

9

For pure Python, use GDAL's OGR module:

import os
from osgeo import ogr
from glob import glob

# Establish a connection to a PostGIS database
pg = ogr.GetDriverByName('PostgreSQL')
if pg is None:
    raise RuntimeError('PostgreSQL driver not available')
conn = pg.Open("PG:dbname='postgis' user='postgres'", True)
if conn is None:
    raise RuntimeError('Cannot open dataset connection')

# Loop through each GPX file
for gpx_file in glob('/path/to/*.gpx'):
    ds = ogr.Open(gpx_file)
    if ds is None:
        print('Skipping ' + gpx_file)
    print('Opened ' + gpx_file)
    prefix = os.path.splitext(os.path.basename(gpx_file))[0]
    # Get each layer
    for iLayer in range(ds.GetLayerCount()):
        layer = ds.GetLayer(iLayer)
        layer_name = prefix + '_' + layer.GetName()
        if layer.GetFeatureCount() == 0:
            print(' -> Skipping ' + layer_name + ' since it is empty')
        else:
            print(' -> Copying ' + layer_name)
            pg_layer = conn.CopyLayer(layer, layer_name)
            if pg_layer is None:
                print(' |-> Failed to copy')
  • Thanks for the solution! I struggled to get the GDAL Python bindings to picking up OGR PostgreSQL driver, but after following these instructions to Installing GDAL & GDAL Python Bindings on Windows 7, I finally got it working. – RyanDalton Feb 20 '13 at 5:15
  • 2 issues I'm now having: 1) is there an "append" option so that all GPX files get appended to the same file (currently, it appears as though it's just the first that is loaded) and 2) is there a way to define the schema to save the new tables into? – RyanDalton Feb 20 '13 at 5:20
  • The destination layer name (table name) is the second parameter in CopyLayer. I've added the name from the GPX file as a prefix, so the tables should be as unique as the input file names. The "append" option from ogr2ogr is a tricky one that I'm not sure how to do at this moment. – Mike T Feb 20 '13 at 5:38
5

After additional research, I wrote my own gpx2postgis.py Python script that automates the process of appending GPX features to existing tables. The script uses portions of the work provided by @Mike T above, and others. I have added it to GitHub if you would like to download or make contributions. It creates new table schemas (as needed) based on the input GPX sublayers, and appends features to those tables.

While not a Python solution, I did come across this similar question on StackOverflow that made me realize I could just loop through all of my GPX files and call the ogr2ogr command line to process them using the GPX feature types.

ogr2ogr -append -f PostgreSQL "PG:dbname=your_db user=xxxx password=yyyy" filename.gpx
  • I settled for GPSBable for quick & dirty transforms. And later moved to R for larger jobs. So looking forward to see Python solution too! – radek Feb 19 '13 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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