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

Richard Greenwood richard.greenwood at gmail.com
Sat Jan 2 17:21:44 PST 2021


On Sat, Jan 2, 2021 at 7:59 AM Michael Saupe <msaupe at web.de> wrote:

> Hi Evan,
>
> thank you for the explanation. That makes sense. However, in my opinion
> ogr2ogr behavior is not really consistent in this case: The column ogc_fid
> , which is in fact the track id, is incremented on each gpx file import (
> regardless if you use -update -append  or not ).
>
>
The ogc_fid is a primary key column created by ogr2ogr. Its values come
from a postgres sequence named TABLE_NAME_ogc_fid_seq. You can see this by
looking at your table definition, for example in pgAdmin in the "SQL" tab.
The ogc_fid is not the track id but might appear to be since it starts at 0.

Rich


>
> Am 02.01.2021 um 14:03 schrieb 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
> >
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>


-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20210102/42e694bd/attachment-0001.html>


More information about the gdal-dev mailing list