Mercurial > gemma
annotate schema/default_sysconfig.sql @ 5093:66270586031a
Adjusted message when review enqueuing is done.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 24 Mar 2020 21:32:10 +0100 |
parents | 8dc27fc1d05c |
children | f11b9b50fcc9 |
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 |
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'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
115 ('waterway', 'waterway_profiles'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
116 ('waterway', 'fairway_dimensions'); |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
117 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
118 -- 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
|
119 INSERT INTO sys_admin.published_services ( |
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4623
diff
changeset
|
120 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
|
121 ) VALUES |
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4623
diff
changeset
|
122 ('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
|
123 SELECT |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
124 isrs_code, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
125 objname, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
126 geom, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
127 applicability_from_km, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
128 applicability_to_km, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
129 zero_point, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
130 geodref, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
131 date_info, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
132 source_organization, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
133 reference_water_levels, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
134 gm_measuredate, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
135 gm_waterlevel, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
136 gm_n_14d, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
137 forecast_accuracy_3d, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
138 forecast_accuracy_1d |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
139 FROM ( |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
140 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
141 ) 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
|
142 WHERE NOT erased |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
143 $$), |
4673
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4623
diff
changeset
|
144 ('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
|
145 SELECT |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
146 s.id, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
147 s.name, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
148 (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
|
149 (s.section).upper::varchar as upper, |
4619
4476ec4db818
Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents:
4618
diff
changeset
|
150 s.area, |
4616
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
151 s.objnam, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
152 s.nobjnam, |
4742
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
4688
diff
changeset
|
153 s.country, |
4616
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
154 s.date_info, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
155 s.source_organization, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
156 s.staging_done, |
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_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
|
158 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
|
159 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
|
160 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
|
161 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
|
162 LEFT JOIN ( |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
163 $$ || (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
|
164 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
|
165 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
|
166 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
|
167 $$), |
4673
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4623
diff
changeset
|
168 ('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
|
169 SELECT |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
170 s.id, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
171 s.name, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
172 (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
|
173 (s.stretch).upper::varchar as upper, |
4619
4476ec4db818
Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents:
4618
diff
changeset
|
174 s.area, |
4616
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
175 s.objnam, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
176 s.nobjnam, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
177 s.date_info, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
178 s.source_organization, |
4618
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents:
4616
diff
changeset
|
179 (SELECT string_agg(country, ', ') |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents:
4616
diff
changeset
|
180 FROM users.stretch_countries |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents:
4616
diff
changeset
|
181 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
|
182 s.staging_done, |
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_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
|
184 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
|
185 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
|
186 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
|
187 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
|
188 LEFT JOIN ( |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
189 $$ || (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
|
190 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
|
191 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
|
192 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
|
193 $$), |
4673
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4623
diff
changeset
|
194 ('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
|
195 SELECT id, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
196 country, |
4619
4476ec4db818
Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents:
4618
diff
changeset
|
197 geom, |
4616
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
198 related_enc, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
199 hectom, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
200 catdis, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
201 position_code |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
202 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
|
203 $$), |
4673
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4623
diff
changeset
|
204 ('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
|
205 SELECT |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
206 isrs_asText(location_code) AS location, |
4619
4476ec4db818
Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents:
4618
diff
changeset
|
207 geom, |
4616
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
208 related_enc, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
209 (location_code).hectometre |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
210 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
|
211 $$), |
4673
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4623
diff
changeset
|
212 ('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
|
213 SELECT bottleneck_id, |
4787
3a8ec3c396e0
Fixed date_info in sounding result area geoserver view.
Raimund Renkert <raimund@renkert.org>
parents:
4744
diff
changeset
|
214 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
|
215 height, |
4688
ad271887fd8d
Added FeatureInfo for sounding results including surtyp attribute.
Raimund Renkert <raimund@renkert.org>
parents:
4673
diff
changeset
|
216 areas, |
ad271887fd8d
Added FeatureInfo for sounding results including surtyp attribute.
Raimund Renkert <raimund@renkert.org>
parents:
4673
diff
changeset
|
217 surtyp |
4623
30bb2d819d57
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
218 FROM waterway.sounding_results_iso_areas ia |
30bb2d819d57
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
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, |
4619
4476ec4db818
Remove unnecessary type casts
Tom Gottfried <tom@intevation.de>
parents:
4618
diff
changeset
|
224 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
|
225 (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
|
226 (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
|
227 sr.current::text, |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
228 responsible_country |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
229 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
|
230 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
|
231 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
|
232 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
|
233 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
|
234 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
|
235 ORDER BY objnam |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
236 $$), |
4673
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4623
diff
changeset
|
237 ('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
|
238 SELECT |
b605e91f08f0
Use GeoServer's SQL view feature instead of in-database VIEWs
Tom Gottfried <tom@intevation.de>
parents:
4615
diff
changeset
|
239 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
|
240 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
|
241 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
|
242 srs.date_info AS subtrahend, |
4623
30bb2d819d57
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
243 sdia.height AS height, |
30bb2d819d57
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
244 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
|
245 FROM caching.sounding_differences sd |
4623
30bb2d819d57
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
246 JOIN caching.sounding_differences_iso_areas sdia |
30bb2d819d57
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
247 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
|
248 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
|
249 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
|
250 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
|
251 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
|
252 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
|
253 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
|
254 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
|
255 $$); |
4364
496bbf0f618c
Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents:
4330
diff
changeset
|
256 |
4998
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
257 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
258 -- GeoServer SQL views with time support |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
259 INSERT INTO sys_admin.published_services ( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
260 schema, name, srid, key_column, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
261 wmst_attribute, wmst_end_attribute, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
262 view_def |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
263 ) VALUES |
5092
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
264 ('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
|
265 '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
|
266 SELECT |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
267 b.id, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
268 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
|
269 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
|
270 b.bottleneck_id, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
271 b.objnam, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
272 b.nobjnm, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
273 b.area, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
274 b.rb, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
275 b.lb, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
276 b.responsible_country, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
277 b.revisiting_time, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
278 b.limiting, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
279 b.date_info, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
280 b.source_organization, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
281 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
|
282 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
|
283 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
|
284 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
|
285 g.gm_measuredate, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
286 g.gm_waterlevel, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
287 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
|
288 srl.date_max, |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
289 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
|
290 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
|
291 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
|
292 LEFT JOIN ( |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
293 $$ || (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
|
294 ) AS g |
8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
295 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
|
296 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
|
297 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
|
298 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
|
299 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
|
300 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
|
301 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
|
302 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
|
303 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
|
304 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
|
305 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
|
306 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
|
307 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
|
308 $$), |
5016
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
309 ('waterway', 'waterway_axis', 4326, 'id', |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
310 'valid_from', 'valid_to', $$ |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
311 SELECT id, |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
312 lower(validity) AS valid_from, |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
313 COALESCE(upper(validity), current_timestamp) AS valid_to, |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
314 wtwaxs, |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
315 objnam, |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
316 nobjnam |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
317 FROM waterway.waterway_axis |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4998
diff
changeset
|
318 $$), |
4998
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
319 ('waterway', 'fairway_marks_bcnlat_hydro', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
320 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
321 (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
|
322 'colour, colpat, condtn, bcnshp, catlam', 'bcnlat_hydro')), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
323 ('waterway', 'fairway_marks_bcnlat_ienc', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
324 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
325 (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
|
326 $$colour, colpat, condtn, bcnshp, catlam, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
327 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
|
328 $$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
|
329 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
|
330 ('waterway', 'fairway_marks_boycar', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
331 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
332 (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
|
333 'colour, colpat, conrad, marsys, boyshp, catcam', 'boycar')), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
334 ('waterway', 'fairway_marks_boylat_hydro', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
335 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
336 (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
|
337 '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
|
338 ('waterway', 'fairway_marks_boylat_ienc', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
339 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
340 (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
|
341 '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
|
342 ('waterway', 'fairway_marks_boysaw', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
343 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
344 (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
|
345 'colour, colpat, conrad, marsys, boyshp', 'boysaw')), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
346 ('waterway', 'fairway_marks_boyspp', 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, catspm', 'boyspp')), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
350 ('waterway', 'fairway_marks_daymar_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, condtn, topshp', 'daymar_hydro')), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
354 ('waterway', 'fairway_marks_daymar_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, condtn, topshp, orient, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
358 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
|
359 $$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
|
360 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
|
361 ('waterway', 'fairway_marks_lights', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
362 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
363 (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
|
364 $$colour, condtn, orient, catlit, exclit, litchr, litvis, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
365 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status$$, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
366 'lights')), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
367 ('waterway', 'fairway_marks_rtpbcn', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
368 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
369 (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
|
370 'condtn, siggrp, catrtb, radwal', 'rtpbcn')), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
371 ('waterway', 'fairway_marks_topmar', 4326, 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
372 'valid_from', 'valid_to', format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
373 (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
|
374 'colour, colpat, condtn, topshp', 'topmar')), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
375 ('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
|
376 format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
377 (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
|
378 $$condtn, marsys, orient, status, addmrk, catnmk, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
379 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
4992
diff
changeset
|
380 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
|
381 $$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
|
382 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
|
383 |
4364
496bbf0f618c
Move definition of published services to default configuration
Tom Gottfried <tom@intevation.de>
parents:
4330
diff
changeset
|
384 -- |
4934
c6af373b0832
Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4929
diff
changeset
|
385 -- group layers |
c6af373b0832
Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4929
diff
changeset
|
386 -- |
c6af373b0832
Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4929
diff
changeset
|
387 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
|
388 |
c6af373b0832
Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4929
diff
changeset
|
389 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
|
390 ('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
|
391 ('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
|
392 ('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
|
393 ('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
|
394 ('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
|
395 ('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
|
396 ('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
|
397 ('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
|
398 ('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
|
399 ('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
|
400 ('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
|
401 ('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
|
402 ('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
|
403 |
c6af373b0832
Added fairwaymarks group layer in configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4929
diff
changeset
|
404 -- |
3625
a688a478e35f
implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents:
1301
diff
changeset
|
405 -- Settings |
a688a478e35f
implemented configuration backend and frontend
Markus Kottlaender <markus@intevation.de>
parents:
1301
diff
changeset
|
406 -- |
3641
32d7bb1afdc9
client: configuration: support wms services with nested layers
Markus Kottlaender <markus@intevation.de>
parents:
3637
diff
changeset
|
407 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
|
408 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
|
409 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
|
410 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
|
411 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
|
412 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
|
413 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
|
414 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
|
415 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
|
416 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
|
417 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
|
418 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
|
419 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
|
420 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
|
421 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
|
422 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
|
423 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
|
424 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
|
425 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
|
426 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
|
427 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
|
428 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
|
429 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
|
430 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
|
431 |
4330
fe01e997d66f
adjust sys-config values for geo styling in schema
Fadi Abbud <fadi.abbud@intevation.de>
parents:
4239
diff
changeset
|
432 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
|
433 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
|
434 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
|
435 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
|
436 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
|
437 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
|
438 |
831
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
439 COMMIT; |