Mercurial > gemma
annotate wamos.sql @ 111:767f3edc502c
Fix infinite recursion and remove unnecessary WHERE clause.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 15 Jun 2018 20:20:29 +0200 |
parents | 3780a1afdc98 |
children |
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 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
3 -- |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
4 -- Infrastructure |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
5 -- |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 CREATE EXTENSION postgis; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
8 -- TODO: will there ever be UPDATEs or can we drop that function due to |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
9 -- historicisation? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 CREATE FUNCTION update_date_info() RETURNS trigger |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 LANGUAGE plpgsql |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 AS $$ |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 BEGIN |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 NEW.date_info = CURRENT_TIMESTAMP; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 RETURN NEW; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 END; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 $$; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 |
37
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
19 -- 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
|
20 -- See RIS-Index Encoding Guide |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 CREATE TYPE isrs AS ( |
37
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
22 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
|
23 -- 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
|
24 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
|
25 -- 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
|
26 fairway_section char(5), |
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
27 object_reference char(5), |
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
28 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
|
29 ); |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 CREATE TYPE isrsrange AS RANGE ( |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 subtype = isrs |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
35 -- |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
36 -- WAMOS data |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
37 -- |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
38 CREATE SCHEMA wamos; |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
39 CREATE SCHEMA wamos_waterway; |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
40 CREATE SCHEMA wamos_fairway; |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
41 SET search_path TO public, wamos, wamos_waterway, wamos_fairway; |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
42 -- TODO: consolidate schemas. The current distribution of tables is mainly |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
43 -- for diagram generation. A privilege based distribution might ease |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
44 -- privilege management. |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
45 |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
46 -- |
83 | 47 -- Auxiliary tables |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
48 -- |
82 | 49 CREATE TABLE wamos.system_config ( |
50 config_key varchar PRIMARY KEY, | |
51 config_val varchar | |
52 ); | |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
53 |
82 | 54 CREATE TABLE wamos.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 wamos.responsibility_areas ( | |
62 country char(2) PRIMARY KEY REFERENCES countries, | |
99
3ad1c35b258f
Add todo regarding PostGIS data type.
Tom Gottfried <tom@intevation.de>
parents:
96
diff
changeset
|
63 area geometry(POLYGON, 4326) --XXX: Should be geography (elsewhere too) |
82 | 64 ); |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
65 |
94
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
66 CREATE TABLE wamos.language_codes ( |
84
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
67 language_code varchar PRIMARY KEY |
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
68 ); |
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
69 |
82 | 70 CREATE TABLE wamos.user_profiles ( |
71 username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger | |
84
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
72 country char(2) NOT NULL REFERENCES countries, |
94
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
73 language_code varchar REFERENCES language_codes, |
84
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
74 map_extent box2d, |
82 | 75 email_adress varchar NOT NULL UNIQUE |
76 ); | |
77 | |
78 CREATE TABLE wamos.templates ( | |
102
3780a1afdc98
Auto-generate template IDs.
Tom Gottfried <tom@intevation.de>
parents:
99
diff
changeset
|
79 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
82 | 80 dummy_attrib varchar, |
81 "..." varchar | |
82 -- TODO: template attributes tbd. | |
83 ); | |
84 | |
85 CREATE TABLE wamos.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 -- | |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
94 |
53 | 95 -- Eventually obsolete. |
96 -- See https://roundup-intern.intevation.de/wamos/issue5 | |
62
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
97 -- CREATE TABLE rwdrs ( |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
98 -- stretch isrsrange PRIMARY KEY, |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
99 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html: |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
100 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
101 -- -- below anyhow. |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
102 -- -- Is it a good idea to build B-tree indexes on relatively large |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
103 -- -- composites of string values or should we use inter PKs? |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
104 -- -- => In case the index is used and cache space becomes a limiting |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
105 -- -- factor, this might be an issue. |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
106 -- rwdr double precision NOT NULL, |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
107 -- EXCLUDE USING GIST (stretch WITH &&) |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
108 -- ); |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
109 |
85
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
110 CREATE TABLE wamos_waterway.catccls ( |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
111 catccl smallint PRIMARY KEY |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
112 -- TODO: Do we need name and/or definition from IENC feature catalogue? |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
113 ); |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
114 |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
115 CREATE TABLE wamos_waterway.dirimps ( |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
116 dirimp smallint PRIMARY KEY |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
117 -- TODO: Do we need name and/or definition from IENC feature catalogue? |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
118 ); |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
119 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
120 CREATE TABLE wamos_waterway.waterway_area ( |
85
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
121 area geometry(POLYGON, 4326) PRIMARY KEY, |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
122 catccl smallint REFERENCES catccls, |
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
123 dirimp smallint REFERENCES dirimps |
69 | 124 ); |
125 | |
93 | 126 CREATE TABLE wamos_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 | |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
137 CREATE TABLE wamos_fairway.reference_water_levels ( |
70
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
138 reference_water_level varchar(20) PRIMARY KEY |
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
139 ); |
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
140 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
141 CREATE TABLE wamos_fairway.gauges ( |
38
529319bc3c5b
Mark gauges table as placeholder.
Tom Gottfried <tom@intevation.de>
parents:
37
diff
changeset
|
142 location isrs PRIMARY KEY, |
93 | 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 | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
156 ); |
93 | 157 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges |
158 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
159 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
160 CREATE TABLE wamos_fairway.gauges_reference_water_levels ( |
71
e9bc0d93bef9
Add missing NOT NULL constraints.
Tom Gottfried <tom@intevation.de>
parents:
70
diff
changeset
|
161 gauge_id isrs NOT NULL REFERENCES gauges, |
e9bc0d93bef9
Add missing NOT NULL constraints.
Tom Gottfried <tom@intevation.de>
parents:
70
diff
changeset
|
162 reference_water_level varchar(20) |
e9bc0d93bef9
Add missing NOT NULL constraints.
Tom Gottfried <tom@intevation.de>
parents:
70
diff
changeset
|
163 NOT NULL REFERENCES reference_water_levels, |
70
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
164 PRIMARY KEY (gauge_id, reference_water_level), |
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
165 value int NOT NULL |
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
166 ); |
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
167 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
168 CREATE TABLE wamos_fairway.gauge_measurements ( |
72
158f776732c7
Add gauge measurements stub.
Tom Gottfried <tom@intevation.de>
parents:
71
diff
changeset
|
169 fk_gauge_id isrs NOT NULL REFERENCES gauges, |
94
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
170 measure_date timestamp with time zone NOT NULL, |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
171 PRIMARY KEY (fk_gauge_id, measure_date), |
72
158f776732c7
Add gauge measurements stub.
Tom Gottfried <tom@intevation.de>
parents:
71
diff
changeset
|
172 -- XXX: Is country_code really relevant for WAMOS or just NtS? |
158f776732c7
Add gauge measurements stub.
Tom Gottfried <tom@intevation.de>
parents:
71
diff
changeset
|
173 -- country_code char(2) NOT NULL REFERENCES countries, |
158f776732c7
Add gauge measurements stub.
Tom Gottfried <tom@intevation.de>
parents:
71
diff
changeset
|
174 -- TODO: add relations to stuff provided as enumerations |
94
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
175 sender varchar NOT NULL, -- "from" attribute from DRC |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
176 language_code varchar NOT NULL REFERENCES language_codes, |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
177 date_issue timestamp with time zone NOT NULL, |
95
c79fd71ebe06
Comment out some rather spurious attributes.
Tom Gottfried <tom@intevation.de>
parents:
94
diff
changeset
|
178 -- reference_code varchar(4) NOT NULL REFERENCES depth_references, |
94
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
179 -- XXX: Always ZPG? |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
180 water_level double precision NOT NULL, |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
181 predicted boolean NOT NULL, |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
182 is_waterlevel boolean NOT NULL, |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
183 -- XXX: "measure_code" if really only W or Q |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
184 -- XXX: Do we need "unit" attribute or can we normalise on import? |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
185 value_min double precision, -- XXX: NOT NULL if predicted? |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
186 value_max double precision, -- XXX: NOT NULL if predicted? |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
187 --- TODO: Add a double range type for checking? |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
188 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
189 source_organization varchar NOT NULL -- "originator" |
72
158f776732c7
Add gauge measurements stub.
Tom Gottfried <tom@intevation.de>
parents:
71
diff
changeset
|
190 ); |
94
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
191 CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements |
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
192 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
72
158f776732c7
Add gauge measurements stub.
Tom Gottfried <tom@intevation.de>
parents:
71
diff
changeset
|
193 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
194 CREATE TABLE wamos_waterway.waterway_axis ( |
86 | 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 | |
73 | 199 ); |
200 | |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
201 CREATE TABLE wamos_waterway.distance_mark_functions ( |
78
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
202 -- XXX: Redundant information to object code in isrs code of dist. mark |
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
203 distance_mark_function varchar(8) PRIMARY KEY |
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
204 ); |
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
205 |
87
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
206 CREATE TABLE wamos_waterway.position_codes ( |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
207 position_code char(2) PRIMARY KEY |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
208 -- Use smallint because of fairway availability provided on daily basis? |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
209 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
210 -- sheet "Position_code" or RIS-Index encoding guide? |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
211 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
212 -- Clarify! |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
213 -- TODO: Do we need an attribute "meaning" or so? |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
214 ); |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
215 |
78
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
216 -- This table allows linkage between the 1D ISRS location codes and 2D space |
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
217 -- e.g. for cutting bottleneck area out of waterway area based on virtual |
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
218 -- distance marks along waterway axis (see SUC7). |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
219 CREATE TABLE wamos_waterway.distance_marks ( |
74 | 220 location_code isrs PRIMARY KEY, |
87
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
221 geom geometry(POINT, 4326) NOT NULL, |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
222 distance_mark_function varchar(8) |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
223 NOT NULL REFERENCES distance_mark_functions, |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
224 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
225 -- to encode the same thing as the object code in ISRS location code. |
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
226 position_code char(2) NOT NULL REFERENCES position_codes |
75
ed7a50cfbc19
Add sections and stretches stub and fix comment.
Tom Gottfried <tom@intevation.de>
parents:
74
diff
changeset
|
227 ); |
ed7a50cfbc19
Add sections and stretches stub and fix comment.
Tom Gottfried <tom@intevation.de>
parents:
74
diff
changeset
|
228 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
229 CREATE TABLE wamos_waterway.sections_stretches ( |
75
ed7a50cfbc19
Add sections and stretches stub and fix comment.
Tom Gottfried <tom@intevation.de>
parents:
74
diff
changeset
|
230 id varchar PRIMARY KEY, |
ed7a50cfbc19
Add sections and stretches stub and fix comment.
Tom Gottfried <tom@intevation.de>
parents:
74
diff
changeset
|
231 is_section boolean NOT NULL, -- maps 'function' from interface |
ed7a50cfbc19
Add sections and stretches stub and fix comment.
Tom Gottfried <tom@intevation.de>
parents:
74
diff
changeset
|
232 stretch isrsrange, |
88
a96fee9654db
Add sections and stretches attributes.
Tom Gottfried <tom@intevation.de>
parents:
87
diff
changeset
|
233 objnam varchar NOT NULL, |
a96fee9654db
Add sections and stretches attributes.
Tom Gottfried <tom@intevation.de>
parents:
87
diff
changeset
|
234 nobjnam varchar, |
a96fee9654db
Add sections and stretches attributes.
Tom Gottfried <tom@intevation.de>
parents:
87
diff
changeset
|
235 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
a96fee9654db
Add sections and stretches attributes.
Tom Gottfried <tom@intevation.de>
parents:
87
diff
changeset
|
236 source_organization varchar NOT NULL |
74 | 237 ); |
88
a96fee9654db
Add sections and stretches attributes.
Tom Gottfried <tom@intevation.de>
parents:
87
diff
changeset
|
238 CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches |
a96fee9654db
Add sections and stretches attributes.
Tom Gottfried <tom@intevation.de>
parents:
87
diff
changeset
|
239 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
74 | 240 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
241 CREATE TABLE wamos_waterway.waterway_profiles ( |
91 | 242 location isrs NOT NULL, |
92 | 243 validity tstzrange, |
91 | 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, | |
92 | 251 -- XXX: further normalise using reference_water_levels? |
252 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL | |
253 OR validity IS NOT NULL), | |
91 | 254 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
255 source_organization varchar NOT NULL | |
79
c22ddffc2071
Add waterway profiles stub.
Tom Gottfried <tom@intevation.de>
parents:
78
diff
changeset
|
256 ); |
91 | 257 CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles |
258 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); | |
79
c22ddffc2071
Add waterway profiles stub.
Tom Gottfried <tom@intevation.de>
parents:
78
diff
changeset
|
259 |
89
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
260 CREATE TABLE wamos_waterway.levels_of_service ( |
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
261 level_of_service smallint PRIMARY KEY |
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
262 ); |
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
263 |
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
264 CREATE TABLE wamos_waterway.fairway_dimensions ( |
90 | 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 | |
89
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
272 ); |
90 | 273 CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions |
274 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); | |
89
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
275 |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
276 -- |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
277 -- Bottlenecks |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
278 -- |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
279 CREATE TABLE wamos_fairway.riverbed_materials ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
280 material varchar PRIMARY KEY |
39
830287983657
Comment: details to clarify riverbed material.
Tom Gottfried <tom@intevation.de>
parents:
38
diff
changeset
|
281 -- 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
|
282 -- 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
|
283 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
284 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
285 CREATE TABLE wamos_fairway.survey_types ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
286 survey_type varchar PRIMARY KEY |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
287 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
288 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
289 CREATE TABLE wamos_fairway.coverage_types ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
290 coverage_type varchar PRIMARY KEY |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
291 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
292 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
293 CREATE TABLE wamos_fairway.limiting_factors ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
294 limiting_factor varchar PRIMARY KEY |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
295 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
296 |
55
ecb9bb0591c9
Note on spurious nullability.
Tom Gottfried <tom@intevation.de>
parents:
54
diff
changeset
|
297 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL |
ecb9bb0591c9
Note on spurious nullability.
Tom Gottfried <tom@intevation.de>
parents:
54
diff
changeset
|
298 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and |
ecb9bb0591c9
Note on spurious nullability.
Tom Gottfried <tom@intevation.de>
parents:
54
diff
changeset
|
299 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
300 CREATE TABLE wamos_fairway.bottlenecks ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
301 bottleneck_id varchar PRIMARY KEY, |
54
285146e8c9c5
Align constraints with WSDL (NOT NULL vs. minOccurs).
Tom Gottfried <tom@intevation.de>
parents:
53
diff
changeset
|
302 fk_g_fid isrs NOT NULL REFERENCES gauges, |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
303 -- XXX: DRC references "ch. 3.1.1", which does not exist in document. |
54
285146e8c9c5
Align constraints with WSDL (NOT NULL vs. minOccurs).
Tom Gottfried <tom@intevation.de>
parents:
53
diff
changeset
|
304 objnam varchar, |
285146e8c9c5
Align constraints with WSDL (NOT NULL vs. minOccurs).
Tom Gottfried <tom@intevation.de>
parents:
53
diff
changeset
|
305 nobjnm varchar, |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
306 stretch isrsrange NOT NULL, |
80 | 307 area geometry(POLYGON, 4326) NOT NULL, |
42 | 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 | |
41
dd858a74b4e9
Set columns referencing countries next to each other.
Tom Gottfried <tom@intevation.de>
parents:
40
diff
changeset
|
310 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
|
311 revisiting_time smallint NOT NULL, |
46 | 312 limiting varchar NOT NULL REFERENCES limiting_factors, |
95
c79fd71ebe06
Comment out some rather spurious attributes.
Tom Gottfried <tom@intevation.de>
parents:
94
diff
changeset
|
313 -- surtyp varchar NOT NULL REFERENCES survey_types, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
314 -- XXX: Also an attribut of sounding result? |
95
c79fd71ebe06
Comment out some rather spurious attributes.
Tom Gottfried <tom@intevation.de>
parents:
94
diff
changeset
|
315 -- coverage varchar REFERENCES coverage_types, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
316 -- XXX: Also an attribut of sounding result? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
317 -- CHECK allowed combinations of surtyp and coverage or |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
318 -- different model approach? |
95
c79fd71ebe06
Comment out some rather spurious attributes.
Tom Gottfried <tom@intevation.de>
parents:
94
diff
changeset
|
319 -- 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
|
320 -- XXX: Also an attribut of sounding result? |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
321 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
60
f0ca64da9446
Add attribute to mark data sets as checked in staging area.
Tom Gottfried <tom@intevation.de>
parents:
59
diff
changeset
|
322 source_organization varchar NOT NULL, |
47
a03ff9076969
Delivered by interface for other uses than WAMOS.
Tom Gottfried <tom@intevation.de>
parents:
46
diff
changeset
|
323 -- additional_data xml -- Currently not relevant for WAMOS |
60
f0ca64da9446
Add attribute to mark data sets as checked in staging area.
Tom Gottfried <tom@intevation.de>
parents:
59
diff
changeset
|
324 staging_done boolean NOT NULL DEFAULT false |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
325 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
326 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
|
327 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
328 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
329 CREATE TABLE wamos_fairway.bottlenecks_riverbed_materials ( |
44
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
330 bottleneck_id varchar REFERENCES bottlenecks, |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
331 riverbed varchar REFERENCES riverbed_materials, |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
332 -- 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
|
333 PRIMARY KEY (bottleneck_id, riverbed) |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
334 ); |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
335 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
336 CREATE TABLE wamos_fairway.sounding_results ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
337 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
338 date_info date NOT NULL, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
339 PRIMARY KEY (bottleneck_id, date_info), |
80 | 340 area geometry(POLYGON, 4326) NOT NULL, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
341 surtyp varchar NOT NULL REFERENCES survey_types, |
54
285146e8c9c5
Align constraints with WSDL (NOT NULL vs. minOccurs).
Tom Gottfried <tom@intevation.de>
parents:
53
diff
changeset
|
342 coverage varchar REFERENCES coverage_types, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
343 depth_reference char(3) NOT NULL REFERENCES depth_references, |
60
f0ca64da9446
Add attribute to mark data sets as checked in staging area.
Tom Gottfried <tom@intevation.de>
parents:
59
diff
changeset
|
344 sounding_data raster NOT NULL, |
f0ca64da9446
Add attribute to mark data sets as checked in staging area.
Tom Gottfried <tom@intevation.de>
parents:
59
diff
changeset
|
345 staging_done boolean NOT NULL DEFAULT false |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
346 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
347 |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
348 -- |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
349 -- Fairway availability |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
350 -- |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
351 CREATE TABLE wamos_fairway.measure_types ( |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
352 measure_type varchar PRIMARY KEY |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
353 ); |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
354 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
355 CREATE TABLE wamos_fairway.fairway_availability ( |
66
21df0e95db4a
Fix thinkos and syntax errors in previous commit.
Tom Gottfried <tom@intevation.de>
parents:
65
diff
changeset
|
356 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
87
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
357 position_code char(2) REFERENCES position_codes, |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
358 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
359 surdat date NOT NULL, |
66
21df0e95db4a
Fix thinkos and syntax errors in previous commit.
Tom Gottfried <tom@intevation.de>
parents:
65
diff
changeset
|
360 UNIQUE (bottleneck_id, surdat), |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
361 -- additional_data xml -- Currently not relevant for WAMOS |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
362 critical boolean, |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
363 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
364 source_organization varchar NOT NULL |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
365 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
366 CREATE TRIGGER fairway_availability_date_info |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
367 BEFORE UPDATE ON fairway_availability |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
368 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
369 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
370 CREATE TABLE wamos_fairway.fa_reference_values ( |
66
21df0e95db4a
Fix thinkos and syntax errors in previous commit.
Tom Gottfried <tom@intevation.de>
parents:
65
diff
changeset
|
371 fairway_availability_id int NOT NULL REFERENCES fairway_availability, |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
372 level_of_service smallint NOT NULL REFERENCES levels_of_service, |
65
cad19acc53b3
Avoid duplicate direct relations in composite primary keys.
Tom Gottfried <tom@intevation.de>
parents:
64
diff
changeset
|
373 PRIMARY KEY (fairway_availability_id, level_of_service), |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
374 fairway_depth smallint, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
375 fairway_width smallint, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
376 fairway_radius int, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
377 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), |
80 | 378 shallowest_spot geometry(POINT, 4326) |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
379 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
380 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
381 CREATE TABLE wamos_fairway.bottleneck_pdfs ( |
66
21df0e95db4a
Fix thinkos and syntax errors in previous commit.
Tom Gottfried <tom@intevation.de>
parents:
65
diff
changeset
|
382 fairway_availability_id int NOT NULL REFERENCES fairway_availability, |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
383 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
384 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? |
65
cad19acc53b3
Avoid duplicate direct relations in composite primary keys.
Tom Gottfried <tom@intevation.de>
parents:
64
diff
changeset
|
385 PRIMARY KEY (fairway_availability_id, profile_pdf_url), |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
386 pdf_generation_date timestamp with time zone NOT NULL, |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
387 source_organization varchar NOT NULL |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
388 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
389 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
390 CREATE TABLE wamos_fairway.effective_fairway_availability ( |
66
21df0e95db4a
Fix thinkos and syntax errors in previous commit.
Tom Gottfried <tom@intevation.de>
parents:
65
diff
changeset
|
391 fairway_availability_id int NOT NULL REFERENCES fairway_availability, |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
392 measure_date timestamp with time zone NOT NULL, |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
393 level_of_service smallint NOT NULL REFERENCES levels_of_service, |
65
cad19acc53b3
Avoid duplicate direct relations in composite primary keys.
Tom Gottfried <tom@intevation.de>
parents:
64
diff
changeset
|
394 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service), |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
395 available_depth_value smallint, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
396 available_width_value smallint, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
397 water_level_value smallint, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
398 CHECK(COALESCE(available_depth_value, available_width_value, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
399 water_level_value) IS NOT NULL), |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
400 measure_type varchar NOT NULL REFERENCES measure_types, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
401 source_organization varchar NOT NULL, |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
402 forecast_generation_time timestamp with time zone, |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
403 CHECK(measure_type <> 'forecasted' |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
404 OR forecast_generation_time IS NOT NULL), |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
405 value_lifetime timestamp with time zone, |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
406 CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
407 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
408 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
409 COMMIT; |