Mercurial > gemma

comparison schema/updates/1113/02.views_to_geoservers.sql @ 4616:b605e91f08f0 geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use GeoServer's SQL view feature instead of in-database VIEWs VIEWs are executed with the rigths of their owner, but we need to access the data with the rights of the current_user in order to have row level security policies applied correctly. Moving the former VIEW definitions into GeoServer SQL views achieves this, as the latter are executed as subqueries in usual statements. Note that a fresh GeoServer setup is required to make this work, which can be achieved e.g. by starting gemma with the geoserver-clean flag. Otherwise, GeoServer will keep trying to access the dropped VIEWs.
author Tom Gottfried <tom@intevation.de>
date Tue, 10 Sep 2019 19:56:43 +0200
parents
children
comparison
equal deleted inserted replaced
4615:32d3e0cecf4f 4616:b605e91f08f0
1 -- Copied from ../../default_sysconfig.sql
2 -- plus conflict resolution to achieve updates
3
4 CREATE TEMP TABLE base_views (name, def) AS VALUES (
5 'gauges_base_view', $$
6 SELECT
7 g.location,
8 isrs_asText(g.location) AS isrs_code,
9 g.objname,
10 g.geom,
11 g.applicability_from_km,
12 g.applicability_to_km,
13 g.validity,
14 g.zero_point,
15 g.geodref,
16 g.date_info,
17 g.source_organization,
18 g.erased,
19 r.rwls AS reference_water_levels,
20 wl.measure_date AS gm_measuredate,
21 wl.water_level AS gm_waterlevel,
22 wl_14d.n AS gm_n_14d,
23 fca.forecast_accuracy_3d,
24 fca.forecast_accuracy_1d
25 FROM waterway.gauges g
26 LEFT JOIN (SELECT location, validity,
27 json_strip_nulls(json_object_agg(
28 coalesce(depth_reference, 'empty'), value)) AS rwls
29 FROM waterway.gauges_reference_water_levels
30 GROUP BY location, validity) AS r
31 USING (location, validity)
32 LEFT JOIN (SELECT DISTINCT ON (location)
33 location,
34 measure_date,
35 water_level
36 FROM waterway.gauge_measurements
37 ORDER BY location, measure_date DESC) AS wl
38 USING (location)
39 LEFT JOIN (SELECT location, count(water_level) AS n
40 FROM waterway.gauge_measurements
41 -- consider all measurements within 14 days plus a tolerance
42 WHERE measure_date
43 >= current_timestamp - '14 days 00:15'::interval
44 GROUP BY location) AS wl_14d
45 USING (location)
46 LEFT JOIN (SELECT location,
47 max(acc) FILTER (WHERE
48 measure_date <= current_timestamp + '1 day'::interval)
49 AS forecast_accuracy_1d,
50 max(acc) AS forecast_accuracy_3d
51 FROM waterway.gauge_predictions,
52 GREATEST(water_level - lower(conf_interval),
53 upper(conf_interval) - water_level) AS acc (acc)
54 WHERE measure_date
55 BETWEEN current_timestamp
56 AND current_timestamp + '3 days'::interval
57 GROUP BY location) AS fca
58 USING (location)
59 $$);
60
61 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
62 ('waterway', 'gauges_geoserver', 4326, $$
63 SELECT
64 isrs_code,
65 objname,
66 geom,
67 applicability_from_km,
68 applicability_to_km,
69 zero_point,
70 geodref,
71 date_info,
72 source_organization,
73 reference_water_levels,
74 gm_measuredate,
75 gm_waterlevel,
76 gm_n_14d,
77 forecast_accuracy_3d,
78 forecast_accuracy_1d
79 FROM (
80 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
81 ) AS gauges_base_view
82 WHERE NOT erased
83 $$),
84 ('waterway', 'sections_geoserver', 4326, $$
85 SELECT
86 s.id,
87 s.name,
88 (s.section).lower::varchar as lower,
89 (s.section).upper::varchar as upper,
90 s.area::Geometry(MULTIPOLYGON, 4326),
91 s.objnam,
92 s.nobjnam,
93 s.date_info,
94 s.source_organization,
95 s.staging_done,
96 min(g.gm_measuredate) AS gm_measuredate,
97 min(g.gm_n_14d) AS gm_n_14d,
98 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
99 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
100 FROM waterway.sections s
101 LEFT JOIN (
102 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
103 WHERE NOT erased) AS g
104 ON g.location <@ s.section
105 GROUP BY s.id
106 $$),
107 ('waterway', 'stretches_geoserver', 4326, $$
108 SELECT
109 s.id,
110 s.name,
111 (s.stretch).lower::varchar as lower,
112 (s.stretch).upper::varchar as upper,
113 s.area::Geometry(MULTIPOLYGON, 4326),
114 s.objnam,
115 s.nobjnam,
116 s.date_info,
117 s.source_organization,
118 (SELECT string_agg(country_code, ', ')
119 FROM waterway.stretch_countries
120 WHERE stretches_id = s.id) AS countries,
121 s.staging_done,
122 min(g.gm_measuredate) AS gm_measuredate,
123 min(g.gm_n_14d) AS gm_n_14d,
124 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
125 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
126 FROM waterway.stretches s
127 LEFT JOIN (
128 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
129 WHERE NOT erased) AS g
130 ON g.location <@ s.stretch
131 GROUP BY s.id
132 $$),
133 ('waterway', 'distance_marks_ashore_geoserver', 4326, $$
134 SELECT id,
135 country,
136 geom::Geometry(POINT, 4326),
137 related_enc,
138 hectom,
139 catdis,
140 position_code
141 FROM waterway.distance_marks
142 $$),
143 ('waterway', 'distance_marks_geoserver', 4326, $$
144 SELECT
145 isrs_asText(location_code) AS location,
146 geom::Geometry(POINT, 4326),
147 related_enc,
148 (location_code).hectometre
149 FROM waterway.distance_marks_virtual
150 $$),
151 ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$
152 SELECT bottleneck_id,
153 date_info,
154 height,
155 CAST(lines AS geometry(multilinestring, 4326)) AS lines
156 FROM waterway.sounding_results_contour_lines cl
157 JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id
158 $$),
159 ('waterway', 'bottlenecks_geoserver', 4326, $$
160 SELECT
161 b.id,
162 b.bottleneck_id,
163 b.objnam,
164 b.nobjnm,
165 b.area,
166 b.rb,
167 b.lb,
168 b.responsible_country,
169 b.revisiting_time,
170 b.limiting,
171 b.date_info,
172 b.source_organization,
173 g.objname AS gauge_objname,
174 g.reference_water_levels,
175 fal.date_info AS fa_date_info,
176 fal.critical AS fa_critical,
177 g.gm_measuredate,
178 g.gm_waterlevel,
179 g.gm_n_14d,
180 srl.date_max,
181 g.forecast_accuracy_3d,
182 g.forecast_accuracy_1d
183 FROM waterway.bottlenecks b
184 LEFT JOIN (
185 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
186 ) AS g
187 ON b.gauge_location = g.location
188 AND g.validity @> current_timestamp
189 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
190 bottleneck_id, date_info, critical
191 FROM waterway.fairway_availability
192 ORDER BY bottleneck_id, date_info DESC) AS fal
193 ON b.bottleneck_id = fal.bottleneck_id
194 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
195 bottleneck_id, max(date_info) AS date_max
196 FROM waterway.sounding_results
197 GROUP BY bottleneck_id
198 ORDER BY bottleneck_id DESC) AS srl
199 ON b.bottleneck_id = srl.bottleneck_id
200 WHERE b.validity @> current_timestamp
201 $$),
202 ('waterway', 'bottleneck_overview', 4326, $$
203 SELECT
204 objnam AS name,
205 ST_Centroid(area)::Geometry(POINT, 4326) AS point,
206 (lower(stretch)).hectometre AS from,
207 (upper(stretch)).hectometre AS to,
208 sr.current::text,
209 responsible_country
210 FROM waterway.bottlenecks bn LEFT JOIN (
211 SELECT bottleneck_id, max(date_info) AS current
212 FROM waterway.sounding_results
213 GROUP BY bottleneck_id) sr
214 ON sr.bottleneck_id = bn.bottleneck_id
215 WHERE bn.validity @> current_timestamp
216 ORDER BY objnam
217 $$),
218 ('waterway', 'sounding_differences', 4326, $$
219 SELECT
220 sd.id AS id,
221 bn.objnam AS objnam,
222 srm.date_info AS minuend,
223 srs.date_info AS subtrahend,
224 sdcl.height AS height,
225 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
226 FROM caching.sounding_differences sd
227 JOIN caching.sounding_differences_contour_lines sdcl
228 ON sd.id = sdcl.sounding_differences_id
229 JOIN waterway.sounding_results srm
230 ON sd.minuend = srm.id
231 JOIN waterway.sounding_results srs
232 ON sd.subtrahend = srs.id
233 JOIN waterway.bottlenecks bn
234 ON srm.bottleneck_id = bn.bottleneck_id
235 AND srm.date_info::timestamptz <@ bn.validity
236 $$)
237 ON CONFLICT (schema, name) DO UPDATE SET
238 srid = EXCLUDED.srid,
239 view_def = EXCLUDED.view_def;
240
241 DROP VIEW
242 waterway.gauges_base_view,
243 waterway.gauges_geoserver,
244 waterway.distance_marks_geoserver,
245 waterway.distance_marks_ashore_geoserver,
246 waterway.bottlenecks_geoserver,
247 waterway.stretches_geoserver,
248 waterway.sections_geoserver,
249 waterway.sounding_results_contour_lines_geoserver,
250 waterway.bottleneck_overview,
251 waterway.sounding_differences;