Unique constraint for SpatialLite geometry column

Question

I am creating a SpatiaLite database where I want to ensure that the contents of the geometry column for a table are unique. For normal SQL I would add a UNIQUE constraint to that column but, I don't know how to do this for a SpatiaLite geometry. In case it is relevant, the geometry column in question contains points.

I found a question here asking how to remove duplicates from a table: Remove duplicates from Spatialite. The solution provided here is potentially useful but, I would prefer preventing duplicates from entering the table in the first place.

How can I ensure that the geometry column for this table is unique?

Answer 1

Create first the table with unique constraint

create table test (id, geometry UNIQUE);

Register the geometry column

select RecoverGeometryColumn('test','geometry',4326,'POINT');

Test

insert into test values (1,ST_GeomFromText('POINT (0 0)',4326));
insert into test values (2,ST_GeomFromText('POINT (0 0)',4326));
insert into test values (2,ST_GeomFromText('POINT (0 1)',4326));

First and third commands are succesful but the second one fails with a UNIQUE constraint failed error.

EDIT The better way than to rely in plain SQLite and UNIQUE constraint would be to write a trigger that uses the SpatiaLite fucntion ST_Equal http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p12

ST_Equals returns TRUE even if the members of geometry collections are expressed in different order like here:

select ST_Equals(
ST_GeomFromText('MULTIPOINT ((0 0),(1 1))'),
ST_GeomFromText('MULTIPOINT ((1 1),(0 0))'));

As a template for such trigger you can use the trigger that SpatiaLite adds automatically to a new geometry columns for addind a SRID constraint

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

Answer 2

SQLite does not provide full support for ALTER TABLE. Specifically to your question, ADD CONSTRAINT is not supported.

Spatialite does not support the creation of a geometry column when the table is first created (you need to use AddGeometryColumn() as mentioned in the link).

The second link is a bit outdated but it's the best I could find. Assuming it's still accurate, it appears there is no way to implement this functionality as a database constraint because constraints can only be created at table creation, and geometry can only be added later. It seems like you would need to implement some sort of second-layer solution. You can try inserting the geometry data into a text column which has a unique constraint, and then running a python script to update the geometry column based on the text column at regular intervals.