ingo@1230: BEGIN; ingo@1230: hans@1238: -- Geodaesie/Flussachse+km/achse ingo@1240: CREATE SEQUENCE RIVER_AXES_ID_SEQ; ingo@1230: CREATE TABLE river_axes ( ingo@1240: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@3948: kind int NOT NULL DEFAULT 0, ingo@3948: name VARCHAR(64), ingo@3948: path VARCHAR(256) ingo@1230: ); ingo@3950: SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2); ingo@1240: ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); ingo@1230: ingo@1230: bjoern@2340: -- TODO: TestMe. bjoern@2339: -- Geodaesie/Flussachse+km/km.shp bjoern@2340: CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; bjoern@2340: CREATE TABLE river_axes_km ( bjoern@2340: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@4931: km FLOAT8 NOT NULL, ingo@3948: name VARCHAR(64), ingo@3948: path VARCHAR(256) bjoern@2340: ); ingo@3950: SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2); bjoern@2340: ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); bjoern@2339: bjoern@2339: bjoern@2339: --Geodaesie/Querprofile/QP-Spuren/qps.shp ingo@1240: CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; ingo@1230: CREATE TABLE cross_section_tracks ( ingo@1240: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@4931: km FLOAT8 NOT NULL, aheinecke@4931: z FLOAT8 NOT NULL DEFAULT 0, ingo@3948: name VARCHAR(64), ingo@3948: path VARCHAR(256) ingo@1230: ); ingo@3950: SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2); ingo@1240: ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); ingo@1230: ingo@1230: hans@1238: -- Geodaesie/Linien/rohre-und-spreen ingo@1240: CREATE SEQUENCE LINES_ID_SEQ; ingo@1230: CREATE TABLE lines ( ingo@1240: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@2365: kind VARCHAR(16) NOT NULL, aheinecke@4931: z FLOAT8 DEFAULT 0, ingo@3948: name VARCHAR(64), ingo@3948: path VARCHAR(256) ingo@1230: ); ingo@3950: SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3); ingo@1240: ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ'); ingo@1230: -- 'kind': ingo@1230: -- 0: ROHR1 ingo@1230: -- 1: DAMM ingo@1230: ingo@1230: hans@1238: -- Geodaesie/Bauwerke/Wehre.shp ingo@1240: CREATE SEQUENCE BUILDINGS_ID_SEQ; ingo@1230: CREATE TABLE buildings ( ingo@1240: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@3948: name VARCHAR(256), ingo@3948: path VARCHAR(256) ingo@1230: ); ingo@3950: SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); ingo@1240: ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); ingo@1230: ingo@1230: hans@1238: -- Geodaesie/Festpunkte/Festpunkte.shp ingo@1240: CREATE SEQUENCE FIXPOINTS_ID_SEQ; ingo@1230: CREATE TABLE fixpoints ( ingo@1240: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@4931: x FLOAT8, aheinecke@4931: y FLOAT8, aheinecke@4931: km FLOAT8 NOT NULL, ingo@3948: HPGP VARCHAR(2), ingo@3948: name VARCHAR(64), ingo@3948: path VARCHAR(256) ingo@1230: ); ingo@3950: SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2); ingo@1240: ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ'); ingo@1230: ingo@1236: hans@1238: -- Hydrologie/Hydr. Grenzen/talaue.shp ingo@1240: CREATE SEQUENCE FLOODPLAIN_ID_SEQ; ingo@1237: CREATE TABLE floodplain ( ingo@1240: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@3948: name VARCHAR(64), ingo@3948: path VARCHAR(256) ingo@1237: ); ingo@3950: SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2); ingo@1240: ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); ingo@1237: ingo@1237: hans@1238: -- Geodaesie/Hoehenmodelle/* ingo@1240: CREATE SEQUENCE DEM_ID_SEQ; ingo@1240: CREATE TABLE dem ( ingo@1240: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@1236: -- XXX Should we use the ranges table instead? ingo@3951: name VARCHAR(64), aheinecke@4931: lower FLOAT8, aheinecke@4931: upper FLOAT8, ingo@3961: year_from VARCHAR(32) NOT NULL, ingo@3961: year_to VARCHAR(32) NOT NULL, ingo@3961: projection VARCHAR(32) NOT NULL, tom@4992: srid int NOT NULL, ingo@3951: elevation_state VARCHAR(32), ingo@3951: format VARCHAR(32), ingo@3951: border_break BOOLEAN NOT NULL DEFAULT FALSE, ingo@3951: resolution VARCHAR(16), ingo@3951: description VARCHAR(256), ingo@3951: path VARCHAR(256) ingo@1236: ); ingo@1240: ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); ingo@1236: hans@1238: bjoern@1241: -- Hydrologie/Einzugsgebiete/EZG.shp bjoern@1241: CREATE SEQUENCE CATCHMENT_ID_SEQ; bjoern@1241: CREATE TABLE catchment ( bjoern@1241: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@4931: area FLOAT8, ingo@3948: name VARCHAR(256), ingo@3948: path VARCHAR(256) bjoern@1241: ); ingo@3950: SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2); bjoern@1241: ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); bjoern@1241: aheinecke@4930: -- Static lookup tables for Hochwasserschutzanlagen aheinecke@4930: CREATE TABLE hws_kinds ( aheinecke@4930: id int PRIMARY KEY NOT NULL, aheinecke@4930: kind VARCHAR(64) NOT NULL aheinecke@4930: ); aheinecke@4930: INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); aheinecke@4930: INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm'); aheinecke@4930: INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); bjoern@1241: aheinecke@4930: CREATE TABLE fed_states ( bjoern@2339: id int PRIMARY KEY NOT NULL, aheinecke@4930: name VARCHAR(23) NOT NULL aheinecke@4930: ); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (2, 'Hessen'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (6, 'Saarland'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (14, 'Berlin'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (15, 'Bremen'); aheinecke@4930: INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); aheinecke@4930: aheinecke@4930: --Hydrologie/HW-Schutzanlagen/*Linien.shp aheinecke@4930: CREATE SEQUENCE HWS_LINES_ID_SEQ; aheinecke@4930: CREATE TABLE hws_lines ( aheinecke@4930: id int PRIMARY KEY NOT NULL, aheinecke@4930: ogr_fid int, aheinecke@4930: kind_id int REFERENCES hws_kinds(id) DEFAULT 2, aheinecke@4930: fed_state_id int REFERENCES fed_states(id), tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@4930: name VARCHAR(256), aheinecke@4930: path VARCHAR(256), aheinecke@4930: offical INT DEFAULT 0, aheinecke@4930: agency VARCHAR(256), aheinecke@4930: range VARCHAR(256), aheinecke@4930: shore_side INT DEFAULT 0, aheinecke@4930: source VARCHAR(256), aheinecke@4930: status_date TIMESTAMP, aheinecke@4930: description VARCHAR(256) bjoern@2339: ); aheinecke@4930: SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2); aheinecke@4930: SELECT AddGeometryColumn('hws_lines', 'geom_target', 31467, 'LINESTRING', 2); -- ? aheinecke@4930: SELECT AddGeometryColumn('hws_lines', 'geom_rated_level', 31467, 'LINESTRING', 2); -- ? aheinecke@4930: -- TODO: dike_km_from dike_km_to, are they geometries? bjoern@1241: aheinecke@4930: ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ'); aheinecke@4930: aheinecke@4930: --Hydrologie/HW-Schutzanlagen/*Punkte.shp aheinecke@4930: CREATE SEQUENCE HWS_POINTS_ID_SEQ; aheinecke@4930: CREATE TABLE hws_points ( aheinecke@4930: id int PRIMARY KEY NOT NULL, aheinecke@4930: ogr_fid int, aheinecke@4930: kind_id int REFERENCES hws_kinds(id) DEFAULT 2, aheinecke@4930: fed_state_id int REFERENCES fed_states(id), tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@4930: name VARCHAR, aheinecke@4930: path VARCHAR, aheinecke@4930: offical INT DEFAULT 0, aheinecke@4930: agency VARCHAR, aheinecke@4930: range VARCHAR, aheinecke@4930: shore_side INT DEFAULT 0, aheinecke@4930: source VARCHAR, aheinecke@4930: status_date VARCHAR, aheinecke@4930: description VARCHAR, aheinecke@4930: freeboard FLOAT8, aheinecke@4930: dike_km FLOAT8, aheinecke@4930: z FLOAT8, aheinecke@4930: z_target FLOAT8, aheinecke@4930: rated_level FLOAT8 aheinecke@4930: ); aheinecke@4930: SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2); aheinecke@4930: aheinecke@4930: ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ'); bjoern@1241: ingo@2797: -- ingo@2796: --Hydrologie/UeSG ingo@2797: -- ingo@2797: -- 'kind' can be one of: ingo@2797: -- 200 = Messung ingo@2797: -- 111 = Berechnung->Aktuell->BfG ingo@2797: -- 112 = Berechnung->Aktuell->Land ingo@2797: -- 121 = Berechnung->Potenziell->BfG ingo@2797: -- 122 = Berechnung->Potenziell->Land ingo@2797: -- ingo@3948: CREATE SEQUENCE FLOODMAPS_ID_SEQ; ingo@2796: CREATE TABLE floodmaps ( ingo@2796: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@2797: name varchar(64) NOT NULL, ingo@2796: kind int NOT NULL, aheinecke@4931: diff FLOAT8, ingo@2796: count int, aheinecke@4931: area FLOAT8, aheinecke@4931: perimeter FLOAT8, ingo@3948: path VARCHAR(256) ingo@2796: ); ingo@3950: SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2); ingo@2797: ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; ingo@2797: ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); ingo@3948: ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); ingo@2796: aheinecke@4999: CREATE TABLE sectie_kinds ( aheinecke@4999: id int PRIMARY KEY NOT NULL, aheinecke@4999: name VARCHAR(64) NOT NULL aheinecke@4999: ); aheinecke@4999: INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt'); aheinecke@4999: INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); aheinecke@4999: INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); aheinecke@4999: INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); aheinecke@4999: aheinecke@4999: CREATE TABLE sobek_kinds ( aheinecke@4999: id int PRIMARY KEY NOT NULL, aheinecke@4999: name VARCHAR(64) NOT NULL aheinecke@4999: ); aheinecke@4999: INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt'); aheinecke@4999: INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); aheinecke@4999: INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); aheinecke@4999: aheinecke@4999: CREATE TABLE boundary_kinds ( aheinecke@4999: id int PRIMARY KEY NOT NULL, aheinecke@4999: name VARCHAR(64) NOT NULL aheinecke@4999: ); aheinecke@4999: INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); aheinecke@4999: INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); aheinecke@4999: INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); aheinecke@4999: INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); aheinecke@4999: ingo@3948: CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; ingo@3948: CREATE TABLE hydr_boundaries ( ingo@3948: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@3948: name VARCHAR(255), aheinecke@4999: kind int REFERENCES boundary_kinds(id), aheinecke@4999: sectie int REFERENCES sectie_kinds(id), aheinecke@4999: sobek int REFERENCES sobek_kinds(id), ingo@3948: path VARCHAR(256) ingo@3948: ); ingo@3950: SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3); ingo@3948: ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); ingo@3948: ingo@3948: ingo@3948: CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; ingo@3948: CREATE TABLE hydr_boundaries_poly ( ingo@3948: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@3948: name VARCHAR(255), aheinecke@4999: kind int REFERENCES boundary_kinds(id), aheinecke@4999: sectie int REFERENCES sectie_kinds(id), aheinecke@4999: sobek int REFERENCES sobek_kinds(id), ingo@3948: path VARCHAR(256) ingo@3948: ); ingo@3950: SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3); ingo@3948: ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); ingo@3948: ingo@3948: ingo@3948: CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; ingo@3948: CREATE TABLE gauge_location ( ingo@3948: id int PRIMARY KEY NOT NULL, tom@4991: river_id int REFERENCES rivers(id) ON DELETE CASCADE, ingo@3948: name VARCHAR(255), ingo@3948: path VARCHAR(256) ingo@3948: ); ingo@3950: SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2); ingo@3948: ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); ingo@3948: ingo@3948: COMMIT;