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:\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)

you need to add the -update flag. check my answer here for an easily customizable Bash/CMD batch import script — geozelot, Nov 20 '19 at 17:03
@ThingumaBob adding -update does not change things I'm afraid. Same error. — Theo F, Nov 20 '19 at 17:14
well, you definitely need to use both flags; but try subsequent calls without the -nln option — geozelot, Nov 20 '19 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 '19 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 '19 at 18:52
In my case my table name had a - in it, which ogr automatically converted to _. For whatever reason that messed with the append behavior. When I changed the dash to an underscore ahead of time, I was able to append to the table — Avocado, Mar 17 '22 at 21:17

score 8 · Accepted Answer · answered Nov 26 '19 at 15:53

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!

answered Nov 26 '19 at 15:53

Antònia

96
1

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 '19 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 '22 at 10:16

score 3 · Answer 2 · answered Oct 29 '20 at 10:18

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 Answers2

gdal

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'

Linked