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

[James Hiebert]

> The ticket #3637 was invalid because there was nothing to fix. The change in
> track_fid values was not unexpected.

Good to know.  I guess as a very periphrial GDAL user, I'm still learning what to expect and what is not core functionality.

> Please let us know how you solved this issue.

It turned out that a pretty simple python script solved my problem (thanks largely to the "Retrieving FID of newly inserted feature" from here: http://www.gdal.org/ogr/drv_pg_advanced.html).  Without any attempt to optimize for performance or catch any errors, it can be expressed in a short page (below) and seems to do the right foreign key mapping; at least for the several test cases that I tried.  We'll see what happens when I go to load years worth of data :)

Thanks for the feedback.

~James

import ogr

rd = ogr.Open('/path/to/my/file.gpx')
wt = ogr.Open("PG:dbname='spatial' host='my.host' user='me' password='******'")

rlyr = rd.GetLayerByName('tracks')
wlyr = wt.GetLayerByName('tracks')

fid_map = {}

# Copy tracks, saving the database fid
for feat in rlyr:
    fFID = feat.GetFID()
    feat.SetFID(-1)
    rv = wlyr.CreateFeature(feat)
    fid_map[fFID] = feat.GetFID()

print "fid map:", fid_map

ptrlyr = rd.GetLayerByName('track_points')
ptwlyr = wt.GetLayerByName('track_points')

# Copy track-points using the database track_fid rather than the file track_fid 
for feat in ptrlyr:
    feat.SetFID(-1)
    feat.SetField('track_fid', fid_map[feat.track_fid])
    rv = ptwlyr.CreateFeature(feat)


On Mon, Mar 26, 2012 at 01:33:51PM +0530, Chaitanya kumar CH wrote:
> James,
> 
> The ticket #3637 was invalid because there was nothing to fix. The change in
> track_fid values was not unexpected.
> 
> Whatever we do, we need to keep track of the foreign keys. That is not a
> trivial case. It is more of a DBMS area. In fact, the solution you specified,
> which is to have a temporary table, was my first idea too.
> 
> Please let us know how you solved this issue.
> 
> On Mon, Mar 26, 2012 at 10:29 AM, James Hiebert <hiebert at uvic.ca> wrote:
> 
>     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
>     _______________________________________________
>     gdal-dev mailing list
>     gdal-dev at lists.osgeo.org
>     http://lists.osgeo.org/mailman/listinfo/gdal-dev
> 
> 
> 
> 
> --
> Best regards,
> Chaitanya kumar CH.
> 
> +91-9494447584
> 17.2416N 80.1426E