Mercurial > dive4elements > river
annotate flys-backend/doc/schema/postgresql.sql @ 4130:df8ac7ff6b1a
Improve warning when user does not select a waterlevel calculation (#914)
author | Christian Lins <christian.lins@intevation.de> |
---|---|
date | Sun, 14 Oct 2012 14:44:28 +0200 |
parents | 948c7289fc42 |
children | 83a42e6a562d |
rev | line source |
---|---|
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
1 BEGIN; |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
2 |
2347
0acf28a3d28a
Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2346
diff
changeset
|
3 CREATE SEQUENCE UNITS_ID_SEQ; |
0acf28a3d28a
Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2346
diff
changeset
|
4 |
0acf28a3d28a
Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2346
diff
changeset
|
5 CREATE TABLE units ( |
0acf28a3d28a
Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2346
diff
changeset
|
6 id int PRIMARY KEY NOT NULL, |
0acf28a3d28a
Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2346
diff
changeset
|
7 name VARCHAR(32) NOT NULL UNIQUE |
0acf28a3d28a
Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2346
diff
changeset
|
8 ); |
0acf28a3d28a
Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2346
diff
changeset
|
9 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
10 -- Gewaesser |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
11 CREATE SEQUENCE RIVERS_ID_SEQ; |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
12 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
13 CREATE TABLE rivers ( |
3946
948c7289fc42
Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3796
diff
changeset
|
14 id int PRIMARY KEY NOT NULL, |
948c7289fc42
Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3796
diff
changeset
|
15 official_number int8 UNIQUE, |
948c7289fc42
Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3796
diff
changeset
|
16 name VARCHAR(256) NOT NULL UNIQUE, |
948c7289fc42
Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3796
diff
changeset
|
17 km_up BOOLEAN NOT NULL DEFAULT true, |
948c7289fc42
Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3796
diff
changeset
|
18 wst_unit_id int NOT NULL REFERENCES units(id) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
19 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
20 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
21 -- Bruecke, Haefen, etc. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
22 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
23 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
24 CREATE TABLE attributes ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
25 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
26 value VARCHAR(256) NOT NULL UNIQUE |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
27 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
28 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
29 -- segments from/to at a river |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
30 CREATE SEQUENCE RANGES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
31 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
32 CREATE TABLE ranges ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
33 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
34 river_id int NOT NULL REFERENCES rivers(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
35 a NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
36 b NUMERIC, |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
37 UNIQUE (river_id, a, b) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
38 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
39 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
40 -- Lage 'links', 'rechts', etc. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
41 CREATE SEQUENCE POSITIONS_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
42 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
43 CREATE TABLE positions ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
44 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
45 value VARCHAR(256) NOT NULL UNIQUE |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
46 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
47 |
758
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
48 -- Kante 'oben', 'unten' |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
49 CREATE SEQUENCE EDGES_ID_SEQ; |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
50 |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
51 CREATE TABLE edges ( |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
52 id int PRIMARY KEY NOT NULL, |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
53 top NUMERIC, |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
54 bottom NUMERIC |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
55 ); |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
56 |
763
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
57 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
58 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
59 |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
60 CREATE TABLE annotation_types ( |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
61 id int PRIMARY KEY NOT NULL, |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
62 name VARCHAR(256) NOT NULL UNIQUE |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
63 ); |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
64 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
65 -- Some object (eg. Hafen) at a segment of river |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
66 -- plus its position. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
67 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
68 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
69 CREATE TABLE annotations ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
70 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
71 range_id int NOT NULL REFERENCES ranges(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
72 attribute_id int NOT NULL REFERENCES attributes(id), |
758
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
73 position_id int REFERENCES positions(id), |
763
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
74 edge_id int REFERENCES edges(id), |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
75 type_id int REFERENCES annotation_types(id) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
76 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
77 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
78 -- Pegel |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
79 CREATE SEQUENCE GAUGES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
80 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
81 CREATE TABLE gauges ( |
2371
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
82 id int PRIMARY KEY NOT NULL, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
83 name VARCHAR(256) NOT NULL, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
84 river_id int NOT NULL REFERENCES rivers(id), |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
85 station NUMERIC NOT NULL UNIQUE, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
86 aeo NUMERIC NOT NULL, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
87 official_number int8 UNIQUE, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
88 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
89 -- Pegelnullpunkt |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
90 datum NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
91 -- Streckengueltigkeit |
2373
056b3a5aa181
Removed "NOT NULL" constraint from gauges.range_id because there are gauges which dont have a Gueltigkeitsbereich.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2371
diff
changeset
|
92 range_id int REFERENCES ranges (id), |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
93 |
167
15d515fe15f5
Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
164
diff
changeset
|
94 UNIQUE (name, river_id), |
194
0f0b98ef9b04
Fixed wrong unique constraint.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
174
diff
changeset
|
95 UNIQUE (river_id, station) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
96 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
97 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
98 -- Type of a Hauptwert 'W', 'Q', 'D', etc. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
99 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
100 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
101 CREATE TABLE main_value_types ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
102 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
103 name VARCHAR(256) NOT NULL UNIQUE |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
104 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
105 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
106 -- Named type of a Hauptwert (eg. HQ100) |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
107 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
108 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
109 CREATE TABLE named_main_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
110 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
111 name VARCHAR(256) NOT NULL UNIQUE, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
112 type_id int NOT NULL REFERENCES main_value_types(id), |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
113 UNIQUE (name, type_id) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
114 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
115 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
116 -- Table for time intervals |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
117 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
118 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
119 CREATE TABLE time_intervals ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
120 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
121 start_time TIMESTAMP NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
122 stop_time TIMESTAMP, |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
123 CHECK (start_time <= stop_time) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
124 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
125 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
126 -- Stammdaten |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
127 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
128 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
129 CREATE TABLE main_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
130 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
131 gauge_id int NOT NULL REFERENCES gauges(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
132 named_value_id int NOT NULL REFERENCES named_main_values(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
133 value NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
134 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
135 time_interval_id int REFERENCES time_intervals(id), |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
136 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
137 -- TODO: better checks |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
138 UNIQUE (gauge_id, named_value_id, time_interval_id) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
139 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
140 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
141 -- Abflusstafeln |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
142 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
143 |
167
15d515fe15f5
Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
164
diff
changeset
|
144 CREATE TABLE discharge_tables ( |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
145 id int PRIMARY KEY NOT NULL, |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
146 gauge_id int NOT NULL REFERENCES gauges(id), |
493
b35c5dc0f8b7
Importer: Make import of historical discharge tables work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
476
diff
changeset
|
147 description VARCHAR(256) NOT NULL, |
470
f4afea9b7537
Forget ',' in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
467
diff
changeset
|
148 kind int NOT NULL DEFAULT 0, |
496
4f2d8980415f
Schema: Dropped constraint which forces discharge tables to have a unique time interval for a given gauge and kind.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
493
diff
changeset
|
149 time_interval_id int REFERENCES time_intervals(id) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
150 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
151 -- TODO: better checks |
496
4f2d8980415f
Schema: Dropped constraint which forces discharge tables to have a unique time interval for a given gauge and kind.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
493
diff
changeset
|
152 -- UNIQUE (gauge_id, kind, time_interval_id) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
153 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
154 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
155 -- Values of the Abflusstafeln |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
156 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
157 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
158 CREATE TABLE discharge_table_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
159 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
160 table_id int NOT NULL REFERENCES discharge_tables(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
161 q NUMERIC NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
162 w NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
163 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
164 UNIQUE (table_id, q, w) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
165 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
166 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
167 -- WST files |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
168 CREATE SEQUENCE WSTS_ID_SEQ; |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
169 |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
170 CREATE TABLE wsts ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
171 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
172 river_id int NOT NULL REFERENCES rivers(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
173 description VARCHAR(256) NOT NULL, |
470
f4afea9b7537
Forget ',' in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
467
diff
changeset
|
174 kind int NOT NULL DEFAULT 0, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
175 -- TODO: more meta infos |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
176 UNIQUE (river_id, description) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
177 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
178 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
179 -- columns of WST files |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
180 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
181 |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
182 CREATE TABLE wst_columns ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
183 id int PRIMARY KEY NOT NULL, |
173
8e2f2ddc507f
Fixed wrong spelled column references in foreign keys introduces with last change.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
171
diff
changeset
|
184 wst_id int NOT NULL REFERENCES wsts(id), |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
185 name VARCHAR(256) NOT NULL, |
174
249390dd24e7
Added foreign key constraint annotations to model classes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
173
diff
changeset
|
186 description VARCHAR(256), |
471
3570e4af8cb2
Added 'position' column to wst_columns to allow order them by there column position in the original wst file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
470
diff
changeset
|
187 position int NOT NULL DEFAULT 0, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
188 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
189 time_interval_id int REFERENCES time_intervals(id), |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
190 |
471
3570e4af8cb2
Added 'position' column to wst_columns to allow order them by there column position in the original wst file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
470
diff
changeset
|
191 UNIQUE (wst_id, name), |
3570e4af8cb2
Added 'position' column to wst_columns to allow order them by there column position in the original wst file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
470
diff
changeset
|
192 UNIQUE (wst_id, position) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
193 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
194 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
195 -- w values in WST file column |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
196 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
197 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
198 CREATE TABLE wst_column_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
199 id int PRIMARY KEY NOT NULL, |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
200 wst_column_id int NOT NULL REFERENCES wst_columns(id), |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
201 position NUMERIC NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
202 w NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
203 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
204 UNIQUE (position, wst_column_id), |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
205 UNIQUE (position, wst_column_id, w) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
206 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
207 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
208 -- bind q values to range |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
209 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
210 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
211 CREATE TABLE wst_q_ranges ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
212 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
213 range_id int NOT NULL REFERENCES ranges(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
214 q NUMERIC NOT NULL |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
215 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
216 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
217 -- bind q ranges to wst columns |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
218 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
219 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
220 CREATE TABLE wst_column_q_ranges ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
221 id int PRIMARY KEY NOT NULL, |
173
8e2f2ddc507f
Fixed wrong spelled column references in foreign keys introduces with last change.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
171
diff
changeset
|
222 wst_column_id int NOT NULL REFERENCES wst_columns(id), |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
223 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
224 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
225 UNIQUE (wst_column_id, wst_q_range_id) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
226 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
227 |
476
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
228 CREATE VIEW wst_value_table AS |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
229 SELECT wcv.position AS position, |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
230 w, |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
231 (SELECT q |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
232 FROM wst_column_q_ranges wcqr |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
233 JOIN wst_q_ranges wqr |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
234 ON wcqr.wst_q_range_id = wqr.id |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
235 JOIN ranges r |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
236 ON r.id = wqr.range_id |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
237 WHERE wcqr.wst_column_id = wc.id |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
238 AND wcv.position BETWEEN r.a AND r.b) AS q, |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
239 wc.position AS column_pos, |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
240 w.id AS wst_id |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
241 FROM wst_column_values wcv |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
242 JOIN wst_columns wc |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
243 ON wcv.wst_column_id = wc.id |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
244 JOIN wsts w |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
245 ON wc.wst_id = w.id |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
246 ORDER BY wcv.position ASC, |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
247 wc.position DESC; |
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
248 |
754
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
249 -- view to select the w values of a WST |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
250 CREATE VIEW wst_w_values AS |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
251 SELECT wcv."position" AS km, |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
252 wcv.w AS w, |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
253 wc."position" AS column_pos, |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
254 w.id AS wst_id |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
255 FROM wst_column_values wcv |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
256 JOIN wst_columns wc ON wcv.wst_column_id = wc.id |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
257 JOIN wsts w ON wc.wst_id = w.id |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
258 ORDER BY wcv."position", wc."position"; |
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
259 |
755
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
260 -- view to select the q values of a WST |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
261 CREATE VIEW wst_q_values AS |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
262 SELECT wc.position AS column_pos, |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
263 wqr.q AS q, |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
264 r.a AS a, |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
265 r.b AS b, |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
266 wc.wst_id AS wst_id |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
267 FROM wst_column_q_ranges wcqr |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
268 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
269 JOIN ranges r ON wqr.range_id = r.id |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
270 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
271 ORDER BY wc.position, wcqr.wst_column_id, r.a; |
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
272 |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
273 -- data for the cross-sections |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
274 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
275 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
276 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
277 CREATE TABLE cross_sections ( |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
278 id int PRIMARY KEY NOT NULL, |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
279 river_id int NOT NULL REFERENCES rivers(id), |
1203
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
280 time_interval_id int REFERENCES time_intervals(id), |
1202
44581b40b968
Schema: Added description column to the cross section table
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1201
diff
changeset
|
281 description VARCHAR(256) |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
282 ); |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
283 |
1205
5f1506fc7636
Made import of cross sections work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1203
diff
changeset
|
284 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; |
1203
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
285 |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
286 CREATE TABLE cross_section_lines ( |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
287 id int PRIMARY KEY NOT NULL, |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
288 km NUMERIC NOT NULL, |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
289 cross_section_id int NOT NULL REFERENCES cross_sections(id), |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
290 UNIQUE (km, cross_section_id) |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
291 ); |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
292 |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
293 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
294 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
295 CREATE TABLE cross_section_points ( |
1203
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
296 id int PRIMARY KEY NOT NULL, |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
297 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id), |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
298 col_pos int NOT NULL, |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
299 x NUMERIC NOT NULL, |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
300 y NUMERIC NOT NULL, |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
301 UNIQUE (cross_section_line_id, col_pos) |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
302 ); |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
303 |
3796
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
304 -- Indices for faster access of the points |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
305 CREATE INDEX cross_section_lines_km_idx |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
306 ON cross_section_lines(km); |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
307 CREATE INDEX cross_section_points_line_idx |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
308 ON cross_section_points(cross_section_line_id); |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
309 |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
310 -- Hydraulische Kenngroessen |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
311 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
312 CREATE SEQUENCE HYKS_ID_SEQ; |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
313 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
314 CREATE TABLE hyks ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
315 id int PRIMARY KEY NOT NULL, |
1226
1dbafbba50ea
Schema: added missing key contraint on hyks table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1217
diff
changeset
|
316 river_id int NOT NULL REFERENCES rivers(id), |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
317 description VARCHAR(256) NOT NULL |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
318 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
319 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
320 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
321 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
322 CREATE TABLE hyk_entries ( |
1212
dd948530fd57
Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1209
diff
changeset
|
323 id int PRIMARY KEY NOT NULL, |
dd948530fd57
Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1209
diff
changeset
|
324 hyk_id int NOT NULL REFERENCES hyks(id), |
dd948530fd57
Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1209
diff
changeset
|
325 km NUMERIC NOT NULL, |
1217
d5e39c16fd2a
Schema: Fixed location of 'Peilungsjahre' in HYKs
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1214
diff
changeset
|
326 measure TIMESTAMP, |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
327 UNIQUE (hyk_id, km) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
328 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
329 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
330 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
331 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
332 CREATE TABLE hyk_formations ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
333 id int PRIMARY KEY NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
334 formation_num int NOT NULL DEFAULT 0, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
335 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id), |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
336 top NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
337 bottom NUMERIC NOT NULL, |
1214
32ee9babe42c
Schema: Argh! Added distance_{vl|hf|vr} to
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1212
diff
changeset
|
338 distance_vl NUMERIC NOT NULL, |
32ee9babe42c
Schema: Argh! Added distance_{vl|hf|vr} to
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1212
diff
changeset
|
339 distance_hf NUMERIC NOT NULL, |
32ee9babe42c
Schema: Argh! Added distance_{vl|hf|vr} to
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1212
diff
changeset
|
340 distance_vr NUMERIC NOT NULL, |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
341 UNIQUE (hyk_entry_id, formation_num) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
342 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
343 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
344 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
345 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
346 CREATE TABLE hyk_flow_zone_types ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
347 id int PRIMARY KEY NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
348 name VARCHAR(50) NOT NULL UNIQUE, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
349 description VARCHAR(256) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
350 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
351 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
352 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
353 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
354 CREATE TABLE hyk_flow_zones ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
355 id int PRIMARY KEY NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
356 formation_id int NOT NULL REFERENCES hyk_formations(id), |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
357 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id), |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
358 a NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
359 b NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
360 CHECK (a <= b) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
361 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
362 |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
363 CREATE VIEW official_lines |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
364 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
365 SELECT w.river_id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
366 w.id AS wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
367 wc.id AS wst_column_id, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
368 wc.name AS name, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
369 wc.position AS wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
370 FROM wsts w |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
371 JOIN wst_columns wc |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
372 ON wc.wst_id = w.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
373 WHERE w.kind = 3; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
374 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
375 CREATE VIEW q_main_values |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
376 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
377 SELECT riv.id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
378 g.id AS gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
379 g.name AS gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
380 r.a AS a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
381 r.b AS b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
382 REGEXP_REPLACE( |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
383 nmv.name, E'[:space:]*\\(.*\\)[:space:]*', '') AS name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
384 CAST(mv.value AS NUMERIC(38, 2)) AS value |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
385 FROM main_values mv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
386 JOIN named_main_values nmv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
387 ON mv.named_value_id = nmv.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
388 JOIN main_value_types mvt |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
389 ON nmv.type_id = mvt.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
390 JOIN gauges g |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
391 ON mv.gauge_id = g.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
392 JOIN ranges r |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
393 ON g.range_id = r.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
394 JOIN rivers riv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
395 ON g.river_id = riv.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
396 WHERE mvt.name = 'Q' |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
397 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
398 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
399 CREATE VIEW official_q_values |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
400 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
401 SELECT ol.river_id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
402 wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
403 wst_column_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
404 gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
405 gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
406 a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
407 b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
408 ol.name, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
409 value, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
410 wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
411 FROM official_lines ol |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
412 JOIN q_main_values qmv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
413 ON ol.river_id = qmv.river_id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
414 AND ol.name = qmv.name; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
415 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
416 COMMIT; |