Mercurial > gemma

annotate schema/default_sysconfig.sql @ 4735:7a40a39853a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: fix comparison in pdf-filename
author Fadi Abbud <fadi.abbud@intevation.de>
date Fri, 18 Oct 2019 10:45:25 +0200
parents ad271887fd8d
children b88ab93dcb2c
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
6
4236
27ed6f709195 Remove unused systemconf.feature_colours from backend
Bernhard Reiter <bernhard@intevation.de>
parents: 4232
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Sascha Wilde <wilde@intevation.de>
4236
27ed6f709195 Remove unused systemconf.feature_colours from backend
Bernhard Reiter <bernhard@intevation.de>
parents: 4232
diff changeset
13 -- * Bernhard Reiter <bernhard.reiter@intevation.de>
27ed6f709195 Remove unused systemconf.feature_colours from backend
Bernhard Reiter <bernhard@intevation.de>
parents: 4232
diff changeset
14 -- * Fadi Abbund <fadi.abbud@intevation.de>
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
15 -- * Tom Gottfried <tom@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
16
831
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 BEGIN;
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 --
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 -- Default system configurations for the GEMMA database
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 --
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 --
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
24 -- Tables and views published via GeoServer
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
25 --
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
26
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
27 -- temporary table to store portions of SQL shared by multiple SQL views
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
28 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: 4615
diff changeset
29 'gauges_base_view', $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
30 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
31 g.location,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
32 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: 4615
diff changeset
33 g.objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
34 g.geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
35 g.applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
36 g.applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
37 g.validity,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
38 g.zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
39 g.geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
40 g.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
41 g.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
42 g.erased,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
43 r.rwls AS reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
44 wl.measure_date AS gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
45 wl.water_level AS gm_waterlevel,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
46 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: 4615
diff changeset
47 fca.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
48 fca.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
49 FROM waterway.gauges g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
50 LEFT JOIN (SELECT location, validity,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
51 json_strip_nulls(json_object_agg(
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
52 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: 4615
diff changeset
53 FROM waterway.gauges_reference_water_levels
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
54 GROUP BY location, validity) AS r
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
55 USING (location, validity)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
56 LEFT JOIN (SELECT DISTINCT ON (location)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
57 location,
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4619
diff changeset
58 date_issue,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
59 measure_date,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
60 water_level,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
61 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: 4615
diff changeset
62 FROM waterway.gauge_measurements
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
63 -- 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: 4615
diff changeset
64 WHERE measure_date
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
65 >= current_timestamp - '14 days 00:15'::interval
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
66 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: 4615
diff changeset
67 USING (location)
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
68 LEFT JOIN (SELECT DISTINCT ON (location)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
69 location,
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4619
diff changeset
70 date_issue,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
71 max(acc) FILTER (WHERE measure_date
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
72 <= current_timestamp + '1 day'::interval)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
73 OVER loc_date_issue AS forecast_accuracy_1d,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
74 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
75 FROM (SELECT location, date_issue, measure_date,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
76 GREATEST(water_level - lower(conf_interval),
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
77 upper(conf_interval) - water_level) AS acc
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
78 FROM waterway.gauge_predictions
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
79 -- consider predictions made within last 14 days ...
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
80 WHERE date_issue
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
81 >= current_timestamp - '14 days 00:15'::interval
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
82 -- ... for the next three days from now
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
83 AND measure_date BETWEEN current_timestamp
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
84 AND current_timestamp + '3 days'::interval) AS acc
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
85 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
86 ORDER BY location, date_issue DESC) AS fca
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4619
diff changeset
87 -- Show only forecasts issued with latest measurements or later
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4619
diff changeset
88 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: 4615
diff changeset
89 $$);
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
90
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
91 INSERT INTO sys_admin.published_services (
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
92 schema, name, srid, key_column, view_def
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
93 ) VALUES
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
94 -- Directly accessed tables
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
95 ('waterway', 'waterway_axis', NULL, NULL, NULL),
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
96 ('waterway', 'waterway_area', NULL, NULL, NULL),
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
97 ('waterway', 'waterway_profiles', NULL, NULL, NULL),
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
98 ('waterway', 'fairway_dimensions', NULL, NULL, NULL),
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
99 -- GeoServer SQL views
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
100 ('waterway', 'gauges_geoserver', 4326, 'isrs_code', $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
101 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
102 isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
103 objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
104 geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
105 applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
106 applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
107 zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
108 geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
109 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
110 source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
111 reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
112 gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
113 gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
114 gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
115 forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
116 forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
117 FROM (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
118 $$ || (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: 4615
diff changeset
119 ) AS gauges_base_view
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
120 WHERE NOT erased
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
121 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
122 ('waterway', 'sections_geoserver', 4326, 'id', $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
123 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
124 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
125 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
126 (s.section).lower::varchar as lower,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
127 (s.section).upper::varchar as upper,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
128 s.area,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
129 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
130 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
131 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
132 s.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
133 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
134 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: 4615
diff changeset
135 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: 4615
diff changeset
136 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: 4615
diff changeset
137 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: 4615
diff changeset
138 FROM waterway.sections s
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
139 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
140 $$ || (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: 4615
diff changeset
141 WHERE NOT erased) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
142 ON g.location <@ s.section
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
143 GROUP BY s.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
144 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
145 ('waterway', 'stretches_geoserver', 4326, 'id', $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
146 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
147 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
148 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
149 (s.stretch).lower::varchar as lower,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
150 (s.stretch).upper::varchar as upper,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
151 s.area,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
152 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
153 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
154 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
155 s.source_organization,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
156 (SELECT string_agg(country, ', ')
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
157 FROM users.stretch_countries
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
158 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: 4615
diff changeset
159 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
160 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: 4615
diff changeset
161 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: 4615
diff changeset
162 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: 4615
diff changeset
163 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
164 FROM users.stretches s
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
165 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
166 $$ || (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: 4615
diff changeset
167 WHERE NOT erased) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
168 ON g.location <@ s.stretch
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
169 GROUP BY s.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
170 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
171 ('waterway', 'distance_marks_ashore_geoserver', 4326, 'id', $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
172 SELECT id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
173 country,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
174 geom,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
175 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
176 hectom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
177 catdis,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
178 position_code
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
179 FROM waterway.distance_marks
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
180 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
181 ('waterway', 'distance_marks_geoserver', 4326, 'location', $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
182 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
183 isrs_asText(location_code) AS location,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
184 geom,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
185 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
186 (location_code).hectometre
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
187 FROM waterway.distance_marks_virtual
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
188 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
189 ('waterway', 'sounding_results_areas_geoserver', 4326, NULL, $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
190 SELECT bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
191 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
192 height,
4688
ad271887fd8d Added FeatureInfo for sounding results including surtyp attribute.
Raimund Renkert <raimund@renkert.org>
parents: 4673
diff changeset
193 areas,
ad271887fd8d Added FeatureInfo for sounding results including surtyp attribute.
Raimund Renkert <raimund@renkert.org>
parents: 4673
diff changeset
194 surtyp
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
195 FROM waterway.sounding_results_iso_areas ia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
196 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: 4615
diff changeset
197 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
198 ('waterway', 'bottlenecks_geoserver', 4326, 'id', $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
199 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
200 b.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
201 b.bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
202 b.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
203 b.nobjnm,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
204 b.area,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
205 b.rb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
206 b.lb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
207 b.responsible_country,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
208 b.revisiting_time,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
209 b.limiting,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
210 b.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
211 b.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
212 g.objname AS gauge_objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
213 g.reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
214 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: 4615
diff changeset
215 fal.critical AS fa_critical,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
216 g.gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
217 g.gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
218 g.gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
219 srl.date_max,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
220 g.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
221 g.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
222 FROM waterway.bottlenecks b
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
223 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
224 $$ || (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: 4615
diff changeset
225 ) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
226 ON b.gauge_location = g.location
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
227 AND g.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
228 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: 4615
diff changeset
229 bottleneck_id, date_info, critical
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
230 FROM waterway.fairway_availability
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
231 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: 4615
diff changeset
232 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: 4615
diff changeset
233 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: 4615
diff changeset
234 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: 4615
diff changeset
235 FROM waterway.sounding_results
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
236 GROUP BY bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
237 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: 4615
diff changeset
238 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: 4615
diff changeset
239 WHERE b.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
240 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
241 ('waterway', 'bottleneck_overview', 4326, NULL, $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
242 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
243 objnam AS name,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
244 ST_Centroid(area) AS point,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
245 (lower(stretch)).hectometre AS from,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
246 (upper(stretch)).hectometre AS to,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
247 sr.current::text,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
248 responsible_country
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
249 FROM waterway.bottlenecks bn LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
250 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: 4615
diff changeset
251 FROM waterway.sounding_results
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
252 GROUP BY bottleneck_id) sr
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
253 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: 4615
diff changeset
254 WHERE bn.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
255 ORDER BY objnam
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
256 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
257 ('waterway', 'sounding_differences', 4326, NULL, $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
258 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
259 sd.id AS id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
260 bn.objnam AS objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
261 srm.date_info AS minuend,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
262 srs.date_info AS subtrahend,
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
263 sdia.height AS height,
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
264 sdia.areas AS areas
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
265 FROM caching.sounding_differences sd
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
266 JOIN caching.sounding_differences_iso_areas sdia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
267 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: 4615
diff changeset
268 JOIN waterway.sounding_results srm
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
269 ON sd.minuend = srm.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
270 JOIN waterway.sounding_results srs
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
271 ON sd.subtrahend = srs.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
272 JOIN waterway.bottlenecks bn
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
273 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: 4615
diff changeset
274 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: 4615
diff changeset
275 $$);
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
276
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
277 --
3625
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
278 -- Settings
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
279 --
3641
32d7bb1afdc9 client: configuration: support wms services with nested layers
Markus Kottlaender <markus@intevation.de>
parents: 3637
diff changeset
280 INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_url', 'https://service.d4d-portal.info/wms/');
32d7bb1afdc9 client: configuration: support wms services with nested layers
Markus Kottlaender <markus@intevation.de>
parents: 3637
diff changeset
281 INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_params', '{"LAYERS": "d4d", "VERSION": "1.1.1", "TILED": true}');
3625
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
282 INSERT INTO sys_admin.system_config VALUES ('bn_revtime_multiplier', 1.5);
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
283 INSERT INTO sys_admin.system_config VALUES ('gm_min_values_14d', 1224);
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
284 INSERT INTO sys_admin.system_config VALUES ('gm_latest_hours', 24);
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
285 INSERT INTO sys_admin.system_config VALUES ('gm_forecast_offset_24h', 15);
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
286 INSERT INTO sys_admin.system_config VALUES ('gm_forecast_offset_72h', 15);
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
287 INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_24h', -12.5);
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
288 INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_72h', -12.5);
3846
f1ce295d9283 added classbreak color for morphology classbreaks
Markus Kottlaender <markus@intevation.de>
parents: 3763
diff changeset
289 INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks', '1:#ff00dd,1.5,1.7,1.9,2.1,2.3,2.5:#f25f20,2.7,2.9,3.1:#f7e40e,3.3,3.5,4:#8ad51a,4.5,5,5.5,6,6.5,7:#1414ff');
3763
a1bb7c894058 client: configuration: morphology classbreaks: added color selection
Markus Kottlaender <markus@intevation.de>
parents: 3641
diff changeset
290 INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks_compare', '-2:#06b100,-1.9,-1.8,-1.7,-1.6,-1.5,-1.4,-1.3,-1.2,-1.1,-1:#1cc68e,-0.9,-0.8,-0.7,-0.6,-0.5,-0.4,-0.3,-0.2,-0.1,0:#c2c2c2,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1:#fff01a,1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8,1.9,2:#f80012');
4203
c56db74e2f9b Forget new system config for geo stylintg in schema.
Fadi Abbud <fadi.abbud@intevation.de>
parents: 3846
diff changeset
291 INSERT INTO sys_admin.system_config VALUES ('bottlenecks_stroke','#fa28ff');
4209
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
292 INSERT INTO sys_admin.system_config VALUES ('bottlenecks_fill','#ff25c424');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
293 INSERT INTO sys_admin.system_config VALUES ('stretches_stroke','#fac800cc');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
294 INSERT INTO sys_admin.system_config VALUES ('stretches_fill','#fac80a4d');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
295 INSERT INTO sys_admin.system_config VALUES ('sections_stroke','#ff960acc');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
296 INSERT INTO sys_admin.system_config VALUES ('sections_fill','#ff96004d');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
297 INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos1_stroke','#0000ffcc');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
298 INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos1_fill','#f0e60033');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
299 INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos2_stroke','#0000ffe6');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
300 INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos2_fill','#f0e6001a');
4203
c56db74e2f9b Forget new system config for geo stylintg in schema.
Fadi Abbud <fadi.abbud@intevation.de>
parents: 3846
diff changeset
301 INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos3_stroke','#0000ff');
4209
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
302 INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos3_fill','#ffffff66');
984bf74ad3cf styles-config: edit some color-values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4207
diff changeset
303 INSERT INTO sys_admin.system_config VALUES ('waterwayprofiles_stroke','#0000ff80');
3625
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
304
4330
fe01e997d66f adjust sys-config values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4239
diff changeset
305 INSERT INTO sys_admin.system_config VALUES ('distance_marks_fill', '#ff99994d');
4232
8aff98c84a5a Example how to style distance_marks WMS layer with templating.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4209
diff changeset
306 INSERT INTO sys_admin.system_config VALUES ('distance_marks_stroke', '#6666ff');
4330
fe01e997d66f adjust sys-config values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4239
diff changeset
307 INSERT INTO sys_admin.system_config VALUES ('distance_marks_ashore_fill', '#8888ff1a');
fe01e997d66f adjust sys-config values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4239
diff changeset
308 INSERT INTO sys_admin.system_config VALUES ('distance_marks_ashore_stroke', '#3333ff');
fe01e997d66f adjust sys-config values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4239
diff changeset
309 INSERT INTO sys_admin.system_config VALUES ('waterway_axis_stroke', '#0000ff');
4239
81153eae048c styls-config: add more system configurations for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4236
diff changeset
310 INSERT INTO sys_admin.system_config VALUES ('waterway_area_stroke', '#006600');
4232
8aff98c84a5a Example how to style distance_marks WMS layer with templating.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4209
diff changeset
311
831
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
312 COMMIT;