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?

ankh-morpork
  • 113
  • 7

2 Answers2

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
user30184
  • 65,331
  • 4
  • 65
  • 118
  • 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 03 '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 04 '18 at 06: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.

DanielG
  • 53
  • 4
  • Up-to-date SpatiaLite commands are found from http://www.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 03 '18 at 20:44