ogr2ogr can't append shapefile to existing Postgres table

Question

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:pathshapefile(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:pathshapefile(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)

Antònia · Accepted Answer

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:pathshapefile(1,2,3...).shp" -progress -nlt MULTIPOLYGON'

Hope that helps!

Theo F · Answer

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'

ogr2ogr can't append shapefile to existing Postgres table

2 Answers

Add your own answers!

Ask a Question