Mercurial > gemma

comparison wamos.sql @ 81:acaa485c0c1e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use different schemas for different diagrams. Add TODOs.
author Tom Gottfried <tom@intevation.de>
date Thu, 31 May 2018 17:54:32 +0200
parents 004198a3cbc0
children c71e43f88ae0
comparison
equal deleted inserted replaced
80:004198a3cbc0 81:acaa485c0c1e
1 BEGIN; 1 BEGIN;
2 2
3 --
4 -- Infrastructure
5 --
3 CREATE EXTENSION postgis; 6 CREATE EXTENSION postgis;
4
5 CREATE SCHEMA wamos;
6 SET search_path TO public, wamos;
7 7
8 CREATE FUNCTION update_date_info() RETURNS trigger 8 CREATE FUNCTION update_date_info() RETURNS trigger
9 LANGUAGE plpgsql 9 LANGUAGE plpgsql
10 AS $$ 10 AS $$
11 BEGIN 11 BEGIN
27 ); 27 );
28 28
29 CREATE TYPE isrsrange AS RANGE ( 29 CREATE TYPE isrsrange AS RANGE (
30 subtype = isrs 30 subtype = isrs
31 ); 31 );
32
33 --
34 -- WAMOS data
35 --
36 CREATE SCHEMA wamos;
37 CREATE SCHEMA wamos_waterway;
38 CREATE SCHEMA wamos_fairway;
39 SET search_path TO public, wamos, wamos_waterway, wamos_fairway;
40
41 --
42 -- Auxilliary tables
43 --
44
45 -- TODO: Add table to manage users e-mail adresses (APUC9)
46 -- TODO: Add table for responsibility areas and link to users
47 -- TODO: Store reports/statistics templates per user (APUC6, SPUC13)
48
49 -- TODO: Do we need to store system config (APUC10) in database?
50 -- TODO: Will we store any pre-calculated cross sections in database (SPUC3)?
32 51
33 -- Eventually obsolete. 52 -- Eventually obsolete.
34 -- See https://roundup-intern.intevation.de/wamos/issue5 53 -- See https://roundup-intern.intevation.de/wamos/issue5
35 -- CREATE TABLE rwdrs ( 54 -- CREATE TABLE rwdrs (
36 -- stretch isrsrange PRIMARY KEY, 55 -- stretch isrsrange PRIMARY KEY,
53 ); 72 );
54 73
55 -- 74 --
56 -- General river information 75 -- General river information
57 -- 76 --
58 CREATE TABLE wamos.waterway_area ( 77 CREATE TABLE wamos_waterway.waterway_area (
59 dummy_attrib varchar, 78 dummy_attrib varchar,
60 "..." varchar 79 "..." varchar
61 -- TODO: add real waterway area attributes (DRC 2.1.3) 80 -- TODO: add real waterway area attributes (DRC 2.1.3)
62 ); 81 );
63 82
64 CREATE TABLE wamos.reference_water_levels ( 83 CREATE TABLE wamos_fairway.reference_water_levels (
65 reference_water_level varchar(20) PRIMARY KEY 84 reference_water_level varchar(20) PRIMARY KEY
66 ); 85 );
67 86
68 CREATE TABLE wamos.gauges ( 87 CREATE TABLE wamos_fairway.gauges (
69 location isrs PRIMARY KEY, 88 location isrs PRIMARY KEY,
70 dummy_attrib varchar, 89 dummy_attrib varchar,
71 "..." varchar 90 "..." varchar
72 -- TODO: add real gauge attributes (DRC 2.1.4) 91 -- TODO: add real gauge attributes (DRC 2.1.4)
73 ); 92 );
74 93
75 CREATE TABLE wamos.gauges_reference_water_levels ( 94 CREATE TABLE wamos_fairway.gauges_reference_water_levels (
76 gauge_id isrs NOT NULL REFERENCES gauges, 95 gauge_id isrs NOT NULL REFERENCES gauges,
77 reference_water_level varchar(20) 96 reference_water_level varchar(20)
78 NOT NULL REFERENCES reference_water_levels, 97 NOT NULL REFERENCES reference_water_levels,
79 PRIMARY KEY (gauge_id, reference_water_level), 98 PRIMARY KEY (gauge_id, reference_water_level),
80 value int NOT NULL 99 value int NOT NULL
81 ); 100 );
82 101
83 CREATE TABLE wamos.gauge_measurements ( 102 CREATE TABLE wamos_fairway.gauge_measurements (
84 fk_gauge_id isrs NOT NULL REFERENCES gauges, 103 fk_gauge_id isrs NOT NULL REFERENCES gauges,
85 -- XXX: Is country_code really relevant for WAMOS or just NtS? 104 -- XXX: Is country_code really relevant for WAMOS or just NtS?
86 -- country_code char(2) NOT NULL REFERENCES countries, 105 -- country_code char(2) NOT NULL REFERENCES countries,
87 -- TODO: add relations to stuff provided as enumerations 106 -- TODO: add relations to stuff provided as enumerations
88 dummy_attrib varchar, 107 dummy_attrib varchar,
89 "..." varchar 108 "..." varchar
90 -- TODO: add real gauge measurement attributes (DRC 2.1.5) 109 -- TODO: add real gauge measurement attributes (DRC 2.1.5)
91 ); 110 );
92 111
93 CREATE TABLE wamos.waterway_axis ( 112 CREATE TABLE wamos_waterway.waterway_axis (
94 dummy_attrib varchar, 113 dummy_attrib varchar,
95 "..." varchar 114 "..." varchar
96 -- TODO: add real waterway area attributes (DRC 2.1.3) 115 -- TODO: add real waterway area attributes (DRC 2.1.3)
97 ); 116 );
98 117
99 CREATE TABLE wamos.distance_mark_functions ( 118 CREATE TABLE wamos_waterway.distance_mark_functions (
100 -- XXX: Redundant information to object code in isrs code of dist. mark 119 -- XXX: Redundant information to object code in isrs code of dist. mark
101 distance_mark_function varchar(8) PRIMARY KEY 120 distance_mark_function varchar(8) PRIMARY KEY
102 ); 121 );
103 122
104 -- This table allows linkage between the 1D ISRS location codes and 2D space 123 -- This table allows linkage between the 1D ISRS location codes and 2D space
105 -- e.g. for cutting bottleneck area out of waterway area based on virtual 124 -- e.g. for cutting bottleneck area out of waterway area based on virtual
106 -- distance marks along waterway axis (see SUC7). 125 -- distance marks along waterway axis (see SUC7).
107 CREATE TABLE wamos.distance_marks ( 126 CREATE TABLE wamos_waterway.distance_marks (
108 location_code isrs PRIMARY KEY, 127 location_code isrs PRIMARY KEY,
109 geom geometry(POINT, 4326), 128 geom geometry(POINT, 4326),
110 distance_mark_function varchar(8) REFERENCES distance_mark_functions, 129 distance_mark_function varchar(8) REFERENCES distance_mark_functions,
111 -- TODO: add relations to stuff provided as enumerations 130 -- TODO: add relations to stuff provided as enumerations
112 dummy_attrib varchar, 131 dummy_attrib varchar,
113 "..." varchar 132 "..." varchar
114 -- TODO: add real distance mark attributes (DRC 2.1.7) 133 -- TODO: add real distance mark attributes (DRC 2.1.7)
115 ); 134 );
116 135
117 CREATE TABLE wamos.sections_stretches ( 136 CREATE TABLE wamos_waterway.sections_stretches (
118 id varchar PRIMARY KEY, 137 id varchar PRIMARY KEY,
119 is_section boolean NOT NULL, -- maps 'function' from interface 138 is_section boolean NOT NULL, -- maps 'function' from interface
120 stretch isrsrange, 139 stretch isrsrange,
121 dummy_attrib varchar, 140 dummy_attrib varchar,
122 "..." varchar 141 "..." varchar
123 -- TODO: add real section/stretch attributes (DRC 2.2.1) 142 -- TODO: add real section/stretch attributes (DRC 2.2.1)
124 ); 143 );
125 144
126 CREATE TABLE wamos.waterway_profiles ( 145 CREATE TABLE wamos_waterway.waterway_profiles (
127 dummy_attrib varchar, 146 dummy_attrib varchar,
128 "..." varchar 147 "..." varchar
129 -- TODO: add real profile attributes (DRC 2.2.3) 148 -- TODO: add real profile attributes (DRC 2.2.3)
130 ); 149 );
131 150
132 -- 151 --
133 -- Bottlenecks 152 -- Bottlenecks
134 -- 153 --
135 CREATE TABLE wamos.riverbed_materials ( 154 CREATE TABLE wamos_fairway.riverbed_materials (
136 material varchar PRIMARY KEY 155 material varchar PRIMARY KEY
137 -- XXX: Should this table contain choices from DRC 2.2.3 or 156 -- XXX: Should this table contain choices from DRC 2.2.3 or
138 -- from IENC Encoding Guide M.4.3, attribute NATSUR? 157 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
139 ); 158 );
140 159
141 CREATE TABLE wamos.survey_types ( 160 CREATE TABLE wamos_fairway.survey_types (
142 survey_type varchar PRIMARY KEY 161 survey_type varchar PRIMARY KEY
143 ); 162 );
144 163
145 CREATE TABLE wamos.coverage_types ( 164 CREATE TABLE wamos_fairway.coverage_types (
146 coverage_type varchar PRIMARY KEY 165 coverage_type varchar PRIMARY KEY
147 ); 166 );
148 167
149 CREATE TABLE wamos.limiting_factors ( 168 CREATE TABLE wamos_fairway.limiting_factors (
150 limiting_factor varchar PRIMARY KEY 169 limiting_factor varchar PRIMARY KEY
151 ); 170 );
152 171
153 CREATE TABLE wamos.depth_references ( 172 CREATE TABLE wamos_fairway.depth_references (
154 depth_reference varchar(4) PRIMARY KEY 173 depth_reference varchar(4) PRIMARY KEY
155 -- See col. AB and AI RIS-Index Encoding Guide 174 -- See col. AB and AI RIS-Index Encoding Guide
156 -- XXX: We need a way to distinguish between geodetic (eg. col. AP 175 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
157 -- RIS-Index) and other references (e.g. col. AB and AI): 176 -- RIS-Index) and other references (e.g. col. AB and AI):
158 -- _ multi-column FK with a boolean column (geodetic/non-geodetic; 177 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
162 ); 181 );
163 182
164 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL 183 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
165 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and 184 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
166 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) 185 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
167 CREATE TABLE wamos.bottlenecks ( 186 CREATE TABLE wamos_fairway.bottlenecks (
168 bottleneck_id varchar PRIMARY KEY, 187 bottleneck_id varchar PRIMARY KEY,
169 fk_g_fid isrs NOT NULL REFERENCES gauges, 188 fk_g_fid isrs NOT NULL REFERENCES gauges,
170 -- XXX: DRC references "ch. 3.1.1", which does not exist in document. 189 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
171 objnam varchar, 190 objnam varchar,
172 nobjnm varchar, 191 nobjnm varchar,
191 staging_done boolean NOT NULL DEFAULT false 210 staging_done boolean NOT NULL DEFAULT false
192 ); 211 );
193 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks 212 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
194 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); 213 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
195 214
196 CREATE TABLE wamos.bottlenecks_riverbed_materials ( 215 CREATE TABLE wamos_fairway.bottlenecks_riverbed_materials (
197 bottleneck_id varchar REFERENCES bottlenecks, 216 bottleneck_id varchar REFERENCES bottlenecks,
198 riverbed varchar REFERENCES riverbed_materials, 217 riverbed varchar REFERENCES riverbed_materials,
199 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 218 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
200 PRIMARY KEY (bottleneck_id, riverbed) 219 PRIMARY KEY (bottleneck_id, riverbed)
201 ); 220 );
202 221
203 CREATE TABLE wamos.sounding_results ( 222 CREATE TABLE wamos_fairway.sounding_results (
204 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 223 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
205 date_info date NOT NULL, 224 date_info date NOT NULL,
206 PRIMARY KEY (bottleneck_id, date_info), 225 PRIMARY KEY (bottleneck_id, date_info),
207 area geometry(POLYGON, 4326) NOT NULL, 226 area geometry(POLYGON, 4326) NOT NULL,
208 surtyp varchar NOT NULL REFERENCES survey_types, 227 surtyp varchar NOT NULL REFERENCES survey_types,
213 ); 232 );
214 233
215 -- 234 --
216 -- Fairway availability 235 -- Fairway availability
217 -- 236 --
218 CREATE TABLE wamos.spot_marks ( 237 CREATE TABLE wamos_fairway.spot_marks (
219 mark_name varchar PRIMARY KEY 238 mark_name varchar PRIMARY KEY
220 -- Use smallint because of fairway availability provided on daily basis? 239 -- Use smallint because of fairway availability provided on daily basis?
221 ); 240 );
222 241
223 CREATE TABLE wamos.levels_of_service ( 242 CREATE TABLE wamos_fairway.levels_of_service (
224 level_of_service smallint PRIMARY KEY 243 level_of_service smallint PRIMARY KEY
225 ); 244 );
226 245
227 CREATE TABLE wamos.measure_types ( 246 CREATE TABLE wamos_fairway.measure_types (
228 measure_type varchar PRIMARY KEY 247 measure_type varchar PRIMARY KEY
229 ); 248 );
230 249
231 CREATE TABLE wamos.fairway_dimensions ( 250 CREATE TABLE wamos_fairway.fairway_dimensions (
232 level_of_service smallint REFERENCES levels_of_service, 251 level_of_service smallint REFERENCES levels_of_service,
233 dummy_attrib varchar, 252 dummy_attrib varchar,
234 "..." varchar 253 "..." varchar
235 -- TODO: add real fairway dimension attributes (DRC 2.2.2) 254 -- TODO: add real fairway dimension attributes (DRC 2.2.2)
236 ); 255 );
237 256
238 CREATE TABLE wamos.fairway_availability ( 257 CREATE TABLE wamos_fairway.fairway_availability (
239 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 258 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
240 position varchar REFERENCES spot_marks, 259 position varchar REFERENCES spot_marks,
241 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 260 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
242 surdat date NOT NULL, 261 surdat date NOT NULL,
243 UNIQUE (bottleneck_id, surdat), 262 UNIQUE (bottleneck_id, surdat),
248 ); 267 );
249 CREATE TRIGGER fairway_availability_date_info 268 CREATE TRIGGER fairway_availability_date_info
250 BEFORE UPDATE ON fairway_availability 269 BEFORE UPDATE ON fairway_availability
251 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); 270 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
252 271
253 CREATE TABLE wamos.fa_reference_values ( 272 CREATE TABLE wamos_fairway.fa_reference_values (
254 fairway_availability_id int NOT NULL REFERENCES fairway_availability, 273 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
255 level_of_service smallint NOT NULL REFERENCES levels_of_service, 274 level_of_service smallint NOT NULL REFERENCES levels_of_service,
256 PRIMARY KEY (fairway_availability_id, level_of_service), 275 PRIMARY KEY (fairway_availability_id, level_of_service),
257 fairway_depth smallint, 276 fairway_depth smallint,
258 fairway_width smallint, 277 fairway_width smallint,
259 fairway_radius int, 278 fairway_radius int,
260 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), 279 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
261 shallowest_spot geometry(POINT, 4326) 280 shallowest_spot geometry(POINT, 4326)
262 ); 281 );
263 282
264 CREATE TABLE wamos.bottleneck_pdfs ( 283 CREATE TABLE wamos_fairway.bottleneck_pdfs (
265 fairway_availability_id int NOT NULL REFERENCES fairway_availability, 284 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
266 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL 285 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
267 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? 286 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
268 PRIMARY KEY (fairway_availability_id, profile_pdf_url), 287 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
269 pdf_generation_date timestamp with time zone NOT NULL, 288 pdf_generation_date timestamp with time zone NOT NULL,
270 source_organization varchar NOT NULL 289 source_organization varchar NOT NULL
271 ); 290 );
272 291
273 CREATE TABLE wamos.effective_fairway_availability ( 292 CREATE TABLE wamos_fairway.effective_fairway_availability (
274 fairway_availability_id int NOT NULL REFERENCES fairway_availability, 293 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
275 measure_date timestamp with time zone NOT NULL, 294 measure_date timestamp with time zone NOT NULL,
276 level_of_service smallint NOT NULL REFERENCES levels_of_service, 295 level_of_service smallint NOT NULL REFERENCES levels_of_service,
277 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service), 296 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
278 available_depth_value smallint, 297 available_depth_value smallint,