sascha@164: BEGIN; sascha@164: ingo@2347: CREATE SEQUENCE UNITS_ID_SEQ; ingo@2347: ingo@2347: CREATE TABLE units ( ingo@2347: id int PRIMARY KEY NOT NULL, ingo@2347: name VARCHAR(32) NOT NULL UNIQUE ingo@2347: ); ingo@2347: sascha@164: -- Gewaesser sascha@168: CREATE SEQUENCE RIVERS_ID_SEQ; sascha@168: sascha@164: CREATE TABLE rivers ( sascha@3946: id int PRIMARY KEY NOT NULL, sascha@3946: official_number int8 UNIQUE, sascha@3946: name VARCHAR(256) NOT NULL UNIQUE, sascha@3946: km_up BOOLEAN NOT NULL DEFAULT true, sascha@3946: wst_unit_id int NOT NULL REFERENCES units(id) sascha@164: ); sascha@164: sascha@164: -- Bruecke, Haefen, etc. sascha@170: CREATE SEQUENCE ATTRIBUTES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE attributes ( sascha@164: 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 ( sascha@164: id int PRIMARY KEY NOT NULL, tom@4991: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, sascha@168: a NUMERIC NOT NULL, sascha@164: b NUMERIC, sascha@164: UNIQUE (river_id, a, b) sascha@164: ); sascha@164: sascha@164: -- Lage 'links', 'rechts', etc. sascha@170: CREATE SEQUENCE POSITIONS_ID_SEQ; sascha@170: sascha@164: CREATE TABLE positions ( sascha@164: 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 ( sascha@758: id int PRIMARY KEY NOT NULL, sascha@758: top NUMERIC, sascha@758: 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 ( sascha@763: 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 ( sascha@164: id int PRIMARY KEY NOT NULL, tom@4991: range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, sascha@168: attribute_id int NOT NULL REFERENCES attributes(id), sascha@758: position_id int REFERENCES positions(id), sascha@763: edge_id int REFERENCES edges(id), sascha@763: 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 ( sascha@2371: 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 tom@4991: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, tom@5207: station NUMERIC NOT NULL, sascha@2371: aeo NUMERIC NOT NULL, tom@5207: official_number int8 UNIQUE, sascha@164: sascha@164: -- Pegelnullpunkt sascha@168: datum NUMERIC NOT NULL, sascha@164: -- Streckengueltigkeit tom@5207: range_id int NOT NULL REFERENCES ranges (id) ON DELETE CASCADE, sascha@164: sascha@167: UNIQUE (name, 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 ( sascha@168: 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 ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@168: name VARCHAR(256) NOT NULL UNIQUE, sascha@168: type_id int NOT NULL REFERENCES main_value_types(id), sascha@164: UNIQUE (name, type_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 ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@168: start_time TIMESTAMP NOT NULL, sascha@164: stop_time TIMESTAMP, sascha@164: CHECK (start_time <= stop_time) sascha@164: ); sascha@164: sascha@164: -- Stammdaten sascha@170: CREATE SEQUENCE MAIN_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE main_values ( sascha@168: id int PRIMARY KEY NOT NULL, tom@4991: gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, sascha@168: named_value_id int NOT NULL REFERENCES named_main_values(id), sascha@168: value NUMERIC NOT NULL, sascha@164: sascha@164: 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 ( sascha@164: id int PRIMARY KEY NOT NULL, tom@4991: gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, sascha@493: description VARCHAR(256) NOT NULL, teichmann@4776: bfg_id VARCHAR(50), sascha@470: kind int NOT NULL DEFAULT 0, sascha@496: time_interval_id int REFERENCES time_intervals(id) sascha@164: sascha@164: -- TODO: better checks sascha@496: -- UNIQUE (gauge_id, kind, time_interval_id) 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 ( sascha@168: id int PRIMARY KEY NOT NULL, tom@4991: table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE, sascha@168: q NUMERIC NOT NULL, sascha@168: 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 ( tom@5202: 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 ( sascha@168: id int PRIMARY KEY NOT NULL, tom@4991: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, sascha@168: description VARCHAR(256) NOT NULL, tom@5202: 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 ( sascha@168: id int PRIMARY KEY NOT NULL, tom@4991: wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE, sascha@168: name VARCHAR(256) NOT NULL, sascha@174: description VARCHAR(256), sascha@471: position int NOT NULL DEFAULT 0, sascha@164: sascha@164: 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 ( sascha@168: id int PRIMARY KEY NOT NULL, tom@4991: wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, sascha@168: position NUMERIC NOT NULL, sascha@168: 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 ( sascha@168: id int PRIMARY KEY NOT NULL, tom@4991: range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, sascha@168: 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 ( sascha@164: id int PRIMARY KEY NOT NULL, tom@4991: wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, tom@4991: 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: sascha@476: CREATE VIEW wst_value_table AS sascha@476: SELECT wcv.position AS position, sascha@476: w, sascha@476: (SELECT q sascha@476: FROM wst_column_q_ranges wcqr sascha@476: JOIN wst_q_ranges wqr sascha@476: ON wcqr.wst_q_range_id = wqr.id sascha@476: JOIN ranges r sascha@476: ON r.id = wqr.range_id sascha@476: WHERE wcqr.wst_column_id = wc.id sascha@476: AND wcv.position BETWEEN r.a AND r.b) AS q, sascha@476: wc.position AS column_pos, sascha@476: w.id AS wst_id sascha@476: FROM wst_column_values wcv sascha@476: JOIN wst_columns wc sascha@476: ON wcv.wst_column_id = wc.id sascha@476: JOIN wsts w sascha@476: ON wc.wst_id = w.id sascha@476: ORDER BY wcv.position ASC, sascha@476: wc.position DESC; sascha@476: sascha@754: -- view to select the w values of a WST sascha@754: CREATE VIEW wst_w_values AS sascha@754: SELECT wcv."position" AS km, sascha@754: wcv.w AS w, sascha@754: 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, sascha@755: wqr.q AS q, sascha@755: 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 ( sascha@1192: id int PRIMARY KEY NOT NULL, tom@4991: river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, sascha@1203: 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 ( sascha@1203: id int PRIMARY KEY NOT NULL, sascha@1203: km NUMERIC NOT NULL, tom@4991: 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 ( sascha@1203: id int PRIMARY KEY NOT NULL, tom@4991: cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE, sascha@1203: col_pos int NOT NULL, sascha@1203: x NUMERIC NOT NULL, sascha@1203: y NUMERIC NOT NULL, sascha@1203: UNIQUE (cross_section_line_id, col_pos) 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 ( sascha@1209: id int PRIMARY KEY NOT NULL, tom@4991: 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 ( sascha@1212: id int PRIMARY KEY NOT NULL, tom@4991: hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE, sascha@1212: km NUMERIC NOT NULL, sascha@1217: 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 ( sascha@1209: id int PRIMARY KEY NOT NULL, sascha@1209: formation_num int NOT NULL DEFAULT 0, tom@4991: hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE, sascha@1209: top NUMERIC NOT NULL, sascha@1209: bottom NUMERIC NOT NULL, sascha@1214: distance_vl NUMERIC NOT NULL, sascha@1214: distance_hf NUMERIC NOT NULL, sascha@1214: 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 ( sascha@1209: 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 ( sascha@1209: id int PRIMARY KEY NOT NULL, tom@4991: formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE, sascha@1209: type_id int NOT NULL REFERENCES hyk_flow_zone_types(id), sascha@1209: a NUMERIC NOT NULL, sascha@1209: b NUMERIC NOT NULL, sascha@1209: CHECK (a <= b) sascha@1209: ); sascha@1209: sascha@3335: CREATE VIEW official_lines sascha@3335: AS sascha@3335: SELECT w.river_id AS river_id, sascha@3335: w.id AS wst_id, sascha@3335: wc.id AS wst_column_id, felix@3470: wc.name AS name, felix@3470: wc.position AS wst_column_pos sascha@3335: FROM wsts w sascha@3335: JOIN wst_columns wc sascha@3335: ON wc.wst_id = w.id sascha@3335: WHERE w.kind = 3; sascha@3335: sascha@3335: CREATE VIEW q_main_values sascha@3335: AS sascha@3335: SELECT riv.id AS river_id, sascha@3335: g.id AS gauge_id, sascha@3335: g.name AS gauge_name, sascha@3335: r.a AS a, sascha@3335: r.b AS b, sascha@3335: REGEXP_REPLACE( sascha@3335: nmv.name, E'[:space:]*\\(.*\\)[:space:]*', '') AS name, sascha@3335: CAST(mv.value AS NUMERIC(38, 2)) AS value sascha@3335: FROM main_values mv sascha@3335: JOIN named_main_values nmv sascha@3335: ON mv.named_value_id = nmv.id sascha@3335: JOIN main_value_types mvt sascha@3335: ON nmv.type_id = mvt.id sascha@3335: JOIN gauges g sascha@3335: ON mv.gauge_id = g.id sascha@3335: JOIN ranges r sascha@3335: ON g.range_id = r.id sascha@3335: JOIN rivers riv sascha@3335: ON g.river_id = riv.id sascha@3335: WHERE mvt.name = 'Q' sascha@3335: ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); sascha@3335: sascha@3335: CREATE VIEW official_q_values sascha@3335: AS sascha@3335: SELECT ol.river_id AS river_id, sascha@3335: wst_id, sascha@3335: wst_column_id, sascha@3335: gauge_id, sascha@3335: gauge_name, sascha@3335: a, sascha@3335: b, sascha@3335: ol.name, felix@3470: value, felix@3470: wst_column_pos sascha@3335: FROM official_lines ol sascha@3335: JOIN q_main_values qmv sascha@3335: ON ol.river_id = qmv.river_id sascha@3335: AND ol.name = qmv.name; sascha@3335: 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;