Mercurial > dive4elements > river

view flys-backend/doc/schema/postgresql-minfo.sql @ 4837:9e25c7523485

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed calculation of effective width in MINFO SQ relation. * Get all (including empty datasets) from db. * Filter empty datasets when processing data of the same date. * Added debug outputs.
author Raimund Renkert <rrenkert@intevation.de>
date Wed, 23 Jan 2013 11:14:41 +0100
parents 504cd5801785
children b79eb203032d
line wrap: on
line source
BEGIN;

CREATE SEQUENCE LOCATION_SYSTEM_SEQ;

CREATE TABLE location_system (
    id          int NOT NULL,
    name        VARCHAR(32)  NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY(id)
);


CREATE SEQUENCE ELEVATION_MODEL_SEQ;

CREATE TABLE elevation_model (
    id          int NOT NULL,
    name        VARCHAR(32)  NOT NULL,
    unit_id     int NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
);

CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;

CREATE TABLE bed_height_type (
    id          int NOT NULL,
    name        VARCHAR(16)  NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY(id)
);



CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;

CREATE TABLE bed_height_single (
    id                      int NOT NULL,
    river_id                int NOT NULL,
    year                    int NOT NULL,
    sounding_width          int NOT NULL,
    type_id                 int NOT NULL,
    location_system_id      int NOT NULL,
    cur_elevation_model_id  int NOT NULL,
    old_elevation_model_id  int,
    range_id                int NOT NULL,
    evaluation_by           VARCHAR(255),
    description             VARCHAR(255),
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
    CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
    CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
);


CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;

CREATE TABLE bed_height_epoch (
    id                      int NOT NULL,
    river_id                int NOT NULL,
    time_interval_id        int NOT NULL,
    -- sounding_with           int NOT NULL,
    -- type_id                 int NOT NULL,
    cur_elevation_model_id  int NOT NULL,
    old_elevation_model_id  int,
    range_id                int NOT NULL,
    evaluation_by           VARCHAR(255),
    description             VARCHAR(255),
    PRIMARY KEY(id),
    CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
    CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
);


CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;

CREATE TABLE bed_height_single_values (
    id                      int NOT NULL,
    bed_height_single_id    int NOT NULL,
    station                 NUMERIC NOT NULL,
    height                  NUMERIC,
    uncertainty             NUMERIC,
    data_gap                NUMERIC,
    sounding_width          NUMERIC,
    width                   NUMERIC,
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
);


CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;

CREATE TABLE bed_height_epoch_values (
    id                      int NOT NULL,
    bed_height_epoch_id     int NOT NULL,
    station                 NUMERIC NOT NULL,
    height                  NUMERIC,
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
);


CREATE SEQUENCE DEPTHS_ID_SEQ;

