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