2

this is my first question here and I have done extensive searches but still can't find the (full?) answer I'm looking for. Hopefully someone can help!

I am loading a number of personal geodatabases (mdb) to PostGIS using OGR2OGR (and GDAL). I am able to get the whole database to load with no issues, but for my requirements I need to store the original filename alongside each record in each table (so that I can query/delete/filter at a later stage).

At the moment I am doing this using a simple OGR2OGR script that loads all tables in the MDB (over 250 of them)...this is about the limit of my ability right now so any solution needs to be fairly simple if possible?

My current simple script: ogr2ogr -f "PostgreSQL" PG:"host=** active_schema=** user=** dbname=** password=** port=5432" *.mdb -skipfailures -append -a_srs EPSG:27700

Thanks.

Improve this question

1 Answer 1

Reset to default
1

og2ogr can source data using an SQL query statement instead of just a layer name.

The following query selects data from the "Routes_Master" layer and adds a table column "tablename" set to "table1".

select *,'table1' AS 'tablename' from Routes_Master

As a command...

ogr2ogr -sql "select *,'table1' AS 'tablename' from Routes_Master" PG:"*" A.mdb -append -nln targetlayer

Note the use of -nln to set the target layer within PostGIS. By default ogr2ogr seems to pick SELECT as the target layer when using an sql query so set something more specific.

Improve this answer
2
  • Matthew - very much appreciated! I had tried similar things but your explanation seems to be what I needed to get it right. Thanks very much!
    – Gareth
    Commented Jul 6, 2013 at 16:28
  • Unfortunately I thought it would be enough but it's not quite what I'm looking for...this now allows me to load a single layer and add a new column. But what I need is all tables from the source database to be loaded (as per the basic OGR2OGR command) with each record in the destination database (in all tables) having the filename of the original mdb from where it came. example: if I have a database called "Wales.mdb" and "england.mdb" I want all layers within these databases loaded into postGIS with each record in all tables containing either "wales.mdb" or "england.mdb" against them.
    – Gareth
    Commented Jul 6, 2013 at 17:31

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.