Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-spatial.sql @ 8965:f89fb9e9abad

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