Mercurial > gemma
changeset 371:d5b707bbd1d5
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()