Mercurial > gemma

comparison 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
comparison
equal deleted inserted replaced
4357:e8af2ed8666e 4358:a7196b55c064
1 DROP VIEW waterway.gauges_geoserver CASCADE;
2 CREATE VIEW waterway.gauges_geoserver AS
3 SELECT
4 isrs_code,
5 objname,
6 geom,
7 applicability_from_km,
8 applicability_to_km,
9 zero_point,
10 geodref,
11 date_info,
12 source_organization,
13 reference_water_levels,
14 gm_measuredate,
15 gm_waterlevel,
16 gm_n_14d,
17 forecast_accuracy_3d,
18 forecast_accuracy_1d
19 FROM waterway.gauges_base_view
20 WHERE NOT erased;
21
22 DROP VIEW waterway.distance_marks_geoserver;
23 CREATE VIEW waterway.distance_marks_geoserver AS
24 SELECT
25 isrs_asText(location_code) AS location,
26 geom::Geometry(POINT, 4326),
27 related_enc,
28 (location_code).hectometre
29 FROM waterway.distance_marks_virtual;
30
31 DROP VIEW waterway.bottlenecks_geoserver;
32 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
33 SELECT
34 b.id,
35 b.bottleneck_id,
36 b.objnam,
37 b.nobjnm,
38 b.area,
39 b.rb,
40 b.lb,
41 b.responsible_country,
42 b.revisiting_time,
43 b.limiting,
44 b.date_info,
45 b.source_organization,
46 g.objname AS gauge_objname,
47 g.reference_water_levels,
48 fal.date_info AS fa_date_info,
49 fal.critical AS fa_critical,
50 g.gm_measuredate,
51 g.gm_waterlevel,
52 g.gm_n_14d,
53 srl.date_max,
54 g.forecast_accuracy_3d,
55 g.forecast_accuracy_1d
56 FROM waterway.bottlenecks b
57 LEFT JOIN waterway.gauges_base_view g
58 ON b.gauge_location = g.location AND g.validity @> current_timestamp
59 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
60 bottleneck_id, date_info, critical
61 FROM waterway.fairway_availability
62 ORDER BY bottleneck_id, date_info DESC) AS fal
63 ON b.bottleneck_id = fal.bottleneck_id
64 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
65 bottleneck_id, max(date_info) AS date_max
66 FROM waterway.sounding_results
67 GROUP BY bottleneck_id
68 ORDER BY bottleneck_id DESC) AS srl
69 ON b.bottleneck_id = srl.bottleneck_id
70 WHERE b.validity @> current_timestamp;
71
72 CREATE VIEW waterway.stretches_geoserver AS
73 SELECT
74 s.id,
75 s.name,
76 (s.stretch).lower::varchar as lower,
77 (s.stretch).upper::varchar as upper,
78 s.area::Geometry(MULTIPOLYGON, 4326),
79 s.objnam,
80 s.nobjnam,
81 s.date_info,
82 s.source_organization,
83 (SELECT string_agg(country_code, ', ')
84 FROM waterway.stretch_countries
85 WHERE stretches_id = s.id) AS countries,
86 s.staging_done,
87 min(g.gm_measuredate) AS gm_measuredate,
88 min(g.gm_n_14d) AS gm_n_14d,
89 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
90 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
91 FROM waterway.stretches s
92 LEFT JOIN waterway.gauges_geoserver g
93 ON isrs_fromtext(g.isrs_code) <@ s.stretch
94 GROUP BY s.id;
95
96 CREATE VIEW waterway.sections_geoserver AS
97 SELECT
98 s.id,
99 s.name,
100 (s.section).lower::varchar as lower,
101 (s.section).upper::varchar as upper,
102 s.area::Geometry(MULTIPOLYGON, 4326),
103 s.objnam,
104 s.nobjnam,
105 s.date_info,
106 s.source_organization,
107 s.staging_done,
108 min(g.gm_measuredate) AS gm_measuredate,
109 min(g.gm_n_14d) AS gm_n_14d,
110 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
111 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
112 FROM waterway.sections s
113 LEFT JOIN waterway.gauges_geoserver g
114 ON isrs_fromtext(g.isrs_code) <@ s.section
115 GROUP BY s.id;
116
117 UPDATE waterway.gt_pk_metadata SET pk_column = 'isrs_code'
118 WHERE table_schema = 'waterway'
119 AND table_name = 'gauges_geoserver';
120 UPDATE waterway.gt_pk_metadata SET pk_column = 'location'
121 WHERE table_schema = 'waterway'
122 AND table_name = 'distance_marks_geoserver';
123
124 GRANT SELECT ON
125 waterway.gauges_geoserver,
126 waterway.distance_marks_geoserver,
127 waterway.bottlenecks_geoserver,
128 waterway.stretches_geoserver,
129 waterway.sections_geoserver
130 TO waterway_user;