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

[Chaitanya kumar CH]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20120326/14787b2c/attachment.html