Mercurial > gemma

annotate schema/updates/1202/01.fix_data_availability.sql @ 5361:ce1fe22bda5a extented-report

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Backed out changeset f845c3b7b68e
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 22 Jun 2021 17:12:17 +0200
parents 9b9f60e26b39
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4453
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
1 CREATE OR REPLACE VIEW waterway.gauges_base_view AS
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
2 SELECT
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
3 g.location,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
4 isrs_asText(g.location) AS isrs_code,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
5 g.objname,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
6 g.geom,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
7 g.applicability_from_km,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
8 g.applicability_to_km,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
9 g.validity,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
10 g.zero_point,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
11 g.geodref,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
12 g.date_info,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
13 g.source_organization,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
14 g.erased,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
15 r.rwls AS reference_water_levels,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
16 wl.measure_date AS gm_measuredate,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
17 wl.water_level AS gm_waterlevel,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
18 wl.n AS gm_n_14d,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
19 fca.forecast_accuracy_3d,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
20 fca.forecast_accuracy_1d
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
21 FROM waterway.gauges g
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
22 LEFT JOIN (SELECT location, validity,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
23 json_strip_nulls(json_object_agg(
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
24 coalesce(depth_reference, 'empty'), value)) AS rwls
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
25 FROM waterway.gauges_reference_water_levels
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
26 GROUP BY location, validity) AS r
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
27 USING (location, validity)
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
28 LEFT JOIN (SELECT DISTINCT ON (location)
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
29 location,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
30 date_issue,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
31 measure_date,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
32 water_level,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
33 count(*) OVER (PARTITION BY location) AS n
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
34 FROM waterway.gauge_measurements
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
35 -- consider all measurements within 14 days plus a tolerance
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
36 WHERE measure_date
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
37 >= current_timestamp - '14 days 00:15'::interval
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
38 ORDER BY location, measure_date DESC) AS wl
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
39 USING (location)
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
40 LEFT JOIN (SELECT DISTINCT ON (location)
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
41 location,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
42 date_issue,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
43 max(acc) FILTER (WHERE measure_date
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
44 <= current_timestamp + '1 day'::interval)
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
45 OVER loc_date_issue AS forecast_accuracy_1d,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
46 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
47 FROM (SELECT location, date_issue, measure_date,
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
48 GREATEST(water_level - lower(conf_interval),
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
49 upper(conf_interval) - water_level) AS acc
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
50 FROM waterway.gauge_predictions
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
51 -- consider predictions made within last 14 days ...
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
52 WHERE date_issue
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
53 >= current_timestamp - '14 days 00:15'::interval
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
54 -- ... for the next three days from now
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
55 AND measure_date BETWEEN current_timestamp
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
56 AND current_timestamp + '3 days'::interval) AS acc
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
57 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
58 ORDER BY location, date_issue DESC) AS fca
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
59 -- Show only forecasts issued with latest measurements or later
9b9f60e26b39 Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents: 4449
diff changeset
60 ON fca.location = g.location AND fca.date_issue >= wl.date_issue;
4449
ce884af9f42f Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61
ce884af9f42f Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 DROP INDEX waterway.gauge_measurements_location_measure_date_desc