Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 5540:25c2505df28f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged
author Christian Lins <christian.lins@intevation.de>
date Wed, 03 Apr 2013 16:00:21 +0200
parents 4c4fed23693f
children 0aaed46a88be
comparison
equal deleted inserted replaced
5539:8d0af912351c 5540:25c2505df28f
3 CREATE TABLE axis_kinds( 3 CREATE TABLE axis_kinds(
4 id int PRIMARY KEY NOT NULL, 4 id int PRIMARY KEY NOT NULL,
5 name VARCHAR(64) 5 name VARCHAR(64)
6 ); 6 );
7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); 7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'Aktuell'); 8 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse');
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige'); 9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
10 10
11 -- Geodaesie/Flussachse+km/achse 11 -- Geodaesie/Flussachse+km/achse
12 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
13 CREATE TABLE river_axes ( 13 CREATE TABLE river_axes (
39 CREATE TABLE cross_section_track_kinds( 39 CREATE TABLE cross_section_track_kinds(
40 id int PRIMARY KEY NOT NULL, 40 id int PRIMARY KEY NOT NULL,
41 name VARCHAR(64) 41 name VARCHAR(64)
42 ); 42 );
43 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); 43 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
44 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell'); 44 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren');
45 45
46 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 46 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
47 CREATE TABLE cross_section_tracks ( 47 CREATE TABLE cross_section_tracks (
48 id int PRIMARY KEY NOT NULL, 48 id int PRIMARY KEY NOT NULL,
49 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 49 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
100 CREATE TABLE floodplain_kinds( 100 CREATE TABLE floodplain_kinds(
101 id int PRIMARY KEY NOT NULL, 101 id int PRIMARY KEY NOT NULL,
102 name VARCHAR(64) 102 name VARCHAR(64)
103 ); 103 );
104 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); 104 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
105 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell'); 105 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue');
106 106
107 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 107 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
108 CREATE TABLE floodplain ( 108 CREATE TABLE floodplain (
109 id int PRIMARY KEY NOT NULL, 109 id int PRIMARY KEY NOT NULL,
110 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 110 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
248 248
249 CREATE TABLE sectie_kinds ( 249 CREATE TABLE sectie_kinds (
250 id int PRIMARY KEY NOT NULL, 250 id int PRIMARY KEY NOT NULL,
251 name VARCHAR(64) NOT NULL 251 name VARCHAR(64) NOT NULL
252 ); 252 );
253 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt'); 253 INSERT INTO sectie_kinds (id, name) VALUES (0, 'SECTIE Unbekannt');
254 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); 254 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
255 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); 255 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
256 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); 256 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
257 257
258 CREATE TABLE sobek_kinds ( 258 CREATE TABLE sobek_kinds (
259 id int PRIMARY KEY NOT NULL, 259 id int PRIMARY KEY NOT NULL,
260 name VARCHAR(64) NOT NULL 260 name VARCHAR(64) NOT NULL
261 ); 261 );
262 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt'); 262 INSERT INTO sobek_kinds (id, name) VALUES (0, 'SOBEK Unbekannt');
263 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); 263 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
264 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); 264 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
265 265
266 CREATE TABLE boundary_kinds ( 266 CREATE TABLE boundary_kinds (
267 id int PRIMARY KEY NOT NULL, 267 id int PRIMARY KEY NOT NULL,
275 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; 275 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
276 CREATE TABLE hydr_boundaries ( 276 CREATE TABLE hydr_boundaries (
277 id int PRIMARY KEY NOT NULL, 277 id int PRIMARY KEY NOT NULL,
278 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 278 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
279 name VARCHAR(255), 279 name VARCHAR(255),
280 kind int REFERENCES boundary_kinds(id), 280 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
281 sectie int REFERENCES sectie_kinds(id), 281 sectie int REFERENCES sectie_kinds(id) NOT NULL DEFAULT 0,
282 sobek int REFERENCES sobek_kinds(id), 282 sobek int REFERENCES sobek_kinds(id) NOT NULL DEFAULT 0,
283 path VARCHAR(256) 283 path VARCHAR(256)
284 ); 284 );
285 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3); 285 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3);
286 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');
287 287
289 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; 289 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
290 CREATE TABLE hydr_boundaries_poly ( 290 CREATE TABLE hydr_boundaries_poly (
291 id int PRIMARY KEY NOT NULL, 291 id int PRIMARY KEY NOT NULL,
292 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 292 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
293 name VARCHAR(255), 293 name VARCHAR(255),
294 kind int REFERENCES boundary_kinds(id), 294 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
295 sectie int REFERENCES sectie_kinds(id), 295 sectie int REFERENCES sectie_kinds(id) NOT NULL DEFAULT 0,
296 sobek int REFERENCES sobek_kinds(id), 296 sobek int REFERENCES sobek_kinds(id) NOT NULL DEFAULT 0,
297 path VARCHAR(256) 297 path VARCHAR(256)
298 ); 298 );
299 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3); 299 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'); 300 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
301 301
329 z FLOAT8 329 z FLOAT8
330 ); 330 );
331 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2); 331 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2);
332 ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ'); 332 ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ');
333 333
334 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ;
335 CREATE TABLE flood_marks (
336 id int PRIMARY KEY NOT NULL,
337 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
338 path VARCHAR(256),
339 km FLOAT8,
340 z FLOAT8,
341 location VARCHAR(64),
342 year int
343 );
344 SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2);
345 ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ');
334 346
335 COMMIT; 347 COMMIT;
http://dive4elements.wald.intevation.org