Mercurial > gemma

comparison wamos.sql @ 35:62e14b4d25fc

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
First working draft of schema for bottlenecks.
author Tom Gottfried <tom@intevation.de>
date Thu, 03 May 2018 18:08:07 +0200
parents
children 6f273a649f08
comparison
equal deleted inserted replaced
-1:000000000000 35:62e14b4d25fc
1 BEGIN;
2
3 CREATE EXTENSION postgis;
4
5 CREATE FUNCTION update_date_info() RETURNS trigger
6 LANGUAGE plpgsql
7 AS $$
8 BEGIN
9 NEW.date_info = CURRENT_TIMESTAMP;
10 RETURN NEW;
11 END;
12 $$;
13
14 CREATE TYPE isrs AS (
15 country_code char(2),
16 locode varchar,
17 fairway_section varchar,
18 object_reference varchar,
19 hectometre int
20 ); -- Likely a composite type with UN/LOCODE, fairway section,
21 -- object reference, hectometre. See RIS-Index Encoding Guide
22
23 CREATE TYPE isrsrange AS RANGE (
24 subtype = isrs
25 );
26 -- Maybe we should bundle types etc. in an EXTENSION 'postris'?
27
28 CREATE TABLE rwdrs (
29 stretch isrsrange PRIMARY KEY,
30 -- XXX: https://www.postgresql.org/docs/10/static/sql-createindex.html:
31 -- Only B-tree supports UNIQUE indexes!
32 -- Is it a good idea to build B-tree indexes on relatively large
33 -- string values or should we use inter PKs? What were our thoughts
34 -- for EUMETSAT? => see section 2.5 in respective report
35 rwdr double precision NOT NULL,
36 EXCLUDE USING GIST (stretch WITH &&)
37 );
38
39 CREATE TABLE gauges (
40 location isrs PRIMARY KEY
41 );
42
43 CREATE TABLE countries (
44 country_code char(2) PRIMARY KEY
45 -- XXX: smallint would require even less disk space i.e. on the FK side
46 -- XXX: char(2) sufficient?
47 );
48
49 CREATE TABLE riverbed_materials (
50 material varchar PRIMARY KEY
51 );
52
53 CREATE TABLE survey_types (
54 survey_type varchar PRIMARY KEY
55 );
56
57 CREATE TABLE coverage_types (
58 coverage_type varchar PRIMARY KEY
59 );
60
61 CREATE TABLE limiting_factors (
62 limiting_factor varchar PRIMARY KEY
63 );
64
65 CREATE TABLE depth_references (
66 depth_reference char(3) PRIMARY KEY
67 -- Possible codes? char(3) ok?
68 );
69
70 CREATE TABLE bottlenecks (
71 bottleneck_id varchar PRIMARY KEY,
72 -- XXX: Why encoding three different data in the ID as described in
73 -- DRC?
74 fk_g_fid isrs REFERENCES gauges,
75 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
76 objnam varchar UNIQUE NOT NULL,
77 nobjnm varchar UNIQUE,
78 stretch isrsrange NOT NULL,
79 rb_lb varchar, -- XXX: Why two data in one attribute? Why not:
80 rb char(2) NOT NULL REFERENCES countries,
81 lb char(2) NOT NULL REFERENCES countries,
82 riverbed varchar NOT NULL REFERENCES riverbed_materials,
83 -- XXX: list of materials differs between IENC Encoding Guide and DRC
84 responsible_country char(2) NOT NULL REFERENCES countries,
85 revisiting_time smallint NOT NULL,
86 surtyp varchar NOT NULL REFERENCES survey_types,
87 -- XXX: Not an attribut of sounding result?
88 coverage varchar NOT NULL REFERENCES coverage_types,
89 -- XXX: Not an attribut of sounding result?
90 -- CHECK allowed combinations of surtyp and coverage or
91 -- different model approach?
92 limiting varchar NOT NULL REFERENCES limiting_factors,
93 depth_reference char(3) NOT NULL REFERENCES depth_references,
94 -- XXX: Not an attribut of sounding result?
95 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
96 source_organization varchar NOT NULL,
97 additional_data xml -- XXX: What is that really for?
98 );
99 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
100 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
101
102 CREATE TABLE vertical_references (
103 vertical_reference varchar PRIMARY KEY
104 );
105
106 CREATE TABLE sounding_results (
107 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
108 date_info date NOT NULL,
109 PRIMARY KEY (bottleneck_id, date_info),
110 area int, -- XX: Check SRS/test data what this should be
111 type int, -- XX: Check SRS/test data what this should be
112 vertical_reference varchar NOT NULL REFERENCES vertical_references,
113 sounding_data raster NOT NULL
114 );
115
116 COMMIT;