How to import GDB to PostgreSQL into separate tables?

Question

I am trying to upload a .gdb data into my postgres database using og2ogr. If I upload whole database by command ogr2ogr -f "PostgreSQL" PG:"" "test.gdb" -nln gdb_test -nlt PROMOTE_TO_MULTI it creates a single table gdb_test, it imports first layer into that and then gives errors Warning 1: Geometry to be inserted is of type Multi Line String, whereas the layer geometry type is Multi Polygon. Insertion is likely to fail ERROR 1: COPY statement failed. ERROR: Geometry type (MultiLineString) does not match column type (MultiPolygon) . I guess it is because other layers are of different geometry types. Is there a way to upload whole .gdb with multiple layers, each different geometry type to a postgres into separate tables?

ogrinfo on my .gdb is following:

1: Ned (Multi Polygon)
2: Str (Multi Line String)
3: Vod (Multi Line String)
4: Kan (Multi Line String)
5: Red (Multi Line String)
6: Uch (Multi Polygon)

Update: I'll leave the question unanswered, if anyone knows how to upload whole package automatically.

• Leon Powałka: Does this answer your question? It seems like you need to run separate commands. gis.stackexchange.com/questions/357062/…
• amelie: @LeonPowałka yes it does, thank you! But I guess there is no way to automatically upload all layers, I have to run the command for every layer by myself? (I'll write a script ofc, but anyway)
• Nil: Yes, but you can write a batch or a script to avoid it.
• Leon Powałka: Batch script/Python script or any other automation tool to call the layers in a loop
• Leon Powałka: I added an example answer for what you want to achieve.

Answer 1

If you are looking for any automatization then getting friendly with Python or batch scripting is the way to go. This solution requires Python with fiona. I tested it on GPKG but it should work the same way with GDB.

You need to fill your entry parameters in the first lines of this script. The tables are created with names corresponding to layernames in the file.

import fiona
import os
#entry parameters - change it to yours
inputFile = '/path_to_file/file.gpkg'
schema='yourschema'
host='yourhost'
user='youruser'
password='yourpass'
database='yourdbname'
srs='EPSG:4326'
cmdBase = 'ogr2ogr -append --config OGR_TRUNCATE YES --config PG_USE_COPY YES \
 -nlt PROMOTE_TO_MULTI \
 -nln {schema}.{table} \
 -f "PostgreSQL" PG:"host={host} user={user} password={password} dbname={database}" \
 -a_srs {srs} "{inputFile}" "{layerName}"'
for layerName in fiona.listlayers(inputFile):
 #new table name corresponds to layerName. You can change it here
 table=layerName
 cmd = cmdBase.format(inputFile=inputFile, schema=schema, table=table, host=host, user=user, password=password, database=database, srs=srs, layerName=layerName)
 print(cmd)
 #run system command
 os.system(cmd)

Answer 2

You need to give the table names inside the GDB as well.

ogr2ogr -f "PostgreSQL" PG:"" "test.gdb" "Ned" -nlt PROMOTE_TO_MULTI
ogr2ogr -f "PostgreSQL" PG:"" "test.gdb" "Str" -nlt PROMOTE_TO_MULTI
ogr2ogr -f "PostgreSQL" PG:"" "test.gdb" "Vod" -nlt PROMOTE_TO_MULTI
ogr2ogr -f "PostgreSQL" PG:"" "test.gdb" "Kan" -nlt PROMOTE_TO_MULTI
ogr2ogr -f "PostgreSQL" PG:"" "test.gdb" "Red" -nlt PROMOTE_TO_MULTI
ogr2ogr -f "PostgreSQL" PG:"" "test.gdb" "Uch" -nlt PROMOTE_TO_MULTI

Answer 3

It's because you are merging every table in the GDB to a new table called test_gdb.

You have to remove the -nln flag.

• amelie: I have tried, unfortunately, it did nothing (nothing was uploaded to database)