[gdal-dev] ogr2ogr, gpx -> PostGIS, preserving fid links

[James Hiebert]

Hi all,

I'm trying to use ogr2ogr to load gpx tracks into PostGIS and am having trouble preserving the foreign keys between the "track_points" layer/table and the "tracks" layer/table.

My use case is as such:
I typically collect gps traces in the field and use gpsbabel to download the traces from one of my gps receivers.  Afterwards I want to load them into PostGIS, adding the trace(s) from the last period of time to my archive of gps traces from the past several years.  When inserting into PostGIS, I would like to include both the track_points layer (to include all of the point attributes such as time, elevation, hdop/vdop, etc.) and the tracks layer.  I would like for each of the track_point rows to include a foreign key to the corresponding track and GDAL's GPX driver seems to support this according to here: http://gis-lab.info/docs/gdal/gdal_ogr_user_docs.html

Whenever running ogr2ogr in append mode, however, the track_fid column of the track_points are never translated to account for what is the actual fid of the corresponding track.  For example, if there are already 10 tracks in my database, and the track fid sequence is at 10, the new track will be loaded in with fid=10, but the track_points from that track will be loaded in with track_fid=0 (i.e. it points to the track_fid in the new gpx file, not the database).

I thought that perhaps the -preserve_fid command line switch would help, but it's essentially useless in -append mode.  If you're adding a new set of tracks to the database, there's no point in preserving the fids; all of the fids (0-n) in the new gpx file have all been used up by the existing tracks in the database and I'll just get primary key errors on anything after the first insertion.  I think that I'm missing something.  For example, I don't really understand why this old ticket http://trac.osgeo.org/gdal/ticket/3637 is marked as invalid. Perhaps it's just that my use case isn't supported by ogr2ogr? (If so, let me know and I'll move on).

What's my best solution here?  Is there an easy way out that I'm not thinking of/don't know about, or do I have to do some several step process like use ogr2ogr to insert into a temprorary table and then move them into the multi-track archive using a select/insert?  Or should I just not use ogr2ogr and write my own program with GDAL which retrieves the track_fid upon writing that layer and then uses it for writing the track_points layer?

FWIW I'm using GDAL 1.9.0, PostgreSQL 9.1.3 and PostGIS 1.5.3-r1 all built on Linux.

Cheers,

~James