Mercurial > dive4elements > river

annotate flys-aft/src/main/resources/sql/flys-common.properties @ 5312:2c1045a1e3fe

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added new states and transitions to UESK calculation and adjusted states and UI. TODO: Some refactoring and code cleanup.
author Raimund Renkert <rrenkert@intevation.de>
date Thu, 14 Mar 2013 17:25:00 +0100
parents da1e897c7224
children 11fba4dabe05
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
5257
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
9 select.gauges = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
10 SELECT id, name, official_number \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
11 FROM gauges \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
12 WHERE river_id = :river_id
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
13 next.gauge.id = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
14 SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
15 insert.gauge = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
16 INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
17 VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum)
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
18 select.timeintervals = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
19 SELECT id, start_time, stop_time FROM time_intervals
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
20 next.timeinterval.id = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
21 SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
22 insert.timeinterval = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
23 INSERT INTO time_intervals (id, start_time, stop_time) \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
24 VALUES (:id, :start_time, :stop_time)
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
25 next.discharge.id = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
26 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
27 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
28 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
29 (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
30 VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id)
5257
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
31 select.discharge.table.values = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
32 SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
33 next.discharge.table.values.id = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
34 SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
35 insert.discharge.table.value = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
36 INSERT INTO discharge_table_values (id, table_id, w, q) \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
37 VALUES (:id, :table_id, :w, :q)
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
38 delete.discharge.table.value = \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
39 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
40 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
41 SELECT \
5257
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
42 dt.id AS 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
43 dt.description AS description, \
5257
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
44 ti.start_time AS start_time, \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
45 ti.stop_time AS stop_time, \
46e984a3d576 Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
46 dt.bfg_id AS bfg_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
47 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
48 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
49 WHERE gauge_id = :gauge_id
5258
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5257
diff changeset
50 select.gauge.master.discharge.table = \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5257
diff changeset
51 SELECT \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5257
diff changeset
52 dt.id AS id, \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5257
diff changeset
53 dt.bfg_id AS bfg_id \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5257
diff changeset
54 FROM discharge_tables dt JOIN gauges g ON dt.gauge_id = g.id \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5257
diff changeset
55 WHERE g.id = :gauge_id AND g.kind = 0
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5257
diff changeset
56 update.bfg.id.discharge.table = \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5257
diff changeset
57 UPDATE discharge_tables SET bfg_id = :bfg_id WHERE id = :id
http://dive4elements.wald.intevation.org