Mercurial > gemma

annotate schema/updates/1112/01.cleanup_views.sql @ 4358:a7196b55c064

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