ingo@1230: BEGIN; ingo@1230: aheinecke@5146: CREATE TABLE axis_kinds( aheinecke@5146: id int PRIMARY KEY NOT NULL, aheinecke@5146: name VARCHAR(64) aheinecke@5146: ); aheinecke@5146: INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); aheinecke@5495: INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse'); aheinecke@5146: INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige'); aheinecke@5146: 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, aheinecke@5146: kind_id int REFERENCES axis_kinds(id) NOT NULL DEFAULT 0, ingo@3948: name VARCHAR(64), ingo@3948: path VARCHAR(256) ingo@1230: ); aheinecke@5137: SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'MULTILINESTRING', 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, tom@5886: fedstate_km FLOAT8, 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 tom@5214: CREATE TABLE cross_section_track_kinds( tom@5214: id int PRIMARY KEY NOT NULL, tom@5214: name VARCHAR(64) tom@5214: ); tom@5214: INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); aheinecke@5495: INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren'); tom@5214: 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, tom@5214: kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, 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: aheinecke@5385: CREATE TABLE building_kinds( aheinecke@5385: id int PRIMARY KEY NOT NULL, aheinecke@5385: name VARCHAR(64) aheinecke@5385: ); aheinecke@5385: INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); aheinecke@5385: INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken'); aheinecke@5385: INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre'); aheinecke@5385: INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel'); ingo@1230: aheinecke@5385: -- Geodaesie/Bauwerke 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, aheinecke@5391: description VARCHAR(256), -- Name taken from attributes, aheinecke@5385: name VARCHAR(256), -- The layername aheinecke@5391: km FLOAT8, aheinecke@5385: kind_id int REFERENCES building_kinds(id) NOT NULL DEFAULT 0, 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, tom@5623: HPGP VARCHAR(64), 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 tom@5214: CREATE TABLE floodplain_kinds( tom@5214: id int PRIMARY KEY NOT NULL, tom@5214: name VARCHAR(64) tom@5214: ); tom@5214: INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); aheinecke@5495: INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue'); tom@5214: 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, tom@5214: kind_id int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0, 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, aheinecke@5210: name VARCHAR(64), felix@5289: range_id INT REFERENCES ranges(id), tom@5291: time_interval_id INT REFERENCES time_intervals(id), aheinecke@5210: projection VARCHAR(32), tom@5783: srid int NOT NULL, aheinecke@5210: elevation_state VARCHAR(32), aheinecke@5210: format VARCHAR(32), aheinecke@5210: border_break BOOLEAN NOT NULL DEFAULT FALSE, aheinecke@5210: resolution VARCHAR(16), aheinecke@5210: description VARCHAR(256), tom@5315: path VARCHAR(256) NOT NULL ingo@1236: ); ingo@1240: ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); ingo@1236: hans@1238: 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), tom@5010: official 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@5137: SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3); 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, tom@5010: official 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 tom@5276: CREATE TABLE floodmap_kinds ( tom@5276: id int PRIMARY KEY NOT NULL, tom@5276: name varchar(64) NOT NULL tom@5276: ); tom@5276: INSERT INTO floodmap_kinds VALUES (200, 'Messung'); tom@5276: INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG'); tom@5276: INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer'); tom@5276: INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG'); tom@5276: INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer'); tom@5276: 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, tom@5276: kind int NOT NULL REFERENCES floodmap_kinds(id), aheinecke@4931: diff FLOAT8, ingo@2796: count int, aheinecke@4931: area FLOAT8, aheinecke@4931: perimeter FLOAT8, tom@5854: waterbody varchar(64), tom@5276: path VARCHAR(256), tom@5276: source varchar(64) 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: ); tom@5748: INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); tom@5748: INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne'); tom@5748: INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich'); tom@5748: INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland'); 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: ); tom@5748: INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); tom@5748: INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt'); tom@5748: INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt'); 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@5513: kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, tom@5748: sectie int REFERENCES sectie_kinds(id), tom@5748: sobek int REFERENCES sobek_kinds(id), ingo@3948: path VARCHAR(256) ingo@3948: ); aheinecke@5182: SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',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@5513: kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, tom@5748: sectie int REFERENCES sectie_kinds(id), tom@5748: sobek int REFERENCES sobek_kinds(id), ingo@3948: path VARCHAR(256) ingo@3948: ); tom@5303: SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',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: tom@5291: tom@5291: CREATE TABLE jetty_kinds( tom@5291: id int PRIMARY KEY NOT NULL, tom@5291: name VARCHAR(64) tom@5291: ); tom@5291: INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); tom@5291: INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); tom@5291: INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); tom@5291: tom@5291: CREATE SEQUENCE JETTIES_ID_SEQ; tom@5291: CREATE TABLE jetties ( tom@5291: id int PRIMARY KEY NOT NULL, tom@5291: river_id int REFERENCES rivers(id) ON DELETE CASCADE, tom@5291: path VARCHAR(256), tom@5291: kind_id int REFERENCES jetty_kinds(id), tom@5291: km FLOAT8, tom@5291: z FLOAT8 tom@5291: ); tom@5291: SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2); tom@5291: ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ'); tom@5291: tom@5409: CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; tom@5409: CREATE TABLE flood_marks ( tom@5409: id int PRIMARY KEY NOT NULL, tom@5409: river_id int REFERENCES rivers(id) ON DELETE CASCADE, tom@5409: path VARCHAR(256), tom@5409: km FLOAT8, tom@5409: z FLOAT8, tom@5409: location VARCHAR(64), tom@5409: year int tom@5409: ); tom@5409: SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2); tom@5409: ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ'); tom@5291: ingo@3948: COMMIT;