1

I am trying to modify column names in a SpatiaLite table. To do that, I run the following SQL statements in the DB Manager :

-- Rename existing table
ALTER TABLE compteur_routier RENAME TO compteur_routier_sav;

-- Create new table with modified column names
CREATE TABLE compteur_routier (
id INTEGER PRIMARY KEY,
geom POINT,
id_compt TEXT,
type_compt TEXT,
angle INTEGER,
id_tmp INTEGER
);

-- Insert data from renamed table
INSERT INTO compteur_routier(id, geom, id_compt, type_compt, angle, id_tmp)
SELECT id, geom, id_compteur, type_compteur, angle, id_tmp FROM compteur_routier_sav;

-- Drop renamed table
DROP TABLE compteur_routier_sav;

This works perfectly in the DB manager, column names are modified and datas are kept. However, when I load the resulting table in QGIS and open the attribute table, I get old column names and datas replaced by column names.

Table before : table before

Table after : table after

1

I tried it and there was no such thing as you said. I think it should be that your SQL is not getting it right. At the time, I found out that for SpatiaLite, if you use the method you used, the geometry will be lost after executing the "ALTER TABLE compteur_routier RENAME TO compteur_routier_sav;" statement. The SQL statement you use is only for SQLite without geometry.

You can use the Refactor fields tool. But when I tested in QGIS 3.2.1, I found that if I use the Refactor fields tool to convert to another SpatiaLite table, I get a failed to check metadata error. The generated SpatialLite table cannot be used. You can use the Refactor fields tool to generate a Shapefile and then export the Shapefile as a SpatiaLite table.I tested it in QGIS 3.2.1.

  • Generate a shapefile is not an option, I precisely try to modify the original Spatialite table. I am aware of the fact that geometry metadata are not handled by my SQL query, but I thought they would be kept as the table name remains the same... – sigeal Aug 11 '18 at 15:44
1

Have you tried renaming the columns in QGIS using the Properties dialog box? (Right click on spatialite layer > Properties > Source Fields > enable editing, double click on layer name, rename > save layer edits)

You won't be able to rename the pkey column or anything associated with a trigger, but others should be fine (tested in QGIS 3.2)enter image description here

  • Unfortunately, after having enabled editing, I still can't rename any column... Also, the delete column button is greyed. I tested this on several spatialite databases/layers... – sigeal Aug 13 '18 at 9:57
  • 1
    What version of QGIS are you using? This was not available in previous versions as it is indeed not straightforward to delete and rename columns in sqlite/spatialite; newer versions have incorporated a workaround to make it possible from the GUI. I recommend using 3.x if you aren't already. Try re-loading your spatialite layer using the Add Spatialite Layer option (when you hover over the layer in the layer panel, ideally there should not be an sql= expression when showing the layer source) – she_weeds Aug 14 '18 at 6:48
  • I definitely use QGIS 3.2, on W10, but still can't get rename and delete column options active... – sigeal Aug 14 '18 at 11:30
  • Also tested with QGIS 3.2 on Ubuntu 16.04 : same results with DB Manager, column rename and delete still inactive in layer properties dialog. – sigeal Aug 26 '18 at 7:36
  • You have tried different ways of loading the spatialite layer? – she_weeds Aug 26 '18 at 11:13
1

Thanks to QGIS and SpatiaLite devs, I finally managed to understand what was wrong in my queries : create table with spatial column without using AddGeometryColumn statement. The proper workflow is the following :

-- Create a copy of the table with data
SELECT CloneTable('main', 'compteur_routier', 'compteur_routier_sav', 1);

-- Drop old table (administration tables will be adapted)
SELECT DropGeoTable('compteur_routier');

-- Create and fill new table from cloned table
-- Create new table with modified column names, WITHOUT geom column
CREATE TABLE compteur_routier (
id INTEGER PRIMARY KEY NOT NULL,
id_compt TEXT,
type_compt TEXT,
angle INTEGER,
id_tmp INTEGER
);

-- Add geom column
SELECT AddGeometryColumn('compteur_routier', 'geom', 2154, 'POINT', 'XY', 1);
SELECT CreateSpatialIndex('compteur_routier', 'geom');

-- Insert data from renamed table
INSERT INTO compteur_routier(id, geom, id_compt, type_compt, angle, id_tmp)
SELECT id, geom, id_compteur, type_compteur, angle, id_tmp FROM compteur_routier_sav;
SELECT UpdateLayerStatitics('compteur_routier', 'geom');

-- Drop cloned table (administration tables will be adapted)
SELECT DropGeoTable('compteur_routier_sav'); -- QGIS 3.4.1

Note : DropGeoTable should be replaced by DropTable when SpatiaLite lib will be updated in QGIS. For more detailed information :

QGIS issue

SpatiaLite ticket

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.