Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-minfo.sql @ 2823:13b3fcaa1b34

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add MINFO schema for PostgreSQL flys-backend/trunk@4240 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 13 Apr 2012 16:15:23 +0000
parents
children c3b2673eafbf
comparison
equal deleted inserted replaced
2822:212c33c9a5e2 2823:13b3fcaa1b34
1 BEGIN;
2
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
4
5 CREATE TABLE location_system (
6 id int NOT NULL,
7 name VARCHAR(32) NOT NULL,
8 description VARCHAR(255),
9 PRIMARY KEY(id)
10 );
11
12
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
14
15 CREATE TABLE elevation_model (
16 id int NOT NULL,
17 name VARCHAR(32) NOT NULL,
18 unit_id int NOT NULL,
19 PRIMARY KEY(id),
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
21 );
22
23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
24
25 CREATE TABLE bed_height_type (
26 id int NOT NULL,
27 name VARCHAR(16) NOT NULL,
28 description VARCHAR(255),
29 PRIMARY KEY(id)
30 );
31
32
33
34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
35
36 CREATE TABLE bed_height_single (
37 id int NOT NULL,
38 river_id int NOT NULL,
39 year int NOT NULL,
40 sounding_width int NOT NULL,
41 type_id int NOT NULL,
42 location_system_id int NOT NULL,
43 cur_elevation_model_id int NOT NULL,
44 old_elevation_model_id int,
45 range_id int NOT NULL,
46 evaluation_by VARCHAR(255),
47 description VARCHAR(255),
48 PRIMARY KEY(id),
49 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
50 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
51 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
52 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
53 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
54 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
55 );
56
57
58 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
59
60 CREATE TABLE bed_height_epoch (
61 id int NOT NULL,
62 river_id int NOT NULL,
63 time_interval_id int NOT NULL,
64 -- sounding_with int NOT NULL,
65 -- type_id int NOT NULL,
66 cur_elevation_model_id int NOT NULL,
67 old_elevation_model_id int,
68 range_id int NOT NULL,
69 evaluation_by VARCHAR(255),
70 description VARCHAR(255),
71 PRIMARY KEY(id),
72 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
73 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
74 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
75 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
76 );
77
78
79 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
80
81 CREATE TABLE bed_height_single_values (
82 id int NOT NULL,
83 bed_height_single_id int NOT NULL,
84 station NUMERIC NOT NULL,
85 height NUMERIC,
86 uncertainty NUMERIC,
87 data_gap NUMERIC NOT NULL,
88 sounding_width NUMERIC NOT NULL,
89 width NUMERIC NOT NULL,
90 PRIMARY KEY(id),
91 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
92 );
93
94
95 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
96
97 CREATE TABLE bed_height_epoch_values (
98 id int NOT NULL,
99 bed_height_epoch_id int NOT NULL,
100 station NUMERIC NOT NULL,
101 height NUMERIC,
102 PRIMARY KEY(id),
103 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
104 );
105
106
107 CREATE SEQUENCE DEPTHS_ID_SEQ;
108
109 CREATE TABLE depths (
110 id int NOT NULL,
111 lower NUMERIC NOT NULL,
112 upper NUMERIC NOT NULL,
113 unit_id int NOT NULL,
114 PRIMARY KEY(id),
115 CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
116 );
117
118
119 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
120
121 CREATE TABLE sediment_density (
122 id int NOT NULL,
123 river_id int NOT NULL,
124 depth_id int NOT NULL,
125 unit_id int NOT NULL,
126 description VARCHAR(256),
127 PRIMARY KEY(id),
128 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
129 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
130 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
131 );
132
133
134 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
135
136 CREATE TABLE sediment_density_values (
137 id int NOT NULL,
138 sediment_density_id int NOT NULL,
139 station NUMERIC NOT NULL,
140 density NUMERIC NOT NULL,
141 description VARCHAR(256),
142 PRIMARY KEY(id),
143 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
144 );
145
146
147 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
148
149 CREATE TABLE morphologic_width (
150 id int NOT NULL,
151 river_id int NOT NULL,
152 unit_id int NOT NULL,
153 PRIMARY KEY(id),
154 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
155 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
156 );
157
158
159 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
160
161 CREATE TABLE morphologic_width_values (
162 id int NOT NULL,
163 morphologic_width_id int NOT NULL,
164 station NUMERIC NOT NULL,
165 width NUMERIC NOT NULL,
166 description VARCHAR(256),
167 PRIMARY KEY(id),
168 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
169 );
170
171 COMMIT;
http://dive4elements.wald.intevation.org