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