view etl/src/main/resources/sql/flys-common.properties @ 6229:3fea9701d58d

Fix gauge determination. If we look upstream ( a > b ) we still have to compare the start value of our range against the gauge and not the end value. The start value is always the relevant value as we handle the direction by checking this against A or B of the gauge. Also: I will never compare doubles with == again. I will never compare doubles with == again. I will never compare doubles with == again. I will never compare doubles with == again. I will never compare doubles with == again.
author Andre Heinecke <aheinecke@intevation.de>
date Thu, 06 Jun 2013 18:23:17 +0200
parents 3bb6c2a3ef72
children 60f39ee19473
line wrap: on
line source
select.rivers = \
    SELECT r.id AS id, r.name AS name, min(wcv.position) AS min_km, max(wcv.position) AS max_km \
    FROM rivers r \
        JOIN wsts w ON r.id = w.river_id \
        JOIN wst_columns wc ON w.id = wc.wst_id \
        JOIN wst_column_values wcv ON wcv.wst_column_id = wc.id \
    WHERE w.kind = 0 \
    GROUP BY r.id, r.name
select.gauges = \
    SELECT id, name, official_number \
    FROM gauges \
    WHERE river_id = :river_id
next.gauge.id = \
    SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id
insert.gauge = \
    INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \
    VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum)
select.timeintervals = \
    SELECT id, start_time, stop_time FROM time_intervals
next.timeinterval.id = \
    SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id
insert.timeinterval = \
    INSERT INTO time_intervals (id, start_time, stop_time) \
    VALUES (:id, :start_time, :stop_time)
next.discharge.id = \
    SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id
insert.dischargetable = \
    INSERT INTO discharge_tables \
    (id, gauge_id, description, bfg_id, kind, time_interval_id) \
    VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id)
select.discharge.table.values = \
    SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id
next.discharge.table.values.id = \
    SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id
insert.discharge.table.value = \
    INSERT INTO discharge_table_values (id, table_id, w, q) \
    VALUES (:id, :table_id, :w, :q)
delete.discharge.table.value = \
    DELETE FROM discharge_table_values WHERE id = :id
select.gauge.discharge.tables = \
    SELECT \
        dt.id          AS id, \
        dt.description AS description, \
        ti.start_time  AS start_time, \
        ti.stop_time   AS stop_time, \
        dt.bfg_id      AS bfg_id \
    FROM discharge_tables dt \
    LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \
    WHERE dt.gauge_id = :gauge_id AND dt.kind = 1
select.gauge.master.discharge.table = \
    SELECT \
        dt.id AS id, \
        dt.bfg_id AS bfg_id \
    FROM discharge_tables dt JOIN gauges g ON dt.gauge_id = g.id \
    WHERE g.id = :gauge_id AND dt.kind = 0
update.bfg.id.discharge.table = \
    UPDATE discharge_tables SET bfg_id = :bfg_id WHERE id = :id
delete.discharge.table.values = \
    DELETE from discharge_table_values where table_id = :id
delete.discharge.table = \
    DELETE from discharge_tables where id = :id and kind = 1

http://dive4elements.wald.intevation.org