Mercurial > gemma

annotate schema/updates/1300/02.views_to_geoservers.sql @ 4623:30bb2d819d57 geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge default into geoserver_sql_views
author Tom Gottfried <tom@intevation.de>
date Wed, 09 Oct 2019 16:40:18 +0200
parents b03aa1502736
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- Copied from ../../default_sysconfig.sql
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- plus conflict resolution to achieve updates
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 CREATE TEMP TABLE base_views (name, def) AS VALUES (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 'gauges_base_view', $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 g.location,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 isrs_asText(g.location) AS isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 g.objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 g.geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 g.applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 g.applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 g.validity,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 g.zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 g.geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 g.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 g.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 g.erased,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 r.rwls AS reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 wl.measure_date AS gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 wl.water_level AS gm_waterlevel,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
22 wl.n AS gm_n_14d,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 fca.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 fca.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 FROM waterway.gauges g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 LEFT JOIN (SELECT location, validity,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 json_strip_nulls(json_object_agg(
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 coalesce(depth_reference, 'empty'), value)) AS rwls
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 FROM waterway.gauges_reference_water_levels
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 GROUP BY location, validity) AS r
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 USING (location, validity)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 LEFT JOIN (SELECT DISTINCT ON (location)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 location,
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
34 date_issue,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 measure_date,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
36 water_level,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
37 count(*) OVER (PARTITION BY location) AS n
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 FROM waterway.gauge_measurements
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 -- consider all measurements within 14 days plus a tolerance
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 WHERE measure_date
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 >= current_timestamp - '14 days 00:15'::interval
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
42 ORDER BY location, measure_date DESC) AS wl
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 USING (location)
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
44 LEFT JOIN (SELECT DISTINCT ON (location)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
45 location,
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
46 date_issue,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
47 max(acc) FILTER (WHERE measure_date
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
48 <= current_timestamp + '1 day'::interval)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
49 OVER loc_date_issue AS forecast_accuracy_1d,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
50 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
51 FROM (SELECT location, date_issue, measure_date,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
52 GREATEST(water_level - lower(conf_interval),
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
53 upper(conf_interval) - water_level) AS acc
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
54 FROM waterway.gauge_predictions
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
55 -- consider predictions made within last 14 days ...
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
56 WHERE date_issue
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
57 >= current_timestamp - '14 days 00:15'::interval
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
58 -- ... for the next three days from now
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
59 AND measure_date BETWEEN current_timestamp
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
60 AND current_timestamp + '3 days'::interval) AS acc
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
61 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
62 ORDER BY location, date_issue DESC) AS fca
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
63 -- Show only forecasts issued with latest measurements or later
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
64 ON fca.location = g.location AND fca.date_issue >= wl.date_issue
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 $$);
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 ('waterway', 'gauges_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 FROM (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 ) AS gauges_base_view
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 WHERE NOT erased
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 ('waterway', 'sections_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 (s.section).lower::varchar as lower,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 (s.section).upper::varchar as upper,
4622
b03aa1502736 Fixup rev. 4476ec4db818
Tom Gottfried <tom@intevation.de>
parents: 4620
diff changeset
96 s.area,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 s.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 min(g.gm_measuredate) AS gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 min(g.gm_n_14d) AS gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106 FROM waterway.sections s
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 WHERE NOT erased) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 ON g.location <@ s.section
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 GROUP BY s.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 ('waterway', 'stretches_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
117 (s.stretch).lower::varchar as lower,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
118 (s.stretch).upper::varchar as upper,
4622
b03aa1502736 Fixup rev. 4476ec4db818
Tom Gottfried <tom@intevation.de>
parents: 4620
diff changeset
119 s.area,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
123 s.source_organization,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
124 (SELECT string_agg(country, ', ')
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
125 FROM users.stretch_countries
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
126 WHERE stretch_id = s.id) AS countries,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
127 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
128 min(g.gm_measuredate) AS gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 min(g.gm_n_14d) AS gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
130 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
131 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4617
diff changeset
132 FROM users.stretches s
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
133 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
134 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
135 WHERE NOT erased) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
136 ON g.location <@ s.stretch
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
137 GROUP BY s.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
138 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
139 ('waterway', 'distance_marks_ashore_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
140 SELECT id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
141 country,
4622
b03aa1502736 Fixup rev. 4476ec4db818
Tom Gottfried <tom@intevation.de>
parents: 4620
diff changeset
142 geom,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
143 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
144 hectom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
145 catdis,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
146 position_code
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
147 FROM waterway.distance_marks
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
148 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
149 ('waterway', 'distance_marks_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
150 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
151 isrs_asText(location_code) AS location,
4622
b03aa1502736 Fixup rev. 4476ec4db818
Tom Gottfried <tom@intevation.de>
parents: 4620
diff changeset
152 geom,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
153 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
154 (location_code).hectometre
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
155 FROM waterway.distance_marks_virtual
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
156 $$),
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
157 ('waterway', 'sounding_results_areas_geoserver', 4326, $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
158 SELECT bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
159 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160 height,
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
161 areas
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
162 FROM waterway.sounding_results_iso_areas ia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
163 JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
164 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
165 ('waterway', 'bottlenecks_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
166 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
167 b.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
168 b.bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
169 b.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
170 b.nobjnm,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
171 b.area,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
172 b.rb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
173 b.lb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
174 b.responsible_country,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
175 b.revisiting_time,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
176 b.limiting,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
177 b.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
178 b.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
179 g.objname AS gauge_objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
180 g.reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
181 fal.date_info AS fa_date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
182 fal.critical AS fa_critical,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
183 g.gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
184 g.gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
185 g.gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
186 srl.date_max,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
187 g.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
188 g.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
189 FROM waterway.bottlenecks b
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
190 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
191 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
192 ) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
193 ON b.gauge_location = g.location
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
194 AND g.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
195 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
196 bottleneck_id, date_info, critical
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
197 FROM waterway.fairway_availability
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
198 ORDER BY bottleneck_id, date_info DESC) AS fal
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
199 ON b.bottleneck_id = fal.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
200 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
201 bottleneck_id, max(date_info) AS date_max
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
202 FROM waterway.sounding_results
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
203 GROUP BY bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
204 ORDER BY bottleneck_id DESC) AS srl
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
205 ON b.bottleneck_id = srl.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
206 WHERE b.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
207 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
208 ('waterway', 'bottleneck_overview', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
209 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
210 objnam AS name,
4622
b03aa1502736 Fixup rev. 4476ec4db818
Tom Gottfried <tom@intevation.de>
parents: 4620
diff changeset
211 ST_Centroid(area) AS point,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
212 (lower(stretch)).hectometre AS from,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
213 (upper(stretch)).hectometre AS to,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
214 sr.current::text,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
215 responsible_country
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
216 FROM waterway.bottlenecks bn LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
217 SELECT bottleneck_id, max(date_info) AS current
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
218 FROM waterway.sounding_results
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
219 GROUP BY bottleneck_id) sr
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
220 ON sr.bottleneck_id = bn.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
221 WHERE bn.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
222 ORDER BY objnam
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
223 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
224 ('waterway', 'sounding_differences', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
225 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
226 sd.id AS id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
227 bn.objnam AS objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
228 srm.date_info AS minuend,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
229 srs.date_info AS subtrahend,
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
230 sdia.height AS height,
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
231 sdia.areas AS areas
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
232 FROM caching.sounding_differences sd
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
233 JOIN caching.sounding_differences_iso_areas sdia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
234 ON sd.id = sdia.sounding_differences_id
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
235 JOIN waterway.sounding_results srm
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
236 ON sd.minuend = srm.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
237 JOIN waterway.sounding_results srs
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
238 ON sd.subtrahend = srs.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
239 JOIN waterway.bottlenecks bn
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
240 ON srm.bottleneck_id = bn.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
241 AND srm.date_info::timestamptz <@ bn.validity
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
242 $$)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
243 ON CONFLICT (schema, name) DO UPDATE SET
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
244 srid = EXCLUDED.srid,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
245 view_def = EXCLUDED.view_def;
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
246
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
247 DROP VIEW
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
248 waterway.gauges_base_view,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
249 waterway.gauges_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
250 waterway.distance_marks_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
251 waterway.distance_marks_ashore_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
252 waterway.bottlenecks_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
253 waterway.stretches_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
254 waterway.sections_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
255 waterway.bottleneck_overview,
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4622
diff changeset
256 waterway.sounding_results_areas_geoserver,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
257 waterway.sounding_differences;