[GIS] Error: Geometry constraint geom-type or SRID not allowed. How to ensure the correct geom-type

qgisspatialitesqlsqlite

Following the top answer on a previous question about storing geometries in a spatialite database, I am trying to update a spatialite table using:

engine.execute("SELECT AddGeometryColumn('your_table', 'geog', 27700, 'polygon', 'XY');")
# add a Spatialite geometry column called 'geog' to the table, using ESPG 27700, data type POLYGON and 2 dimensions
    # (x, y)
engine.execute("UPDATE your_table SET geog = GeomFromWKB(geometry, 27700);")

The code worked fine when I was using point data. However, when I use vector data I keep getting the error
your_table.geog violates Geometry constraint [geom-type or SRID not allowed]
I know the SRID is ok, so from the error I assume it is the geom-type that is incorrect. I have checked the geom-type in a couple ways: 1. opening the layer in QGIS and checking the porperties; and 2. opening the database in spatialite-gui and 'check geometries'. I have also tried using polygon, multipolygon and multipolygonz as the 4th argument in the AddGeometryColumn selection, but I get the same error. So, what is the best way to check which geometry it should be?

Best Answer

Using @user30184's comment, I initialized the table for multipolygons, using the CastToMultiPolygon function found here. The second part of my above code was changed to:

    # Spatialite geometry objects
    engine.execute("UPDATE your_table SET geom = CastToMultiPolygon(ST_GeomFromWKB(geometry, 27700));")```

Related Solutions

[GIS] Converting multipoint layer to point using PostGIS

SpatialLite has no way of converting multi-geometries to single-parts itself. There are some 'CastTo' functions but they are for special cases (where your multigeometry contains a single geometry - it won't fan-out). I have seen a reference to a function in the SpatialLite GUI but never found it (perhaps you need to compile from the latest source code. I'm just using a pre-compiled binary). So your best bets are:

In PostGIS you can use ST_Dump. This is a useful function for expanding multi-geometries.
In QGIS you could use Vector->Geometry Tools->Multipart to singlepart

[GIS] Setting SRID in Spatialite table

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

Related Question