Mercurial > gemma

comparison schema/gemma.sql @ 1963:f7a35ba9f409

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added view for publishing bottlenecks The new view adds reference gauge and reference water level to the bottleneck information.
author Sascha Wilde <wilde@intevation.de>
date Tue, 22 Jan 2019 18:22:57 +0100
parents f538d9a23329
children d966f03ea819
comparison
equal deleted inserted replaced
1962:b275d5e14087 1963:f7a35ba9f409
469 riverbed varchar REFERENCES riverbed_materials, 469 riverbed varchar REFERENCES riverbed_materials,
470 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 470 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
471 PRIMARY KEY (bottleneck_id, riverbed) 471 PRIMARY KEY (bottleneck_id, riverbed)
472 ) 472 )
473 473
474 -- Published view for GeoServer
475 CREATE VIEW bottlenecks_geoserver AS SELECT
476 b.bottleneck_id,
477 b.objnam,
478 b.nobjnm,
479 b.stretch,
480 b.area,
481 b.rb,
482 b.lb,
483 b.responsible_country,
484 b.revisiting_time,
485 b.limiting,
486 b.date_info,
487 b.source_organization,
488 g.location AS gauge_isrs_code,
489 g.objname AS gauge_objname,
490 grwl.reference_water_level,
491 grwl.value AS reference_water_level_value
492 FROM bottlenecks b, gauges g, gauges_reference_water_levels grwl
493 WHERE b.fk_g_fid = g.location AND g.location = grwl.gauge_id
494
474 CREATE TABLE sounding_results ( 495 CREATE TABLE sounding_results (
475 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 496 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
476 bottleneck_id int NOT NULL REFERENCES bottlenecks(id), 497 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
477 date_info date NOT NULL, 498 date_info date NOT NULL,
478 UNIQUE (bottleneck_id, date_info), 499 UNIQUE (bottleneck_id, date_info),