Mercurial > gemma

annotate wamos.sql @ 37:6f273a649f08

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Be more specific with ISRS and update some comments.
author Tom Gottfried <tom@intevation.de>
date Fri, 04 May 2018 19:01:46 +0200
parents 62e14b4d25fc
children 529319bc3c5b
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 (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 location isrs PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 CREATE TABLE countries (
37
6f273a649f08 Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents: 35
diff changeset
48 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
49 -- 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
50 -- 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
51 -- 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
52 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 CREATE TABLE riverbed_materials (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 material varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 CREATE TABLE survey_types (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 survey_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 CREATE TABLE coverage_types (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 coverage_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 CREATE TABLE limiting_factors (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 limiting_factor varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 CREATE TABLE depth_references (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 depth_reference char(3) PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 -- Possible codes? char(3) ok?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 CREATE TABLE bottlenecks (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 bottleneck_id varchar PRIMARY KEY,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 -- 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
78 -- DRC?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 fk_g_fid isrs REFERENCES gauges,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 -- 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
81 objnam varchar UNIQUE NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 nobjnm varchar UNIQUE,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 stretch isrsrange NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 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
85 rb char(2) NOT NULL REFERENCES countries,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 lb char(2) NOT NULL REFERENCES countries,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 riverbed varchar NOT NULL REFERENCES riverbed_materials,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 -- 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
89 responsible_country char(2) NOT NULL REFERENCES countries,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 revisiting_time smallint NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 surtyp varchar NOT NULL REFERENCES survey_types,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 -- XXX: Not an attribut of sounding result?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 coverage varchar NOT NULL REFERENCES coverage_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 -- CHECK allowed combinations of surtyp and coverage or
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 -- different model approach?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 limiting varchar NOT NULL REFERENCES limiting_factors,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 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
99 -- XXX: Not an attribut of sounding result?
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 source_organization varchar NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 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
103 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 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
105 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 CREATE TABLE vertical_references (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 vertical_reference varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 CREATE TABLE sounding_results (
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 date_info date NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 PRIMARY KEY (bottleneck_id, date_info),
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 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
116 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
117 vertical_reference varchar NOT NULL REFERENCES vertical_references,
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
118 sounding_data raster NOT NULL
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119 );
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121 COMMIT;