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: 
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@4953:     kind       int, -- 1 BfG / 2 Land / 3 Sonstige
aheinecke@4953:     sectie     int, -- 1 stromführend / 2 stromspeichernd
aheinecke@4953:     sobek      int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
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@4953:     kind       int, -- 1 BfG / 2 Land / 3 Sonstige
aheinecke@4953:     sectie     int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
aheinecke@4953:     sobek      int, -- 1 stromführend / 2 stromspeichernd
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;