1

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?

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
  • Awesome! this works for my use case and passes my tests. I don't know how geometries are represented internally by SpatiaLite but, is there a possibility that for more complex geometries, there could be multiple equivalent yet unique (in the eyes if SqLite) internal representation for a single geometry? Perhaps different ordering of elements in a GeometryCollection would cause this issue. – ankh-morpork Jun 3 '18 at 22:29
  • I believe that you have a valid worry about GeometryCollections. The proper way to handle those would be create a trigger that utilizes ST_Equals but the plain SQLite UNIQUE is much easier to write. – user30184 Jun 4 '18 at 6:29
0

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.

  • Up-to-date SpatiaLite commands are found from gaia-gis.it/gaia-sins/spatialite-sql-latest.html. The tutorial was probably not quite correct even when it was written. It is possible to register an existing column as a geometry column directlt with RecoverGeometryColumn() without using AddGeometryColumn first. – user30184 Jun 3 '18 at 20:44

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.