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, ingo@1230: river_id int REFERENCES rivers(id), ingo@1230: kind int NOT NULL DEFAULT 0 ingo@1230: ); ingo@1230: SELECT AddGeometryColumn('river_axes', 'geom', 31466, '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, bjoern@2340: river_id int REFERENCES rivers(id), bjoern@2340: km NUMERIC NOT NULL bjoern@2340: ); bjoern@2340: SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, '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, ingo@1230: river_id int REFERENCES rivers(id), ingo@1230: km NUMERIC NOT NULL, ingo@1230: z NUMERIC NOT NULL DEFAULT 0 ingo@1230: ); ingo@1230: SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, '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, ingo@1230: river_id int REFERENCES rivers(id), ingo@2365: kind VARCHAR(16) NOT NULL, ingo@1230: z NUMERIC DEFAULT 0 ingo@1230: ); ingo@1230: SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4); 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, ingo@1230: river_id int REFERENCES rivers(id), bjoern@2339: name VARCHAR(256) ingo@1230: ); ingo@1230: SELECT AddGeometryColumn('buildings', 'geom', 31466, '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, ingo@1230: river_id int REFERENCES rivers(id), ingo@1230: x int, ingo@1230: y int, ingo@1230: km NUMERIC NOT NULL, ingo@1230: HPGP VARCHAR(2) ingo@1230: ); ingo@1230: SELECT AddGeometryColumn('fixpoints', 'geom', 31466, '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, ingo@1237: river_id int REFERENCES rivers(id) ingo@1237: ); bjoern@2356: SELECT AddGeometryColumn('floodplain', 'geom', 31466, '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, ingo@1236: river_id int REFERENCES rivers(id), ingo@1236: -- XXX Should we use the ranges table instead? ingo@1236: lower NUMERIC, ingo@1236: upper NUMERIC, ingo@1236: path VARCHAR(256), ingo@1236: UNIQUE (river_id, lower, upper) 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, bjoern@1241: river_id int REFERENCES rivers(id), bjoern@2339: area NUMERIC, bjoern@2339: name VARCHAR(256) bjoern@1241: ); bjoern@1241: SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); bjoern@1241: ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); bjoern@1241: bjoern@1241: bjoern@2339: --Hydrologie/HW-Schutzanlagen/hws.shp bjoern@2339: CREATE SEQUENCE HWS_ID_SEQ; bjoern@2339: CREATE TABLE hws ( bjoern@2339: id int PRIMARY KEY NOT NULL, bjoern@2339: river_id int REFERENCES rivers(id), bjoern@2339: hws_facility VARCHAR(256), ingo@2363: type VARCHAR(256) bjoern@2339: ); bjoern@2356: SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); sascha@2376: ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); bjoern@1241: 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@2797: CREATE SEQUENCE FLOODMAPS_SEQ; ingo@2797: CREATE FUNCTION floodmaps_id_func() RETURNS trigger AS $floodmaps_id_func$ ingo@2797: BEGIN ingo@2797: NEW.id := nextval('floodmaps_seq'); ingo@2797: RETURN NEW; ingo@2797: END; ingo@2797: $floodmaps_id_func$ LANGUAGE plpgsql; ingo@2797: ingo@2796: CREATE TABLE floodmaps ( ingo@2796: id int PRIMARY KEY NOT NULL, ingo@2796: river_id int REFERENCES rivers(id), ingo@2797: name varchar(64) NOT NULL, ingo@2796: kind int NOT NULL, ingo@2797: diff real, ingo@2796: count int, ingo@2797: area real, ingo@2797: perimeter real ingo@2796: ); ingo@2797: SELECT AddGeometryColumn('floodmaps', 'geom', 31466, '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@2797: ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_SEQ'); ingo@2796: ingo@2797: CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps ingo@2797: FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func(); ingo@1230: END;