--- a/schema/gemma.sql	Fri Mar 22 14:20:08 2019 +0100
+++ b/schema/gemma.sql	Fri Mar 22 14:35:32 2019 +0100
@@ -16,12 +16,15 @@
 BEGIN;
 
 --
--- Infrastructure
+-- Extensions
 --
 CREATE EXTENSION postgis;
 -- needed for multi-column GiST indexes with otherwise unsupported types:
 CREATE EXTENSION btree_gist;
 
+--
+-- Trigger functions
+--
 -- TODO: will there ever be UPDATEs or can we drop that function due to
 -- historicisation?
 CREATE FUNCTION update_date_info() RETURNS trigger
@@ -33,6 +36,42 @@
     END;
 $$;
 
+-- Trigger function to be used as a constraint trigger to enforce uniqueness
+-- of geometries in the column with its name given as an argument to the
+-- trigger function
+CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
+$$
+DECLARE
+    new_geom geometry;
+    has_equal boolean;
+BEGIN
+    EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
+        INTO new_geom
+        USING NEW;
+    EXECUTE format(
+            'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I '
+                'WHERE id <> $2',
+            TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME)
+        INTO has_equal
+        USING new_geom, NEW.id;
+    IF has_equal THEN
+        RAISE EXCEPTION
+            'new row for relation "%" violates constraint trigger "%"',
+                TG_TABLE_NAME, TG_NAME
+            USING
+                DETAIL = format('Failing row contains geometry in %s',
+                    Box2D(new_geom)),
+                ERRCODE = 23505,
+                SCHEMA = TG_TABLE_SCHEMA,
+                TABLE = TG_TABLE_NAME,
+                COLUMN = TG_ARGV[0],
+                CONSTRAINT = TG_NAME;
+    END IF;
+    RETURN NEW;
+END;
+$$
+LANGUAGE plpgsql;
+
 --
 -- GEMMA data
 --
@@ -222,6 +261,10 @@
         catccl smallint REFERENCES catccls,
         dirimp smallint REFERENCES dirimps
     )
+    CREATE CONSTRAINT TRIGGER waterway_area_area_unique
+        AFTER INSERT OR UPDATE OF area ON waterway_area
+        FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area')
+
 
     CREATE TABLE gauges (
         location isrs PRIMARY KEY CHECK(
@@ -308,6 +351,9 @@
         objnam varchar NOT NULL,
         nobjnam varchar
     )
+    CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
+        AFTER INSERT OR UPDATE OF wtwaxs ON waterway_axis
+        FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs')
 
     -- This table allows linkage between 1D ISRS location codes and 2D space
     -- e.g. for cutting bottleneck area out of waterway area based on virtual