James,<br><br>The ticket #3637 was invalid because there was nothing to fix. The change in track_fid values was not unexpected.<br><br>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.<br>
<br>Please let us know how you solved this issue.<br><br><div class="gmail_quote">On Mon, Mar 26, 2012 at 10:29 AM, James Hiebert <span dir="ltr"><<a href="mailto:hiebert@uvic.ca">hiebert@uvic.ca</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi all,<br>
<br>
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.<br>
<br>
My use case is as such:<br>
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: <a href="http://gis-lab.info/docs/gdal/gdal_ogr_user_docs.html" target="_blank">http://gis-lab.info/docs/gdal/gdal_ogr_user_docs.html</a><br>
<br>
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).<br>
<br>
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 <a href="http://trac.osgeo.org/gdal/ticket/3637" target="_blank">http://trac.osgeo.org/gdal/ticket/3637</a> 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).<br>
<br>
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?<br>
<br>
FWIW I'm using GDAL 1.9.0, PostgreSQL 9.1.3 and PostGIS 1.5.3-r1 all built on Linux.<br>
<br>
Cheers,<br>
<br>
~James<br>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">http://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</blockquote></div><br><br clear="all"><br>-- <br>Best regards,<br>Chaitanya kumar CH.<br><br>+91-9494447584<br>17.2416N 80.1426E<br>