Mercurial > gemma

comparison schema/geoserver_views.sql @ 3008:f394e828a6d2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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;