Mercurial > gemma

annotate schema/default_sysconfig.sql @ 5402:f5063fa7f666 marking-single-beam

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add schema change for marking vessel single beam scans.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 06 Jul 2021 00:30:39 +0200
parents d1dba3611644
children 24156a964eaa
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 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
221 ('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
222 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
223 objnam AS name,
5229
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents: 5144
diff changeset
224 bn.bottleneck_id,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
225 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
226 (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
227 (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
228 sr.current::text,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
229 responsible_country
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
230 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
231 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
232 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
233 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
234 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
235 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
236 ORDER BY objnam
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
237 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
238 ('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
239 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
240 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
241 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
242 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
243 srs.date_info AS subtrahend,
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
244 sdia.height AS height,
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
245 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
246 FROM caching.sounding_differences sd
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
247 JOIN caching.sounding_differences_iso_areas sdia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
248 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
249 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
250 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
251 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
252 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
253 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
254 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
255 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
256 $$);
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
257
4998
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
258
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
259 -- GeoServer SQL views with time support
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
260 INSERT INTO sys_admin.published_services (
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
261 schema, name, srid, key_column,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
262 wmst_attribute, wmst_end_attribute,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
263 view_def
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
264 ) VALUES
5092
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
265 ('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
266 '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
267 SELECT
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
268 b.id,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
269 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
270 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
271 b.bottleneck_id,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
272 b.objnam,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
273 b.nobjnm,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
274 b.area,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
275 b.rb,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
276 b.lb,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
277 b.responsible_country,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
278 b.revisiting_time,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
279 b.limiting,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
280 b.date_info,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
281 b.source_organization,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
282 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
283 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
284 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
285 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
286 g.gm_measuredate,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
287 g.gm_waterlevel,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
288 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
289 srl.date_max,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
290 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
291 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
292 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
293 LEFT JOIN (
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
294 $$ || (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
295 ) AS g
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
296 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
297 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
298 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
299 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
300 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
301 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
302 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
303 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
304 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
305 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
306 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
307 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
308 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
309 $$),
5144
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
310 ('waterway', 'fairway_dimensions', 4326, 'id',
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
311 'valid_from', 'valid_to', $$
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
312 SELECT id,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
313 lower(validity) AS valid_from,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
314 COALESCE(upper(validity), current_timestamp) AS valid_to,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
315 area,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
316 level_of_service,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
317 min_width,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
318 max_width,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
319 min_depth,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
320 date_info,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
321 source_organization,
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
322 staging_done
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
323 FROM waterway.fairway_dimensions
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents: 5092
diff changeset
324 $$),
5016
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
325 ('waterway', 'waterway_axis', 4326, 'id',
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
326 'valid_from', 'valid_to', $$
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
327 SELECT id,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
328 lower(validity) AS valid_from,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
329 COALESCE(upper(validity), current_timestamp) AS valid_to,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
330 wtwaxs,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
331 objnam,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
332 nobjnam
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
333 FROM waterway.waterway_axis
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4998
diff changeset
334 $$),
4998
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
335 ('waterway', 'fairway_marks_bcnlat_hydro', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
336 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
337 (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
338 'colour, colpat, condtn, bcnshp, catlam', 'bcnlat_hydro')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
339 ('waterway', 'fairway_marks_bcnlat_ienc', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
340 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
341 (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
342 $$colour, colpat, condtn, bcnshp, catlam,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
343 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
344 $$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
345 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
346 ('waterway', 'fairway_marks_boycar', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
347 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
348 (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
349 'colour, colpat, conrad, marsys, boyshp, catcam', 'boycar')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
350 ('waterway', 'fairway_marks_boylat_hydro', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
351 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
352 (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
353 '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
354 ('waterway', 'fairway_marks_boylat_ienc', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
355 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
356 (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
357 '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
358 ('waterway', 'fairway_marks_boysaw', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
359 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
360 (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
361 'colour, colpat, conrad, marsys, boyshp', 'boysaw')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
362 ('waterway', 'fairway_marks_boyspp', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
363 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
364 (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
365 'colour, colpat, conrad, marsys, boyshp, catspm', 'boyspp')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
366 ('waterway', 'fairway_marks_daymar_hydro', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
367 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
368 (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
369 'colour, colpat, condtn, topshp', 'daymar_hydro')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
370 ('waterway', 'fairway_marks_daymar_ienc', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
371 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
372 (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
373 $$colour, colpat, condtn, topshp, orient,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
374 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
375 $$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
376 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
377 ('waterway', 'fairway_marks_lights', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
378 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
379 (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
380 $$colour, condtn, orient, catlit, exclit, litchr, litvis,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
381 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status$$,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
382 'lights')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
383 ('waterway', 'fairway_marks_rtpbcn', 4326, 'id',
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
384 'valid_from', 'valid_to', format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
385 (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
386 'condtn, siggrp, catrtb, radwal', 'rtpbcn')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
387 ('waterway', 'fairway_marks_topmar', 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, colpat, condtn, topshp', 'topmar')),
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
391 ('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
392 format(
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
393 (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
394 $$condtn, marsys, orient, status, addmrk, catnmk,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
395 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw,
bb2123358bd8 Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents: 4992
diff changeset
396 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
397 $$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
398 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
399
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
400 --
4934
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
401 -- group layers
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
402 --
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
403 INSERT INTO sys_admin.layer_groups VALUES ('fairway_marks');
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
404
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
405 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
406 ('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
407 ('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
408 ('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
409 ('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
410 ('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
411 ('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
412 ('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
413 ('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
414 ('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
415 ('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
416 ('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
417 ('fairway_marks', 'waterway', 'fairway_marks_topmar', 8),
97533bbfaa2d Add missing fairway mark types to layer group
Tom Gottfried <tom@intevation.de>
parents: 4934
diff changeset
418 ('fairway_marks', 'waterway', 'fairway_marks_notmrk', 9);
4934
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
419
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
420 --
3625
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
421 -- Settings
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
422 --
3641
32d7bb1afdc9 client: configuration: support wms services with nested layers
Markus Kottlaender <markus@intevation.de>
parents: 3637
diff changeset
423 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
424 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
425 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
426 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
427 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
428 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
429 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
430 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
431 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
432 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
433 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
434 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
435 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
436 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
437 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
438 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
439 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
440 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
441 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
442 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
443 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
444 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
445 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
446 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
447
4330
fe01e997d66f adjust sys-config values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4239
diff changeset
448 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
449 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
450 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
451 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
452 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
453 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
454
831
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
455 COMMIT;