aheinecke@4954: DROP table hws;
aheinecke@5026: DROP sequence HWS_ID_SEQ;
aheinecke@5068: DROP table lines;
aheinecke@5068: DROP sequence LINES_ID_SEQ;
aheinecke@5068: DROP table catchment;
aheinecke@5068: DROP sequence CATCHMENT_ID_SEQ;
aheinecke@5068: 
aheinecke@4954: -- Static lookup tables for Hochwasserschutzanlagen
aheinecke@4954: CREATE TABLE hws_kinds (
aheinecke@4954:     id int PRIMARY KEY NOT NULL,
aheinecke@4954:     kind VARCHAR(64) NOT NULL
aheinecke@4954: );
aheinecke@4954: INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
aheinecke@4954: INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
aheinecke@4954: INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
aheinecke@4954: 
aheinecke@4954: CREATE TABLE fed_states (
aheinecke@4954:     id int PRIMARY KEY NOT NULL,
aheinecke@4954:     name VARCHAR(23) NOT NULL
aheinecke@4954: );
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
aheinecke@4954: INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
aheinecke@4954: 
aheinecke@5026: CREATE TABLE sectie_kinds (
aheinecke@5026:     id int PRIMARY KEY NOT NULL,
aheinecke@5026:     name VARCHAR(64) NOT NULL
aheinecke@5026: );
aheinecke@5026: INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
aheinecke@5026: INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
aheinecke@5026: INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
aheinecke@5026: INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
aheinecke@5026: 
aheinecke@5026: CREATE TABLE sobek_kinds (
aheinecke@5026:     id int PRIMARY KEY NOT NULL,
aheinecke@5026:     name VARCHAR(64) NOT NULL
aheinecke@5026: );
aheinecke@5026: INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
aheinecke@5026: INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
aheinecke@5026: INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
aheinecke@5026: 
aheinecke@5026: CREATE TABLE boundary_kinds (
aheinecke@5026:     id int PRIMARY KEY NOT NULL,
aheinecke@5026:     name VARCHAR(64) NOT NULL
aheinecke@5026: );
aheinecke@5026: INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
aheinecke@5026: INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
aheinecke@5026: INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
aheinecke@5026: INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
aheinecke@5026: 
aheinecke@4954: --Hydrologie/HW-Schutzanlagen/*Linien.shp
aheinecke@4954: CREATE SEQUENCE HWS_LINES_ID_SEQ;
aheinecke@4954: CREATE TABLE hws_lines (
aheinecke@4954:     id int PRIMARY KEY NOT NULL,
aheinecke@4954:     ogr_fid int,
aheinecke@4954:     kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
aheinecke@4954:     fed_state_id int REFERENCES fed_states(id),
aheinecke@4954:     river_id int REFERENCES rivers(id),
aheinecke@4954:     name VARCHAR(256),
aheinecke@4954:     path VARCHAR(256),
aheinecke@4954:     offical INT DEFAULT 0,
aheinecke@4954:     agency VARCHAR(256),
aheinecke@4954:     range VARCHAR(256),
aheinecke@4954:     shore_side INT DEFAULT 0,
aheinecke@4954:     source VARCHAR(256),
aheinecke@4954:     status_date TIMESTAMP,
aheinecke@4954:     description VARCHAR(256)
aheinecke@4954: );
aheinecke@5037: SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 3);
aheinecke@4954: -- TODO: dike_km_from dike_km_to, are they geometries?
aheinecke@4954: 
aheinecke@4954: ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
aheinecke@4954: 
aheinecke@4954: --Hydrologie/HW-Schutzanlagen/*Punkte.shp
aheinecke@4954: CREATE SEQUENCE HWS_POINTS_ID_SEQ;
aheinecke@4954: CREATE TABLE hws_points (
aheinecke@4954:     id int PRIMARY KEY NOT NULL,
aheinecke@4954:     ogr_fid int,
aheinecke@4954:     kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
aheinecke@4954:     fed_state_id int REFERENCES fed_states(id),
aheinecke@4954:     river_id int REFERENCES rivers(id),
aheinecke@4954:     name VARCHAR,
aheinecke@4954:     path VARCHAR,
aheinecke@4954:     offical INT DEFAULT 0,
aheinecke@4954:     agency VARCHAR,
aheinecke@4954:     range VARCHAR,
aheinecke@4954:     shore_side INT DEFAULT 0,
aheinecke@4954:     source VARCHAR,
aheinecke@4954:     status_date VARCHAR,
aheinecke@4954:     description VARCHAR,
aheinecke@4954:     freeboard FLOAT8,
aheinecke@4954:     dike_km FLOAT8,
aheinecke@4954:     z FLOAT8,
aheinecke@4954:     z_target FLOAT8,
aheinecke@4954:     rated_level FLOAT8
aheinecke@4954: );
aheinecke@4954: SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
aheinecke@4954: 
aheinecke@4954: ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
aheinecke@4954: 
aheinecke@5026: ALTER TABLE hydr_boundaries_poly ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
aheinecke@5026: ALTER TABLE hydr_boundaries_poly ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
aheinecke@5068: ALTER TABLE hydr_boundaries_poly ADD FOREIGN KEY (kind) REFERENCES boundary_kinds(id);
aheinecke@5026: ALTER TABLE hydr_boundaries ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
aheinecke@5026: ALTER TABLE hydr_boundaries ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
aheinecke@5026: ALTER TABLE hydr_boundaries ADD FOREIGN KEY (kind) REFERENCES boundary_kinds(id);
aheinecke@5026: ALTER TABLE dem ADD COLUMN srid INT NOT NULL;
tom@5121: ALTER TABLE dem ALTER COLUMN year_from DROP NOT NULL;
tom@5121: ALTER TABLE dem ALTER COLUMN year_to DROP NOT NULL;
tom@5121: ALTER TABLE dem ALTER COLUMN projection DROP NOT NULL;
tom@5121: ALTER TABLE dem ALTER COLUMN path SET NOT NULL;
aheinecke@4954: 
aheinecke@5026: COMMIT;
aheinecke@5026: