2

I already found out how to add a gpx track information into my postgres database:

 `ogr2ogr -append -f PostgreSQL "PG:dbname=your_db user=xxxx password=yyyy" filename.gpx`

Everything works fine but now I'd like to have an additional column for each track, so that I can find a specific track...

Is this somehow possible?

A hack would be to execute a psql command after each import like this

`"psql -U postgres -d tracks -c 'UPDATE tracks SET track_id = 1234 WHERE ogc_fid = (SELECT max( ogc_fid ) FROM tracks);'"`

But maybe there is an option for ogr2ogr to do that?

Thanks

1 Answer 1

2

I made a test with Spatialite and I do not see a reason why it wouldn't work about similarly with PostGIS. The first command creates a new Spatialite database, inserts the "tracks" layer from the first gpx file and writes a value "id_323" into a new column "id". The second command appends tracks from the next gpx files and gives id "id_324". Note that if the gpx file contains several tracks then they all will get the same id.

C:\temp\>ogr2ogr -f sqlite -dsco spatialite=yes gpx.sqlite gpxtest1.gpx -sql "select t.*,'id_323' as id from tracks t"

C:\temp\>ogr2ogr -f sqlite -append gpx.sqlite gpxtest2.gpx -sql "select t.*,'id_324' as id from tracks t"

It should be possible to run your update command also from GDAL command window by using ogrinfo this way:

ogrinfo -f PostgreSQL "PG:dbname=your_db user=xxxx password=yyyy" -sql "UPDATE tracks SET track_id = 1234 WHERE ogc_fid = (SELECT max( ogc_fid ) FROM tracks)"
3
  • I don't really get it... why a select? I guess it doesn't really add the value to the DB, does it?
    – Georg
    Commented Jun 16, 2014 at 7:25
  • "SELECT 'id_323' AS id" selects a string between the single quates as a value of "id". Ogr2ogr is using that value for the attribute when it makes insert into your db and the value really gets added into the table. Try it, it is all standard SQL. Selecting fixed strings ans concatenating them with values selected from database fields is sometimes useful if there is a need to construct strings which need to have a certain structure, see example about WKT polygon from gis.stackexchange.com/questions/101721/…
    – user30184
    Commented Jun 16, 2014 at 7:42
  • great... thx for explaining!
    – Georg
    Commented Jun 17, 2014 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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