Mercurial > gemma

annotate schema/updates/1112/01.cleanup_views.sql @ 4594:db4e7fecad0d iso-areas

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated SLD templates for sounding results and differences.
author Raimund Renkert <raimund@renkert.org>
date Wed, 09 Oct 2019 13:29:43 +0200
parents 81dc260b38aa
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4366
81dc260b38aa Fixup database schema update
Tom Gottfried <tom@intevation.de>
parents: 4358
diff changeset
1 -- Avoid orphaned entries due to changed OIDs
81dc260b38aa Fixup database schema update
Tom Gottfried <tom@intevation.de>
parents: 4358
diff changeset
2 ALTER TABLE sys_admin.published_services ADD _name varchar;
81dc260b38aa Fixup database schema update
Tom Gottfried <tom@intevation.de>
parents: 4358
diff changeset
3 UPDATE sys_admin.published_services SET _name = name;
81dc260b38aa Fixup database schema update
Tom Gottfried <tom@intevation.de>
parents: 4358
diff changeset
4
4358
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 DROP VIEW waterway.gauges_geoserver CASCADE;
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 CREATE VIEW waterway.gauges_geoserver AS
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 SELECT
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 isrs_code,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 objname,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 geom,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 applicability_from_km,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 applicability_to_km,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 zero_point,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 geodref,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 date_info,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 source_organization,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 reference_water_levels,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 gm_measuredate,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 gm_waterlevel,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 gm_n_14d,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 forecast_accuracy_3d,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 forecast_accuracy_1d
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 FROM waterway.gauges_base_view
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 WHERE NOT erased;
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 DROP VIEW waterway.distance_marks_geoserver;
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 CREATE VIEW waterway.distance_marks_geoserver AS
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 SELECT
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 isrs_asText(location_code) AS location,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 geom::Geometry(POINT, 4326),
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 related_enc,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 (location_code).hectometre
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 FROM waterway.distance_marks_virtual;
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 DROP VIEW waterway.bottlenecks_geoserver;
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 SELECT
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 b.id,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 b.bottleneck_id,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 b.objnam,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 b.nobjnm,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 b.area,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 b.rb,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 b.lb,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 b.responsible_country,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 b.revisiting_time,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 b.limiting,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 b.date_info,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 b.source_organization,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 g.objname AS gauge_objname,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 g.reference_water_levels,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 fal.date_info AS fa_date_info,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 fal.critical AS fa_critical,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 g.gm_measuredate,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 g.gm_waterlevel,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 g.gm_n_14d,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 srl.date_max,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 g.forecast_accuracy_3d,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 g.forecast_accuracy_1d
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 FROM waterway.bottlenecks b
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 LEFT JOIN waterway.gauges_base_view g
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 ON b.gauge_location = g.location AND g.validity @> current_timestamp
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 bottleneck_id, date_info, critical
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 FROM waterway.fairway_availability
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 ORDER BY bottleneck_id, date_info DESC) AS fal
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 ON b.bottleneck_id = fal.bottleneck_id
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 bottleneck_id, max(date_info) AS date_max
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 FROM waterway.sounding_results
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 GROUP BY bottleneck_id
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 ORDER BY bottleneck_id DESC) AS srl
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 ON b.bottleneck_id = srl.bottleneck_id
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 WHERE b.validity @> current_timestamp;
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 CREATE VIEW waterway.stretches_geoserver AS
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 SELECT
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 s.id,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 s.name,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 (s.stretch).lower::varchar as lower,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 (s.stretch).upper::varchar as upper,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 s.area::Geometry(MULTIPOLYGON, 4326),
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 s.objnam,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 s.nobjnam,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 s.date_info,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 s.source_organization,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 (SELECT string_agg(country_code, ', ')
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 FROM waterway.stretch_countries
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 WHERE stretches_id = s.id) AS countries,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 s.staging_done,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 min(g.gm_measuredate) AS gm_measuredate,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 min(g.gm_n_14d) AS gm_n_14d,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 FROM waterway.stretches s
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 LEFT JOIN waterway.gauges_geoserver g
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 ON isrs_fromtext(g.isrs_code) <@ s.stretch
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 GROUP BY s.id;
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 CREATE VIEW waterway.sections_geoserver AS
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 SELECT
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 s.id,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 s.name,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 (s.section).lower::varchar as lower,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 (s.section).upper::varchar as upper,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106 s.area::Geometry(MULTIPOLYGON, 4326),
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 s.objnam,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 s.nobjnam,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 s.date_info,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 s.source_organization,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 s.staging_done,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 min(g.gm_measuredate) AS gm_measuredate,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 min(g.gm_n_14d) AS gm_n_14d,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116 FROM waterway.sections s
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
117 LEFT JOIN waterway.gauges_geoserver g
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
118 ON isrs_fromtext(g.isrs_code) <@ s.section
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119 GROUP BY s.id;
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120
4366
81dc260b38aa Fixup database schema update
Tom Gottfried <tom@intevation.de>
parents: 4358
diff changeset
121 -- Avoid orphaned entries due to changed OIDs
81dc260b38aa Fixup database schema update
Tom Gottfried <tom@intevation.de>
parents: 4358
diff changeset
122 UPDATE sys_admin.published_services SET name = _name;
81dc260b38aa Fixup database schema update
Tom Gottfried <tom@intevation.de>
parents: 4358
diff changeset
123 ALTER TABLE sys_admin.published_services DROP _name;
81dc260b38aa Fixup database schema update
Tom Gottfried <tom@intevation.de>
parents: 4358
diff changeset
124
4358
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
125 UPDATE waterway.gt_pk_metadata SET pk_column = 'isrs_code'
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
126 WHERE table_schema = 'waterway'
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
127 AND table_name = 'gauges_geoserver';
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
128 UPDATE waterway.gt_pk_metadata SET pk_column = 'location'
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 WHERE table_schema = 'waterway'
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
130 AND table_name = 'distance_marks_geoserver';
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
131
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
132 GRANT SELECT ON
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
133 waterway.gauges_geoserver,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
134 waterway.distance_marks_geoserver,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
135 waterway.bottlenecks_geoserver,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
136 waterway.stretches_geoserver,
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
137 waterway.sections_geoserver
a7196b55c064 Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
138 TO waterway_user;