1

I want to import a few .gpx files to postgresql using ogr2ogr. The data in each .gpx looks like this:

<trkpt lat="41.842607000" lon="12.490628000"><ele>40.5</ele><time>2010-05-31T14:12:11Z</time> </trkpt>

Firstly, I create a new table in postgresql

CREATE TABLE test (
           fid serial NOT NULL, --
           geom geometry(Point,4326), 
           ele double precision, 
           "time" timestamp with time zone, 
           CONSTRAINT activities_pk PRIMARY KEY (fid)
);

Then when I run the ogr2ogr script:

ogr2ogr -update -append -f "PostgreSQL"  PG:"host="xx" user="xx" dbname="xx" password="xx"" 1st.gpx  -nln test

However, the script failed due to geometry type mismatch.

Warning 1: Geometry to be inserted is of type Multi Line String, whereas the layer geometry type is Point. Insertion is likely to fail ERROR 1: ERROR: Geometry type (MultiLineString) does not match column type (Point)

ERROR 1: INSERT command for new feature failed. ERROR: Geometry type (MultiLineString) does not match column type (Point)

Can anyone help with this?

10
  • 1
    Think you will need to specify a sub dataset in the gpx file, like track, or track points...
    – nmtoken
    Dec 3, 2020 at 6:18
  • well, it does make sense. But how can I specify a sub dataset? There is only lat/lon in the .pgx file.@nmtoken Dec 3, 2020 at 6:24
  • 1
    Have you found the documentation of the GDAL gpx driver gdal.org/drivers/vector/gpx.html? There are some examples which could be useful. Ogr2ogr can also take care of the creation of the tables so you do not need to do it.
    – user30184
    Dec 3, 2020 at 7:23
  • 1
    What does ogrinfo -so 1st.gpx give you? I expect 1: waypoints (Point), 2: routes (Line String), 3: tracks (Multi Line String), 4: route_points (Point), 5: track_points (Point)
    – nmtoken
    Dec 3, 2020 at 10:05
  • 1
    If so you will want to use ... 1st.gpx track_points... in your ogr2ogr, for example I use the following to add gpx points to a GeoPackage ogr2ogr -f GPKG /home/hxxxx/gidataL/GPS/gpsmap64_tracker.gpkg /home/hxxxx/testing/TRACKS/Tracks-toMay7th.gpx track_points -t_srs epsg:27700 -nln Tracks-toMay7th_PTS -update
    – nmtoken
    Dec 3, 2020 at 10:15

1 Answer 1

3

Thanks, everyone for the hints.

After poring over the documentation here https://gdal.org/drivers/vector/gpx.html

I realise that the .gpx file contains either points(track_points) or lines(tracks) geometry type. I fix the error by adding a SQL query, which explicitly import points only.

ogr2ogr -update -append -f "PostgreSQL"  PG:"host="xx" user="xx" dbname="xx" password="xx"" 1st.gpx  -nln test -sql "Select * From track_points"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.