Mercurial > gemma

annotate schema/updates/1202/01.fix_data_availability.sql @ 5095:e21cbb9768a2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Prevent duplicate fairway areas In principal, there can be only one or no fairway area at each point on the map. Since polygons from real data will often be topologically inexact, just disallow equal geometries. This will also help to avoid importing duplicates with concurrent imports, once the history of fairway dimensions will be preserved.
author Tom Gottfried <tom@intevation.de>
date Wed, 25 Mar 2020 18:10:02 +0100
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