Mercurial > gemma

annotate wamos.sql @ 38:529319bc3c5b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Mark gauges table as placeholder.
author Tom Gottfried <tom@intevation.de>
date Fri, 04 May 2018 19:06:29 +0200
parents 6f273a649f08
children 830287983657
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 CREATE EXTENSION postgis;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 AS $$
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 END;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 $$;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13
37
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
14 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
15 -- See RIS-Index Encoding Guide
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 CREATE TYPE isrs AS (
37
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
17 country_code char(2), -- ISO 3166 country code
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
18 -- could be validated against countries table.
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
19 locode char(3), -- without the country code:
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
20 -- http://www.unece.org/cefact/locode/welcome.html
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
21 fairway_section char(5),
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
22 object_reference char(5),
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
23 hectometre int -- should be constrained to five digits
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
24 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 CREATE TYPE isrsrange AS RANGE (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 subtype = isrs
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 CREATE TABLE rwdrs (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 stretch isrsrange PRIMARY KEY,
37
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
32 -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
33 -- Only B-tree supports UNIQUE indexes, but we need the GIST index
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
34 -- below anyhow.
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 -- Is it a good idea to build B-tree indexes on relatively large
37
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
36 -- composites of string values or should we use inter PKs?
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
37 -- => In case the index is used and cache space becomes a limiting
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
38 -- factor, this might be an issue.
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 rwdr double precision NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 EXCLUDE USING GIST (stretch WITH &&)
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 CREATE TABLE gauges (
38
529319bc3c5b Mark gauges table as placeholder.
Tom Gottfried <tom@intevation.de>
parents: 37
diff changeset
44 location isrs PRIMARY KEY,
529319bc3c5b Mark gauges table as placeholder.
Tom Gottfried <tom@intevation.de>
parents: 37
diff changeset
45 dummy_attrib varchar
529319bc3c5b Mark gauges table as placeholder.
Tom Gottfried <tom@intevation.de>
parents: 37
diff changeset
46 -- TODO: add real gauge attributes (DRC 2.1.4)
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 CREATE TABLE countries (
37
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
50 country_code char(2) PRIMARY KEY -- ISO 3166 country code
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
51 -- A smallint PK would require even less disk space i.e. on the FK side.
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
52 -- This might be an issue in case cache space becomes a limiting
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
53 -- factor when there are many FKs pointing here.
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 CREATE TABLE riverbed_materials (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 material varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 CREATE TABLE survey_types (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 survey_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 CREATE TABLE coverage_types (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 coverage_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 CREATE TABLE limiting_factors (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 limiting_factor varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 CREATE TABLE depth_references (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 depth_reference char(3) PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 -- Possible codes? char(3) ok?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 CREATE TABLE bottlenecks (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 bottleneck_id varchar PRIMARY KEY,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 -- XXX: Why encoding three different data in the ID as described in
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 -- DRC?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 fk_g_fid isrs REFERENCES gauges,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 objnam varchar UNIQUE NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 nobjnm varchar UNIQUE,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 stretch isrsrange NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 rb_lb varchar, -- XXX: Why two data in one attribute? Why not:
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 rb char(2) NOT NULL REFERENCES countries,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 lb char(2) NOT NULL REFERENCES countries,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 riverbed varchar NOT NULL REFERENCES riverbed_materials,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 -- XXX: list of materials differs between IENC Encoding Guide and DRC
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 responsible_country char(2) NOT NULL REFERENCES countries,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 revisiting_time smallint NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 surtyp varchar NOT NULL REFERENCES survey_types,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 -- XXX: Not an attribut of sounding result?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 coverage varchar NOT NULL REFERENCES coverage_types,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 -- XXX: Not an attribut of sounding result?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 -- CHECK allowed combinations of surtyp and coverage or
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 -- different model approach?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 limiting varchar NOT NULL REFERENCES limiting_factors,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 depth_reference char(3) NOT NULL REFERENCES depth_references,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 -- XXX: Not an attribut of sounding result?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 source_organization varchar NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 additional_data xml -- XXX: What is that really for?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 CREATE TABLE vertical_references (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 vertical_reference varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 CREATE TABLE sounding_results (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 date_info date NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116 PRIMARY KEY (bottleneck_id, date_info),
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
117 area int, -- XX: Check SRS/test data what this should be
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
118 type int, -- XX: Check SRS/test data what this should be
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119 vertical_reference varchar NOT NULL REFERENCES vertical_references,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120 sounding_data raster NOT NULL
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
123 COMMIT;