Mercurial > gemma

comparison schema/geoserver_views.sql @ 3632:943c454d5633 single-beam

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default into single-beam branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 11 Jun 2019 14:46:14 +0200
parents 7e7a2b501595
children 89a39783c20a
comparison
equal deleted inserted replaced
3623:1973fa69b2bb 3632:943c454d5633
1 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS 1 CREATE OR REPLACE VIEW waterway.gauges_base_view AS
2 SELECT 2 SELECT
3 g.location, 3 g.location,
4 isrs_asText(g.location) AS isrs_code, 4 isrs_asText(g.location) AS isrs_code,
5 g.objname, 5 g.objname,
6 g.geom, 6 g.geom,
9 g.validity, 9 g.validity,
10 g.zero_point, 10 g.zero_point,
11 g.geodref, 11 g.geodref,
12 g.date_info, 12 g.date_info,
13 g.source_organization, 13 g.source_organization,
14 g.erased,
14 r.rwls AS reference_water_levels, 15 r.rwls AS reference_water_levels,
15 wl.measure_date AS gm_measuredate, 16 wl.measure_date AS gm_measuredate,
16 wl.water_level AS gm_waterlevel, 17 wl.water_level AS gm_waterlevel,
17 wl_14d.n AS gm_n_14d, 18 wl_14d.n AS gm_n_14d,
18 fca.forecast_accuracy_3d, 19 fca.forecast_accuracy_3d,
48 upper(conf_interval) - water_level) AS acc (acc) 49 upper(conf_interval) - water_level) AS acc (acc)
49 WHERE measure_date 50 WHERE measure_date
50 BETWEEN current_timestamp 51 BETWEEN current_timestamp
51 AND current_timestamp + '3 days'::interval 52 AND current_timestamp + '3 days'::interval
52 GROUP BY location) AS fca 53 GROUP BY location) AS fca
53 USING (location) 54 USING (location);
54 WHERE NOT g.erased; 55
56 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
57 SELECT
58 location,
59 isrs_code,
60 objname,
61 geom,
62 applicability_from_km,
63 applicability_to_km,
64 validity,
65 zero_point,
66 geodref,
67 date_info,
68 source_organization,
69 reference_water_levels,
70 gm_measuredate,
71 gm_waterlevel,
72 gm_n_14d,
73 forecast_accuracy_3d,
74 forecast_accuracy_1d
75 FROM waterway.gauges_base_view
76 WHERE NOT erased;
55 77
56 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS 78 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS
57 SELECT location_code, 79 SELECT location_code,
58 isrs_asText(location_code) AS location, 80 isrs_asText(location_code) AS location,
59 geom::Geometry(POINT, 4326), 81 geom::Geometry(POINT, 4326),
106 g.gm_n_14d, 128 g.gm_n_14d,
107 srl.date_max, 129 srl.date_max,
108 g.forecast_accuracy_3d, 130 g.forecast_accuracy_3d,
109 g.forecast_accuracy_1d 131 g.forecast_accuracy_1d
110 FROM waterway.bottlenecks b 132 FROM waterway.bottlenecks b
111 LEFT JOIN waterway.gauges_geoserver g 133 LEFT JOIN waterway.gauges_base_view g
112 ON b.gauge_location = g.location AND b.gauge_validity = g.validity 134 ON b.gauge_location = g.location AND b.gauge_validity = g.validity
113 LEFT JOIN fairway_availability_latest fal 135 LEFT JOIN fairway_availability_latest fal
114 ON b.id = fal.bottleneck_id 136 ON b.id = fal.bottleneck_id
115 LEFT JOIN sounding_result_latest srl 137 LEFT JOIN sounding_result_latest srl
116 ON b.id = srl.bottleneck_id; 138 ON b.id = srl.bottleneck_id;