Mercurial > gemma

comparison schema/gemma.sql @ 2451:960550ccca55

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added gauges layer.
author Sascha Wilde <wilde@intevation.de>
date Fri, 01 Mar 2019 16:28:24 +0100
parents 7ca6bdb2d174
children 45d51a49f191
comparison
equal deleted inserted replaced
2450:7677a2850a2d 2451:960550ccca55
249 -- names, too: 249 -- names, too:
250 depth_reference varchar NOT NULL, -- REFERENCES depth_references, 250 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
251 PRIMARY KEY (gauge_id, depth_reference), 251 PRIMARY KEY (gauge_id, depth_reference),
252 value int NOT NULL 252 value int NOT NULL
253 ) 253 )
254
255 CREATE VIEW gauges_geoserver AS
256 SELECT
257 g.location,
258 isrs_asText(g.location) AS isrs_code,
259 g.objname,
260 g.geom,
261 g.applicability_from_km,
262 g.applicability_to_km,
263 g.validity,
264 g.zero_point,
265 g.geodref,
266 g.date_info,
267 g.source_organization,
268 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
269 r.value))
270 AS reference_water_levels
271 FROM gauges g LEFT JOIN LATERAL (
272 SELECT gauge_id, depth_reference, value
273 FROM gauges_reference_water_levels
274 ) r ON r.gauge_id = g.location
275 GROUP BY g.location
254 276
255 CREATE TABLE gauge_measurements ( 277 CREATE TABLE gauge_measurements (
256 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 278 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
257 fk_gauge_id isrs NOT NULL REFERENCES gauges, 279 fk_gauge_id isrs NOT NULL REFERENCES gauges,
258 measure_date timestamp with time zone NOT NULL, 280 measure_date timestamp with time zone NOT NULL,
628 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level; 650 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
629 ; 651 ;
630 652
631 -- Configure primary keys for geoserver views 653 -- Configure primary keys for geoserver views
632 INSERT INTO waterway.gt_pk_metadata VALUES 654 INSERT INTO waterway.gt_pk_metadata VALUES
655 ('waterway', 'gauges_geoserver', 'location'),
633 ('waterway', 'distance_marks_geoserver', 'location_code'), 656 ('waterway', 'distance_marks_geoserver', 'location_code'),
634 ('waterway', 'distance_marks_ashore_geoserver', 'id'), 657 ('waterway', 'distance_marks_ashore_geoserver', 'id'),
635 ('waterway', 'bottlenecks_geoserver', 'id'), 658 ('waterway', 'bottlenecks_geoserver', 'id'),
636 ('waterway', 'stretches_geoserver', 'id'); 659 ('waterway', 'stretches_geoserver', 'id');
637 660