Mercurial > gemma
changeset 2085:bca8bda0b805
Changed bottlenecks_geoserver view and added config to prevent duplications.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Thu, 31 Jan 2019 17:22:19 +0100 |
parents | ddbac0f22ffb |
children | 6096ec4951f8 |
files | schema/gemma.sql |
diffstat | 1 files changed, 16 insertions(+), 7 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Thu Jan 31 16:32:18 2019 +0100 +++ b/schema/gemma.sql Thu Jan 31 17:22:19 2019 +0100 @@ -480,6 +480,7 @@ -- Published view for GeoServer CREATE VIEW bottlenecks_geoserver AS SELECT + b.id, b.bottleneck_id, b.objnam, b.nobjnm, @@ -494,10 +495,18 @@ b.source_organization, g.location AS gauge_isrs_code, g.objname AS gauge_objname, - grwl.reference_water_level, - grwl.value AS reference_water_level_value - FROM bottlenecks b, gauges g, gauges_reference_water_levels grwl - WHERE b.fk_g_fid = g.location AND g.location = grwl.gauge_id + rwl_ldc.value AS ldc, + rwl_mw.value AS mw, + rwl_hdc.value AS hdc + FROM bottlenecks b, gauges g, + (SELECT gauge_id, value FROM gauges_reference_water_levels + WHERE reference_water_level='LDC') rwl_ldc, + (SELECT gauge_id, value FROM gauges_reference_water_levels + WHERE reference_water_level='MW') rwl_mw, + (SELECT gauge_id, value FROM gauges_reference_water_levels + WHERE reference_water_level='HDC') rwl_hdc + WHERE b.fk_g_fid = g.location AND g.location = rwl_ldc.gauge_id + AND g.location = rwl_mw.gauge_id AND g.location = rwl_hdc.gauge_id CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, @@ -609,9 +618,9 @@ ; -- Configure primary keys for geoserver views -INSERT INTO waterway.gt_pk_metadata VALUES ('waterway', - 'distance_marks_geoserver', - 'location_code'); +INSERT INTO waterway.gt_pk_metadata VALUES + ('waterway', 'distance_marks_geoserver', 'location_code'), + ('waterway', 'bottlenecks_geoserver', 'id'); -- -- Import queue and respective logging