4

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]

  • So you have accidentally stored some data with wrong SRID or without SRID and there is no need to change the coordianates, just tell that they are in another coordinate system? – user30184 Jan 14 '15 at 16:05
  • Yes, that's right. I have some SpatiaLite tables that were created without SRIDs. The coordinates are correctly stored in UTM 83 10, but I have to set the SRID manually every time that I load the table into QGIS. – user44994 Jan 14 '15 at 16:19
8

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);

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.