CREATE TABLE depths (
    id      int NOT NULL,
    lower   NUMERIC NOT NULL,
    upper   NUMERIC NOT NULL,
    unit_id int NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;

CREATE TABLE sediment_density (
    id          int NOT NULL,
    river_id    int NOT NULL,
    depth_id    int NOT NULL,
    unit_id     int NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY(id),
    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
    CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;

CREATE TABLE sediment_density_values (
    id                  int NOT NULL,
    sediment_density_id int NOT NULL,
    station             NUMERIC NOT NULL,
    density             NUMERIC NOT NULL,
    description         VARCHAR(256),
    year                int,
    PRIMARY KEY(id),
    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
);


CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;

CREATE TABLE morphologic_width (
    id          int NOT NULL,
    river_id    int NOT NULL,
    unit_id     int NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
    CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
);


CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;

CREATE TABLE morphologic_width_values (
    id                      int NOT NULL,
    morphologic_width_id    int NOT NULL,
    station                 NUMERIC NOT NULL,
    width                   NUMERIC NOT NULL,
    description             VARCHAR(256),
    PRIMARY KEY(id),
    CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
);


CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;

CREATE TABLE discharge_zone (
    id                      int NOT NULL,
    river_id                int NOT NULL,
    gauge_name              VARCHAR(64)  NOT NULL, -- this is not very proper, but there are gauges with no db instance
    value                   NUMERIC NOT NULL,
    lower_discharge         VARCHAR(16)  NOT NULL,
    upper_discharge         VARCHAR(16),
    PRIMARY KEY(id),
    CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
);


CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;

CREATE TABLE flow_velocity_model (
    id                  int NOT NULL,
    river_id            int NOT NULL,
    discharge_zone_id   int NOT NULL,
    description         VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
);


CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;

CREATE TABLE flow_velocity_model_values (
    id                      int NOT NULL,
    flow_velocity_model_id  int NOT NULL,
    station                 NUMERIC NOT NULL,
    q                       NUMERIC NOT NULL,
    total_channel           NUMERIC NOT NULL,
    main_channel            NUMERIC NOT NULL,
    shear_stress            NUMERIC NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
);



CREATE SEQUENCE FV_MEASURE_ID_SEQ;

CREATE TABLE flow_velocity_measurements (
    id          int NOT NULL,
    river_id    int NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
);

CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;

CREATE TABLE flow_velocity_measure_values (
    id              int NOT NULL,
    measurements_id int NOT NULL,
    station         NUMERIC NOT NULL,
    datetime        TIMESTAMP,
    w               NUMERIC NOT NULL,
    q               NUMERIC NOT NULL,
    v               NUMERIC NOT NULL,
    description     VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
);


CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;

CREATE TABLE grain_fraction (
    id      int   NOT NULL,
    name    VARCHAR(64)    NOT NULL,
    lower   NUMERIC,
    upper   NUMERIC,
    unit_id int,
    PRIMARY KEY (id),
    CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;

CREATE TABLE sediment_yield (
    id                  int NOT NULL,
    river_id            int NOT NULL,
    grain_fraction_id   int,
    unit_id             int NOT NULL,
    time_interval_id    int NOT NULL,
    description         VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
    CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
    CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
);


CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;

CREATE TABLE sediment_yield_values (
    id                  int NOT NULL,
    sediment_yield_id   int NOT NULL,
    station             NUMERIC NOT NULL,
    value               NUMERIC NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
);


CREATE SEQUENCE WATERLEVEL_ID_SEQ;

CREATE TABLE waterlevel (
    id          int NOT NULL,
    river_id    int NOT NULL,
    unit_id     int NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;

CREATE TABLE waterlevel_q_range (
    id              int NOT NULL,
    waterlevel_id   int NOT NULL,
    q               NUMERIC NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
);


CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ;

CREATE TABLE waterlevel_values (
    id                      int NOT NULL,
    waterlevel_q_range_id   int NOT NULL,
    station                 NUMERIC NOT NULL,
    w                       NUMERIC NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
);


CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;

CREATE TABLE waterlevel_difference (
    id          int NOT NULL,
    river_id    int NOT NULL,
    unit_id     int NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
    CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;

CREATE TABLE waterlevel_difference_column (
    id              int NOT NULL,
    difference_id   int NOT NULL,
    description     VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
);


CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;

CREATE TABLE waterlevel_difference_values (
    id          int NOT NULL,
    column_id   int NOT NULL,
    station     NUMERIC NOT NULL,
    value       NUMERIC NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
);


CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
CREATE TABLE measurement_station (
	id			             int          NOT NULL,
	name		             VARCHAR(256) NOT NULL,
	river_id                 int          NOT NULL,
	station                  NUMERIC      NOT NULL,
	range_id                 int          NOT NULL,
	measurement_type         VARCHAR(64)  NOT NULL,
	riverside                VARCHAR(16),
	reference_gauge_id       int,
	observation_timerange_id int,
	operator				 VARCHAR(64),
	comment					 VARCHAR(512),
	PRIMARY KEY (id),
	CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
	CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id),
	CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id),
	CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
	UNIQUE (river_id, station)
);


CREATE SEQUENCE SQ_RELATION_ID_SEQ;

CREATE TABLE sq_relation (
    id               int NOT NULL,
    river_id         int NOT NULL,
    time_interval_id int NOT NULL,
    description      VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
);


CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;

CREATE TABLE sq_relation_value (
    id             int NOT NULL,
    sq_relation_id int NOT NULL,
    parameter      VARCHAR(16)  NOT NULL,
    fraction       VARCHAR(32)  NOT NULL,
    function       VARCHAR(32)  NOT NULL,
    km             NUMERIC NOT NULL,
    a              NUMERIC NOT NULL,
    b              NUMERIC NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id)
);
COMMIT;
http://dive4elements.wald.intevation.org