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>