Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-spatial.sql @ 8943:71b17f731762

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Same data types, precision and scale as in Oracle (as far as possible)
author mschaefer
date Tue, 13 Mar 2018 09:42:41 +0100
parents cc53aae06303
children f89fb9e9abad
comparison
equal deleted inserted replaced
8941:a9950a3a71e5 8943:71b17f731762
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;
http://dive4elements.wald.intevation.org