4

Using ogr2ogr within python (and Windows 10), I can upload a shapefile to Postgres as a new table. However, my shapefile is made of 10 parts (10 shapefiles). I can upload the first .shp but when trying to append the next .shp to the table, I get an 'Error: layer already exists'. Even though I'm using the -append tag in my command.

Here is my command for the first shapefile AND subequent shapefiles to append:

ogr2ogr -f "PostgreSQL" PG:"host=url port=5432 dbname=db1 user=username password=password" -append -nln tablename -lco GEOMETRY_NAME=shape -lco SCHEMA=schema1 "D:\path\shapefile(1,2,3...).shp" -progress -nlt MULTIPOLYGON

or more specifically (as I'm using python):

from subprocess import call
command = r'ogr2ogr -f "PostgreSQL" PG:"host=url port=5432 dbname=db1 user=username password=password" -append -nln tablename -lco GEOMETRY_NAME=shape -lco SCHEMA=schema1 "D:\path\shapefile(1,2,3...).shp" -progress -nlt MULTIPOLYGON'
call(command)

and the error that occurs when trying to append the 2nd shapefile to the newly created table:

ERROR 1: Layer schema1.tablename already exists, CreateLayer failed.
Use the layer creation option OVERWRITE=YES to replace it.
ERROR 1: Terminating translation prematurely after failed
translation of layer shapefile2 (use -skipfailures to skip errors)
6
  • you need to add the -update flag. check my answer here for an easily customizable Bash/CMD batch import script
    – geozelot
    Nov 20, 2019 at 17:03
  • @ThingumaBob adding -update does not change things I'm afraid. Same error.
    – Theo F
    Nov 20, 2019 at 17:14
  • well, you definitely need to use both flags; but try subsequent calls without the -nln option
    – geozelot
    Nov 20, 2019 at 17:19
  • You need the -nln option to tell the command which table you're appending the shapefile to. Removing -nln just uploads the 2nd shapefile as a new table.
    – Theo F
    Nov 20, 2019 at 17:24
  • not necessarily, but correct here...I cannot reproduce the isssue, using ogr2ogr ... -append -update ... works just fine for me (Ubuntu, Bash).
    – geozelot
    Nov 20, 2019 at 18:52

2 Answers 2

8

I had the same problem. I solved it removing any reference to the layer creation options (-lco): include them only in the first call. The following calls should look like this:

command = r'ogr2ogr -f "PostgreSQL" PG:"host=url port=5432 dbname=db1 user=username password=password" -append -update -nln schemaname.tablename "D:\path\shapefile(1,2,3...).shp" -progress -nlt MULTIPOLYGON'

Hope that helps!

2
  • I tried this ammended command to append the next shapefile, and although the command appears to execute without error, it hangs on 0% progress indefintiely.
    – Theo F
    Dec 11, 2019 at 10:19
  • It's not working for me: ogr2ogr -f MySQL MySQL:tiger,host=localhost,user=root,password=password -append -update -nln tiger.county_shapes tl_2020_us_county.shp -nln county_shapes -progress -nlt MULTIPOLYGON
    – neubert
    May 11, 2022 at 10:16
3

Expanding on Antonia's answer where yes: removing -lco SCHEMA=schema1 and instead refer to the schema in the -nln tag, ie: -nln schema.table works...

We can improve on the whole process by iterating through a list of shapefiles in a directory and upload them all into the same new table in PostgreSQL like so:

import os
from subprocess import call

dir = r'C:/pathtoshapefiles/'

shpList = []
fileList = []

for file in os.listdir(dir):
if file.endswith(r'.shp'):
    shpList.append(os.path.join(dir,file))
    fileList.append(file[len(file)-100:len(file)-4]) #ignore the last 4 characters (.shp) to make a list of layers to pass into any SQL statements you use in the ogr2ogr string

# gdal
for x in range(0, len(shpList)):
command = fr'ogr2ogr -append -update -progress -f "PostgreSQL" PG:"host=url port=5432 dbname=db1 user=username password=password" -nln schema.table "{shpList[x]}" -lco GEOMETRY_NAME=shape -nlt MULTIPOLYGON -sql "SELECT column1, column2, column3 FROM {fileList[x]}"'
print(command)
call(command)

# change '-nlt MULTIPOLYGON' to POINT or POLYGON or LINESTRING if needed above. If uploading as MULTIPOLYGONs you can force it with '-nlt PROMOTE_TO_MULTI'

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.