Setting SRID in Spatialite table?

Question

How do you update the SRID in a SpatiaLite table?

I am using the QspatiaLite plugin or the SpatiaLite_Gui program to send this SQL to the table:

UPDATE myTable
SET geom = SETSRID(geom,26910)

...but I get:

violates Geometry constraint [geom-type or SRID not allowed]

Answer 1

Every spatial table in a SpatiaLite database has a foreign key constraint so that the SRID has to match the value stored in a metadata table named geometry_columns. You can see the constraint with:

SELECT sql FROM sqlite_master
WHERE type = 'trigger' 
    AND name = 'ggu_mytable_geom';

You can see the actual SRID enforced for the table with:

SELECT * FROM geometry_columns
WHERE f_table_name = 'mytable';

Note that although you have used myTable in the example above, SpatiaLite enforces lower-case table and geometry column names in the metadata tables. If these queries don't work, you may have to use the Lower() function to get the strings to match.

You can also see this in the SpatiaLite-GUI interface by going to the table's geometry column in the database tree in the left pane, right-clicking the column name and choosing Show Spatial Metadata.

Updating the SRID is easy, but you must change the expected SRID in the geometry_columns table first. Do this with:

UPDATE geometry_columns
SET srid = 26910
WHERE f_table_name = 'mytable';

Then you can run the operation you were trying to:

UPDATE mytable
SET geom = SetSRID(geom, 22910);