bjoern@2351: -- ANNOTATION_TYPES bjoern@2351: CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE annotation_types ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: name VARCHAR2(255), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- ANNOTATIONS bjoern@2351: CREATE SEQUENCE ANNOTATIONS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE annotations ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: attribute_id NUMBER(38,0), bjoern@2351: edge_id NUMBER(38,0), bjoern@2351: position_id NUMBER(38,0), bjoern@2351: range_id NUMBER(38,0), bjoern@2351: type_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- ATTRIBUTES bjoern@2351: CREATE SEQUENCE ATTRIBUTES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE attributes ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: value VARCHAR2(255), bjoern@2351: primary key (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- CROSS_SECTION_LINES bjoern@2351: CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE cross_section_lines ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: km NUMBER(38,2), bjoern@2351: cross_section_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- CROSS_SECTION_POINTS bjoern@2351: CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE cross_section_points ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: col_pos NUMBER(38,0), bjoern@2351: x NUMBER(38,2), bjoern@2351: y NUMBER(38,2), bjoern@2353: cross_section_line_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- CROSS_SECTIONS ingo@2855: CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; bjoern@2351: ingo@2855: CREATE TABLE cross_sections ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: sascha@3339: -- Indices for faster access of the points sascha@3339: CREATE INDEX cross_section_lines_km_idx sascha@3339: ON cross_section_lines(km); sascha@3339: CREATE INDEX cross_section_points_line_idx sascha@3339: ON cross_section_points(cross_section_line_id); bjoern@2351: bjoern@2351: -- DISCHARGE_TABLE_VALUES bjoern@2351: CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE discharge_table_values ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: q NUMBER(38,2), bjoern@2351: w NUMBER(38,2), bjoern@2351: table_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- DISCHARGE_TABLES bjoern@2351: CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE discharge_tables ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), teichmann@4776: bfg_id VARCHAR2(50), bjoern@2351: kind NUMBER(38,0), bjoern@2351: gauge_id NUMBER(38,0), bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- EDGES bjoern@2351: CREATE SEQUENCE EDGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE edges ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: bottom NUMBER(38,2), bjoern@2351: top NUMBER(38,2), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- GAUGES bjoern@2351: CREATE SEQUENCE GAUGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE gauges ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5207: aeo NUMBER(38,2) NOT NULL, tom@5207: datum NUMBER(38,2) NOT NULL, tom@5207: name VARCHAR2(255) NOT NULL, tom@5207: station NUMBER(38,2) NOT NULL, tom@5207: official_number NUMBER(38,0) UNIQUE, tom@5207: range_id NUMBER(38,0) NOT NULL, tom@5207: -- remove river id here because range_id references river already bjoern@2351: river_id NUMBER(38,0), tom@5207: PRIMARY KEY (id), tom@5207: UNIQUE (name, river_id), tom@5207: UNIQUE (river_id, station) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYK_ENTRIES bjoern@2351: CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyk_entries ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: km NUMBER(38,2), bjoern@2351: measure TIMESTAMP, bjoern@2351: hyk_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYK_FLOW_ZONE_TYPES bjoern@2351: CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyk_flow_zone_types ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: name VARCHAR2(255), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYK_FLOW_ZONES bjoern@2351: CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyk_flow_zones ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: a NUMBER(38,2), bjoern@2351: b NUMBER(38,2), bjoern@2351: formation_id NUMBER(38,0), bjoern@2351: type_id NUMBER(38,0), bjoern@2351: primary key (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYK_FORMATIONS bjoern@2351: CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyk_formations ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: bottom NUMBER(38,2), bjoern@2351: distance_hf NUMBER(38,2), bjoern@2351: distance_vl NUMBER(38,2), bjoern@2351: distance_vr NUMBER(38,2), bjoern@2351: formation_num NUMBER(38,0), bjoern@2351: top NUMBER(38,2), bjoern@2351: hyk_entry_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYKS bjoern@2351: CREATE SEQUENCE HYKS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyks ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: primary key (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- MAIN_VALUE_TYPES bjoern@2351: CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE main_value_types ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: name VARCHAR2(255), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- MAIN_VALUES bjoern@2351: CREATE SEQUENCE MAIN_VALUES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE main_values ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: value NUMBER(38,2), bjoern@2351: gauge_id NUMBER(38,0), bjoern@2351: named_value_id NUMBER(38,0), bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- NAMED_MAIN_VALUES bjoern@2351: CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE named_main_values ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: name VARCHAR2(255), bjoern@2351: type_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- POSITIONS bjoern@2351: CREATE SEQUENCE POSITIONS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE positions ( bjoern@2353: id NUMBER(38,0) NOT NULL, bjoern@2351: value VARCHAR2(255 char), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: --- RANGES bjoern@2351: CREATE SEQUENCE RANGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE ranges ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5158: a NUMBER(38,10) NOT NULL, sascha@2367: b NUMBER(38,10), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); tom@5321: bjoern@2351: bjoern@2351: bjoern@2351: -- RIVERS bjoern@2351: CREATE SEQUENCE RIVERS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE rivers ( bjoern@2351: id NUMBER(38,0) NOT NULL, sascha@3946: official_number NUMBER(38,0), bjoern@2351: km_up NUMBER(38,0), bjoern@2351: name VARCHAR2(255), bjoern@2351: wst_unit_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- TIME_INTERVALS bjoern@2351: CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; bjoern@2351: aheinecke@5206: CREATE TABLE time_intervals ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5158: start_time TIMESTAMP NOT NULL, bjoern@2351: stop_time TIMESTAMP, tom@5158: PRIMARY KEY (id), tom@5158: CHECK (start_time <= stop_time) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: --- UNITS bjoern@2351: CREATE SEQUENCE UNITS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE units ( bjoern@2353: id NUMBER(38,0) NOT NULL, bjoern@2351: name VARCHAR2(255), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WST_COLUMN_Q_RANGES bjoern@2351: CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wst_column_q_ranges ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: wst_column_id NUMBER(38,0), bjoern@2351: wst_q_range_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WST_COLUMN_VALUES bjoern@2351: CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wst_column_values ( bjoern@2351: id NUMBER(38,0) NOT NULL, sascha@3338: position NUMBER(38,5), sascha@3338: w NUMBER(38,5), bjoern@2351: wst_column_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WST_COLUMNS bjoern@2351: CREATE SEQUENCE WST_COLUMNS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wst_columns ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: name VARCHAR2(255), bjoern@2351: position NUMBER(38,0), bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: wst_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WST_Q_RANGES bjoern@2351: CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wst_q_ranges ( bjoern@2351: id NUMBER(38,0) NOT NULL, sascha@3338: q NUMBER(38,5), bjoern@2351: range_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WSTS tom@5224: --lookup table for wst kinds tom@5224: CREATE TABLE wst_kinds ( tom@5224: id NUMBER PRIMARY KEY NOT NULL, tom@5224: kind VARCHAR(64) NOT NULL tom@5224: ); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels'); tom@5224: tom@5224: bjoern@2351: CREATE SEQUENCE WSTS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wsts ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: kind NUMBER(38,0), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2352: -- ADD CONSTRAINTs aheinecke@5113: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes; bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; bjoern@2352: ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2352: ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2352: ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; aheinecke@5113: ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; bjoern@2352: ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2352: ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; bjoern@2352: ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; bjoern@2352: ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; aheinecke@5113: aheinecke@5113: -- Cascading references aheinecke@5113: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE; aheinecke@5113: ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE; aheinecke@5113: ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; aheinecke@5113: ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE; aheinecke@5113: ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; aheinecke@5113: ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE; aheinecke@5113: ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE; aheinecke@5113: ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE; aheinecke@5113: ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; aheinecke@5113: ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; tom@5224: ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; bjoern@2355: bjoern@2355: -- VIEWS bjoern@2355: bjoern@2355: CREATE VIEW wst_value_table AS bjoern@2355: SELECT wcv.position AS position, bjoern@2355: w, bjoern@2355: (SELECT q bjoern@2355: FROM wst_column_q_ranges wcqr bjoern@2355: JOIN wst_q_ranges wqr bjoern@2355: ON wcqr.wst_q_range_id = wqr.id bjoern@2355: JOIN ranges r bjoern@2355: ON r.id = wqr.range_id bjoern@2355: WHERE wcqr.wst_column_id = wc.id bjoern@2355: AND wcv.position BETWEEN r.a AND r.b) AS q, bjoern@2355: wc.position AS column_pos, bjoern@2355: w.id AS wst_id bjoern@2355: FROM wst_column_values wcv bjoern@2355: JOIN wst_columns wc bjoern@2355: ON wcv.wst_column_id = wc.id bjoern@2355: JOIN wsts w bjoern@2355: ON wc.wst_id = w.id bjoern@2355: ORDER BY wcv.position ASC, bjoern@2355: wc.position DESC; bjoern@2355: bjoern@2355: -- view to select the w values of a WST bjoern@2355: CREATE VIEW wst_w_values AS bjoern@2355: SELECT wcv.position AS km, bjoern@2355: wcv.w AS w, bjoern@2355: wc.position AS column_pos, bjoern@2355: w.id AS wst_id bjoern@2355: FROM wst_column_values wcv bjoern@2355: JOIN wst_columns wc ON wcv.wst_column_id = wc.id bjoern@2355: JOIN wsts w ON wc.wst_id = w.id bjoern@2355: ORDER BY wcv.position, wc.position; bjoern@2355: bjoern@2355: -- view to select the q values of a WST bjoern@2355: CREATE VIEW wst_q_values AS bjoern@2355: SELECT wc.position AS column_pos, bjoern@2355: wqr.q AS q, bjoern@2355: r.a AS a, bjoern@2355: r.b AS b, bjoern@2355: wc.wst_id AS wst_id bjoern@2355: FROM wst_column_q_ranges wcqr bjoern@2355: JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id bjoern@2355: JOIN ranges r ON wqr.range_id = r.id bjoern@2355: JOIN wst_columns wc ON wcqr.wst_column_id = wc.id bjoern@2355: ORDER BY wc.position, wcqr.wst_column_id, r.a; sascha@3335: sascha@3335: -- Views to make the 'Amtlichen Linien' easier to access. sascha@3335: 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, '[:space:]*\(.*\)[:space:]*', '') AS name, sascha@3338: CAST(mv.value AS NUMERIC(38, 5)) 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@3338: ORDER BY g.id, CAST(mv.value AS NUMERIC(38,5)); 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; teichmann@4651: 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;