11

Spatial layers (tables) in SpatiaLite include various supporting objects and metadata, including triggers, indices, and entries in (at minimum) the geometry_columns table. I'm looking for (preferably) a GUI that would handle all of the necessary changes in one go, or (fallback) documentation of all required changes so as not to break the spatial layers.

Tables have:

  1. Entry in geometry_columns.f_table_name.
  2. Five triggers named [prefix]_[table_name]_geometry, where prefix indicates whether it is a transaction rollback or index update.
  3. Three spatial indices named idx_[table_name]_geometry[_suffix]

I have tried this in two applications, QGIS DB Manager and SpatiaLite-GUI.

QGIS DB Manager appears have the following effects:

  1. Correctly updates geometry_columns with new table name
  2. Does not rename triggers. Trigger definition is partially modified so that BEFORE [INSERT|UPDATE|DELETE] refers to new table name, but condition still searches for old name in geometry_columns.
  3. Does not rename spatial indices. Not sure if this matters because triggers still refer to old index names.

When you choose Maintenance→Rename table in SpatiaLite-GUI, you just get the stub of a SQL ALTER TABLE statement. This is straightforward SQL and does even less than QGIS DB Manager. If you fill in the new table name, the table gets renamed. Other effects:

  1. Table does not get renamed in geometry_columns.f_table_name, which means many GISes won't see the table as a spatial layer.
  2. Does not rename triggers. Trigger definition is partially modified so that BEFORE [INSERT|UPDATE|DELETE] refers to new table name, but condition still searches for old name in geometry_columns.
  3. Does not rename spatial indices. Not sure if this matters because triggers still refer to old index names.

Note that Spatialite-GUI does give the option to recover a geometry column which adds the correct entry to geometry_columns (but requires you to specify SRID, geometry type, and dimensions), to build or rebuild a spatial index, and to recover triggers, but none of these functions remove old table rows, triggers, or indices, leading to a DB with a lot of (possibly harmless but annoying) schmutz in it.

  • 3
    I think there is a great need for a "Table Manager" for rearranging, renaming, deleting etc fields in SpatiaLite if we ever want to get rid of shape files. But, for unknown reasons, there is no such tool that would help us with that, as far as I know. Would be maybe a good project for a crowd funding initiative! – Bernd V. Sep 30 '15 at 11:34
  • Which version of spatialite-gui you have? – user30184 Sep 30 '15 at 18:50
  • SpatiaLite-GUI 1.7.1. – Lee Hachadoorian Oct 2 '15 at 0:02
2

You need to copy the table with a new table name and rename the columns when you're copying the data over. A table manager function would be ideal for this but hasn't been created for the gui yet. The link below shows some formal information for this with sample code in SQLite. SpatiaLite is a part of SQLite so the coding is the same. Good luck!

https://blog.xojo.com/2013/12/04/renaming-columns-in-sqlite-tables/

  • Note that the question is about renaming tables, not columns. The linked page discusses the ALTER TABLE statement, but the question already addresses why this is inadequate for a spatial database. – Lee Hachadoorian Jul 12 '17 at 20:01
  • After looking this over further with your comment in mind, I do not think that there is an adequate way to do this and maintain the triggers and other structural components. It would seem that you would need to re-write the code or when you go to export the table (if you plan on doing so), use a different name in the db manager in QGIS. – Julia Jul 18 '17 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.