Mercurial > dive4elements > river
annotate flys-aft/src/main/resources/sql/flys-common.properties @ 5246:d9deec811152
Removed accidentally ommitted file.
author | Felix Wolfsteller <felix.wolfsteller@intevation.de> |
---|---|
date | Tue, 12 Mar 2013 12:15:47 +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 |