Mercurial > gemma

changeset 371:d5b707bbd1d5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.
author Tom Gottfried <tom@intevation.de>
date Thu, 09 Aug 2018 17:21:12 +0200
parents fe87457a05d7
children 15369b41be74
files schema/gemma.sql
diffstat 1 files changed, 6 insertions(+), 3 deletions(-) [+]
line wrap: on
line diff
--- 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()