
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,
sascha@168:     river_id int             NOT NULL REFERENCES rivers(id),
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,
sascha@168:     range_id     int             NOT NULL REFERENCES ranges(id),
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,
sascha@2371:     river_id        int             NOT NULL REFERENCES rivers(id),
sascha@2371:     station         NUMERIC         NOT NULL UNIQUE,
sascha@2371:     aeo             NUMERIC         NOT NULL,
sascha@2371:     official_number int8                     UNIQUE,
sascha@164: 
sascha@164:     -- Pegelnullpunkt
sascha@168:     datum    NUMERIC NOT NULL,
sascha@164:     -- Streckengueltigkeit
sascha@2373:     range_id int REFERENCES ranges (id),
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,
sascha@168:     gauge_id       int NOT NULL REFERENCES gauges(id),
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,
sascha@164:     gauge_id         int NOT NULL REFERENCES gauges(id),
sascha@493:     description      VARCHAR(256) NOT NULL,
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,
sascha@168:     table_id int NOT NULL REFERENCES discharge_tables(id),
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
sascha@171: CREATE SEQUENCE WSTS_ID_SEQ;
sascha@170: 
sascha@171: CREATE TABLE wsts (
sascha@168:     id          int PRIMARY KEY NOT NULL,
sascha@168:     river_id    int NOT NULL REFERENCES rivers(id),
sascha@168:     description VARCHAR(256) NOT NULL,
sascha@470:     kind        int NOT NULL 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,
sascha@173:     wst_id      int NOT NULL REFERENCES wsts(id),
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,
sascha@171:     wst_column_id int NOT NULL REFERENCES wst_columns(id),
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,
sascha@168:     range_id int NOT NULL REFERENCES ranges(id),
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,
sascha@173:     wst_column_id  int NOT NULL REFERENCES wst_columns(id),
sascha@164:     wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id),
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,
sascha@1192:     river_id         int             NOT NULL REFERENCES rivers(id),
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,
sascha@1203:     cross_section_id int             NOT NULL REFERENCES cross_sections(id),
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,
sascha@1203:     cross_section_line_id int             NOT NULL REFERENCES cross_section_lines(id),
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,
sascha@1226:     river_id    int             NOT NULL REFERENCES rivers(id),
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,
sascha@1212:     hyk_id      int             NOT NULL REFERENCES hyks(id),
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,
sascha@1209:     hyk_entry_id  int             NOT NULL REFERENCES hyk_entries(id),
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,
sascha@1209:     formation_id int             NOT NULL REFERENCES hyk_formations(id),
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: 
sascha@164: COMMIT;




