3

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.

amelie
  • 141
  • 5
  • 1
    Does this answer your question? It seems like you need to run separate commands. https://gis.stackexchange.com/questions/357062/upload-multiple-tables-from-a-single-geopackage-file-to-postgresql-postgis – Leon Powałka Jan 29 '21 at 07:15
  • @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) – amelie Jan 29 '21 at 07:26
  • 2
    Yes, but you can write a batch or a script to avoid it. – Nil Jan 29 '21 at 07:38
  • 1
    Batch script/Python script or any other automation tool to call the layers in a loop – Leon Powałka Jan 29 '21 at 08:28
  • 1
    I added an example answer for what you want to achieve. – Leon Powałka Jan 29 '21 at 09:25

3 Answers3

3

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)
Leon Powałka
  • 1,653
  • 5
  • 18
3

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
HeikkiVesanto
  • 16,433
  • 2
  • 46
  • 68
0

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.

SaultDon
  • 10,389
  • 1
  • 43
  • 78