7

Context

I'm trying to update a PostgreSQL+PostGIS table from an ESRI Shapefile using the ogr2ogr tool from gdal.
The Shapefile is being updated from external sources on a regular basis.
I don't want to append its content, I prefer starting again from scratch.

This is the command I run for the moment;

ogr2ogr \
  -lco OVERWRITE=yes \
  -nlt PROMOTE_TO_MULTI \
  -nln vector_data.lines \
  -f "PostgreSQL" \
     PG:"host=localhost user=username dbname=my_database" \ 
  -a_srs EPSG:4326 "${shapedir}/el_lines.shp"

Issues

1:

I get this warning message:

Warning 1: Layer creation options ignored since an existing layer is
         being appended to.

And the features are being append to the table, hence doubling the number of rows.

2:

If I add the -overwrite option, features (i.e. records in the table) are being successfully updated, as I expect, but the downside is I lose all the views and relations which have a link to the table I want to update. Needless to say I don't want to lose these views and relations.

Question

How to wisely use ogr2ogr so that it builds the table as if it's newly created (such as the really first time I ran ogr2ogr), but while keeping all relations to this table in the database?


Related questions

Environment

PostgreSQL:

"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, \
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

PostGIS:

"2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

Gdal:

$ ogr2ogr --version
GDAL 2.4.0, released 2018/12/14
Improve this question
1
  • I've had good performance with loading to a new temporary table, then using INSERT/UPDATE/DELETE to drive changes to the existing table. A lot depends on whether you need to access the data from a concurrent process (e.g. a map service) or whether you can TRUNCATE and INSERT from the staged table after the initial load has taken place.
    – Vince
    Commented Apr 3, 2020 at 4:39

1 Answer 1

Reset to default
11

Read https://gdal.org/drivers/vector/pg.html

Configuration Options

OGR_TRUNCATE:

(GDAL >= 1.11) If set to “YES”, the content of the table will be first erased with the SQL TRUNCATE command before inserting the first feature. This is an alternative to using the -overwrite flag of ogr2ogr, that avoids views based on the table to be destroyed. Typical use case: 

ogr2ogr -append PG:dbname=foo abc.shp --config OGR_TRUNCATE YES”
Improve this answer
4
  • Is the -append flag mandatory? It sounds strange. In addition, why I cannot find the --config option in the man of ogr2ogr (gdal.org/programs/ogr2ogr.html)? Is it the same as the -lco? And how to use several --config options in a row?
    – swiss_knight
    Commented Apr 3, 2020 at 9:42
  • 1
    Sounds logical as well Append to existing layer instead of creating new. Usage of Config options is documented in gdal.org/programs/vector_common_options.html. Options may be driver specific and documented in the driver manuals. Some are generic and documented in gdal.org/user/configoptions.html and trac.osgeo.org/gdal/wiki/ConfigOptions. Config options are not the same as Layer Creation Options. There are also Open Options (-oo) that you may meet later when working with GDAL. Several options are used simply --config option1 value --config option2 value.
    – user30184
    Commented Apr 3, 2020 at 10:00
  • Ok. Actually, I noticed that the behavior is the same with or without the -append flag. Thanks for the links.
    – swiss_knight
    Commented Apr 3, 2020 at 10:16
  • 1
    Yes, the flags should behave in the same way for all the drivers gdal.org/drivers/vector/index.html but it is not always the case. For example Oracle driver has own options for setting SRID, DIM, OVERWRITE etc. and the common ogr2ogr options do not work.
    – user30184
    Commented Apr 3, 2020 at 11:32

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.