Mercurial > gemma
comparison schema/gemma.sql @ 1963:f7a35ba9f409
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), |