Mercurial > gemma

comparison schema/default_sysconfig.sql @ 4618:0f2c3cb139cc geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge default into geoserver_sql_views
author Tom Gottfried <tom@intevation.de>
date Fri, 20 Sep 2019 15:35:16 +0200
parents b605e91f08f0
children 4476ec4db818
comparison
equal deleted inserted replaced
4617:ae840f9eb4c8 4618:0f2c3cb139cc
41 g.source_organization, 41 g.source_organization,
42 g.erased, 42 g.erased,
43 r.rwls AS reference_water_levels, 43 r.rwls AS reference_water_levels,
44 wl.measure_date AS gm_measuredate, 44 wl.measure_date AS gm_measuredate,
45 wl.water_level AS gm_waterlevel, 45 wl.water_level AS gm_waterlevel,
46 wl_14d.n AS gm_n_14d, 46 wl.n AS gm_n_14d,
47 fca.forecast_accuracy_3d, 47 fca.forecast_accuracy_3d,
48 fca.forecast_accuracy_1d 48 fca.forecast_accuracy_1d
49 FROM waterway.gauges g 49 FROM waterway.gauges g
50 LEFT JOIN (SELECT location, validity, 50 LEFT JOIN (SELECT location, validity,
51 json_strip_nulls(json_object_agg( 51 json_strip_nulls(json_object_agg(
54 GROUP BY location, validity) AS r 54 GROUP BY location, validity) AS r
55 USING (location, validity) 55 USING (location, validity)
56 LEFT JOIN (SELECT DISTINCT ON (location) 56 LEFT JOIN (SELECT DISTINCT ON (location)
57 location, 57 location,
58 measure_date, 58 measure_date,
59 water_level 59 water_level,
60 FROM waterway.gauge_measurements 60 count(*) OVER (PARTITION BY location) AS n
61 ORDER BY location, measure_date DESC) AS wl
62 USING (location)
63 LEFT JOIN (SELECT location, count(water_level) AS n
64 FROM waterway.gauge_measurements 61 FROM waterway.gauge_measurements
65 -- consider all measurements within 14 days plus a tolerance 62 -- consider all measurements within 14 days plus a tolerance
66 WHERE measure_date 63 WHERE measure_date
67 >= current_timestamp - '14 days 00:15'::interval 64 >= current_timestamp - '14 days 00:15'::interval
68 GROUP BY location) AS wl_14d 65 ORDER BY location, measure_date DESC) AS wl
69 USING (location) 66 USING (location)
70 LEFT JOIN (SELECT location, 67 LEFT JOIN (SELECT DISTINCT ON (location)
71 max(acc) FILTER (WHERE 68 location,
72 measure_date <= current_timestamp + '1 day'::interval) 69 max(acc) FILTER (WHERE measure_date
73 AS forecast_accuracy_1d, 70 <= current_timestamp + '1 day'::interval)
74 max(acc) AS forecast_accuracy_3d 71 OVER loc_date_issue AS forecast_accuracy_1d,
75 FROM waterway.gauge_predictions, 72 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
76 GREATEST(water_level - lower(conf_interval), 73 FROM (SELECT location, date_issue, measure_date,
77 upper(conf_interval) - water_level) AS acc (acc) 74 GREATEST(water_level - lower(conf_interval),
78 WHERE measure_date 75 upper(conf_interval) - water_level) AS acc
79 BETWEEN current_timestamp 76 FROM waterway.gauge_predictions
80 AND current_timestamp + '3 days'::interval 77 -- consider predictions made within last 14 days ...
81 GROUP BY location) AS fca 78 WHERE date_issue
79 >= current_timestamp - '14 days 00:15'::interval
80 -- ... for the next three days from now
81 AND measure_date BETWEEN current_timestamp
82 AND current_timestamp + '3 days'::interval) AS acc
83 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
84 ORDER BY location, date_issue DESC) AS fca
82 USING (location) 85 USING (location)
83 $$); 86 $$);
84 87
85 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES 88 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
86 -- Directly accessed tables 89 -- Directly accessed tables
143 s.area::Geometry(MULTIPOLYGON, 4326), 146 s.area::Geometry(MULTIPOLYGON, 4326),
144 s.objnam, 147 s.objnam,
145 s.nobjnam, 148 s.nobjnam,
146 s.date_info, 149 s.date_info,
147 s.source_organization, 150 s.source_organization,
148 (SELECT string_agg(country_code, ', ') 151 (SELECT string_agg(country, ', ')
149 FROM waterway.stretch_countries 152 FROM users.stretch_countries
150 WHERE stretches_id = s.id) AS countries, 153 WHERE stretch_id = s.id) AS countries,
151 s.staging_done, 154 s.staging_done,
152 min(g.gm_measuredate) AS gm_measuredate, 155 min(g.gm_measuredate) AS gm_measuredate,
153 min(g.gm_n_14d) AS gm_n_14d, 156 min(g.gm_n_14d) AS gm_n_14d,
154 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, 157 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
155 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d 158 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
156 FROM waterway.stretches s 159 FROM users.stretches s
157 LEFT JOIN ( 160 LEFT JOIN (
158 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ 161 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
159 WHERE NOT erased) AS g 162 WHERE NOT erased) AS g
160 ON g.location <@ s.stretch 163 ON g.location <@ s.stretch
161 GROUP BY s.id 164 GROUP BY s.id