Mercurial > gemma

comparison schema/gemma.sql @ 115:d349db18bece

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
s/wamos/gemma/g on database schema.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 26 Jun 2018 16:20:04 +0200
parents schema/wamos.sql@25b28fd0e256
children 731fb359b29c
comparison
equal deleted inserted replaced
114:79f5ba414586 115:d349db18bece
1 BEGIN;
2
3 --
4 -- Infrastructure
5 --
6 CREATE EXTENSION postgis;
7
8 -- TODO: will there ever be UPDATEs or can we drop that function due to
9 -- historicisation?
10 CREATE FUNCTION update_date_info() RETURNS trigger
11 LANGUAGE plpgsql
12 AS $$
13 BEGIN
14 NEW.date_info = CURRENT_TIMESTAMP;
15 RETURN NEW;
16 END;
17 $$;
18
19 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
20 -- See RIS-Index Encoding Guide
21 CREATE TYPE isrs AS (
22 country_code char(2), -- ISO 3166 country code
23 -- could be validated against countries table.
24 locode char(3), -- without the country code:
25 -- http://www.unece.org/cefact/locode/welcome.html
26 fairway_section char(5),
27 object_reference char(5),
28 hectometre int -- should be constrained to five digits
29 );
30
31 CREATE TYPE isrsrange AS RANGE (
32 subtype = isrs
33 );
34
35 --
36 -- GEMMA data
37 --
38 CREATE SCHEMA gemma;
39 CREATE SCHEMA gemma_waterway;
40 CREATE SCHEMA gemma_fairway;
41 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
42 -- TODO: consolidate schemas. The current distribution of tables is mainly
43 -- for diagram generation. A privilege based distribution might ease
44 -- privilege management.
45
46 --
47 -- Auxiliary tables
48 --
49 CREATE TABLE gemma.system_config (
50 config_key varchar PRIMARY KEY,
51 config_val varchar
52 );
53
54 CREATE TABLE gemma.countries (
55 country_code char(2) PRIMARY KEY -- ISO 3166 country code
56 -- A smallint PK would require even less disk space i.e. on the FK side.
57 -- This might be an issue in case cache space becomes a limiting
58 -- factor when there are many FKs pointing here.
59 );
60
61 CREATE TABLE gemma.responsibility_areas (
62 country char(2) PRIMARY KEY REFERENCES countries,
63 area geometry(POLYGON, 4326) --XXX: Should be geography (elsewhere too)
64 );
65
66 CREATE TABLE gemma.language_codes (
67 language_code varchar PRIMARY KEY
68 );
69
70 CREATE TABLE gemma.user_profiles (
71 username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
72 country char(2) NOT NULL REFERENCES countries,
73 language_code varchar REFERENCES language_codes,
74 map_extent box2d,
75 email_adress varchar NOT NULL UNIQUE
76 );
77
78 CREATE TABLE gemma.templates (
79 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
80 dummy_attrib varchar,
81 "..." varchar
82 -- TODO: template attributes tbd.
83 );
84
85 CREATE TABLE gemma.user_templates (
86 username varchar NOT NULL REFERENCES user_profiles,
87 template_id int NOT NULL REFERENCES templates,
88 PRIMARY KEY (username, template_id)
89 );
90
91 --
92 -- General river information
93 --
94
95 -- Eventually obsolete.
96 -- See https://roundup-intern.intevation.de/gemma/issue5
97 -- CREATE TABLE rwdrs (
98 -- stretch isrsrange PRIMARY KEY,
99 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
100 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index
101 -- -- below anyhow.
102 -- -- Is it a good idea to build B-tree indexes on relatively large
103 -- -- composites of string values or should we use inter PKs?
104 -- -- => In case the index is used and cache space becomes a limiting
105 -- -- factor, this might be an issue.
106 -- rwdr double precision NOT NULL,
107 -- EXCLUDE USING GIST (stretch WITH &&)
108 -- );
109
110 CREATE TABLE gemma_waterway.catccls (
111 catccl smallint PRIMARY KEY
112 -- TODO: Do we need name and/or definition from IENC feature catalogue?
113 );
114
115 CREATE TABLE gemma_waterway.dirimps (
116 dirimp smallint PRIMARY KEY
117 -- TODO: Do we need name and/or definition from IENC feature catalogue?
118 );
119
120 CREATE TABLE gemma_waterway.waterway_area (
121 area geometry(POLYGON, 4326) PRIMARY KEY,
122 catccl smallint REFERENCES catccls,
123 dirimp smallint REFERENCES dirimps
124 );
125
126 CREATE TABLE gemma_fairway.depth_references (
127 depth_reference varchar(4) PRIMARY KEY
128 -- See col. AB and AI RIS-Index Encoding Guide
129 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
130 -- RIS-Index) and other references (e.g. col. AB and AI):
131 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
132 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
133 -- _ Do not mixup things with different meanings in one table at all
134 -- (which would mean a model differing a bit from RIS-Index ideas)
135 );
136
137 CREATE TABLE gemma_fairway.reference_water_levels (
138 reference_water_level varchar(20) PRIMARY KEY
139 );
140
141 CREATE TABLE gemma_fairway.gauges (
142 location isrs PRIMARY KEY,
143 function_code varchar(10) NOT NULL, -- XXX: What is this really for?
144 objname varchar NOT NULL,
145 is_left boolean, -- XXX: Or reference position_codes?
146 geom geometry(POINT, 4326) NOT NULL,
147 applicability isrsrange,
148 validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy
149 -- pasted text from a more general specification is given
150 -- (a gauge is not a berth!)
151 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
152 zero_point double precision NOT NULL,
153 geodref varchar(4) REFERENCES depth_references,
154 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
155 source_organization varchar NOT NULL
156 );
157 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
158 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
159
160 CREATE TABLE gemma_fairway.gauges_reference_water_levels (
161 gauge_id isrs NOT NULL REFERENCES gauges,
162 reference_water_level varchar(20)
163 NOT NULL REFERENCES reference_water_levels,
164 PRIMARY KEY (gauge_id, reference_water_level),
165 value int NOT NULL
166 );
167
168 CREATE TABLE gemma_fairway.gauge_measurements (
169 fk_gauge_id isrs NOT NULL REFERENCES gauges,
170 measure_date timestamp with time zone NOT NULL,
171 PRIMARY KEY (fk_gauge_id, measure_date),
172 -- XXX: Is country_code really relevant for GEMMA or just NtS?
173 -- country_code char(2) NOT NULL REFERENCES countries,
174 -- TODO: add relations to stuff provided as enumerations
175 sender varchar NOT NULL, -- "from" attribute from DRC
176 language_code varchar NOT NULL REFERENCES language_codes,
177 date_issue timestamp with time zone NOT NULL,
178 -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
179 -- XXX: Always ZPG?
180 water_level double precision NOT NULL,
181 predicted boolean NOT NULL,
182 is_waterlevel boolean NOT NULL,
183 -- XXX: "measure_code" if really only W or Q
184 -- XXX: Do we need "unit" attribute or can we normalise on import?
185 value_min double precision, -- XXX: NOT NULL if predicted?
186 value_max double precision, -- XXX: NOT NULL if predicted?
187 --- TODO: Add a double range type for checking?
188 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
189 source_organization varchar NOT NULL -- "originator"
190 );
191 CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements
192 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
193
194 CREATE TABLE gemma_waterway.waterway_axis (
195 wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
196 -- TODO: Do we need to check data set quality as described in DRC 2.1.6?
197 objnam varchar NOT NULL,
198 nobjnam varchar
199 );
200
201 CREATE TABLE gemma_waterway.distance_mark_functions (
202 -- XXX: Redundant information to object code in isrs code of dist. mark
203 distance_mark_function varchar(8) PRIMARY KEY
204 );
205
206 CREATE TABLE gemma_waterway.position_codes (
207 position_code char(2) PRIMARY KEY
208 -- Use smallint because of fairway availability provided on daily basis?
209 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
210 -- sheet "Position_code" or RIS-Index encoding guide?
211 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
212 -- Clarify!
213 -- TODO: Do we need an attribute "meaning" or so?
214 );
215
216 -- This table allows linkage between the 1D ISRS location codes and 2D space
217 -- e.g. for cutting bottleneck area out of waterway area based on virtual
218 -- distance marks along waterway axis (see SUC7).
219 CREATE TABLE gemma_waterway.distance_marks (
220 location_code isrs PRIMARY KEY,
221 geom geometry(POINT, 4326) NOT NULL,
222 distance_mark_function varchar(8)
223 NOT NULL REFERENCES distance_mark_functions,
224 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
225 -- to encode the same thing as the object code in ISRS location code.
226 position_code char(2) NOT NULL REFERENCES position_codes
227 );
228
229 CREATE TABLE gemma_waterway.sections_stretches (
230 id varchar PRIMARY KEY,
231 is_section boolean NOT NULL, -- maps 'function' from interface
232 stretch isrsrange,
233 objnam varchar NOT NULL,
234 nobjnam varchar,
235 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
236 source_organization varchar NOT NULL
237 );
238 CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches
239 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
240
241 CREATE TABLE gemma_waterway.waterway_profiles (
242 location isrs NOT NULL,
243 validity tstzrange,
244 EXCLUDE USING GIST (validity WITH &&),
245 PRIMARY KEY (location, validity),
246 lnwl smallint,
247 mwl smallint,
248 hnwl smallint,
249 fe30 smallint,
250 fe100 smallint,
251 -- XXX: further normalise using reference_water_levels?
252 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
253 OR validity IS NOT NULL),
254 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
255 source_organization varchar NOT NULL
256 );
257 CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles
258 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
259
260 CREATE TABLE gemma_waterway.levels_of_service (
261 level_of_service smallint PRIMARY KEY
262 );
263
264 CREATE TABLE gemma_waterway.fairway_dimensions (
265 area geometry(POLYGON, 4326) PRIMARY KEY,
266 level_of_service smallint NOT NULL REFERENCES levels_of_service,
267 min_width smallint NOT NULL,
268 max_width smallint NOT NULL,
269 min_depth smallint NOT NULL,
270 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
271 source_organization varchar NOT NULL
272 );
273 CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions
274 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
275
276 --
277 -- Bottlenecks
278 --
279 CREATE TABLE gemma_fairway.riverbed_materials (
280 material varchar PRIMARY KEY
281 -- XXX: Should this table contain choices from DRC 2.2.3 or
282 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
283 );
284
285 CREATE TABLE gemma_fairway.survey_types (
286 survey_type varchar PRIMARY KEY
287 );
288
289 CREATE TABLE gemma_fairway.coverage_types (
290 coverage_type varchar PRIMARY KEY
291 );
292
293 CREATE TABLE gemma_fairway.limiting_factors (
294 limiting_factor varchar PRIMARY KEY
295 );
296
297 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
298 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
299 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
300 CREATE TABLE gemma_fairway.bottlenecks (
301 bottleneck_id varchar PRIMARY KEY,
302 fk_g_fid isrs NOT NULL REFERENCES gauges,
303 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
304 objnam varchar,
305 nobjnm varchar,
306 stretch isrsrange NOT NULL,
307 area geometry(POLYGON, 4326) NOT NULL,
308 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
309 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
310 responsible_country char(2) NOT NULL REFERENCES countries,
311 revisiting_time smallint NOT NULL,
312 limiting varchar NOT NULL REFERENCES limiting_factors,
313 -- surtyp varchar NOT NULL REFERENCES survey_types,
314 -- XXX: Also an attribut of sounding result?
315 -- coverage varchar REFERENCES coverage_types,
316 -- XXX: Also an attribut of sounding result?
317 -- CHECK allowed combinations of surtyp and coverage or
318 -- different model approach?
319 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
320 -- XXX: Also an attribut of sounding result?
321 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
322 source_organization varchar NOT NULL,
323 -- additional_data xml -- Currently not relevant for GEMMA
324 staging_done boolean NOT NULL DEFAULT false
325 );
326 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
327 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
328
329 CREATE TABLE gemma_fairway.bottlenecks_riverbed_materials (
330 bottleneck_id varchar REFERENCES bottlenecks,
331 riverbed varchar REFERENCES riverbed_materials,
332 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
333 PRIMARY KEY (bottleneck_id, riverbed)
334 );
335
336 CREATE TABLE gemma_fairway.sounding_results (
337 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
338 date_info date NOT NULL,
339 PRIMARY KEY (bottleneck_id, date_info),
340 area geometry(POLYGON, 4326) NOT NULL,
341 surtyp varchar NOT NULL REFERENCES survey_types,
342 coverage varchar REFERENCES coverage_types,
343 depth_reference char(3) NOT NULL REFERENCES depth_references,
344 sounding_data raster NOT NULL,
345 staging_done boolean NOT NULL DEFAULT false
346 );
347
348 --
349 -- Fairway availability
350 --
351 CREATE TABLE gemma_fairway.measure_types (
352 measure_type varchar PRIMARY KEY
353 );
354
355 CREATE TABLE gemma_fairway.fairway_availability (
356 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
357 position_code char(2) REFERENCES position_codes,
358 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
359 surdat date NOT NULL,
360 UNIQUE (bottleneck_id, surdat),
361 -- additional_data xml -- Currently not relevant for GEMMA
362 critical boolean,
363 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
364 source_organization varchar NOT NULL
365 );
366 CREATE TRIGGER fairway_availability_date_info
367 BEFORE UPDATE ON fairway_availability
368 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
369
370 CREATE TABLE gemma_fairway.fa_reference_values (
371 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
372 level_of_service smallint NOT NULL REFERENCES levels_of_service,
373 PRIMARY KEY (fairway_availability_id, level_of_service),
374 fairway_depth smallint,
375 fairway_width smallint,
376 fairway_radius int,
377 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
378 shallowest_spot geometry(POINT, 4326)
379 );
380
381 CREATE TABLE gemma_fairway.bottleneck_pdfs (
382 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
383 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
384 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
385 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
386 pdf_generation_date timestamp with time zone NOT NULL,
387 source_organization varchar NOT NULL
388 );
389
390 CREATE TABLE gemma_fairway.effective_fairway_availability (
391 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
392 measure_date timestamp with time zone NOT NULL,
393 level_of_service smallint NOT NULL REFERENCES levels_of_service,
394 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
395 available_depth_value smallint,
396 available_width_value smallint,
397 water_level_value smallint,
398 CHECK(COALESCE(available_depth_value, available_width_value,
399 water_level_value) IS NOT NULL),
400 measure_type varchar NOT NULL REFERENCES measure_types,
401 source_organization varchar NOT NULL,
402 forecast_generation_time timestamp with time zone,
403 CHECK(measure_type <> 'forecasted'
404 OR forecast_generation_time IS NOT NULL),
405 value_lifetime timestamp with time zone,
406 CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
407 );
408
409 COMMIT;