Mercurial > dive4elements > river
comparison backend/doc/schema/postgresql-spatial.sql @ 8945:4a6b6a3c279c
Merge
author | mschaefer |
---|---|
date | Tue, 13 Mar 2018 09:55:53 +0100 |
parents | 71b17f731762 |
children | f89fb9e9abad |
comparison
equal
deleted
inserted
replaced
8942:11bf13cf0463 | 8945:4a6b6a3c279c |
---|---|
1 BEGIN; | 1 BEGIN; |
2 | 2 |
3 --FIXME: Adjust precision and scale to reasonable numbers | |
4 --FIXME: Make precision and scale equal for all km columns in the database | |
5 | |
3 CREATE TABLE axis_kinds( | 6 CREATE TABLE axis_kinds( |
4 id int PRIMARY KEY NOT NULL, | 7 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
5 name VARCHAR(64) | 8 name VARCHAR(64) |
6 ); | 9 ); |
7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); | 10 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); |
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse'); | 11 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse'); |
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige'); | 12 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige'); |
10 | 13 |
11 -- Geodaesie/Flussachse+km/achse | 14 -- Geodaesie/Flussachse+km/achse |
12 CREATE SEQUENCE RIVER_AXES_ID_SEQ; | 15 CREATE SEQUENCE RIVER_AXES_ID_SEQ; |
13 CREATE TABLE river_axes ( | 16 CREATE TABLE river_axes ( |
14 id int PRIMARY KEY NOT NULL, | 17 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
15 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 18 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
16 kind_id int REFERENCES axis_kinds(id) NOT NULL DEFAULT 0, | 19 kind_id NUMERIC(9,0) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0, |
17 name VARCHAR(64), | 20 name VARCHAR(64), |
18 path VARCHAR(256) | 21 path VARCHAR(256) |
19 ); | 22 ); |
20 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'MULTILINESTRING', 2); | 23 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'MULTILINESTRING', 2); |
21 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); | 24 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); |
23 | 26 |
24 -- TODO: TestMe. | 27 -- TODO: TestMe. |
25 -- Geodaesie/Flussachse+km/km.shp | 28 -- Geodaesie/Flussachse+km/km.shp |
26 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; | 29 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; |
27 CREATE TABLE river_axes_km ( | 30 CREATE TABLE river_axes_km ( |
28 id int PRIMARY KEY NOT NULL, | 31 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
29 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 32 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
30 km FLOAT8 NOT NULL, | 33 km NUMERIC(7,3) NOT NULL, |
31 fedstate_km FLOAT8, | 34 fedstate_km NUMERIC(7,3), |
32 name VARCHAR(64), | 35 name VARCHAR(64), |
33 path VARCHAR(256) | 36 path VARCHAR(256) |
34 ); | 37 ); |
35 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2); | 38 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2); |
36 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); | 39 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); |
37 | 40 |
38 | 41 |
39 --Geodaesie/Querprofile/QP-Spuren/qps.shp | 42 --Geodaesie/Querprofile/QP-Spuren/qps.shp |
40 CREATE TABLE cross_section_track_kinds( | 43 CREATE TABLE cross_section_track_kinds( |
41 id int PRIMARY KEY NOT NULL, | 44 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
42 name VARCHAR(64) | 45 name VARCHAR(64) |
43 ); | 46 ); |
44 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); | 47 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); |
45 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren'); | 48 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren'); |
46 | 49 |
47 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; | 50 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; |
48 CREATE TABLE cross_section_tracks ( | 51 CREATE TABLE cross_section_tracks ( |
49 id int PRIMARY KEY NOT NULL, | 52 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
50 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 53 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
51 kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, | 54 kind_id NUMERIC(9,0) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, |
52 km FLOAT8 NOT NULL, | 55 km NUMERIC(16,12) NOT NULL, |
53 z FLOAT8 NOT NULL DEFAULT 0, | 56 z NUMERIC(16,12) NOT NULL DEFAULT 0, |
54 name VARCHAR(64), | 57 name VARCHAR(64), |
55 path VARCHAR(256) | 58 path VARCHAR(256) |
56 ); | 59 ); |
57 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2); | 60 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2); |
58 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); | 61 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); |
59 | 62 |
60 CREATE TABLE building_kinds( | 63 CREATE TABLE building_kinds( |
61 id int PRIMARY KEY NOT NULL, | 64 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
62 name VARCHAR(64) | 65 name VARCHAR(64) |
63 ); | 66 ); |
64 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); | 67 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); |
65 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken'); | 68 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken'); |
66 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre'); | 69 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre'); |
67 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel'); | 70 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel'); |
68 | 71 |
69 -- Geodaesie/Bauwerke | 72 -- Geodaesie/Bauwerke |
70 CREATE SEQUENCE BUILDINGS_ID_SEQ; | 73 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
71 CREATE TABLE buildings ( | 74 CREATE TABLE buildings ( |
72 id int PRIMARY KEY NOT NULL, | 75 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
73 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 76 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
74 description VARCHAR(256), -- Name taken from attributes, | 77 description VARCHAR(256), -- Name taken from attributes, |
75 name VARCHAR(256), -- The layername | 78 name VARCHAR(256), -- The layername |
76 km FLOAT8, | 79 km NUMERIC(15,11), |
77 kind_id int REFERENCES building_kinds(id) NOT NULL DEFAULT 0, | 80 kind_id NUMERIC(9,0) REFERENCES building_kinds(id) NOT NULL DEFAULT 0, |
78 path VARCHAR(256) | 81 path VARCHAR(256) |
79 ); | 82 ); |
80 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); | 83 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); |
81 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); | 84 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); |
82 | 85 |
83 | 86 |
84 -- Geodaesie/Festpunkte/Festpunkte.shp | 87 -- Geodaesie/Festpunkte/Festpunkte.shp |
85 CREATE SEQUENCE FIXPOINTS_ID_SEQ; | 88 CREATE SEQUENCE FIXPOINTS_ID_SEQ; |
86 CREATE TABLE fixpoints ( | 89 CREATE TABLE fixpoints ( |
87 id int PRIMARY KEY NOT NULL, | 90 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
88 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 91 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
89 x FLOAT8, | 92 x NUMERIC(15,11), |
90 y FLOAT8, | 93 y NUMERIC(15,11), |
91 km FLOAT8 NOT NULL, | 94 km NUMERIC(15,11) NOT NULL, |
92 HPGP VARCHAR(64), | 95 HPGP VARCHAR(64), |
93 name VARCHAR(64), | 96 name VARCHAR(64), |
94 path VARCHAR(256) | 97 path VARCHAR(256) |
95 ); | 98 ); |
96 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2); | 99 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2); |
97 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ'); | 100 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ'); |
98 | 101 |
99 | 102 |
100 -- Hydrologie/Hydr. Grenzen/talaue.shp | 103 -- Hydrologie/Hydr. Grenzen/talaue.shp |
101 CREATE TABLE floodplain_kinds( | 104 CREATE TABLE floodplain_kinds( |
102 id int PRIMARY KEY NOT NULL, | 105 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
103 name VARCHAR(64) | 106 name VARCHAR(64) |
104 ); | 107 ); |
105 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); | 108 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); |
106 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue'); | 109 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue'); |
107 | 110 |
108 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; | 111 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; |
109 CREATE TABLE floodplain ( | 112 CREATE TABLE floodplain ( |
110 id int PRIMARY KEY NOT NULL, | 113 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
111 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 114 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
112 kind_id int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0, | 115 kind_id NUMERIC(9,0) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0, |
113 name VARCHAR(64), | 116 name VARCHAR(64), |
114 path VARCHAR(256) | 117 path VARCHAR(256) |
115 ); | 118 ); |
116 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2); | 119 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2); |
117 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); | 120 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); |
118 | 121 |
119 | 122 |
120 -- Geodaesie/Hoehenmodelle/* | 123 -- Geodaesie/Hoehenmodelle/* |
121 CREATE SEQUENCE DEM_ID_SEQ; | 124 CREATE SEQUENCE DEM_ID_SEQ; |
122 CREATE TABLE dem ( | 125 CREATE TABLE dem ( |
123 id int PRIMARY KEY NOT NULL, | 126 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
124 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 127 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
125 name VARCHAR(64), | 128 name VARCHAR(64), |
126 range_id INT REFERENCES ranges(id) ON DELETE CASCADE, | 129 range_id NUMERIC(9,0) REFERENCES ranges(id) ON DELETE CASCADE, |
127 time_interval_id INT REFERENCES time_intervals(id), | 130 time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), |
128 projection VARCHAR(32), | 131 projection VARCHAR(32), |
129 srid int NOT NULL, | 132 srid NUMERIC(9,0) NOT NULL, |
130 elevation_state VARCHAR(32), | 133 elevation_state VARCHAR(32), |
131 format VARCHAR(32), | 134 format VARCHAR(32), |
132 border_break BOOLEAN NOT NULL DEFAULT FALSE, | 135 border_break NUMERIC(1,0) NOT NULL DEFAULT 0, |
133 resolution VARCHAR(16), | 136 resolution VARCHAR(16), |
134 description VARCHAR(256), | 137 description VARCHAR(256), |
135 path VARCHAR(256) NOT NULL | 138 path VARCHAR(256) NOT NULL |
136 ); | 139 ); |
137 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); | 140 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); |
138 | 141 |
139 | 142 |
140 -- Static lookup tables for Hochwasserschutzanlagen | 143 -- Static lookup tables for Hochwasserschutzanlagen |
141 CREATE TABLE hws_kinds ( | 144 CREATE TABLE hws_kinds ( |
142 id int PRIMARY KEY NOT NULL, | 145 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
143 kind VARCHAR(64) NOT NULL | 146 kind VARCHAR(64) NOT NULL |
144 ); | 147 ); |
145 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); | 148 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); |
146 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm'); | 149 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm'); |
147 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); | 150 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); |
148 | 151 |
149 CREATE TABLE fed_states ( | 152 CREATE TABLE fed_states ( |
150 id int PRIMARY KEY NOT NULL, | 153 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
151 name VARCHAR(23) NOT NULL | 154 name VARCHAR(23) NOT NULL |
152 ); | 155 ); |
153 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); | 156 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); |
154 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen'); | 157 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen'); |
155 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen'); | 158 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen'); |
168 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); | 171 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); |
169 | 172 |
170 --Hydrologie/HW-Schutzanlagen/*Linien.shp | 173 --Hydrologie/HW-Schutzanlagen/*Linien.shp |
171 CREATE SEQUENCE HWS_LINES_ID_SEQ; | 174 CREATE SEQUENCE HWS_LINES_ID_SEQ; |
172 CREATE TABLE hws_lines ( | 175 CREATE TABLE hws_lines ( |
173 id int PRIMARY KEY NOT NULL, | 176 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
174 ogr_fid int, | 177 ogr_fid NUMERIC(9,0), |
175 kind_id int REFERENCES hws_kinds(id) DEFAULT 2, | 178 kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2, |
176 fed_state_id int REFERENCES fed_states(id), | 179 fed_state_id NUMERIC(9,0) REFERENCES fed_states(id), |
177 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 180 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
178 name VARCHAR(256), | 181 name VARCHAR(256), |
179 path VARCHAR(256), | 182 path VARCHAR(256), |
180 official INT DEFAULT 0, | 183 official NUMERIC(9,0) DEFAULT 0, |
181 agency VARCHAR(256), | 184 agency VARCHAR(256), |
182 range VARCHAR(256), | 185 range VARCHAR(256), |
183 shore_side INT DEFAULT 0, | 186 shore_side NUMERIC(9,0) DEFAULT 0, |
184 source VARCHAR(256), | 187 source VARCHAR(256), |
185 status_date TIMESTAMP, | 188 status_date TIMESTAMP(0), |
186 description VARCHAR(256) | 189 description VARCHAR(256) |
187 ); | 190 ); |
188 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3); | 191 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3); |
189 -- TODO: dike_km_from dike_km_to, are they geometries? | 192 -- TODO: dike_km_from dike_km_to, are they geometries? |
190 | 193 |
191 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ'); | 194 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ'); |
192 | 195 |
193 --Hydrologie/HW-Schutzanlagen/*Punkte.shp | 196 --Hydrologie/HW-Schutzanlagen/*Punkte.shp |
194 CREATE SEQUENCE HWS_POINTS_ID_SEQ; | 197 CREATE SEQUENCE HWS_POINTS_ID_SEQ; |
195 CREATE TABLE hws_points ( | 198 CREATE TABLE hws_points ( |
196 id int PRIMARY KEY NOT NULL, | 199 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
197 ogr_fid int, | 200 ogr_fid NUMERIC(9,0), |
198 kind_id int REFERENCES hws_kinds(id) DEFAULT 2, | 201 kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2, |
199 fed_state_id int REFERENCES fed_states(id), | 202 fed_state_id NUMERIC(9,0) REFERENCES fed_states(id), |
200 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 203 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
201 name VARCHAR, | 204 name VARCHAR, |
202 path VARCHAR, | 205 path VARCHAR, |
203 official INT DEFAULT 0, | 206 official NUMERIC(9,0) DEFAULT 0, |
204 agency VARCHAR, | 207 agency VARCHAR, |
205 range VARCHAR, | 208 range VARCHAR, |
206 shore_side INT DEFAULT 0, | 209 shore_side NUMERIC(9,0) DEFAULT 0, |
207 source VARCHAR, | 210 source VARCHAR, |
208 status_date VARCHAR, | 211 status_date VARCHAR, |
209 description VARCHAR, | 212 description VARCHAR, |
210 freeboard FLOAT8, | 213 freeboard NUMERIC(19,5), |
211 dike_km FLOAT8, | 214 dike_km NUMERIC(19,5), |
212 z FLOAT8, | 215 z NUMERIC(19,5), |
213 z_target FLOAT8, | 216 z_target NUMERIC(19,5), |
214 rated_level FLOAT8 | 217 rated_level NUMERIC(19,5) |
215 ); | 218 ); |
216 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2); | 219 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2); |
217 | 220 |
218 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ'); | 221 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ'); |
219 | 222 |
220 -- | 223 -- |
221 --Hydrologie/UeSG | 224 --Hydrologie/UeSG |
222 CREATE TABLE floodmap_kinds ( | 225 CREATE TABLE floodmap_kinds ( |
223 id int PRIMARY KEY NOT NULL, | 226 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
224 name varchar(64) NOT NULL | 227 name varchar(64) NOT NULL |
225 ); | 228 ); |
226 INSERT INTO floodmap_kinds VALUES (200, 'Messung'); | 229 INSERT INTO floodmap_kinds VALUES (200, 'Messung'); |
227 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG'); | 230 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG'); |
228 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer'); | 231 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer'); |
229 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG'); | 232 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG'); |
230 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer'); | 233 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer'); |
231 | 234 |
232 CREATE SEQUENCE FLOODMAPS_ID_SEQ; | 235 CREATE SEQUENCE FLOODMAPS_ID_SEQ; |
233 CREATE TABLE floodmaps ( | 236 CREATE TABLE floodmaps ( |
234 id int PRIMARY KEY NOT NULL, | 237 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
235 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 238 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
236 name varchar(64) NOT NULL, | 239 name varchar(64) NOT NULL, |
237 kind int NOT NULL REFERENCES floodmap_kinds(id), | 240 kind NUMERIC(9,0) NOT NULL REFERENCES floodmap_kinds(id), |
238 diff FLOAT8, | 241 diff NUMERIC(19,5), |
239 count int, | 242 count NUMERIC(9,0), |
240 area FLOAT8, | 243 area NUMERIC(19,5), |
241 perimeter FLOAT8, | 244 perimeter NUMERIC(19,5), |
242 waterbody varchar(64), | 245 waterbody varchar(64), |
243 path VARCHAR(256), | 246 path VARCHAR(256), |
244 source varchar(64) | 247 source varchar(64) |
245 ); | 248 ); |
246 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2); | 249 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2); |
247 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); | 250 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); |
248 | 251 |
249 CREATE TABLE sectie_kinds ( | 252 CREATE TABLE sectie_kinds ( |
250 id int PRIMARY KEY NOT NULL, | 253 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
251 name VARCHAR(64) NOT NULL | 254 name VARCHAR(64) NOT NULL |
252 ); | 255 ); |
253 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); | 256 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); |
254 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne'); | 257 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne'); |
255 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich'); | 258 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich'); |
256 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland'); | 259 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland'); |
257 | 260 |
258 CREATE TABLE sobek_kinds ( | 261 CREATE TABLE sobek_kinds ( |
259 id int PRIMARY KEY NOT NULL, | 262 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
260 name VARCHAR(64) NOT NULL | 263 name VARCHAR(64) NOT NULL |
261 ); | 264 ); |
262 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); | 265 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); |
263 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt'); | 266 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt'); |
264 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt'); | 267 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt'); |
265 | 268 |
266 CREATE TABLE boundary_kinds ( | 269 CREATE TABLE boundary_kinds ( |
267 id int PRIMARY KEY NOT NULL, | 270 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
268 name VARCHAR(64) NOT NULL | 271 name VARCHAR(64) NOT NULL |
269 ); | 272 ); |
270 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); | 273 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); |
271 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); | 274 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); |
272 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); | 275 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); |
273 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); | 276 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); |
274 | 277 |
275 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; | 278 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; |
276 CREATE TABLE hydr_boundaries ( | 279 CREATE TABLE hydr_boundaries ( |
277 id int PRIMARY KEY NOT NULL, | 280 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
278 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 281 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
279 name VARCHAR(255), | 282 name VARCHAR(255), |
280 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, | 283 kind NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, |
281 sectie int REFERENCES sectie_kinds(id), | 284 sectie NUMERIC(9,0) REFERENCES sectie_kinds(id), |
282 sobek int REFERENCES sobek_kinds(id), | 285 sobek NUMERIC(9,0) REFERENCES sobek_kinds(id), |
283 path VARCHAR(256) | 286 path VARCHAR(256) |
284 ); | 287 ); |
285 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3); | 288 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3); |
286 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); | 289 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); |
287 | 290 |
288 | 291 |
289 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; | 292 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; |
290 CREATE TABLE hydr_boundaries_poly ( | 293 CREATE TABLE hydr_boundaries_poly ( |
291 id int PRIMARY KEY NOT NULL, | 294 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
292 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 295 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
293 name VARCHAR(255), | 296 name VARCHAR(255), |
294 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, | 297 kind NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, |
295 sectie int REFERENCES sectie_kinds(id), | 298 sectie NUMERIC(9,0) REFERENCES sectie_kinds(id), |
296 sobek int REFERENCES sobek_kinds(id), | 299 sobek NUMERIC(9,0) REFERENCES sobek_kinds(id), |
297 path VARCHAR(256) | 300 path VARCHAR(256) |
298 ); | 301 ); |
299 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3); | 302 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3); |
300 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); | 303 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); |
301 | 304 |
302 | 305 |
303 CREATE TABLE jetty_kinds( | 306 CREATE TABLE jetty_kinds( |
304 id int PRIMARY KEY NOT NULL, | 307 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
305 name VARCHAR(64) | 308 name VARCHAR(64) |
306 ); | 309 ); |
307 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); | 310 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); |
308 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); | 311 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); |
309 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); | 312 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); |
310 | 313 |
311 CREATE SEQUENCE JETTIES_ID_SEQ; | 314 CREATE SEQUENCE JETTIES_ID_SEQ; |
312 CREATE TABLE jetties ( | 315 CREATE TABLE jetties ( |
313 id int PRIMARY KEY NOT NULL, | 316 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
314 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 317 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
315 path VARCHAR(256), | 318 path VARCHAR(256), |
316 kind_id int REFERENCES jetty_kinds(id), | 319 kind_id NUMERIC(9,0) REFERENCES jetty_kinds(id), |
317 km FLOAT8, | 320 km FLOAT8, |
318 z FLOAT8 | 321 z FLOAT8 |
319 ); | 322 ); |
320 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2); | 323 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2); |
321 ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ'); | 324 ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ'); |
322 | 325 |
323 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; | 326 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; |
324 CREATE TABLE flood_marks ( | 327 CREATE TABLE flood_marks ( |
325 id int PRIMARY KEY NOT NULL, | 328 id NUMERIC(9,0) PRIMARY KEY NOT NULL, |
326 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 329 river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, |
327 path VARCHAR(256), | 330 path VARCHAR(256), |
328 km FLOAT8, | 331 km NUMERIC(7,3), |
329 z FLOAT8, | 332 z NUMERIC(16,12), |
330 location VARCHAR(64), | 333 location VARCHAR(64), |
331 year int | 334 year NUMERIC(4,0) |
332 ); | 335 ); |
333 SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2); | 336 SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2); |
334 ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ'); | 337 ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ'); |
335 | 338 |
336 COMMIT; | 339 COMMIT; |