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;