Mercurial > gemma

comparison schema/geoserver_views.sql @ 3642:6bb8def12f20 configuration

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged default into configuration branch
author Markus Kottlaender <markus@intevation.de>
date Wed, 12 Jun 2019 16:12:11 +0200
parents 89a39783c20a
children 02951a62e8c6
comparison
equal deleted inserted replaced
3641:32d7bb1afdc9 3642:6bb8def12f20
137 LEFT JOIN sounding_result_latest srl 137 LEFT JOIN sounding_result_latest srl
138 ON b.id = srl.bottleneck_id; 138 ON b.id = srl.bottleneck_id;
139 139
140 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS 140 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
141 SELECT 141 SELECT
142 id, 142 s.id,
143 name, 143 s.name,
144 (stretch).lower::varchar as lower, 144 (s.stretch).lower::varchar as lower,
145 (stretch).upper::varchar as upper, 145 (s.stretch).upper::varchar as upper,
146 area::Geometry(MULTIPOLYGON, 4326), 146 s.area::Geometry(MULTIPOLYGON, 4326),
147 objnam, 147 s.objnam,
148 nobjnam, 148 s.nobjnam,
149 date_info, 149 s.date_info,
150 source_organization, 150 s.source_organization,
151 (SELECT string_agg(country_code, ', ') 151 (SELECT string_agg(country_code, ', ')
152 FROM waterway.stretch_countries 152 FROM waterway.stretch_countries
153 WHERE stretches_id = id) AS countries, 153 WHERE stretches_id = s.id) AS countries,
154 staging_done 154 s.staging_done,
155 FROM waterway.stretches; 155 min(g.gm_measuredate) AS gm_measuredate,
156 min(g.gm_n_14d) AS gm_n_14d,
157 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
158 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
159 FROM waterway.stretches s
160 LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.stretch
161 GROUP BY s.id;
156 162
157 CREATE OR REPLACE VIEW waterway.sections_geoserver AS 163 CREATE OR REPLACE VIEW waterway.sections_geoserver AS
158 SELECT 164 SELECT
159 id, 165 s.id,
160 name, 166 s.name,
161 (section).lower::varchar as lower, 167 (s.section).lower::varchar as lower,
162 (section).upper::varchar as upper, 168 (s.section).upper::varchar as upper,
163 area::Geometry(MULTIPOLYGON, 4326), 169 s.area::Geometry(MULTIPOLYGON, 4326),
164 objnam, 170 s.objnam,
165 nobjnam, 171 s.nobjnam,
166 date_info, 172 s.date_info,
167 source_organization, 173 s.source_organization,
168 staging_done 174 s.staging_done,
169 FROM waterway.sections; 175 min(g.gm_measuredate) AS gm_measuredate,
176 min(g.gm_n_14d) AS gm_n_14d,
177 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
178 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
179 FROM waterway.sections s
180 LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.section
181 GROUP BY s.id;
170 182
171 CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS 183 CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS
172 SELECT bottleneck_id, 184 SELECT bottleneck_id,
173 date_info, 185 date_info,
174 height, 186 height,