Mercurial > gemma

comparison schema/default_sysconfig.sql @ 4673:443867b548b5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix identifiers in layers generated from SQL views
author Tom Gottfried <tom@intevation.de>
date Tue, 15 Oct 2019 15:59:38 +0200
parents 30bb2d819d57
children ad271887fd8d
comparison
equal deleted inserted replaced
4672:c7dc1a6da93d 4673:443867b548b5
86 ORDER BY location, date_issue DESC) AS fca 86 ORDER BY location, date_issue DESC) AS fca
87 -- Show only forecasts issued with latest measurements or later 87 -- Show only forecasts issued with latest measurements or later
88 ON fca.location = g.location AND fca.date_issue >= wl.date_issue 88 ON fca.location = g.location AND fca.date_issue >= wl.date_issue
89 $$); 89 $$);
90 90
91 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES 91 INSERT INTO sys_admin.published_services (
92 schema, name, srid, key_column, view_def
93 ) VALUES
92 -- Directly accessed tables 94 -- Directly accessed tables
93 ('waterway', 'waterway_axis', NULL, NULL), 95 ('waterway', 'waterway_axis', NULL, NULL, NULL),
94 ('waterway', 'waterway_area', NULL, NULL), 96 ('waterway', 'waterway_area', NULL, NULL, NULL),
95 ('waterway', 'waterway_profiles', NULL, NULL), 97 ('waterway', 'waterway_profiles', NULL, NULL, NULL),
96 ('waterway', 'fairway_dimensions', NULL, NULL), 98 ('waterway', 'fairway_dimensions', NULL, NULL, NULL),
97 -- GeoServer SQL views 99 -- GeoServer SQL views
98 ('waterway', 'gauges_geoserver', 4326, $$ 100 ('waterway', 'gauges_geoserver', 4326, 'isrs_code', $$
99 SELECT 101 SELECT
100 isrs_code, 102 isrs_code,
101 objname, 103 objname,
102 geom, 104 geom,
103 applicability_from_km, 105 applicability_from_km,
115 FROM ( 117 FROM (
116 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ 118 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
117 ) AS gauges_base_view 119 ) AS gauges_base_view
118 WHERE NOT erased 120 WHERE NOT erased
119 $$), 121 $$),
120 ('waterway', 'sections_geoserver', 4326, $$ 122 ('waterway', 'sections_geoserver', 4326, 'id', $$
121 SELECT 123 SELECT
122 s.id, 124 s.id,
123 s.name, 125 s.name,
124 (s.section).lower::varchar as lower, 126 (s.section).lower::varchar as lower,
125 (s.section).upper::varchar as upper, 127 (s.section).upper::varchar as upper,
138 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ 140 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
139 WHERE NOT erased) AS g 141 WHERE NOT erased) AS g
140 ON g.location <@ s.section 142 ON g.location <@ s.section
141 GROUP BY s.id 143 GROUP BY s.id
142 $$), 144 $$),
143 ('waterway', 'stretches_geoserver', 4326, $$ 145 ('waterway', 'stretches_geoserver', 4326, 'id', $$
144 SELECT 146 SELECT
145 s.id, 147 s.id,
146 s.name, 148 s.name,
147 (s.stretch).lower::varchar as lower, 149 (s.stretch).lower::varchar as lower,
148 (s.stretch).upper::varchar as upper, 150 (s.stretch).upper::varchar as upper,
164 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ 166 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
165 WHERE NOT erased) AS g 167 WHERE NOT erased) AS g
166 ON g.location <@ s.stretch 168 ON g.location <@ s.stretch
167 GROUP BY s.id 169 GROUP BY s.id
168 $$), 170 $$),
169 ('waterway', 'distance_marks_ashore_geoserver', 4326, $$ 171 ('waterway', 'distance_marks_ashore_geoserver', 4326, 'id', $$
170 SELECT id, 172 SELECT id,
171 country, 173 country,
172 geom, 174 geom,
173 related_enc, 175 related_enc,
174 hectom, 176 hectom,
175 catdis, 177 catdis,
176 position_code 178 position_code
177 FROM waterway.distance_marks 179 FROM waterway.distance_marks
178 $$), 180 $$),
179 ('waterway', 'distance_marks_geoserver', 4326, $$ 181 ('waterway', 'distance_marks_geoserver', 4326, 'location', $$
180 SELECT 182 SELECT
181 isrs_asText(location_code) AS location, 183 isrs_asText(location_code) AS location,
182 geom, 184 geom,
183 related_enc, 185 related_enc,
184 (location_code).hectometre 186 (location_code).hectometre
185 FROM waterway.distance_marks_virtual 187 FROM waterway.distance_marks_virtual
186 $$), 188 $$),
187 ('waterway', 'sounding_results_areas_geoserver', 4326, $$ 189 ('waterway', 'sounding_results_areas_geoserver', 4326, NULL, $$
188 SELECT bottleneck_id, 190 SELECT bottleneck_id,
189 date_info, 191 date_info,
190 height, 192 height,
191 areas 193 areas
192 FROM waterway.sounding_results_iso_areas ia 194 FROM waterway.sounding_results_iso_areas ia
193 JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id 195 JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id
194 $$), 196 $$),
195 ('waterway', 'bottlenecks_geoserver', 4326, $$ 197 ('waterway', 'bottlenecks_geoserver', 4326, 'id', $$
196 SELECT 198 SELECT
197 b.id, 199 b.id,
198 b.bottleneck_id, 200 b.bottleneck_id,
199 b.objnam, 201 b.objnam,
200 b.nobjnm, 202 b.nobjnm,
233 GROUP BY bottleneck_id 235 GROUP BY bottleneck_id
234 ORDER BY bottleneck_id DESC) AS srl 236 ORDER BY bottleneck_id DESC) AS srl
235 ON b.bottleneck_id = srl.bottleneck_id 237 ON b.bottleneck_id = srl.bottleneck_id
236 WHERE b.validity @> current_timestamp 238 WHERE b.validity @> current_timestamp
237 $$), 239 $$),
238 ('waterway', 'bottleneck_overview', 4326, $$ 240 ('waterway', 'bottleneck_overview', 4326, NULL, $$
239 SELECT 241 SELECT
240 objnam AS name, 242 objnam AS name,
241 ST_Centroid(area) AS point, 243 ST_Centroid(area) AS point,
242 (lower(stretch)).hectometre AS from, 244 (lower(stretch)).hectometre AS from,
243 (upper(stretch)).hectometre AS to, 245 (upper(stretch)).hectometre AS to,
249 GROUP BY bottleneck_id) sr 251 GROUP BY bottleneck_id) sr
250 ON sr.bottleneck_id = bn.bottleneck_id 252 ON sr.bottleneck_id = bn.bottleneck_id
251 WHERE bn.validity @> current_timestamp 253 WHERE bn.validity @> current_timestamp
252 ORDER BY objnam 254 ORDER BY objnam
253 $$), 255 $$),
254 ('waterway', 'sounding_differences', 4326, $$ 256 ('waterway', 'sounding_differences', 4326, NULL, $$
255 SELECT 257 SELECT
256 sd.id AS id, 258 sd.id AS id,
257 bn.objnam AS objnam, 259 bn.objnam AS objnam,
258 srm.date_info AS minuend, 260 srm.date_info AS minuend,
259 srs.date_info AS subtrahend, 261 srs.date_info AS subtrahend,