5

I'm implementing a recipe from Paolo Corti's PostGIS Cookbook (by the way, a great help!) in order to import track points from multiple GPX files into a PostGIS table in two steps (pages 87-88):

1 Creating table by a query file

CREATE TABLE myschema.mytable(
    fid serial NOT NULL,
    the_geom geometry(Point,4326),
    ele double precision,
    "time" timestamp with time zone,
    CONSTRAINT activities_pk PRIMARY KEY (fid)
);

2 Importing multiple files with a batch

@echo off
for %%I in (Tracks\*.gpx*) do (
    echo Importing gpx file %%~nxI to myschema.mytable PostGIS table...
    ogr2ogr -append -update -f PostgreSQL PG:"dbname='db' user='me' password='pw'" Tracks/%%~nxI -nln myscheme.mytable -sql "SELECT ele, time FROM track_points"
)

My question is maybe a really beginner's question but I would like to include one column in the PostGIS table indicating the file's name, where each track points come from.

As far as I know, I may first consider a column for the file's name in the Query file (for instance using varchar(20)) but I don't know how to fill it in the second step.

  • 1
    I have not the chance to try, but this should work (after creating a filename column in the table): ogr2ogr -append -update -f PostgreSQL PG:"dbname='db' user='me' password='pw'" Tracks/%%~nxI -nln myscheme.mytable -sql "SELECT ele, time, '%%~nxI' as filename FROM track_points" – capooti Aug 16 '16 at 13:07
  • 1
    Excelent! I came across following post regarding the file names and I used '%%~nI' as filename in the batch instead. Thus I got the file names in a column without file extensions. – Migue Lo Aug 16 '16 at 16:04

Your Answer

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

Browse other questions tagged or ask your own question.