Mercurial > gemma

comparison schema/gemma.sql @ 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 84597b9da68e
comparison
equal deleted inserted replaced
370:fe87457a05d7 371:d5b707bbd1d5
195 -- rwdr double precision NOT NULL, 195 -- rwdr double precision NOT NULL,
196 -- EXCLUDE USING GIST (stretch WITH &&) 196 -- EXCLUDE USING GIST (stretch WITH &&)
197 --) 197 --)
198 198
199 CREATE TABLE waterway_area ( 199 CREATE TABLE waterway_area (
200 area geography(POLYGON, 4326) PRIMARY KEY, 200 area geography(POLYGON, 4326) NOT NULL,
201 catccl smallint REFERENCES catccls, 201 catccl smallint REFERENCES catccls,
202 dirimp smallint REFERENCES dirimps 202 dirimp smallint REFERENCES dirimps
203 ) 203 )
204 CREATE UNIQUE INDEX ON waterway_area ((ST_GeoHash(area, 23)))
204 205
205 CREATE TABLE gauges ( 206 CREATE TABLE gauges (
206 location isrs PRIMARY KEY, 207 location isrs PRIMARY KEY,
207 function_code varchar(10) NOT NULL, -- XXX: What is this really for? 208 function_code varchar(10) NOT NULL, -- XXX: What is this really for?
208 objname varchar NOT NULL, 209 objname varchar NOT NULL,
256 CREATE TRIGGER gauge_measurements_date_info 257 CREATE TRIGGER gauge_measurements_date_info
257 BEFORE UPDATE ON gauge_measurements 258 BEFORE UPDATE ON gauge_measurements
258 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 259 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
259 260
260 CREATE TABLE waterway_axis ( 261 CREATE TABLE waterway_axis (
261 wtwaxs geography(LINESTRING, 4326) PRIMARY KEY, 262 wtwaxs geography(LINESTRING, 4326) NOT NULL,
262 -- TODO: Do we need to check data set quality (DRC 2.1.6)? 263 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
263 objnam varchar NOT NULL, 264 objnam varchar NOT NULL,
264 nobjnam varchar 265 nobjnam varchar
265 ) 266 )
267 CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23)))
266 268
267 -- This table allows linkage between 1D ISRS location codes and 2D space 269 -- This table allows linkage between 1D ISRS location codes and 2D space
268 -- e.g. for cutting bottleneck area out of waterway area based on virtual 270 -- e.g. for cutting bottleneck area out of waterway area based on virtual
269 -- distance marks along waterway axis (see SUC7). 271 -- distance marks along waterway axis (see SUC7).
270 CREATE TABLE distance_marks ( 272 CREATE TABLE distance_marks (
311 CREATE TRIGGER waterway_profiles_date_info 313 CREATE TRIGGER waterway_profiles_date_info
312 BEFORE UPDATE ON waterway_profiles 314 BEFORE UPDATE ON waterway_profiles
313 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 315 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
314 316
315 CREATE TABLE fairway_dimensions ( 317 CREATE TABLE fairway_dimensions (
316 area geography(POLYGON, 4326) PRIMARY KEY, 318 area geography(POLYGON, 4326) NOT NULL,
317 level_of_service smallint NOT NULL REFERENCES levels_of_service, 319 level_of_service smallint NOT NULL REFERENCES levels_of_service,
318 min_width smallint NOT NULL, 320 min_width smallint NOT NULL,
319 max_width smallint NOT NULL, 321 max_width smallint NOT NULL,
320 min_depth smallint NOT NULL, 322 min_depth smallint NOT NULL,
321 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 323 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
322 source_organization varchar NOT NULL, 324 source_organization varchar NOT NULL,
323 staging_done boolean NOT NULL DEFAULT false 325 staging_done boolean NOT NULL DEFAULT false
324 ) 326 )
327 CREATE UNIQUE INDEX ON fairway_dimensions ((ST_GeoHash(area, 23)))
325 CREATE TRIGGER fairway_dimensions_date_info 328 CREATE TRIGGER fairway_dimensions_date_info
326 BEFORE UPDATE ON fairway_dimensions 329 BEFORE UPDATE ON fairway_dimensions
327 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 330 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
328 331
329 -- 332 --