OGR – Field Name Changes in GPKG Layer Not Retained Using OGR

fields-attributesgeopackageogrpython

I am trying to update the names of the fields in a gpkg layer using the ogr Python package. I have gotten most of the way using this answer to another question on the subject using SetName on the fieldDefn:

    gpkg_layer = gpkg_src.GetLayerByName(layer_name)
    layer_defn = gpkg_layer.GetLayerDefn()
    for i in range(layer_defn.GetFieldCount()):
        fieldDefn = layer_defn.GetFieldDefn(i)
        fieldName = slugify(fieldDefn.GetName(), separator='_')
        fieldDefn.SetName(fieldName)

However, after updating the fields the changes are not retained if I reopen the gpkg – the names have reverted back to what they were before.

I am opening the file with update=True. I also tried to call SyncToDisk on the Layer.

Best Answer

GeoPackage is a SQLite database and renaming columns in SQLite is not trivial. Traditionally it required to do all this (from https://www.sqlitetutorial.net/sqlite-rename-column/)

First, start a transaction.

Second, create a new table whose structure is the same as the original one except for the column that you want to rename.

Third, copy data from the original table to the new table.

Fourth, drop the original table.

Fifth, rename the new table to the original table. Finally, commit the transaction.

GDAL has for some time had an own SQL function to help with renaming and SQLite itself has now native support for standard SQL way of ALTER TABLE ... RENAME COLUMN ... TO ...

You can find Python code for renaming from https://github.com/OSGeo/gdal/blob/master/autotest/ogr/ogr_gpkg.py

Essential lines:

ogr.Open(dbname, update=1)
    ds.ExecuteSQL('ALTER TABLE "weird2\'layer""name" RENAME COLUMN "foo" TO "bar"')

Some changes to SQLite schema may get so complicated that it is faster and at least easier to avoid errors by creating a new GeoPackage with desired structure instead of making manual fixes. See the diagram with 11 steps in https://www.sqlitetutorial.net/sqlite-alter-table/.

Related Question