Mercurial > dive4elements > river
view flys-backend/doc/schema/postgresql.sql @ 4445:0eca080fc162
Move inverted from LongitudinalSectionGenerator to XYChartGenerator
author | Björn Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Wed, 07 Nov 2012 14:22:54 +0100 |
parents | 948c7289fc42 |
children | 83a42e6a562d |
line wrap: on
line source
BEGIN; CREATE SEQUENCE UNITS_ID_SEQ; CREATE TABLE units ( id int PRIMARY KEY NOT NULL, name VARCHAR(32) NOT NULL UNIQUE ); -- Gewaesser CREATE SEQUENCE RIVERS_ID_SEQ; CREATE TABLE rivers ( id int PRIMARY KEY NOT NULL, official_number int8 UNIQUE, name VARCHAR(256) NOT NULL UNIQUE, km_up BOOLEAN NOT NULL DEFAULT true, wst_unit_id int NOT NULL REFERENCES units(id) ); -- Bruecke, Haefen, etc. CREATE SEQUENCE ATTRIBUTES_ID_SEQ; CREATE TABLE attributes ( id int PRIMARY KEY NOT NULL, value VARCHAR(256) NOT NULL UNIQUE ); -- segments from/to at a river CREATE SEQUENCE RANGES_ID_SEQ; CREATE TABLE ranges ( id int PRIMARY KEY NOT NULL, river_id int NOT NULL REFERENCES rivers(id), a NUMERIC NOT NULL, b NUMERIC, UNIQUE (river_id, a, b) ); -- Lage 'links', 'rechts', etc. CREATE SEQUENCE POSITIONS_ID_SEQ; CREATE TABLE positions ( id int PRIMARY KEY NOT NULL, value VARCHAR(256) NOT NULL UNIQUE ); -- Kante 'oben', 'unten' CREATE SEQUENCE EDGES_ID_SEQ; CREATE TABLE edges ( id int PRIMARY KEY NOT NULL, top NUMERIC, bottom NUMERIC ); -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; CREATE TABLE annotation_types ( id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL UNIQUE ); -- Some object (eg. Hafen) at a segment of river -- plus its position. CREATE SEQUENCE ANNOTATIONS_ID_SEQ; CREATE TABLE annotations ( id int PRIMARY KEY NOT NULL, range_id int NOT NULL REFERENCES ranges(id), attribute_id int NOT NULL REFERENCES attributes(id), position_id int REFERENCES positions(id), edge_id int REFERENCES edges(id), type_id int REFERENCES annotation_types(id) ); -- Pegel CREATE SEQUENCE GAUGES_ID_SEQ; CREATE TABLE gauges ( id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL, river_id int NOT NULL REFERENCES rivers(id), station NUMERIC NOT NULL UNIQUE, aeo NUMERIC NOT NULL, official_number int8 UNIQUE, -- Pegelnullpunkt datum NUMERIC NOT NULL, -- Streckengueltigkeit range_id int REFERENCES ranges (id), UNIQUE (name, river_id), UNIQUE (river_id, station) ); -- Type of a Hauptwert 'W', 'Q', 'D', etc. CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; CREATE TABLE main_value_types ( id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL UNIQUE ); -- Named type of a Hauptwert (eg. HQ100) CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; CREATE TABLE named_main_values ( id int PRIMARY KEY NOT NULL, name VARCHAR(256) NOT NULL UNIQUE, type_id int NOT NULL REFERENCES main_value_types(id), UNIQUE (name, type_id) ); -- Table for time intervals CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; CREATE TABLE time_intervals ( id int PRIMARY KEY NOT NULL, start_time TIMESTAMP NOT NULL, stop_time TIMESTAMP, CHECK (start_time <= stop_time) ); -- Stammdaten CREATE SEQUENCE MAIN_VALUES_ID_SEQ; CREATE TABLE main_values ( id int PRIMARY KEY NOT NULL, gauge_id int NOT NULL REFERENCES gauges(id), named_value_id int NOT NULL REFERENCES named_main_values(id), value NUMERIC NOT NULL, time_interval_id int REFERENCES time_intervals(id), -- TODO: better checks UNIQUE (gauge_id, named_value_id, time_interval_id) ); -- Abflusstafeln CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; CREATE TABLE discharge_tables ( id int PRIMARY KEY NOT NULL, gauge_id int NOT NULL REFERENCES gauges(id), description VARCHAR(256) NOT NULL, kind int NOT NULL DEFAULT 0, time_interval_id int REFERENCES time_intervals(id) -- TODO: better checks -- UNIQUE (gauge_id, kind, time_interval_id) ); -- Values of the Abflusstafeln CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; CREATE TABLE discharge_table_values ( id int PRIMARY KEY NOT NULL, table_id int NOT NULL REFERENCES discharge_tables(id), q NUMERIC NOT NULL, w NUMERIC NOT NULL, UNIQUE (table_id, q, w) ); -- WST files CREATE SEQUENCE WSTS_ID_SEQ; CREATE TABLE wsts ( id int PRIMARY KEY NOT NULL, river_id int NOT NULL REFERENCES rivers(id), description VARCHAR(256) NOT NULL, kind int NOT NULL DEFAULT 0, -- TODO: more meta infos UNIQUE (river_id, description) ); -- columns of WST files CREATE SEQUENCE WST_COLUMNS_ID_SEQ; CREATE TABLE wst_columns ( id int PRIMARY KEY NOT NULL, wst_id int NOT NULL REFERENCES wsts(id), name VARCHAR(256) NOT NULL, description VARCHAR(256), position int NOT NULL DEFAULT 0, time_interval_id int REFERENCES time_intervals(id), UNIQUE (wst_id, name), UNIQUE (wst_id, position) ); -- w values in WST file column CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; CREATE TABLE wst_column_values ( id int PRIMARY KEY NOT NULL, wst_column_id int NOT NULL REFERENCES wst_columns(id), position NUMERIC NOT NULL, w NUMERIC NOT NULL, UNIQUE (position, wst_column_id), UNIQUE (position, wst_column_id, w) ); -- bind q values to range CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; CREATE TABLE wst_q_ranges ( id int PRIMARY KEY NOT NULL, range_id int NOT NULL REFERENCES ranges(id), q NUMERIC NOT NULL ); -- bind q ranges to wst columns CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; CREATE TABLE wst_column_q_ranges ( id int PRIMARY KEY NOT NULL, wst_column_id int NOT NULL REFERENCES wst_columns(id), wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), UNIQUE (wst_column_id, wst_q_range_id) ); CREATE VIEW wst_value_table AS SELECT wcv.position AS position, w, (SELECT q FROM wst_column_q_ranges wcqr JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id JOIN ranges r ON r.id = wqr.range_id WHERE wcqr.wst_column_id = wc.id AND wcv.position BETWEEN r.a AND r.b) AS q, wc.position AS column_pos, w.id AS wst_id FROM wst_column_values wcv JOIN wst_columns wc ON wcv.wst_column_id = wc.id JOIN wsts w ON wc.wst_id = w.id ORDER BY wcv.position ASC, wc.position DESC; -- view to select the w values of a WST CREATE VIEW wst_w_values AS SELECT wcv."position" AS km, wcv.w AS w, wc."position" AS column_pos, w.id AS wst_id FROM wst_column_values wcv JOIN wst_columns wc ON wcv.wst_column_id = wc.id JOIN wsts w ON wc.wst_id = w.id ORDER BY wcv."position", wc."position"; -- view to select the q values of a WST CREATE VIEW wst_q_values AS SELECT wc.position AS column_pos, wqr.q AS q, r.a AS a, r.b AS b, wc.wst_id AS wst_id FROM wst_column_q_ranges wcqr JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id JOIN ranges r ON wqr.range_id = r.id JOIN wst_columns wc ON wcqr.wst_column_id = wc.id ORDER BY wc.position, wcqr.wst_column_id, r.a; -- data for the cross-sections CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; CREATE TABLE cross_sections ( id int PRIMARY KEY NOT NULL, river_id int NOT NULL REFERENCES rivers(id), time_interval_id int REFERENCES time_intervals(id), description VARCHAR(256) ); CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; CREATE TABLE cross_section_lines ( id int PRIMARY KEY NOT NULL, km NUMERIC NOT NULL, cross_section_id int NOT NULL REFERENCES cross_sections(id), UNIQUE (km, cross_section_id) ); CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; CREATE TABLE cross_section_points ( id int PRIMARY KEY NOT NULL, cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id), col_pos int NOT NULL, x NUMERIC NOT NULL, y NUMERIC NOT NULL, UNIQUE (cross_section_line_id, col_pos) ); -- Indices for faster access of the points CREATE INDEX cross_section_lines_km_idx ON cross_section_lines(km); CREATE INDEX cross_section_points_line_idx ON cross_section_points(cross_section_line_id); -- Hydraulische Kenngroessen CREATE SEQUENCE HYKS_ID_SEQ; CREATE TABLE hyks ( id int PRIMARY KEY NOT NULL, river_id int NOT NULL REFERENCES rivers(id), description VARCHAR(256) NOT NULL ); CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; CREATE TABLE hyk_entries ( id int PRIMARY KEY NOT NULL, hyk_id int NOT NULL REFERENCES hyks(id), km NUMERIC NOT NULL, measure TIMESTAMP, UNIQUE (hyk_id, km) ); CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; CREATE TABLE hyk_formations ( id int PRIMARY KEY NOT NULL, formation_num int NOT NULL DEFAULT 0, hyk_entry_id int NOT NULL REFERENCES hyk_entries(id), top NUMERIC NOT NULL, bottom NUMERIC NOT NULL, distance_vl NUMERIC NOT NULL, distance_hf NUMERIC NOT NULL, distance_vr NUMERIC NOT NULL, UNIQUE (hyk_entry_id, formation_num) ); CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; CREATE TABLE hyk_flow_zone_types ( id int PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(256) ); CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; CREATE TABLE hyk_flow_zones ( id int PRIMARY KEY NOT NULL, formation_id int NOT NULL REFERENCES hyk_formations(id), type_id int NOT NULL REFERENCES hyk_flow_zone_types(id), a NUMERIC NOT NULL, b NUMERIC NOT NULL, CHECK (a <= b) ); CREATE VIEW official_lines AS SELECT w.river_id AS river_id, w.id AS wst_id, wc.id AS wst_column_id, wc.name AS name, wc.position AS wst_column_pos FROM wsts w JOIN wst_columns wc ON wc.wst_id = w.id WHERE w.kind = 3; CREATE VIEW q_main_values AS SELECT riv.id AS river_id, g.id AS gauge_id, g.name AS gauge_name, r.a AS a, r.b AS b, REGEXP_REPLACE( nmv.name, E'[:space:]*\\(.*\\)[:space:]*', '') AS name, CAST(mv.value AS NUMERIC(38, 2)) AS value FROM main_values mv JOIN named_main_values nmv ON mv.named_value_id = nmv.id JOIN main_value_types mvt ON nmv.type_id = mvt.id JOIN gauges g ON mv.gauge_id = g.id JOIN ranges r ON g.range_id = r.id JOIN rivers riv ON g.river_id = riv.id WHERE mvt.name = 'Q' ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); CREATE VIEW official_q_values AS SELECT ol.river_id AS river_id, wst_id, wst_column_id, gauge_id, gauge_name, a, b, ol.name, value, wst_column_pos FROM official_lines ol JOIN q_main_values qmv ON ol.river_id = qmv.river_id AND ol.name = qmv.name; COMMIT;