Mercurial > dive4elements > river

annotate etl/src/main/resources/sql/flys-common.properties @ 6601:5ecc6d4d73f2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add official fixings to Waterlevel CSV Export (issue1384) This searches the collection for staticwqkms artifacts that contain official data and adds that data to the export. The data is filtered by the calculation range and sorted by the calculation direction.
author Andre Heinecke <aheinecke@intevation.de>
date Thu, 18 Jul 2013 13:16:33 +0200
parents 3bb6c2a3ef72
children 60f39ee19473
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 \
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