Allow a replace option in OGR2OGR

[msmitherdc]

It would be very helpful to be able to replace the data in a layer (I'm referring to database data layers specifically) without having to delete the layer first (eg Drop Table) and just have it do a truncate table instead and reload the data (accepting the layer creation options).

[warmerdam]

I am not clear on why truncating would be significantly better than what the existing -overwrite option does (which is to internally drop the table and recreate it). Currently OGR has no mechanism for bulk deleting of features. So the only way to truncate the table generically is to delete each feature individually which would be very expensive.

[msmitherdc]

Replying to warmerdam:

I am not clear on why truncating would be significantly better than what the existing -overwrite option does (which is to internally drop the table and recreate it). Currently OGR has no mechanism for bulk deleting of features. So the only way to truncate the table generically is to delete each feature individually which would be very expensive.

The advantage is that we often have dependancies on the tables. Dropping the tables invalidates all the dependencies, removes all the grants, indexes, etc. We really just need to replace the data. Rather than deleting one at a time, we need a truncate table option and then it would just be an append operation. But the lco's are needed as well (setting dimensionality, srid, etc) since the lack of data would prevent them from being discovered.

[Even Rouault]

If it's a SQL DBMS datasource, you should be able to do : ogrinfo the_datasource -sql "DELETE FROM your_table"

[msmitherdc]

Replying to rouault:

If it's a SQL DBMS datasource, you should be able to do : ogrinfo the_datasource -sql "DELETE FROM your_table"

Or -sql "TRUNCATE TABLE your_table" I suppose.

You'd have to issue 2 ogr commands, correct. One to truncate, and the next to -append to load the data. The lco options are still needed, however as OGR tries to load DIM=3 by default and there will be no data to set the dimensionality. Same for SRID and GEOMETRY_NAME. These options are needed when appending as well.

[Even Rouault]

I don't understand your statement : "The lco options are still needed, however as OGR tries to load DIM=3 by default and there will be no data to set the dimensionality. Same for SRID and GEOMETRY_NAME. These options are needed when appending as well. "

Are you refering to the PostgreSQL driver ? If so, the DIM, SRID and GEOMETRY_NAME options are used by CreateLayer() to define the columns of the table and/or add an entry to geometry_columns table. So if you remove all the data from it, this should be fine. They will be retrieved when opening the layer even if it has zero content. I've just tested it indeed.

[msmitherdc]

It does not work with oracle which is the driver I was testing it with.

[msmitherdc]

For example with format OCI. Dimension and SRID are not preserved. Geometry name is kept.

u4rt9mds@maps:/htdocs/dhs/marine$ ogr2ogr -append -update -f oci OCI:user/pass@tns:mds.Marine_ogr_test MarineTraffic.kmz -nln mds.marine_ogr_test ERROR 1: ORA-29875: failed in the execution of the ODCIINDEXINSERT routine ORA-13365: layer SRID does not match geometry SRID ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 709 ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225

in INSERT INTO mds.Marine_ogr_test (SHAPE, OGR_FID, "NAME", "DESCRIPTION", "TESSELLATE", "EXTRUDE", "VISIBILITY") VALUES (MDSYS.SDO_GEOMETRY(3001,NULL,MDSYS.SDO_POINT_TYPE(140.1126,35.59063,0),NULL,NULL), 1, 'ATAGO', '<b>Speed/Course:</b>&nbsp;0 knots / 141&deg;<br/><b>Flag:</b>&nbsp;PA&nbsp;<img src="​http://www.marinetraffic.com/ais/flags/PA.gif"/><br/><br/><a href="​http://www.marinetraffic.com/ais/shipdetails.aspx?mmsi=355478000"><img src="​http://photos.marinetraffic.com/ais/showphoto.aspx?size=thumb&mmsi=355478000"/><br/>Vessels details on MarineTraffic.com<br/></a><a href="​http://www.marinetraffic.com/ais/gettrackkml.aspx?mmsi=355478000">Show Vessels Track</a>', -1, -1, -1)

ERROR 1: Terminating translation prematurely after failed translation of layer 0 (use -skipfailures to skip errors)

table_name,column_name,diminfo,srid MARINE_OGR_TEST,SHAPE,((X, -178.8405003, 175.0387003, 1E-7), (Y, -86.7249703, 81.2695803, 1E-7), , ),8265

[Jukka Rahkonen]

Implemented for PostGIS by the ticket #5091 with config option OGR_TRUNCATE Oracle OCI driver has a layer creation option "TRUNCATE"

SQLite/SpatiaLite/GPKG and MS-SQL etc. probably do not support truncating yet.

[Even Rouault]

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to ​https://github.com/OSGeo/gdal/issues if it is not already reported there.