Mercurial > gemma

comparison schema/geoserver_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 0ba3fc89b499
children 5e38667f740c
comparison
equal deleted inserted replaced
4357:e8af2ed8666e 4358:a7196b55c064
53 GROUP BY location) AS fca 53 GROUP BY location) AS fca
54 USING (location); 54 USING (location);
55 55
56 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS 56 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
57 SELECT 57 SELECT
58 location,
59 isrs_code, 58 isrs_code,
60 objname, 59 objname,
61 geom, 60 geom,
62 applicability_from_km, 61 applicability_from_km,
63 applicability_to_km, 62 applicability_to_km,
64 validity,
65 zero_point, 63 zero_point,
66 geodref, 64 geodref,
67 date_info, 65 date_info,
68 source_organization, 66 source_organization,
69 reference_water_levels, 67 reference_water_levels,
74 forecast_accuracy_1d 72 forecast_accuracy_1d
75 FROM waterway.gauges_base_view 73 FROM waterway.gauges_base_view
76 WHERE NOT erased; 74 WHERE NOT erased;
77 75
78 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS 76 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS
79 SELECT location_code, 77 SELECT
80 isrs_asText(location_code) AS location, 78 isrs_asText(location_code) AS location,
81 geom::Geometry(POINT, 4326), 79 geom::Geometry(POINT, 4326),
82 related_enc, 80 related_enc,
83 (location_code).hectometre 81 (location_code).hectometre
84 FROM waterway.distance_marks_virtual; 82 FROM waterway.distance_marks_virtual;
97 SELECT 95 SELECT
98 b.id, 96 b.id,
99 b.bottleneck_id, 97 b.bottleneck_id,
100 b.objnam, 98 b.objnam,
101 b.nobjnm, 99 b.nobjnm,
102 b.stretch,
103 b.area, 100 b.area,
104 b.rb, 101 b.rb,
105 b.lb, 102 b.lb,
106 b.responsible_country, 103 b.responsible_country,
107 b.revisiting_time, 104 b.revisiting_time,
108 b.limiting, 105 b.limiting,
109 b.date_info, 106 b.date_info,
110 b.source_organization, 107 b.source_organization,
111 g.location AS gauge_isrs_code,
112 g.objname AS gauge_objname, 108 g.objname AS gauge_objname,
113 g.reference_water_levels, 109 g.reference_water_levels,
114 fal.date_info AS fa_date_info, 110 fal.date_info AS fa_date_info,
115 fal.critical AS fa_critical, 111 fal.critical AS fa_critical,
116 g.gm_measuredate, 112 g.gm_measuredate,
153 min(g.gm_measuredate) AS gm_measuredate, 149 min(g.gm_measuredate) AS gm_measuredate,
154 min(g.gm_n_14d) AS gm_n_14d, 150 min(g.gm_n_14d) AS gm_n_14d,
155 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, 151 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
156 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d 152 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
157 FROM waterway.stretches s 153 FROM waterway.stretches s
158 LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.stretch 154 LEFT JOIN waterway.gauges_geoserver g
155 ON isrs_fromtext(g.isrs_code) <@ s.stretch
159 GROUP BY s.id; 156 GROUP BY s.id;
160 157
161 CREATE OR REPLACE VIEW waterway.sections_geoserver AS 158 CREATE OR REPLACE VIEW waterway.sections_geoserver AS
162 SELECT 159 SELECT
163 s.id, 160 s.id,
173 min(g.gm_measuredate) AS gm_measuredate, 170 min(g.gm_measuredate) AS gm_measuredate,
174 min(g.gm_n_14d) AS gm_n_14d, 171 min(g.gm_n_14d) AS gm_n_14d,
175 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, 172 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
176 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d 173 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
177 FROM waterway.sections s 174 FROM waterway.sections s
178 LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.section 175 LEFT JOIN waterway.gauges_geoserver g
176 ON isrs_fromtext(g.isrs_code) <@ s.section
179 GROUP BY s.id; 177 GROUP BY s.id;
180 178
181 CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS 179 CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS
182 SELECT bottleneck_id, 180 SELECT bottleneck_id,
183 date_info, 181 date_info,