
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@5146: INSERT INTO axis_kinds(id, name) VALUES (1, 'Aktuell');
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,
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');
tom@5214: INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell');
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,
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
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');
tom@5214: INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell');
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),
aheinecke@5210:     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@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: );
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: );
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@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: );
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;




