Mercurial > gemma
annotate wamos.sql @ 52:70c6efae5a3d
Remove redundant stray attribute.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 07 May 2018 23:05:38 +0200 |
parents | bb81404e8ad1 |
children | 6e8d571c720e |
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, |
43 | 45 dummy_attrib varchar, |
46 "..." varchar | |
38
529319bc3c5b
Mark gauges table as placeholder.
Tom Gottfried <tom@intevation.de>
parents:
37
diff
changeset
|
47 -- 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
|
48 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 CREATE TABLE countries ( |
37
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
51 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
|
52 -- 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
|
53 -- 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
|
54 -- 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
|
55 ); |
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 CREATE TABLE riverbed_materials ( |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 material varchar PRIMARY KEY |
39
830287983657
Comment: details to clarify riverbed material.
Tom Gottfried <tom@intevation.de>
parents:
38
diff
changeset
|
59 -- XXX: Should this table contain choices from DRC 2.2.3 or |
830287983657
Comment: details to clarify riverbed material.
Tom Gottfried <tom@intevation.de>
parents:
38
diff
changeset
|
60 -- from IENC Encoding Guide M.4.3, attribute NATSUR? |
35
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 |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 CREATE TABLE survey_types ( |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
64 survey_type varchar PRIMARY KEY |
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 |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 CREATE TABLE coverage_types ( |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 coverage_type varchar PRIMARY KEY |
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 |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 CREATE TABLE limiting_factors ( |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 limiting_factor varchar PRIMARY KEY |
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 depth_references ( |
40
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
76 depth_reference varchar(4) PRIMARY KEY |
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
77 -- See col. AB and AI RIS-Index Encoding Guide |
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
78 -- XXX: We need a way to distinguish between geodetic (eg. col. AP |
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
79 -- RIS-Index) and other references (e.g. col. AB and AI): |
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
80 -- _ multi-column FK with a boolean column (geodetic/non-geodetic; |
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
81 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. |
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
82 -- _ Do not mixup things with different meanings in one table at all |
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
83 -- (which would mean a model differing a bit from RIS-Index ideas) |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
84 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
85 |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
86 CREATE TABLE bottlenecks ( |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
87 bottleneck_id varchar PRIMARY KEY, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 fk_g_fid isrs REFERENCES gauges, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 -- 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
|
90 objnam varchar UNIQUE NOT NULL, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 nobjnm varchar UNIQUE, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
92 stretch isrsrange NOT NULL, |
50
a7cb0c2c3dd7
An area will be processed on upload for bottlenecks (see SUC8).
Tom Gottfried <tom@intevation.de>
parents:
49
diff
changeset
|
93 area geometry(POLYGON, 3146) NOT NULL, |
42 | 94 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface |
95 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface | |
41
dd858a74b4e9
Set columns referencing countries next to each other.
Tom Gottfried <tom@intevation.de>
parents:
40
diff
changeset
|
96 responsible_country char(2) NOT NULL REFERENCES countries, |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
97 revisiting_time smallint NOT NULL, |
46 | 98 limiting varchar NOT NULL REFERENCES limiting_factors, |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 surtyp varchar NOT NULL REFERENCES survey_types, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
100 -- XXX: Also an attribut of sounding result? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
101 coverage varchar NOT NULL REFERENCES coverage_types, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
102 -- XXX: Also an attribut of sounding result? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
103 -- CHECK allowed combinations of surtyp and coverage or |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
104 -- different model approach? |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
105 depth_reference char(3) NOT NULL REFERENCES depth_references, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
106 -- XXX: Also an attribut of sounding result? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
107 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
48 | 108 source_organization varchar NOT NULL |
47
a03ff9076969
Delivered by interface for other uses than WAMOS.
Tom Gottfried <tom@intevation.de>
parents:
46
diff
changeset
|
109 -- additional_data xml -- Currently not relevant for WAMOS |
35
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 TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
112 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
113 |
44
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
114 CREATE TABLE bottlenecks_riverbed_materials ( |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
115 bottleneck_id varchar REFERENCES bottlenecks, |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
116 riverbed varchar REFERENCES riverbed_materials, |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
117 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
118 PRIMARY KEY (bottleneck_id, riverbed) |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
119 ); |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
120 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
121 CREATE TABLE sounding_results ( |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
122 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
123 date_info date NOT NULL, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
124 PRIMARY KEY (bottleneck_id, date_info), |
51
bb81404e8ad1
An area will be processed or given for clipping for surveys (see SUC10).
Tom Gottfried <tom@intevation.de>
parents:
50
diff
changeset
|
125 area geometry(POLYGON, 3146) NOT NULL, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
126 surtyp varchar NOT NULL REFERENCES survey_types, |
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
127 coverage varchar NOT NULL REFERENCES coverage_types, |
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
128 depth_reference char(3) NOT NULL REFERENCES depth_references, |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
129 sounding_data raster NOT NULL |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
130 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
131 |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
132 COMMIT; |