Mercurial > gemma

annotate schema/default_sysconfig.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents 67ffa7517888
children
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
5229
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents: 5144
diff changeset
7 -- Copyright (C) 2018, 2019, 2020 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
4998
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
89 $$), (
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
90 'fairway_marks_tmpl', $$
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
91 SELECT id,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
92 lower(validity) AS valid_from,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
93 COALESCE(upper(validity), current_timestamp) AS valid_to,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
94 geom,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
95 datsta,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
96 datend,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
97 persta,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
98 perend,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
99 objnam,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
100 nobjnm,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
101 inform,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
102 ninfom,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
103 scamin,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
104 picrep,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
105 txtdsc,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
106 sordat,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
107 sorind,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
108 %s
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
109 FROM waterway.fairway_marks_%s
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
110 $$);
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
111
4998
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
112 -- Directly accessed tables
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
113 INSERT INTO sys_admin.published_services (schema, name) VALUES
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
114 ('waterway', 'waterway_area'),
5144
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
115 ('waterway', 'waterway_profiles');
4998
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
116
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
117 -- GeoServer SQL views without time support
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
118 INSERT INTO sys_admin.published_services (
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
119 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
120 ) VALUES
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
121 ('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
122 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
123 isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
124 objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
125 geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
126 applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
127 applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
128 zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
129 geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
130 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
131 source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
132 reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
133 gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
134 gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
135 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 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 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 (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
139 $$ || (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
140 ) 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
141 WHERE NOT erased
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
142 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
143 ('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
144 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
145 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
146 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
147 (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
148 (s.section).upper::varchar as upper,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
149 s.area,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
150 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
151 s.nobjnam,
4742
b88ab93dcb2c Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents: 4688
diff changeset
152 s.country,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
153 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
154 s.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
155 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
156 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
157 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
158 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
159 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
160 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
161 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
162 $$ || (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
163 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
164 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
165 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
166 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
167 ('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
168 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
169 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
170 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
171 (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
172 (s.stretch).upper::varchar as upper,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
173 s.area,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
174 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
175 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
176 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
177 s.source_organization,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
178 (SELECT string_agg(country, ', ')
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
179 FROM users.stretch_countries
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
180 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
181 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
182 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
183 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
184 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
185 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
186 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
187 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
188 $$ || (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
189 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
190 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
191 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
192 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
193 ('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
194 SELECT id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
195 country,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
196 geom,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
197 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
198 hectom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
199 catdis,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
200 position_code
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
201 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
202 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
203 ('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
204 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
205 isrs_asText(location_code) AS location,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
206 geom,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
207 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
208 (location_code).hectometre
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
209 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
210 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
211 ('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
212 SELECT bottleneck_id,
4787
3a8ec3c396e0 Fixed date_info in sounding result area geoserver view.
Raimund Renkert <raimund@renkert.org>
parents: 4744
diff changeset
213 to_char(date_info, 'YYYY-MM-DD') AS date_info,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
214 height,
4688
ad271887fd8d Added FeatureInfo for sounding results including surtyp attribute.
Raimund Renkert <raimund@renkert.org>
parents: 4673
diff changeset
215 areas,
5306
6b0274693b48 Extended view for GeoServer to contain zpg_exception.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5229
diff changeset
216 surtyp,
5308
d1dba3611644 Fixed schema, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5306
diff changeset
217 zpg_exception
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
218 FROM waterway.sounding_results_iso_areas ia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
219 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
220 $$),
5423
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
221 ('waterway', 'sounding_results_marking_points_geoserver', 4326, NULL, $$
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
222 SELECT bottleneck_id,
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
223 to_char(date_info, 'YYYY-MM-DD') AS date_info,
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
224 height,
5425
345515bc4548 Make GeoServer layer sounding_results_marking_points_geoserver working.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5423
diff changeset
225 points::geometry(MULTIPOINTZ, 4326),
5423
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
226 surtyp,
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
227 zpg_exception
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
228 FROM waterway.sounding_results_marking_points mp
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
229 JOIN waterway.sounding_results sr ON sr.id = mp.sounding_result_id
24156a964eaa [WIP] Add support for scan marking points in geoserver.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5308
diff changeset
230 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
231 ('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
232 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
233 objnam AS name,
5229
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents: 5144
diff changeset
234 bn.bottleneck_id,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
235 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
236 (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
237 (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
238 sr.current::text,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
239 responsible_country
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
240 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
241 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
242 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
243 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
244 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
245 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
246 ORDER BY objnam
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
247 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
248 ('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
249 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
250 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
251 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
252 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
253 srs.date_info AS subtrahend,
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
254 sdia.height AS height,
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
255 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
256 FROM caching.sounding_differences sd
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
257 JOIN caching.sounding_differences_iso_areas sdia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
258 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
259 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
260 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
261 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
262 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
263 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
264 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
265 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
266 $$);
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
267
4998
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
268
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
269 -- GeoServer SQL views with time support
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
270 INSERT INTO sys_admin.published_services (
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
271 schema, name, srid, key_column,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
272 wmst_attribute, wmst_end_attribute,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
273 view_def
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
274 ) VALUES
5092
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
275 ('waterway', 'bottlenecks_geoserver', 4326, 'id',
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
276 'valid_from', 'valid_to', $$
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
277 SELECT
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
278 b.id,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
279 lower(b.validity) AS valid_from,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
280 COALESCE(upper(b.validity), current_timestamp) AS valid_to,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
281 b.bottleneck_id,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
282 b.objnam,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
283 b.nobjnm,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
284 b.area,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
285 b.rb,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
286 b.lb,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
287 b.responsible_country,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
288 b.revisiting_time,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
289 b.limiting,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
290 b.date_info,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
291 b.source_organization,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
292 g.objname AS gauge_objname,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
293 g.reference_water_levels,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
294 fal.date_info AS fa_date_info,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
295 fal.critical AS fa_critical,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
296 g.gm_measuredate,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
297 g.gm_waterlevel,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
298 g.gm_n_14d,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
299 srl.date_max,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
300 g.forecast_accuracy_3d,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
301 g.forecast_accuracy_1d
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
302 FROM waterway.bottlenecks b
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
303 LEFT JOIN (
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
304 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
305 ) AS g
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
306 ON b.gauge_location = g.location
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
307 AND g.validity @> current_timestamp
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
308 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
309 bottleneck_id, date_info, critical
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
310 FROM waterway.fairway_availability
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
311 ORDER BY bottleneck_id, date_info DESC) AS fal
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
312 ON b.bottleneck_id = fal.bottleneck_id
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
313 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
314 bottleneck_id, max(date_info) AS date_max
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
315 FROM waterway.sounding_results
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
316 GROUP BY bottleneck_id
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
317 ORDER BY bottleneck_id DESC) AS srl
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
318 ON b.bottleneck_id = srl.bottleneck_id
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
319 $$),
5144
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
320 ('waterway', 'fairway_dimensions', 4326, 'id',
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
321 'valid_from', 'valid_to', $$
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
322 SELECT id,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
323 lower(validity) AS valid_from,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
324 COALESCE(upper(validity), current_timestamp) AS valid_to,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
325 area,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
326 level_of_service,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
327 min_width,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
328 max_width,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
329 min_depth,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
330 date_info,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
331 source_organization,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
332 staging_done
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
333 FROM waterway.fairway_dimensions
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
334 $$),
5016
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
335 ('waterway', 'waterway_axis', 4326, 'id',
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
336 'valid_from', 'valid_to', $$
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
337 SELECT id,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
338 lower(validity) AS valid_from,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
339 COALESCE(upper(validity), current_timestamp) AS valid_to,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
340 wtwaxs,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
341 objnam,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
342 nobjnam
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
343 FROM waterway.waterway_axis
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
344 $$),
4998
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
345 ('waterway', 'fairway_marks_bcnlat_hydro', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
346 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
347 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
348 'colour, colpat, condtn, bcnshp, catlam', 'bcnlat_hydro')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
349 ('waterway', 'fairway_marks_bcnlat_ienc', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
350 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
351 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
352 $$colour, colpat, condtn, bcnshp, catlam,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
353 string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
354 $$bcnlat_ienc LEFT JOIN waterway.fairway_marks_bcnlat_dirimps
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
355 ON id = fm_bcnlat_id GROUP BY id$$)),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
356 ('waterway', 'fairway_marks_boycar', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
357 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
358 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
359 'colour, colpat, conrad, marsys, boyshp, catcam', 'boycar')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
360 ('waterway', 'fairway_marks_boylat_hydro', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
361 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
362 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
363 'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_hydro')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
364 ('waterway', 'fairway_marks_boylat_ienc', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
365 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
366 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
367 'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_ienc')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
368 ('waterway', 'fairway_marks_boysaw', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
369 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
370 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
371 'colour, colpat, conrad, marsys, boyshp', 'boysaw')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
372 ('waterway', 'fairway_marks_boyspp', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
373 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
374 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
375 'colour, colpat, conrad, marsys, boyshp, catspm', 'boyspp')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
376 ('waterway', 'fairway_marks_daymar_hydro', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
377 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
378 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
379 'colour, colpat, condtn, topshp', 'daymar_hydro')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
380 ('waterway', 'fairway_marks_daymar_ienc', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
381 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
382 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
383 $$colour, colpat, condtn, topshp, orient,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
384 string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
385 $$daymar_ienc LEFT JOIN waterway.fairway_marks_daymar_dirimps
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
386 ON id = fm_daymar_id GROUP BY id$$)),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
387 ('waterway', 'fairway_marks_lights', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
388 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
389 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
390 $$colour, condtn, orient, catlit, exclit, litchr, litvis,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
391 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status$$,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
392 'lights')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
393 ('waterway', 'fairway_marks_rtpbcn', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
394 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
395 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
396 'condtn, siggrp, catrtb, radwal', 'rtpbcn')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
397 ('waterway', 'fairway_marks_topmar', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
398 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
399 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
400 'colour, colpat, condtn, topshp', 'topmar')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
401 ('waterway', 'fairway_marks_notmrk', 4326, 'id', 'valid_from', 'valid_to',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
402 format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
403 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
404 $$condtn, marsys, orient, status, addmrk, catnmk,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
405 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
406 string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
407 $$notmrk LEFT JOIN waterway.fairway_marks_notmrk_dirimps
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
408 ON id = fm_notmrk_id GROUP BY id$$));
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
409
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
410 --
4934
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
411 -- group layers
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
412 --
5426
67ffa7517888 Added new group layer 'sounding_results'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5425
diff changeset
413 INSERT INTO sys_admin.layer_groups VALUES
67ffa7517888 Added new group layer 'sounding_results'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5425
diff changeset
414 ('fairway_marks'),
67ffa7517888 Added new group layer 'sounding_results'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5425
diff changeset
415 ('sounding_results');
4934
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
416
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
417 INSERT INTO sys_admin.grouped_layers VALUES
4992
ac846e085553 Distinguish between HYDRO and IENC features in layer names
Tom Gottfried <tom@intevation.de>
parents: 4945
diff changeset
418 ('fairway_marks', 'waterway', 'fairway_marks_boylat_hydro', 0),
ac846e085553 Distinguish between HYDRO and IENC features in layer names
Tom Gottfried <tom@intevation.de>
parents: 4945
diff changeset
419 ('fairway_marks', 'waterway', 'fairway_marks_boylat_ienc', 0),
4934
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
420 ('fairway_marks', 'waterway', 'fairway_marks_boycar', 1),
4992
ac846e085553 Distinguish between HYDRO and IENC features in layer names
Tom Gottfried <tom@intevation.de>
parents: 4945
diff changeset
421 ('fairway_marks', 'waterway', 'fairway_marks_bcnlat_hydro', 2),
ac846e085553 Distinguish between HYDRO and IENC features in layer names
Tom Gottfried <tom@intevation.de>
parents: 4945
diff changeset
422 ('fairway_marks', 'waterway', 'fairway_marks_bcnlat_ienc', 2),
4945
97533bbfaa2d Add missing fairway mark types to layer group
Tom Gottfried <tom@intevation.de>
parents: 4934
diff changeset
423 ('fairway_marks', 'waterway', 'fairway_marks_boysaw', 3),
97533bbfaa2d Add missing fairway mark types to layer group
Tom Gottfried <tom@intevation.de>
parents: 4934
diff changeset
424 ('fairway_marks', 'waterway', 'fairway_marks_boyspp', 4),
4992
ac846e085553 Distinguish between HYDRO and IENC features in layer names
Tom Gottfried <tom@intevation.de>
parents: 4945
diff changeset
425 ('fairway_marks', 'waterway', 'fairway_marks_daymar_hydro', 5),
ac846e085553 Distinguish between HYDRO and IENC features in layer names
Tom Gottfried <tom@intevation.de>
parents: 4945
diff changeset
426 ('fairway_marks', 'waterway', 'fairway_marks_daymar_ienc', 5),
4945
97533bbfaa2d Add missing fairway mark types to layer group
Tom Gottfried <tom@intevation.de>
parents: 4934
diff changeset
427 ('fairway_marks', 'waterway', 'fairway_marks_lights', 6),
97533bbfaa2d Add missing fairway mark types to layer group
Tom Gottfried <tom@intevation.de>
parents: 4934
diff changeset
428 ('fairway_marks', 'waterway', 'fairway_marks_rtpbcn', 7),
97533bbfaa2d Add missing fairway mark types to layer group
Tom Gottfried <tom@intevation.de>
parents: 4934
diff changeset
429 ('fairway_marks', 'waterway', 'fairway_marks_topmar', 8),
5426
67ffa7517888 Added new group layer 'sounding_results'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5425
diff changeset
430 ('fairway_marks', 'waterway', 'fairway_marks_notmrk', 9),
67ffa7517888 Added new group layer 'sounding_results'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5425
diff changeset
431 ('sounding_results', 'waterway', 'sounding_results_areas_geoserver', 0),
67ffa7517888 Added new group layer 'sounding_results'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5425
diff changeset
432 ('sounding_results', 'waterway', 'sounding_results_marking_points_geoserver', 1);
4934
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
433
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
434 --
3625
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
435 -- Settings
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
436 --
3641
32d7bb1afdc9 client: configuration: support wms services with nested layers
Markus Kottlaender <markus@intevation.de>
parents: 3637
diff changeset
437 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
438 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
439 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
440 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
441 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
442 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
443 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
444 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
445 INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_72h', -12.5);
4744
63ead1fab71b Simplified defaults for class breaks.
Sascha Wilde <wilde@intevation.de>
parents: 4742
diff changeset
446 INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks', '-2.5:#5A3300,-2,-1.5,-1,-0.5,0:#D8B050,0.5:#FF0000,1,1.5,2:#FF9999,2.5:#A6B8FA,3,3.5,4,4.5,5,6:#0101FF,7:#88DD55,8,9,10,11,12:#146E33');
63ead1fab71b Simplified defaults for class breaks.
Sascha Wilde <wilde@intevation.de>
parents: 4742
diff changeset
447 INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks_compare', '-2:#06b100,-1.8,-1.6,-1.4,-1.2,-1:#1cc68e,-0.8,-0.6,-0.4,-0.2,0:#c2c2c2,0.2,0.4,0.6,0.8,1:#fff01a,1.2,1.4,1.6,1.8,2:#f80012');
4203
c56db74e2f9b Forget new system config for geo stylintg in schema.
Fadi Abbud <fadi.abbud@intevation.de>
parents: 3846
diff changeset
448 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
449 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
450 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
451 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
452 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
453 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
454 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
455 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
456 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
457 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
458 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
459 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
460 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
461
4330
fe01e997d66f adjust sys-config values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4239
diff changeset
462 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
463 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
464 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
465 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
466 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
467 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
468
831
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
469 COMMIT;