sascha@164: BEGIN; sascha@164: ingo@2347: CREATE SEQUENCE UNITS_ID_SEQ; ingo@2347: ingo@2347: CREATE TABLE units ( mschaefer@8965: id int PRIMARY KEY NOT NULL, ingo@2347: name VARCHAR(32) NOT NULL UNIQUE ingo@2347: ); ingo@2347: andre@8683: -- SEDDB_NAME tom@8687: -- Lookup table for optional matching with differing river names in SedDB tom@8687: -- Add name here and set rivers.seddb_name_id to id andre@8683: CREATE TABLE seddb_name ( mschaefer@8965: id int PRIMARY KEY NOT NULL, andre@8683: name VARCHAR(256) NOT NULL andre@8683: ); andre@8683: sascha@164: -- Gewaesser sascha@168: CREATE SEQUENCE RIVERS_ID_SEQ; sascha@168: sascha@164: CREATE TABLE rivers ( mschaefer@8965: id int PRIMARY KEY NOT NULL, tom@7743: model_uuid CHAR(36) UNIQUE, mschaefer@8965: official_number int8, sascha@3946: name VARCHAR(256) NOT NULL UNIQUE, mschaefer@8965: km_up int DEFAULT 0 NOT NULL, mschaefer@8965: wst_unit_id int NOT NULL REFERENCES units(id), mschaefer@8965: seddb_name_id int REFERENCES seddb_name(id), tom@8411: CHECK(km_up IN(0,1)) sascha@164: ); sascha@164: sascha@164: -- Bruecke, Haefen, etc. sascha@170: CREATE SEQUENCE ATTRIBUTES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE attributes ( mschaefer@8965: id int PRIMARY KEY NOT NULL, sascha@168: value VARCHAR(256) NOT NULL UNIQUE sascha@164: ); sascha@164: sascha@164: -- segments from/to at a river sascha@170: CREATE SEQUENCE RANGES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE ranges ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8965: a NUMERIC NOT NULL, mschaefer@8965: b NUMERIC, tom@7005: UNIQUE (river_id, a, b), tom@7005: CHECK (a < b) sascha@164: ); tom@5297: sascha@164: sascha@164: -- Lage 'links', 'rechts', etc. sascha@170: CREATE SEQUENCE POSITIONS_ID_SEQ; sascha@170: sascha@164: CREATE TABLE positions ( mschaefer@8965: id int PRIMARY KEY NOT NULL, sascha@168: value VARCHAR(256) NOT NULL UNIQUE sascha@164: ); sascha@164: sascha@758: -- Kante 'oben', 'unten' sascha@758: CREATE SEQUENCE EDGES_ID_SEQ; sascha@758: sascha@758: CREATE TABLE edges ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: top NUMERIC, mschaefer@8965: bottom NUMERIC sascha@758: ); sascha@758: sascha@763: -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) sascha@763: CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; sascha@763: sascha@763: CREATE TABLE annotation_types ( mschaefer@8965: id int PRIMARY KEY NOT NULL, sascha@763: name VARCHAR(256) NOT NULL UNIQUE sascha@763: ); sascha@763: sascha@164: -- Some object (eg. Hafen) at a segment of river sascha@164: -- plus its position. sascha@170: CREATE SEQUENCE ANNOTATIONS_ID_SEQ; sascha@170: sascha@164: CREATE TABLE annotations ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, mschaefer@8965: attribute_id int NOT NULL REFERENCES attributes(id), mschaefer@8965: position_id int REFERENCES positions(id), mschaefer@8965: edge_id int REFERENCES edges(id), mschaefer@8965: type_id int REFERENCES annotation_types(id) sascha@164: ); sascha@164: sascha@164: -- Pegel sascha@170: CREATE SEQUENCE GAUGES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE gauges ( mschaefer@8965: id int PRIMARY KEY NOT NULL, sascha@2371: name VARCHAR(256) NOT NULL, tom@5207: -- remove river id here because range_id references river already mschaefer@8965: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8965: station NUMERIC NOT NULL, mschaefer@8965: aeo NUMERIC NOT NULL, mschaefer@8965: official_number int8, sascha@164: sascha@164: -- Pegelnullpunkt mschaefer@8965: datum NUMERIC NOT NULL, sascha@164: -- Streckengueltigkeit mschaefer@8965: range_id int REFERENCES ranges (id) ON DELETE CASCADE, sascha@164: sascha@167: UNIQUE (name, river_id), tom@5894: UNIQUE (official_number, river_id), sascha@194: UNIQUE (river_id, station) sascha@164: ); sascha@164: sascha@164: -- Type of a Hauptwert 'W', 'Q', 'D', etc. sascha@170: CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE main_value_types ( mschaefer@8965: id int PRIMARY KEY NOT NULL, sascha@168: name VARCHAR(256) NOT NULL UNIQUE sascha@164: ); sascha@164: sascha@164: -- Named type of a Hauptwert (eg. HQ100) sascha@170: CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE named_main_values ( mschaefer@8965: id int PRIMARY KEY NOT NULL, tom@5915: name VARCHAR(256) NOT NULL, mschaefer@8965: type_id int NOT NULL REFERENCES main_value_types(id) sascha@164: ); sascha@164: sascha@164: -- Table for time intervals sascha@170: CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; sascha@170: sascha@164: CREATE TABLE time_intervals ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: start_time TIMESTAMP NOT NULL, mschaefer@8965: stop_time TIMESTAMP, sascha@164: CHECK (start_time <= stop_time) sascha@164: ); tom@5298: sascha@164: sascha@164: -- Stammdaten sascha@170: CREATE SEQUENCE MAIN_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE main_values ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, mschaefer@8965: named_value_id int NOT NULL REFERENCES named_main_values(id), mschaefer@8965: value NUMERIC NOT NULL, sascha@164: mschaefer@8965: time_interval_id int REFERENCES time_intervals(id), sascha@164: sascha@164: -- TODO: better checks sascha@164: UNIQUE (gauge_id, named_value_id, time_interval_id) sascha@164: ); sascha@164: sascha@164: -- Abflusstafeln sascha@170: CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; sascha@170: sascha@167: CREATE TABLE discharge_tables ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, sascha@493: description VARCHAR(256) NOT NULL, teichmann@4776: bfg_id VARCHAR(50), mschaefer@8965: kind int NOT NULL DEFAULT 0, mschaefer@8965: time_interval_id int REFERENCES time_intervals(id), tom@5923: UNIQUE(gauge_id, bfg_id, kind) sascha@164: ); sascha@164: sascha@164: -- Values of the Abflusstafeln sascha@170: CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE discharge_table_values ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE, mschaefer@8965: q NUMERIC NOT NULL, mschaefer@8965: w NUMERIC NOT NULL, sascha@164: sascha@164: UNIQUE (table_id, q, w) sascha@164: ); sascha@164: sascha@164: -- WST files tom@5202: --lookup table for wst kinds tom@5202: CREATE TABLE wst_kinds ( mschaefer@8965: id int PRIMARY KEY NOT NULL, tom@5202: kind VARCHAR(64) NOT NULL tom@5202: ); tom@5202: INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); tom@5202: INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks'); tom@5202: INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst'); tom@5202: INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials'); tom@5202: INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points'); tom@5202: INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points'); tom@5202: INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences'); tom@5202: INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels'); tom@5202: tom@5202: sascha@171: CREATE SEQUENCE WSTS_ID_SEQ; sascha@170: sascha@171: CREATE TABLE wsts ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, sascha@168: description VARCHAR(256) NOT NULL, mschaefer@8965: kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0, sascha@164: -- TODO: more meta infos sascha@164: UNIQUE (river_id, description) sascha@164: ); sascha@164: sascha@164: -- columns of WST files sascha@171: CREATE SEQUENCE WST_COLUMNS_ID_SEQ; sascha@170: sascha@171: CREATE TABLE wst_columns ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE, sascha@168: name VARCHAR(256) NOT NULL, sascha@174: description VARCHAR(256), tom@6502: source VARCHAR(256), mschaefer@8965: position int NOT NULL DEFAULT 0, sascha@164: mschaefer@8965: time_interval_id int REFERENCES time_intervals(id), sascha@164: sascha@471: UNIQUE (wst_id, name), sascha@471: UNIQUE (wst_id, position) sascha@164: ); sascha@164: sascha@164: -- w values in WST file column sascha@170: CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE wst_column_values ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, mschaefer@8965: position NUMERIC NOT NULL, mschaefer@8965: w NUMERIC NOT NULL, sascha@164: sascha@164: UNIQUE (position, wst_column_id), sascha@164: UNIQUE (position, wst_column_id, w) sascha@164: ); sascha@164: sascha@164: -- bind q values to range sascha@170: CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE wst_q_ranges ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, mschaefer@8965: q NUMERIC NOT NULL sascha@164: ); sascha@164: sascha@164: -- bind q ranges to wst columns sascha@170: CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE wst_column_q_ranges ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, mschaefer@8965: wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE, sascha@164: sascha@164: UNIQUE (wst_column_id, wst_q_range_id) sascha@164: ); sascha@164: teichmann@6300: CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; teichmann@6300: teichmann@6300: CREATE TABLE official_lines ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, mschaefer@8965: named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE, teichmann@6300: teichmann@6300: UNIQUE (wst_column_id, named_main_value_id) teichmann@6300: ); teichmann@6300: sascha@476: CREATE VIEW wst_value_table AS tom@8658: SELECT tom@6078: wcv.position AS position, tom@8658: w, tom@6078: q, tom@6078: wc.position AS column_pos, tom@6078: w.id AS wst_id tom@6078: FROM wsts w tom@6078: JOIN wst_columns wc tom@6078: ON wc.wst_id=w.id tom@6078: JOIN wst_column_q_ranges wcqr tom@6078: ON wcqr.wst_column_id=wc.id tom@6078: JOIN wst_q_ranges wqr tom@6078: ON wcqr.wst_q_range_id=wqr.id tom@6078: JOIN ranges r tom@6078: ON wqr.range_id=r.id tom@6078: JOIN wst_column_values wcv felix@6177: ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b sascha@476: ORDER BY wcv.position ASC, tom@6078: wc.position DESC; sascha@476: sascha@754: -- view to select the w values of a WST sascha@754: CREATE VIEW wst_w_values AS tom@8658: SELECT wcv."position" AS km, tom@8658: wcv.w AS w, tom@8658: wc."position" AS column_pos, sascha@754: w.id AS wst_id sascha@754: FROM wst_column_values wcv sascha@754: JOIN wst_columns wc ON wcv.wst_column_id = wc.id sascha@754: JOIN wsts w ON wc.wst_id = w.id sascha@754: ORDER BY wcv."position", wc."position"; sascha@754: sascha@755: -- view to select the q values of a WST sascha@755: CREATE VIEW wst_q_values AS sascha@755: SELECT wc.position AS column_pos, tom@8658: wqr.q AS q, tom@8658: r.a AS a, sascha@755: r.b AS b, sascha@755: wc.wst_id AS wst_id sascha@755: FROM wst_column_q_ranges wcqr sascha@755: JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id sascha@755: JOIN ranges r ON wqr.range_id = r.id sascha@755: JOIN wst_columns wc ON wcqr.wst_column_id = wc.id sascha@755: ORDER BY wc.position, wcqr.wst_column_id, r.a; sascha@755: sascha@1192: -- data for the cross-sections sascha@1192: sascha@1192: CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; sascha@1192: sascha@1192: CREATE TABLE cross_sections ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8965: time_interval_id int REFERENCES time_intervals(id), sascha@1202: description VARCHAR(256) sascha@1192: ); sascha@1192: sascha@1205: CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; sascha@1203: sascha@1203: CREATE TABLE cross_section_lines ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: km NUMERIC NOT NULL, mschaefer@8965: cross_section_id int NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE, sascha@1203: UNIQUE (km, cross_section_id) sascha@1203: ); sascha@1203: sascha@1192: CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; sascha@1192: sascha@1192: CREATE TABLE cross_section_points ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE, mschaefer@8965: col_pos int NOT NULL, mschaefer@8965: x NUMERIC NOT NULL, mschaefer@8965: y NUMERIC NOT NULL sascha@1192: ); sascha@1192: sascha@3796: -- Indices for faster access of the points sascha@3796: CREATE INDEX cross_section_lines_km_idx sascha@3796: ON cross_section_lines(km); sascha@3796: CREATE INDEX cross_section_points_line_idx sascha@3796: ON cross_section_points(cross_section_line_id); sascha@3796: sascha@1209: -- Hydraulische Kenngroessen sascha@1209: sascha@1209: CREATE SEQUENCE HYKS_ID_SEQ; sascha@1209: sascha@1209: CREATE TABLE hyks ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, sascha@1209: description VARCHAR(256) NOT NULL sascha@1209: ); sascha@1209: sascha@1209: CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; sascha@1209: sascha@1209: CREATE TABLE hyk_entries ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE, mschaefer@8965: km NUMERIC NOT NULL, mschaefer@8965: measure TIMESTAMP, sascha@1209: UNIQUE (hyk_id, km) sascha@1209: ); sascha@1209: sascha@1209: CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; sascha@1209: sascha@1209: CREATE TABLE hyk_formations ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: formation_num int NOT NULL DEFAULT 0, mschaefer@8965: hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE, mschaefer@8965: top NUMERIC NOT NULL, mschaefer@8965: bottom NUMERIC NOT NULL, mschaefer@8965: distance_vl NUMERIC NOT NULL, mschaefer@8965: distance_hf NUMERIC NOT NULL, mschaefer@8965: distance_vr NUMERIC NOT NULL, sascha@1209: UNIQUE (hyk_entry_id, formation_num) sascha@1209: ); sascha@1209: sascha@1209: CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; sascha@1209: sascha@1209: CREATE TABLE hyk_flow_zone_types ( mschaefer@8965: id int PRIMARY KEY NOT NULL, sascha@1209: name VARCHAR(50) NOT NULL UNIQUE, sascha@1209: description VARCHAR(256) sascha@1209: ); sascha@1209: sascha@1209: CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; sascha@1209: sascha@1209: CREATE TABLE hyk_flow_zones ( mschaefer@8965: id int PRIMARY KEY NOT NULL, mschaefer@8965: formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE, mschaefer@8965: type_id int NOT NULL REFERENCES hyk_flow_zone_types(id), mschaefer@8965: a NUMERIC NOT NULL, mschaefer@8965: b NUMERIC NOT NULL, sascha@1209: CHECK (a <= b) sascha@1209: ); sascha@1209: teichmann@4651: CREATE VIEW wst_ranges teichmann@4651: AS teichmann@4651: SELECT wc.id AS wst_column_id, teichmann@4651: wc.wst_id AS wst_id, teichmann@4651: Min(wcv.position) AS a, teichmann@4651: Max(wcv.position) AS b teichmann@4651: FROM wst_columns wc teichmann@4651: JOIN wst_column_values wcv teichmann@4651: ON wc.id = wcv.wst_column_id teichmann@4651: GROUP BY wc.id, teichmann@4651: wc.wst_id; teichmann@4651: sascha@164: COMMIT;