Mercurial > gemma
annotate wamos.sql @ 64:1e9589cf9c00
Create specific wamos schema.
This allows to use the -s option for postgresql_autodoc.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 25 May 2018 21:34:02 +0200 |
parents | 39e0e3d8dd1b |
children | cad19acc53b3 |
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 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
5 CREATE SCHEMA wamos; |
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
6 SET search_path TO public, wamos; |
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
7 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 CREATE FUNCTION update_date_info() RETURNS trigger |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 LANGUAGE plpgsql |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 AS $$ |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 BEGIN |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 NEW.date_info = CURRENT_TIMESTAMP; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 RETURN NEW; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 END; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 $$; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 |
37
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
17 -- 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
|
18 -- See RIS-Index Encoding Guide |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 CREATE TYPE isrs AS ( |
37
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
20 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
|
21 -- 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
|
22 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
|
23 -- 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
|
24 fairway_section char(5), |
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
25 object_reference char(5), |
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
26 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
|
27 ); |
35
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 CREATE TYPE isrsrange AS RANGE ( |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 subtype = isrs |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 |
53 | 33 -- Eventually obsolete. |
34 -- See https://roundup-intern.intevation.de/wamos/issue5 | |
62
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
35 -- CREATE TABLE rwdrs ( |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
36 -- stretch isrsrange PRIMARY KEY, |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
37 -- -- 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
|
38 -- -- 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
|
39 -- -- below anyhow. |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
40 -- -- 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
|
41 -- -- 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
|
42 -- -- => 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
|
43 -- -- factor, this might be an issue. |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
44 -- rwdr double precision NOT NULL, |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
45 -- EXCLUDE USING GIST (stretch WITH &&) |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
46 -- ); |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
48 CREATE TABLE wamos.gauges ( |
38
529319bc3c5b
Mark gauges table as placeholder.
Tom Gottfried <tom@intevation.de>
parents:
37
diff
changeset
|
49 location isrs PRIMARY KEY, |
43 | 50 dummy_attrib varchar, |
51 "..." varchar | |
38
529319bc3c5b
Mark gauges table as placeholder.
Tom Gottfried <tom@intevation.de>
parents:
37
diff
changeset
|
52 -- 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
|
53 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
55 CREATE TABLE wamos.countries ( |
37
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
56 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
|
57 -- 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
|
58 -- 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
|
59 -- 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
|
60 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
62 -- |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
63 -- Bottlenecks |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
64 -- |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
65 CREATE TABLE wamos.riverbed_materials ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 material varchar PRIMARY KEY |
39
830287983657
Comment: details to clarify riverbed material.
Tom Gottfried <tom@intevation.de>
parents:
38
diff
changeset
|
67 -- 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
|
68 -- 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
|
69 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
70 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
71 CREATE TABLE wamos.survey_types ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 survey_type 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 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
75 CREATE TABLE wamos.coverage_types ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 coverage_type varchar PRIMARY KEY |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
77 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
79 CREATE TABLE wamos.limiting_factors ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 limiting_factor varchar PRIMARY KEY |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
83 CREATE TABLE wamos.depth_references ( |
40
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
84 depth_reference varchar(4) PRIMARY KEY |
535e57373a93
Fix and comments on depth references.
Tom Gottfried <tom@intevation.de>
parents:
39
diff
changeset
|
85 -- 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
|
86 -- 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
|
87 -- 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
|
88 -- _ 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
|
89 -- 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
|
90 -- _ 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
|
91 -- (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
|
92 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 |
55
ecb9bb0591c9
Note on spurious nullability.
Tom Gottfried <tom@intevation.de>
parents:
54
diff
changeset
|
94 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL |
ecb9bb0591c9
Note on spurious nullability.
Tom Gottfried <tom@intevation.de>
parents:
54
diff
changeset
|
95 -- (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
|
96 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
97 CREATE TABLE wamos.bottlenecks ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 bottleneck_id varchar PRIMARY KEY, |
54
285146e8c9c5
Align constraints with WSDL (NOT NULL vs. minOccurs).
Tom Gottfried <tom@intevation.de>
parents:
53
diff
changeset
|
99 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
|
100 -- 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
|
101 objnam varchar, |
285146e8c9c5
Align constraints with WSDL (NOT NULL vs. minOccurs).
Tom Gottfried <tom@intevation.de>
parents:
53
diff
changeset
|
102 nobjnm varchar, |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
103 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
|
104 area geometry(POLYGON, 3146) NOT NULL, |
42 | 105 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface |
106 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
|
107 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
|
108 revisiting_time smallint NOT NULL, |
46 | 109 limiting varchar NOT NULL REFERENCES limiting_factors, |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 surtyp varchar NOT NULL REFERENCES survey_types, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
111 -- XXX: Also an attribut of sounding result? |
54
285146e8c9c5
Align constraints with WSDL (NOT NULL vs. minOccurs).
Tom Gottfried <tom@intevation.de>
parents:
53
diff
changeset
|
112 coverage varchar REFERENCES coverage_types, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
113 -- XXX: Also an attribut of sounding result? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
114 -- CHECK allowed combinations of surtyp and coverage or |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
115 -- different model approach? |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
116 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
|
117 -- 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
|
118 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
|
119 source_organization varchar NOT NULL, |
47
a03ff9076969
Delivered by interface for other uses than WAMOS.
Tom Gottfried <tom@intevation.de>
parents:
46
diff
changeset
|
120 -- 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
|
121 staging_done boolean NOT NULL DEFAULT false |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
122 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
123 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
|
124 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
125 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
126 CREATE TABLE wamos.bottlenecks_riverbed_materials ( |
44
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
127 bottleneck_id varchar REFERENCES bottlenecks, |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
128 riverbed varchar REFERENCES riverbed_materials, |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
129 -- 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
|
130 PRIMARY KEY (bottleneck_id, riverbed) |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
131 ); |
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
132 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
133 CREATE TABLE wamos.sounding_results ( |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
134 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
135 date_info date NOT NULL, |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
136 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
|
137 area geometry(POLYGON, 3146) NOT NULL, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
138 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
|
139 coverage varchar REFERENCES coverage_types, |
45
cb25ddf16cbf
Probably attributes of sounding results
Tom Gottfried <tom@intevation.de>
parents:
44
diff
changeset
|
140 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
|
141 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
|
142 staging_done boolean NOT NULL DEFAULT false |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
143 ); |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
144 |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
145 -- |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
146 -- Fairway availability |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
147 -- |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
148 CREATE TABLE wamos.spot_marks ( |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
149 mark_name varchar PRIMARY KEY |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
150 -- Use smallint because of fairway availability provided on daily basis? |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
151 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
152 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
153 CREATE TABLE wamos.levels_of_service ( |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
154 level_of_service smallint PRIMARY KEY |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
155 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
156 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
157 CREATE TABLE wamos.measure_types ( |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
158 measure_type varchar PRIMARY KEY |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
159 ); |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
160 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
161 CREATE TABLE wamos.fairway_availability ( |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
162 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
163 surdat date NOT NULL, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
164 PRIMARY KEY (bottleneck_id, surdat), |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
165 position varchar REFERENCES spot_marks, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
166 -- 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
|
167 critical boolean, |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
168 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
|
169 source_organization varchar NOT NULL |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
170 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
171 CREATE TRIGGER fairway_availability_date_info |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
172 BEFORE UPDATE ON fairway_availability |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
173 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
|
174 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
175 CREATE TABLE wamos.fa_reference_values ( |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
176 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
177 surdat date NOT NULL, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
178 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
179 level_of_service smallint NOT NULL REFERENCES levels_of_service, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
180 PRIMARY KEY (bottleneck_id, surdat, level_of_service), |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
181 fairway_depth smallint, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
182 fairway_width smallint, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
183 fairway_radius int, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
184 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
185 shallowest_spot geometry(POINT, 3146) |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
186 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
187 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
188 CREATE TABLE wamos.bottleneck_pdfs ( |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
189 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
190 surdat date NOT NULL, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
191 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
192 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
|
193 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
194 PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url), |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
195 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
|
196 source_organization varchar NOT NULL |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
197 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
198 |
64
1e9589cf9c00
Create specific wamos schema.
Tom Gottfried <tom@intevation.de>
parents:
62
diff
changeset
|
199 CREATE TABLE wamos.effective_fairway_availability ( |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
200 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
201 surdat date NOT NULL, |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
202 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
203 measure_date timestamp with time zone NOT NULL, |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
204 level_of_service smallint NOT NULL REFERENCES levels_of_service, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
205 PRIMARY KEY (bottleneck_id, surdat, measure_date, level_of_service), |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
206 available_depth_value smallint, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
207 available_width_value smallint, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
208 water_level_value smallint, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
209 CHECK(COALESCE(available_depth_value, available_width_value, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
210 water_level_value) IS NOT NULL), |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
211 measure_type varchar NOT NULL REFERENCES measure_types, |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
212 source_organization varchar NOT NULL, |
59
37faa7359ea6
Interfaces may provide timestamps with time zone.
Tom Gottfried <tom@intevation.de>
parents:
58
diff
changeset
|
213 forecast_generation_time timestamp with time zone, |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
214 CHECK(measure_type <> 'forecasted' |
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
215 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
|
216 value_lifetime timestamp with time zone, |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
217 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
|
218 ); |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
219 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
220 COMMIT; |