Mercurial > dive4elements > river
annotate flys-aft/src/main/resources/sql/flys-common.properties @ 4798:39885bdfc6fc
Added calculation of the "Umhuellende" to calculation of "W fuer ungleichwertige Abfluesse".
This is done by figuring out the WST columns that imfold the data and then do
simple "gleichwertige" calculations from the start of the interval. This
is too much because only the Qs are needed for the "Umhuellende".
author | Sascha L. Teichmann <teichmann@intevation.de> |
---|---|
date | Sun, 13 Jan 2013 16:18:28 +0100 |
parents | d9f1202cef78 |
children | 46e984a3d576 |
rev | line source |
---|---|
4753
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
1 select.rivers = \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
2 SELECT r.id AS id, r.name AS name, min(wcv.position) AS min_km, max(wcv.position) AS max_km \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
3 FROM rivers r \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
4 JOIN wsts w ON r.id = w.river_id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
5 JOIN wst_columns wc ON w.id = wc.wst_id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
6 JOIN wst_column_values wcv ON wcv.wst_column_id = wc.id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
7 WHERE w.kind = 0 \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
8 GROUP BY r.id, r.name |
4085
067341e86375
Separate FLYS gauges to be updated or to create.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4076
diff
changeset
|
9 select.gauges = SELECT id, name, official_number FROM gauges WHERE river_id = :river_id |
4087
aad1886ea226
Store new gauges in FLYS db.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4085
diff
changeset
|
10 next.gauge.id = SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id |
aad1886ea226
Store new gauges in FLYS db.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4085
diff
changeset
|
11 insert.gauge = INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \ |
aad1886ea226
Store new gauges in FLYS db.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4085
diff
changeset
|
12 VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum) |
4089
859b4781554a
Prefetch existing time intervals from FLYS db.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4087
diff
changeset
|
13 select.timeintervals = SELECT id, start_time, stop_time FROM time_intervals |
4090
d556e29592f5
Create new discharge tables if needed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4089
diff
changeset
|
14 next.timeinterval.id = SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id |
d556e29592f5
Create new discharge tables if needed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4089
diff
changeset
|
15 insert.timeinterval = INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:id, :start_time, :stop_time) |
d556e29592f5
Create new discharge tables if needed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4089
diff
changeset
|
16 next.discharge.id = SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
17 insert.dischargetable = \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
18 INSERT INTO discharge_tables \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
19 (id, gauge_id, description, bfg_id, kind, time_interval_id) \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
20 VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id) |
4091
a91c7e982c32
Make W/Q values from AFT and FLYS loadable.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4090
diff
changeset
|
21 select.discharge.table.values = SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id |
4092
b3fc044f75ba
Added code to store W/Q values into FLYS database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4091
diff
changeset
|
22 next.discharge.table.values.id = SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id |
4096
82f5266f881b
Add code to build the difference of the W/Q values of two discharge tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4092
diff
changeset
|
23 insert.discharge.table.value = INSERT INTO discharge_table_values (id, table_id, w, q) VALUES (:id, :table_id, :w, :q) |
82f5266f881b
Add code to build the difference of the W/Q values of two discharge tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4092
diff
changeset
|
24 delete.discharge.table.value = DELETE FROM discharge_table_values WHERE id = :id |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
25 select.gauge.discharge.tables = \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
26 SELECT \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
27 dt.id AS id, \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
28 dt.description AS description, \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
29 ti.start_time AS start_time, \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
30 ti.stop_time AS stop_time, \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
31 dt.bfg_id AS bfg_id \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
32 FROM discharge_tables dt \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
33 LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
34 WHERE gauge_id = :gauge_id |