Mercurial > gemma
comparison schema/geoserver_views.sql @ 3008:f394e828a6d2
Separate view definitions for GeoServer from general schema definition
That way OR REPLACE can be used which makes development on the
views a lot easier, since the views can be altered without having
to set up a completely new database each time.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 11 Apr 2019 12:01:27 +0200 |
parents | |
children | ddc4d54b8331 |
comparison
equal
deleted
inserted
replaced
3007:792d4476d5d5 | 3008:f394e828a6d2 |
---|---|
1 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS | |
2 SELECT | |
3 g.location, | |
4 isrs_asText(g.location) AS isrs_code, | |
5 g.objname, | |
6 g.geom, | |
7 g.applicability_from_km, | |
8 g.applicability_to_km, | |
9 g.validity, | |
10 g.zero_point, | |
11 g.geodref, | |
12 g.date_info, | |
13 g.source_organization, | |
14 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), | |
15 r.value)) | |
16 AS reference_water_levels | |
17 FROM waterway.gauges g LEFT JOIN LATERAL ( | |
18 SELECT gauge_id, depth_reference, value | |
19 FROM waterway.gauges_reference_water_levels | |
20 ) r ON r.gauge_id = g.location | |
21 GROUP BY g.location; | |
22 | |
23 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS | |
24 SELECT location_code, | |
25 isrs_asText(location_code) AS location, | |
26 geom::Geometry(POINT, 4326), | |
27 related_enc, | |
28 (location_code).hectometre | |
29 FROM waterway.distance_marks_virtual; | |
30 | |
31 CREATE OR REPLACE VIEW waterway.distance_marks_ashore_geoserver AS | |
32 SELECT id, | |
33 country, | |
34 geom::Geometry(POINT, 4326), | |
35 related_enc, | |
36 hectom, | |
37 catdis, | |
38 position_code | |
39 FROM waterway.distance_marks; | |
40 | |
41 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS | |
42 WITH | |
43 fairway_availability_latest AS ( | |
44 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical | |
45 FROM waterway.fairway_availability | |
46 ORDER BY bottleneck_id, date_info DESC), | |
47 waterlevel_latest AS ( | |
48 SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level | |
49 FROM waterway.gauge_measurements | |
50 WHERE is_waterlevel AND NOT predicted | |
51 ORDER BY fk_gauge_id, measure_date DESC) | |
52 SELECT | |
53 b.id, | |
54 b.bottleneck_id, | |
55 b.objnam, | |
56 b.nobjnm, | |
57 b.stretch, | |
58 b.area, | |
59 b.rb, | |
60 b.lb, | |
61 b.responsible_country, | |
62 b.revisiting_time, | |
63 b.limiting, | |
64 b.date_info, | |
65 b.source_organization, | |
66 g.location AS gauge_isrs_code, | |
67 g.objname AS gauge_objname, | |
68 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), | |
69 r.value)) | |
70 AS reference_water_levels, | |
71 fal.date_info AS fa_date_info, | |
72 fal.critical AS fa_critical, | |
73 wl.water_level AS gm_waterlevel | |
74 FROM waterway.bottlenecks b | |
75 LEFT JOIN waterway.gauges g | |
76 ON b.fk_g_fid = g.location | |
77 LEFT JOIN waterway.gauges_reference_water_levels r | |
78 ON g.location = r.gauge_id | |
79 LEFT JOIN fairway_availability_latest fal | |
80 ON b.id = fal.bottleneck_id | |
81 LEFT JOIN waterlevel_latest wl | |
82 ON b.fk_g_fid = wl.fk_gauge_id | |
83 GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level; | |
84 | |
85 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS | |
86 SELECT | |
87 id, | |
88 name, | |
89 (stretch).lower::varchar as lower, | |
90 (stretch).upper::varchar as upper, | |
91 area::Geometry(MULTIPOLYGON, 4326), | |
92 objnam, | |
93 nobjnam, | |
94 date_info, | |
95 source_organization, | |
96 (SELECT string_agg(country_code, ', ') | |
97 FROM waterway.stretch_countries | |
98 WHERE stretches_id = id) AS countries, | |
99 staging_done | |
100 FROM waterway.stretches; | |
101 | |
102 CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS | |
103 SELECT bottleneck_id, | |
104 date_info, | |
105 height, | |
106 CAST(lines AS geometry(multilinestring, 4326)) AS lines | |
107 FROM waterway.sounding_results_contour_lines cl | |
108 JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id; | |
109 | |
110 CREATE OR REPLACE VIEW waterway.bottleneck_overview AS | |
111 SELECT | |
112 objnam AS name, | |
113 ST_Centroid(area)::Geometry(POINT, 4326) AS point, | |
114 (lower(stretch)).hectometre AS from, | |
115 (upper(stretch)).hectometre AS to, | |
116 sr.current::text, | |
117 responsible_country | |
118 FROM waterway.bottlenecks bn LEFT JOIN ( | |
119 SELECT bottleneck_id, max(date_info) AS current | |
120 FROM waterway.sounding_results | |
121 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id | |
122 ORDER BY objnam; | |
123 | |
124 CREATE OR REPLACE VIEW waterway.sounding_differences AS | |
125 SELECT | |
126 sd.id AS id, | |
127 bn.objnam AS objnam, | |
128 srm.date_info AS minuend, | |
129 srs.date_info AS subtrahend, | |
130 sdcl.height AS height, | |
131 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines | |
132 FROM | |
133 caching.sounding_differences sd | |
134 JOIN caching.sounding_differences_contour_lines sdcl | |
135 ON sd.id = sdcl.sounding_differences_id | |
136 JOIN waterway.sounding_results srm | |
137 ON sd.minuend = srm.id | |
138 JOIN waterway.sounding_results srs | |
139 ON sd.subtrahend = srs.id | |
140 JOIN waterway.bottlenecks bn | |
141 ON srm.bottleneck_id = bn.id; |