Mercurial > gemma

comparison schema/gemma.sql @ 1905:4f58bada50b8 dev-pdf-generation

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merging in default branch
author Bernhard Reiter <bernhard@intevation.de>
date Fri, 18 Jan 2019 17:10:16 +0100
parents d72a1539ef3c
children 32c56e6c089a
comparison
equal deleted inserted replaced
1903:7247ac316cb7 1905:4f58bada50b8
232 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 232 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
233 area geography(POLYGON, 4326) NOT NULL, 233 area geography(POLYGON, 4326) NOT NULL,
234 catccl smallint REFERENCES catccls, 234 catccl smallint REFERENCES catccls,
235 dirimp smallint REFERENCES dirimps 235 dirimp smallint REFERENCES dirimps
236 ) 236 )
237 CREATE UNIQUE INDEX ON waterway_area ((ST_GeoHash(area, 23)))
238 237
239 CREATE TABLE gauges ( 238 CREATE TABLE gauges (
240 location isrs PRIMARY KEY CHECK( 239 location isrs PRIMARY KEY CHECK(
241 (location).orc SIMILAR TO 'G[[:digit:]]{4}' 240 (location).orc SIMILAR TO 'G[[:digit:]]{4}'
242 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), 241 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
243 objname varchar NOT NULL, 242 objname varchar NOT NULL,
244 is_left boolean, -- XXX: Or reference position_codes?
245 geom geography(POINT, 4326) NOT NULL, 243 geom geography(POINT, 4326) NOT NULL,
246 applicability isrsrange, 244 applicability_from_km int8,
247 validity tstzrange,-- XXX: Should ranges be NOT NULL? In DRC, only copy 245 applicability_to_km int8,
246 validity tstzrange,
248 -- pasted text from a more general specification is given 247 -- pasted text from a more general specification is given
249 -- (a gauge is not a berth!) 248 -- (a gauge is not a berth!)
250 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps? 249 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
251 zero_point double precision NOT NULL, 250 zero_point double precision NOT NULL,
252 geodref varchar(4) REFERENCES depth_references, 251 geodref varchar(4) REFERENCES depth_references,
253 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 252 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
254 source_organization varchar NOT NULL 253 source_organization varchar
255 ) 254 )
256 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges 255 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
257 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 256 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
258 257
259 CREATE TABLE gauges_reference_water_levels ( 258 CREATE TABLE gauges_reference_water_levels (
298 wtwaxs geography(LINESTRING, 4326) NOT NULL, 297 wtwaxs geography(LINESTRING, 4326) NOT NULL,
299 -- TODO: Do we need to check data set quality (DRC 2.1.6)? 298 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
300 objnam varchar NOT NULL, 299 objnam varchar NOT NULL,
301 nobjnam varchar 300 nobjnam varchar
302 ) 301 )
303 -- TODO: @tom: Why did you choose this index kind?
304 -- CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23)))
305 302
306 -- This table allows linkage between 1D ISRS location codes and 2D space 303 -- This table allows linkage between 1D ISRS location codes and 2D space
307 -- e.g. for cutting bottleneck area out of waterway area based on virtual 304 -- e.g. for cutting bottleneck area out of waterway area based on virtual
308 -- distance marks along waterway axis (see SUC7). 305 -- distance marks along waterway axis (see SUC7).
309 CREATE TABLE distance_marks_virtual ( 306 CREATE TABLE distance_marks_virtual (
311 geom geography(POINT, 4326) NOT NULL, 308 geom geography(POINT, 4326) NOT NULL,
312 related_enc varchar(12) NOT NULL 309 related_enc varchar(12) NOT NULL
313 ) 310 )
314 311
315 CREATE TABLE distance_marks ( 312 CREATE TABLE distance_marks (
316 country char(2) NOT NULL REFERENCES countries, 313 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
317 hectom int NOT NULL, 314 country char(2) REFERENCES countries,
315 hectom int,
318 geom geography(POINT, 4326) NOT NULL, 316 geom geography(POINT, 4326) NOT NULL,
319 -- include location in primary key, because we have no fairway code: 317 -- include location in primary key, because we have no fairway code:
320 PRIMARY KEY (country, hectom, geom), 318 catdis smallint REFERENCES catdis,
321 catdis smallint NOT NULL REFERENCES catdis, 319 position_code char(2) REFERENCES position_codes,
322 position_code char(2) NOT NULL REFERENCES position_codes, 320 related_enc varchar(12)
323 related_enc varchar(12) NOT NULL
324 ) 321 )
325 322
326 -- A table to help geoserver serve the distance marks as WFS 1.1.0. 323 -- A table to help geoserver serve the distance marks as WFS 1.1.0.
327 -- At least geoserver-2.13.2 does not serve type geography correctly 324 -- At least geoserver-2.13.2 does not serve type geography correctly
328 -- and does not serve the location_code as isrs type 325 -- and does not serve the location_code as isrs type
329 CREATE VIEW waterway.distance_marks_geoserver AS 326 CREATE VIEW distance_marks_geoserver AS
330 SELECT location_code::VARCHAR, 327 SELECT location_code::VARCHAR,
331 geom::Geometry(POINT, 4326), 328 geom::Geometry(POINT, 4326),
332 related_enc, 329 related_enc,
333 (location_code).hectometre 330 (location_code).hectometre
334 FROM waterway.distance_marks_virtual 331 FROM distance_marks_virtual
335 332
336 -- We need to configure primary keys for the views used by 333 -- We need to configure primary keys for the views used by
337 -- geoserver for wfs, otherwise it will generate ids on the fly, 334 -- geoserver for wfs, otherwise it will generate ids on the fly,
338 -- which will change for the same feature... 335 -- which will change for the same feature...
339 -- See 336 -- See
348 pk_sequence VARCHAR(64), 345 pk_sequence VARCHAR(64),
349 unique (table_schema, table_name, pk_column), 346 unique (table_schema, table_name, pk_column),
350 check (pk_policy in ('sequence', 'assigned', 'autogenerated')) 347 check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
351 ) 348 )
352 349
353 CREATE TABLE sections_stretches ( 350 CREATE TABLE stretches (
354 id varchar PRIMARY KEY, 351 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
355 is_section boolean NOT NULL, -- maps 'function' from interface 352 name varchar NOT NULL,
356 stretch isrsrange, 353 stretch isrsrange NOT NULL,
354 -- TODO: make it a (MULTI)-LINESTRING.
355 -- POLYGON is chosen for the convinience
356 -- of re-using ISRSrange_area.
357 geom geography(POLYGON, 4326) NOT NULL,
357 objnam varchar NOT NULL, 358 objnam varchar NOT NULL,
358 nobjnam varchar, 359 nobjnam varchar,
359 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 360 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
360 source_organization varchar NOT NULL, 361 source_organization varchar NOT NULL,
361 staging_done boolean NOT NULL DEFAULT false 362 staging_done boolean NOT NULL DEFAULT false,
362 ) 363 UNIQUE(name, staging_done)
364 )
365
366 CREATE TABLE stretch_countries (
367 stretches_id int NOT NULL REFERENCES stretches(id),
368 country_code char(2) NOT NULL REFERENCES countries(country_code),
369 UNIQUE(stretches_id, country_code)
370 )
371
363 CREATE TRIGGER sections_stretches_date_info 372 CREATE TRIGGER sections_stretches_date_info
364 BEFORE UPDATE ON sections_stretches 373 BEFORE UPDATE ON stretches
365 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 374 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
366 375
367 CREATE TABLE waterway_profiles ( 376 CREATE TABLE waterway_profiles (
368 location isrs NOT NULL, 377 location isrs NOT NULL,
369 validity tstzrange, 378 validity tstzrange,
394 min_depth smallint NOT NULL, 403 min_depth smallint NOT NULL,
395 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 404 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
396 source_organization varchar NOT NULL, 405 source_organization varchar NOT NULL,
397 staging_done boolean NOT NULL DEFAULT false 406 staging_done boolean NOT NULL DEFAULT false
398 ) 407 )
399 CREATE UNIQUE INDEX ON fairway_dimensions ((ST_GeoHash(area, 23)))
400 CREATE TRIGGER fairway_dimensions_date_info 408 CREATE TRIGGER fairway_dimensions_date_info
401 BEFORE UPDATE ON fairway_dimensions 409 BEFORE UPDATE ON fairway_dimensions
402 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 410 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
403 411
404 -- 412 --
466 lines geography(multilinestring, 4326) NOT NULL, 474 lines geography(multilinestring, 4326) NOT NULL,
467 PRIMARY KEY (sounding_result_id, height) 475 PRIMARY KEY (sounding_result_id, height)
468 ) 476 )
469 -- A view to help geoserver serve contour lines. 477 -- A view to help geoserver serve contour lines.
470 -- At least geoserver-2.13.2 does not serve type geography correctly 478 -- At least geoserver-2.13.2 does not serve type geography correctly
471 CREATE VIEW waterway.sounding_results_contour_lines_geoserver AS 479 CREATE VIEW sounding_results_contour_lines_geoserver AS
472 SELECT bottleneck_id, 480 SELECT bottleneck_id,
473 date_info, 481 date_info,
474 height, 482 height,
475 CAST(lines AS geometry(multilinestring, 4326)) AS lines 483 CAST(lines AS geometry(multilinestring, 4326)) AS lines
476 FROM waterway.sounding_results_contour_lines cl 484 FROM sounding_results_contour_lines cl
477 JOIN waterway.sounding_results sr 485 JOIN sounding_results sr
478 ON sr.id = cl.sounding_result_id 486 ON sr.id = cl.sounding_result_id
479 487
480 -- 488 --
481 -- Fairway availability 489 -- Fairway availability
482 -- 490 --
534 value_lifetime timestamp with time zone, 542 value_lifetime timestamp with time zone,
535 CHECK(measure_type = 'minimum guaranteed' 543 CHECK(measure_type = 'minimum guaranteed'
536 OR value_lifetime IS NOT NULL) 544 OR value_lifetime IS NOT NULL)
537 ) 545 )
538 546
539 CREATE VIEW waterway.bottleneck_overview AS 547 CREATE VIEW bottleneck_overview AS
540 SELECT 548 SELECT
541 objnam AS name, 549 objnam AS name,
542 ST_Centroid(area)::Geometry AS point, 550 ST_Centroid(area)::Geometry AS point,
543 (lower(stretch)).hectometre AS from, 551 (lower(stretch)).hectometre AS from,
544 (upper(stretch)).hectometre AS to, 552 (upper(stretch)).hectometre AS to,
545 sr.current::text 553 sr.current::text
546 FROM waterway.bottlenecks bn LEFT JOIN ( 554 FROM bottlenecks bn LEFT JOIN (
547 SELECT bottleneck_id, max(date_info) AS current FROM 555 SELECT bottleneck_id, max(date_info) AS current FROM sounding_results
548 waterway.sounding_results
549 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id 556 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
550 ORDER BY objnam 557 ORDER BY objnam
551 558
552 CREATE TABLE import_configuration ( 559 CREATE TABLE import_configuration (
553 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 560 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,