[gdal-dev] ogr2ogr from gpx to postgis - track_fid not maintained for track_points

[Even Rouault]

Michael,

You have a single track in each file, so it is expected that track_fid doesn't 
change and is set at 0 (as there's no attribute to uniquely identify a track)
For what you want to do (that is have distinct track_fid for each file), 
you'll likely have to use an intermediate postgresql schema, where you would 
correct the value of track_fid, before merging them together

Even

> clarification: second table of my points is 'track_points'.
> 
> Also adding my 2 gpx files for reference:
> 
> *file1.gpx:*
> 
> <?xml version="1.0" encoding="UTF-8"?>
> <gpx version="1.1" xmlns="http://www.topografix.com/GPX/1/1">
>   <metadata>
>   </metadata>
>   <trk>
>     <trkseg>
>       <trkpt lat="49.250324000" lon="8.687196000">
>         <ele>1</ele>
>       </trkpt>
>       <trkpt lat="49.250019001" lon="8.687469003">
>         <ele>1</ele>
>       </trkpt>
>       <trkpt lat="49.249714001" lon="8.687742003">
>         <ele>1</ele>
>       </trkpt>
>     </trkseg>
>     <trkseg>
>       <trkpt lat="49.125652000" lon="8.714194000">
>         <ele>2</ele>
>       </trkpt>
>       <trkpt lat="49.125519000" lon="8.714731000">
>         <ele>2</ele>
>       </trkpt>
>       <trkpt lat="49.125414000" lon="8.715057001">
>         <ele>2</ele>
>       </trkpt>
>     </trkseg>
>   </trk>
> </gpx>
> 
> *file2.gpx:*
> 
> <?xml version="1.0" encoding="UTF-8"?>
> <gpx version="1.1" xmlns="http://www.topografix.com/GPX/1/1">
>   <metadata>
>   </metadata>
>   <trk>
>     <trkseg>
>       <trkpt lat="49.11" lon="8.7">
>         <ele>3</ele>
>       </trkpt>
>       <trkpt lat="49.12" lon="8.7">
>         <ele>3</ele>
>       </trkpt>
>       <trkpt lat="49.13" lon="8.7">
>         <ele>3</ele>
>       </trkpt>
>     </trkseg>
>   </trk>
> </gpx
> 
> Am 01.01.2021 um 18:04 schrieb Michael Saupe:
> > Hello folks,
> > 
> > I am importing multiple gpx files into a postgis database. The tables
> > 'tracks' and 'track_points' are created / updated.
> > 
> > The column track_fid in in the track_points table should be a reference to
> > the track feature id as documented in
> > https://gdal.org/drivers/vector/gpx.html, however it is always zero. 
> > 
> > Is this a bug or am I missing something?
> > 
> >       Details
> > 
> > I am creating an empty database, then create postgis extension with
> > 'CREATE EXTENSION POSTGIS'
> > 
> > I have two simple gpx files ( see attachments )
> > 
> >   * file1.gpx - containing 2 track segments - each with 3 track points
> >   * file2.gpx - containing 1 track segment  with 3 track points
> > 
> > I am using commands
> > 
> > ogr2ogr -f PostgreSQL -update -append "PG:dbname=testdb host=localhost
> > user=postgres password=xx port=5432" file1.gpx
> > 
> > ogr2ogr -f PostgreSQL -update -append "PG:dbname=testdb host=localhost
> > user=postgres password=xx port=5432" file2.gpx
> > 
> > Resulting data in 'tracks'
> > 
> > ogc_fid 	name 	cmt 	desc 	src 	link1_href 	link1_text 	link1_type
> > 	link2_href 	link2_text 	link2_type 	number 	type 	wkb_geometry 1 	
NULL
> > 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	
NULL 	... 2
> > 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	
NULL 	NULL
> > 	...
> Resulting data in 'track_points'
> 
> ogc_fid 	track_fid 	track_seg_id 	track_seg_point_id 	ele 	time 	.... 
other
> columns omitted ... 1 	0 	0 	0 	1 	NULL
> 2 	0 	0 	1 	1 	NULL
> 3 	0 	0 	2 	1 	NULL
> 4 	0 	1 	0 	2 	NULL
> 5 	0 	1 	1 	2 	NULL
> 6 	0 	1 	2 	2 	NULL
> 7 	0 	0 	0 	3 	NULL
> 8 	0 	0 	1 	3 	NULL
> 9 	0 	0 	2 	3 	NULL
> 
> > Expected:
> > 
> > ogc_fid 	track_fid 	track_seg_id 	track_seg_point_id 	ele 	time 	....
> > other columns omitted ... 1 	1
> > 
> > 	0 	0 	1 	NULL
> > 
> > 2 	1
> > 
> > 	0 	1 	1 	NULL
> > 
> > 3 	1
> > 
> > 	0 	2 	1 	NULL
> > 
> > 4 	1
> > 
> > 	1 	0 	2 	NULL
> > 
> > 5 	1
> > 
> > 	1 	1 	2 	NULL
> > 
> > 6 	1
> > 
> > 	1 	2 	2 	NULL
> > 
> > 7 	2
> > 
> > 	0 	0 	3 	NULL
> > 
> > 8 	2
> > 
> > 	0 	1 	3 	NULL
> > 
> > 9 	2
> > 
> > 	0 	2 	3 	NULL
> > 
> > I also tried to omit -update -append, but same result.
> > 
> > Versions:
> >   * Postgres: "PostgreSQL 12.5 on x86_64-pc-linux-musl, compiled by gcc
> >   (Alpine 9.3.0) 9.3.0, 64-bit" * Postgis: "POSTGIS="3.0.3 0" [EXTENSION]
> >   PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" PROJ="7.0.1" LIBXML="2.9.10"
> >   LIBJSON="0.14" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"" * Gdal:
> >   3.2.0


-- 
Spatialys - Geospatial professional services
http://www.spatialys.com