Mercurial > gemma

comparison schema/updates/1300/02.views_to_geoservers.sql @ 4622:b03aa1502736 geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixup rev. 4476ec4db818 Missed to change also database update script.
author Tom Gottfried <tom@intevation.de>
date Fri, 04 Oct 2019 17:26:56 +0200
parents f77a6f9216ae
children 30bb2d819d57
comparison
equal deleted inserted replaced
4621:d2eac69ba86b 4622:b03aa1502736
91 SELECT 91 SELECT
92 s.id, 92 s.id,
93 s.name, 93 s.name,
94 (s.section).lower::varchar as lower, 94 (s.section).lower::varchar as lower,
95 (s.section).upper::varchar as upper, 95 (s.section).upper::varchar as upper,
96 s.area::Geometry(MULTIPOLYGON, 4326), 96 s.area,
97 s.objnam, 97 s.objnam,
98 s.nobjnam, 98 s.nobjnam,
99 s.date_info, 99 s.date_info,
100 s.source_organization, 100 s.source_organization,
101 s.staging_done, 101 s.staging_done,
114 SELECT 114 SELECT
115 s.id, 115 s.id,
116 s.name, 116 s.name,
117 (s.stretch).lower::varchar as lower, 117 (s.stretch).lower::varchar as lower,
118 (s.stretch).upper::varchar as upper, 118 (s.stretch).upper::varchar as upper,
119 s.area::Geometry(MULTIPOLYGON, 4326), 119 s.area,
120 s.objnam, 120 s.objnam,
121 s.nobjnam, 121 s.nobjnam,
122 s.date_info, 122 s.date_info,
123 s.source_organization, 123 s.source_organization,
124 (SELECT string_agg(country, ', ') 124 (SELECT string_agg(country, ', ')
137 GROUP BY s.id 137 GROUP BY s.id
138 $$), 138 $$),
139 ('waterway', 'distance_marks_ashore_geoserver', 4326, $$ 139 ('waterway', 'distance_marks_ashore_geoserver', 4326, $$
140 SELECT id, 140 SELECT id,
141 country, 141 country,
142 geom::Geometry(POINT, 4326), 142 geom,
143 related_enc, 143 related_enc,
144 hectom, 144 hectom,
145 catdis, 145 catdis,
146 position_code 146 position_code
147 FROM waterway.distance_marks 147 FROM waterway.distance_marks
148 $$), 148 $$),
149 ('waterway', 'distance_marks_geoserver', 4326, $$ 149 ('waterway', 'distance_marks_geoserver', 4326, $$
150 SELECT 150 SELECT
151 isrs_asText(location_code) AS location, 151 isrs_asText(location_code) AS location,
152 geom::Geometry(POINT, 4326), 152 geom,
153 related_enc, 153 related_enc,
154 (location_code).hectometre 154 (location_code).hectometre
155 FROM waterway.distance_marks_virtual 155 FROM waterway.distance_marks_virtual
156 $$), 156 $$),
157 ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$ 157 ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$
158 SELECT bottleneck_id, 158 SELECT bottleneck_id,
159 date_info, 159 date_info,
160 height, 160 height,
161 CAST(lines AS geometry(multilinestring, 4326)) AS lines 161 lines
162 FROM waterway.sounding_results_contour_lines cl 162 FROM waterway.sounding_results_contour_lines cl
163 JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id 163 JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id
164 $$), 164 $$),
165 ('waterway', 'bottlenecks_geoserver', 4326, $$ 165 ('waterway', 'bottlenecks_geoserver', 4326, $$
166 SELECT 166 SELECT
206 WHERE b.validity @> current_timestamp 206 WHERE b.validity @> current_timestamp
207 $$), 207 $$),
208 ('waterway', 'bottleneck_overview', 4326, $$ 208 ('waterway', 'bottleneck_overview', 4326, $$
209 SELECT 209 SELECT
210 objnam AS name, 210 objnam AS name,
211 ST_Centroid(area)::Geometry(POINT, 4326) AS point, 211 ST_Centroid(area) AS point,
212 (lower(stretch)).hectometre AS from, 212 (lower(stretch)).hectometre AS from,
213 (upper(stretch)).hectometre AS to, 213 (upper(stretch)).hectometre AS to,
214 sr.current::text, 214 sr.current::text,
215 responsible_country 215 responsible_country
216 FROM waterway.bottlenecks bn LEFT JOIN ( 216 FROM waterway.bottlenecks bn LEFT JOIN (
226 sd.id AS id, 226 sd.id AS id,
227 bn.objnam AS objnam, 227 bn.objnam AS objnam,
228 srm.date_info AS minuend, 228 srm.date_info AS minuend,
229 srs.date_info AS subtrahend, 229 srs.date_info AS subtrahend,
230 sdcl.height AS height, 230 sdcl.height AS height,
231 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines 231 sdcl.lines AS lines
232 FROM caching.sounding_differences sd 232 FROM caching.sounding_differences sd
233 JOIN caching.sounding_differences_contour_lines sdcl 233 JOIN caching.sounding_differences_contour_lines sdcl
234 ON sd.id = sdcl.sounding_differences_id 234 ON sd.id = sdcl.sounding_differences_id
235 JOIN waterway.sounding_results srm 235 JOIN waterway.sounding_results srm
236 ON sd.minuend = srm.id 236 ON sd.minuend = srm.id