Mercurial > gemma

annotate schema/default_sysconfig.sql @ 4934:c6af373b0832 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added fairwaymarks group layer in configuration.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sat, 15 Feb 2020 21:29:38 +0100
parents 9b9b4150f296
children 97533bbfaa2d
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
6
4236
27ed6f709195 Remove unused systemconf.feature_colours from backend
Bernhard Reiter <bernhard@intevation.de>
parents: 4232
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Sascha Wilde <wilde@intevation.de>
4236
27ed6f709195 Remove unused systemconf.feature_colours from backend
Bernhard Reiter <bernhard@intevation.de>
parents: 4232
diff changeset
13 -- * Bernhard Reiter <bernhard.reiter@intevation.de>
27ed6f709195 Remove unused systemconf.feature_colours from backend
Bernhard Reiter <bernhard@intevation.de>
parents: 4232
diff changeset
14 -- * Fadi Abbund <fadi.abbud@intevation.de>
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
15 -- * Tom Gottfried <tom@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 904
diff changeset
16
831
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 BEGIN;
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 --
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 -- Default system configurations for the GEMMA database
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 --
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 --
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
24 -- Tables and views published via GeoServer
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
25 --
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
26
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
27 -- temporary table to store portions of SQL shared by multiple SQL views
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
28 CREATE TEMP TABLE base_views (name, def) AS VALUES (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
29 'gauges_base_view', $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
30 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
31 g.location,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
32 isrs_asText(g.location) AS isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
33 g.objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
34 g.geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
35 g.applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
36 g.applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
37 g.validity,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
38 g.zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
39 g.geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
40 g.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
41 g.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
42 g.erased,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
43 r.rwls AS reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
44 wl.measure_date AS gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
45 wl.water_level AS gm_waterlevel,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
46 wl.n AS gm_n_14d,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
47 fca.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
48 fca.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
49 FROM waterway.gauges g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
50 LEFT JOIN (SELECT location, validity,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
51 json_strip_nulls(json_object_agg(
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
52 coalesce(depth_reference, 'empty'), value)) AS rwls
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
53 FROM waterway.gauges_reference_water_levels
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
54 GROUP BY location, validity) AS r
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
55 USING (location, validity)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
56 LEFT JOIN (SELECT DISTINCT ON (location)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
57 location,
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4619
diff changeset
58 date_issue,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
59 measure_date,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
60 water_level,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
61 count(*) OVER (PARTITION BY location) AS n
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
62 FROM waterway.gauge_measurements
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
63 -- consider all measurements within 14 days plus a tolerance
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
64 WHERE measure_date
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
65 >= current_timestamp - '14 days 00:15'::interval
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
66 ORDER BY location, measure_date DESC) AS wl
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
67 USING (location)
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
68 LEFT JOIN (SELECT DISTINCT ON (location)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
69 location,
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4619
diff changeset
70 date_issue,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
71 max(acc) FILTER (WHERE measure_date
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
72 <= current_timestamp + '1 day'::interval)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
73 OVER loc_date_issue AS forecast_accuracy_1d,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
74 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
75 FROM (SELECT location, date_issue, measure_date,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
76 GREATEST(water_level - lower(conf_interval),
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
77 upper(conf_interval) - water_level) AS acc
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
78 FROM waterway.gauge_predictions
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
79 -- consider predictions made within last 14 days ...
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
80 WHERE date_issue
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
81 >= current_timestamp - '14 days 00:15'::interval
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
82 -- ... for the next three days from now
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
83 AND measure_date BETWEEN current_timestamp
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
84 AND current_timestamp + '3 days'::interval) AS acc
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
85 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
86 ORDER BY location, date_issue DESC) AS fca
4620
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4619
diff changeset
87 -- Show only forecasts issued with latest measurements or later
f77a6f9216ae Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4619
diff changeset
88 ON fca.location = g.location AND fca.date_issue >= wl.date_issue
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
89 $$);
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
90
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
91 INSERT INTO sys_admin.published_services (
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
92 schema, name, srid, key_column, view_def
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
93 ) VALUES
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
94 -- Directly accessed tables
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
95 ('waterway', 'waterway_axis', NULL, NULL, NULL),
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
96 ('waterway', 'waterway_area', NULL, NULL, NULL),
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
97 ('waterway', 'waterway_profiles', NULL, NULL, NULL),
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
98 ('waterway', 'fairway_dimensions', NULL, NULL, NULL),
4929
9b9b4150f296 Add fairway marks layers to GeoServer
Tom Gottfried <tom@intevation.de>
parents: 4787
diff changeset
99 ('waterway', 'fairway_marks_bcnlat', NULL, NULL, NULL),
9b9b4150f296 Add fairway marks layers to GeoServer
Tom Gottfried <tom@intevation.de>
parents: 4787
diff changeset
100 ('waterway', 'fairway_marks_boycar', NULL, NULL, NULL),
9b9b4150f296 Add fairway marks layers to GeoServer
Tom Gottfried <tom@intevation.de>
parents: 4787
diff changeset
101 ('waterway', 'fairway_marks_boylat', NULL, NULL, NULL),
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
102 -- GeoServer SQL views
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
103 ('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
104 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
105 isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
106 objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
107 geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
108 applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
109 applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
110 zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
111 geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
112 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
113 source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
114 reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
115 gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
116 gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
117 gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
118 forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
119 forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
120 FROM (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
121 $$ || (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
122 ) 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
123 WHERE NOT erased
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
124 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
125 ('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
126 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
127 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
128 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
129 (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
130 (s.section).upper::varchar as upper,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
131 s.area,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
132 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
133 s.nobjnam,
4742
b88ab93dcb2c Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents: 4688
diff changeset
134 s.country,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
135 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
136 s.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
137 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
138 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
139 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
140 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
141 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
142 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
143 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
144 $$ || (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
145 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
146 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
147 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
148 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
149 ('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
150 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
151 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
152 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
153 (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
154 (s.stretch).upper::varchar as upper,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
155 s.area,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
156 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
157 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
158 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
159 s.source_organization,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
160 (SELECT string_agg(country, ', ')
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
161 FROM users.stretch_countries
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4616
diff changeset
162 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
163 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
164 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
165 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
166 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
167 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
168 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
169 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
170 $$ || (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
171 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
172 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
173 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
174 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
175 ('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
176 SELECT id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
177 country,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
178 geom,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
179 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
180 hectom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
181 catdis,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
182 position_code
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
183 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
184 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
185 ('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
186 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
187 isrs_asText(location_code) AS location,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
188 geom,
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
189 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
190 (location_code).hectometre
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
191 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
192 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
193 ('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
194 SELECT bottleneck_id,
4787
3a8ec3c396e0 Fixed date_info in sounding result area geoserver view.
Raimund Renkert <raimund@renkert.org>
parents: 4744
diff changeset
195 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
196 height,
4688
ad271887fd8d Added FeatureInfo for sounding results including surtyp attribute.
Raimund Renkert <raimund@renkert.org>
parents: 4673
diff changeset
197 areas,
ad271887fd8d Added FeatureInfo for sounding results including surtyp attribute.
Raimund Renkert <raimund@renkert.org>
parents: 4673
diff changeset
198 surtyp
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
199 FROM waterway.sounding_results_iso_areas ia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
200 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
201 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
202 ('waterway', 'bottlenecks_geoserver', 4326, 'id', $$
4616
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
203 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
204 b.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
205 b.bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
206 b.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
207 b.nobjnm,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
208 b.area,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
209 b.rb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
210 b.lb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
211 b.responsible_country,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
212 b.revisiting_time,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
213 b.limiting,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
214 b.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
215 b.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
216 g.objname AS gauge_objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
217 g.reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
218 fal.date_info AS fa_date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
219 fal.critical AS fa_critical,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
220 g.gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
221 g.gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
222 g.gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
223 srl.date_max,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
224 g.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
225 g.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
226 FROM waterway.bottlenecks b
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
227 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
228 $$ || (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
229 ) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
230 ON b.gauge_location = g.location
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
231 AND g.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
232 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
233 bottleneck_id, date_info, critical
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
234 FROM waterway.fairway_availability
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
235 ORDER BY bottleneck_id, date_info DESC) AS fal
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
236 ON b.bottleneck_id = fal.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
237 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
238 bottleneck_id, max(date_info) AS date_max
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
239 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
240 GROUP BY bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
241 ORDER BY bottleneck_id DESC) AS srl
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
242 ON b.bottleneck_id = srl.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
243 WHERE b.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
244 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
245 ('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
246 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
247 objnam AS name,
4619
4476ec4db818 Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents: 4618
diff changeset
248 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
249 (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
250 (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
251 sr.current::text,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
252 responsible_country
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
253 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
254 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
255 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
256 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
257 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
258 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
259 ORDER BY objnam
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
260 $$),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4623
diff changeset
261 ('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
262 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents: 4615
diff changeset
263 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
264 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
265 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
266 srs.date_info AS subtrahend,
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
267 sdia.height AS height,
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
268 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
269 FROM caching.sounding_differences sd
4623
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
270 JOIN caching.sounding_differences_iso_areas sdia
30bb2d819d57 Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4620 4585
diff changeset
271 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
272 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
273 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
274 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
275 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
276 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
277 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
278 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
279 $$);
4364
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
280
496bbf0f618c Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents: 4330
diff changeset
281 --
4934
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
282 -- group layers
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
283 --
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
284 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
285
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
286 INSERT INTO sys_admin.grouped_layers VALUES
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
287 ('fairway_marks', 'waterway', 'fairway_marks_boylat', 0),
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
288 ('fairway_marks', 'waterway', 'fairway_marks_boycar', 1),
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
289 ('fairway_marks', 'waterway', 'fairway_marks_bcnlat', 2);
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
290
c6af373b0832 Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4929
diff changeset
291 --
3625
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
292 -- Settings
a688a478e35f implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents: 1301
diff changeset
293 --
3641
32d7bb1afdc9 client: configuration: support wms services with nested layers
Markus Kottlaender <markus@intevation.de>
parents: 3637
diff changeset
294 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
295 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
296 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
297 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
298 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
299 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
300 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
301 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
302 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
303 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
304 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
305 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
306 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
307 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
308 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
309 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
310 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
311 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
312 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
313 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
314 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
315 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
316 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
317 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
318
4330
fe01e997d66f adjust sys-config values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents: 4239
diff changeset
319 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
320 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
321 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
322 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
323 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
324 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
325
831
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
326 COMMIT;