# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1533828072 -7200
# Node ID d5b707bbd1d5d7b197ab20c4d4ead93c8527e04d
# Parent  fe87457a05d789e08b8be070a30c0f752dc38d42
Do not create B-Tree indexes on potentially large spatial data

Index rows are limited to the 8 kB page size, which does not fit
with real spatial data. Thus enforce uniqueness via an index
on a geohash value of the geometry.

diff -r fe87457a05d7 -r d5b707bbd1d5 schema/gemma.sql
--- a/schema/gemma.sql	Thu Aug 09 15:45:16 2018 +0200
+++ b/schema/gemma.sql	Thu Aug 09 17:21:12 2018 +0200
@@ -197,10 +197,11 @@
     --)
 
     CREATE TABLE waterway_area (
-        area geography(POLYGON, 4326) PRIMARY KEY,
+        area geography(POLYGON, 4326) NOT NULL,
         catccl smallint REFERENCES catccls,
         dirimp smallint REFERENCES dirimps
     )
+    CREATE UNIQUE INDEX ON waterway_area ((ST_GeoHash(area, 23)))
 
     CREATE TABLE gauges (
         location isrs PRIMARY KEY,
@@ -258,11 +259,12 @@
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE waterway_axis (
-        wtwaxs geography(LINESTRING, 4326) PRIMARY KEY,
+        wtwaxs geography(LINESTRING, 4326) NOT NULL,
         -- TODO: Do we need to check data set quality (DRC 2.1.6)?
         objnam varchar NOT NULL,
         nobjnam varchar
     )
+    CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23)))
 
     -- 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
@@ -313,7 +315,7 @@
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE fairway_dimensions (
-        area geography(POLYGON, 4326) PRIMARY KEY,
+        area geography(POLYGON, 4326) NOT NULL,
         level_of_service smallint NOT NULL REFERENCES levels_of_service,
         min_width smallint NOT NULL,
         max_width smallint NOT NULL,
@@ -322,6 +324,7 @@
         source_organization varchar NOT NULL,
         staging_done boolean NOT NULL DEFAULT false
     )
+    CREATE UNIQUE INDEX ON fairway_dimensions ((ST_GeoHash(area, 23)))
     CREATE TRIGGER fairway_dimensions_date_info
         BEFORE UPDATE ON fairway_dimensions
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()