SpatiaLite v4.0.0 RecoverGeometryColumn violates Geometry constraint [geom-type or SRID not allowed]'

Question

When creating a spatial table and recovering the geometry using the RecoverGeometryColumn function if you don't specify the geometry column as lower case you end up getting a 'violates Geometry constraint [geom-type or SRID not allowed]' error.

Am I doing something wrong or I have missed some documentation somewhere?

I had the same problem. My issue was that I was setting that I was expecting points as a type via AddGeometryColumn and then trying to put Polygons. — grasshopper, Jan 08 '15 at 21:09

score 5 · Answer 1 · answered Mar 04 '13 at 14:06

To give an example:

Create a table that has a spatial column

CREATE TABLE "test1" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"Geometry" MULTIPOLYGON)

Recover the geometry

SELECT RecoverGeometryColumn('test1', 'Geometry', 2, 'MULTIPOLYGON',2);

Look at the triggers that were created

select * from sqlite_master where type='trigger' and lower(tbl_name)='test1'

Results:

    CREATE TRIGGER "ggi_test1_Geometry" BEFORE INSERT ON "test1"
    FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'test1.Geometry violates Geometry constraint [geom-type or SRID not allowed]')
    WHERE (SELECT type FROM geometry_columns
    WHERE f_table_name = 'test1' AND f_geometry_column = 'Geometry'
    AND GeometryConstraints(NEW."Geometry", type, srid, 'XY') = 1) IS NULL;
    END

    CREATE TRIGGER "ggu_test1_Geometry" BEFORE UPDATE ON "test1"
    FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'test1.Geometry violates Geometry constraint [geom-type or SRID not allowed]')
    WHERE (SELECT type FROM geometry_columns
    WHERE f_table_name = 'test1' AND f_geometry_column = 'Geometry'
    AND GeometryConstraints(NEW."Geometry", type, srid, 'XY') = 1) IS NULL;
    END

Compare with what is in the geometry_columns table

    select * from geometry_columns

Results:

f_table_name f_geometry_column type coord_dimension srid spaitial_index_enabled

test1 geometry MULTIPOLYGON XY 2 0

Conclusion: Note that the geometry_columns table the f_geometry_column is specified as 'geometry' but the trigger is looking for 'Geometry'. This causes the 'Geometry constraint [geom-type or SRID not allowed]' issue. So the fix is keep you spatial column names lower case.

I've tried this but I'm still getting the same error with "CREATE TABLE Sessions([SessionId] TEXT NOT NULL PRIMARY KEY, [SessionCompletedDate] TEXT NOT NULL, [shape] POINT NULL)", "SELECT RecoverGeometryColumn('Sessions', 'shape', -1, 'POINT', 2)", "SELECT CreateSpatialIndex('Sessions', 'shape')", — Chucky, Oct 26 '15 at 10:48

SpatiaLite v4.0.0 RecoverGeometryColumn violates Geometry constraint [geom-type or SRID not allowed]'

1 Answers1