Mercurial > dive4elements > river

annotate etl/src/main/resources/sql/flys-common.properties @ 9378:063befcaef74

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added datacage for S-Info collision and U-Info salix line, added datacage grouping for previous calculations of collisions, flood durations, salix lines
author mschaefer
date Mon, 06 Aug 2018 11:51:19 +0200
parents e5675e85ac38
children
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 = \
6895
e5675e85ac38 Potential fix for flys/1453.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6894
diff changeset
10 SELECT id, name, official_number, station \
5257
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 \
5995
3bb6c2a3ef72 River ETL: Limit the discharge tables to load to the historical (kind = 1) ones. This prevents the wrong tables (values) to be deleted.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5930
diff changeset
49 WHERE dt.gauge_id = :gauge_id AND dt.kind = 1
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 \
5479
11fba4dabe05 AFT/DIPS: Fixed wrond SQL statement.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5258
diff changeset
55 WHERE g.id = :gauge_id AND dt.kind = 0
5258
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
5930
33e514a0c6b7 Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5838
diff changeset
58 delete.discharge.table.values = \
33e514a0c6b7 Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5838
diff changeset
59 DELETE from discharge_table_values where table_id = :id
33e514a0c6b7 Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5838
diff changeset
60 delete.discharge.table = \
33e514a0c6b7 Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5838
diff changeset
61 DELETE from discharge_tables where id = :id and kind = 1
http://dive4elements.wald.intevation.org