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