Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/SedimentLoadDataFactory.java @ 8215:204905c16ade

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fetch missing values from database and show them in diagrams.
author Tom Gottfried <tom@intevation.de>
date Mon, 08 Sep 2014 12:21:32 +0200
parents 3bb00338228c
children 25c3a62863d2
comparison
equal deleted inserted replaced
8214:ceeeaf228fe4 8215:204905c16ade
29 public static final String CACHE_NAME = "sediment-load-data"; 29 public static final String CACHE_NAME = "sediment-load-data";
30 30
31 public static final String SUSPENDED_STRING = "Schwebstoff"; 31 public static final String SUSPENDED_STRING = "Schwebstoff";
32 32
33 public static final String SQL_LOAD_RIVER_SEDIMENT_LOADS = 33 public static final String SQL_LOAD_RIVER_SEDIMENT_LOADS =
34 "WITH load_at_river AS (" +
35 "SELECT DISTINCT sl.id, " +
36 "sl.kind, " +
37 "sl.description, " +
38 "sl.time_interval_id, " +
39 "sl.grain_fraction_id, " +
40 "sl.sq_time_interval_id " +
41 "FROM sediment_load sl " +
42 "JOIN sediment_load_values slv ON sl.id = slv.sediment_load_id " +
43 "JOIN measurement_station ms ON ms.id = slv.measurement_station_id " +
44 "JOIN rivers r ON r.id = ms.river_id " +
45 "WHERE r.name = :river) " +
34 "SELECT " + 46 "SELECT " +
35 "sl.id AS sl_id, " + 47 "sl.id AS sl_id, " +
36 "sl.kind AS sl_kind, " + 48 "sl.kind AS sl_kind, " +
37 "sl.description AS sl_description, " + 49 "sl.description AS sl_description, " +
38 "ti.start_time AS ti_start_time, " + 50 "ti.start_time AS ti_start_time, " +
43 "slv.value AS slv_value, " + 55 "slv.value AS slv_value, " +
44 "gf.name AS gf_name, " + 56 "gf.name AS gf_name, " +
45 "ms.id AS ms_id, " + 57 "ms.id AS ms_id, " +
46 "ms.station AS ms_station, " + 58 "ms.station AS ms_station, " +
47 "ms.measurement_type AS ms_type " + 59 "ms.measurement_type AS ms_type " +
48 "FROM sediment_load_values slv " + 60 "FROM load_at_river sl " +
49 "JOIN sediment_load sl ON slv.sediment_load_id = sl.id " + 61 "CROSS JOIN measurement_station ms " +
50 "JOIN time_intervals ti ON sl.time_interval_id = ti.id " + 62 "JOIN time_intervals ti ON sl.time_interval_id = ti.id " +
51 "LEFT JOIN time_intervals sqti ON sl.sq_time_interval_id = sqti.id " + 63 "LEFT JOIN time_intervals sqti ON sl.sq_time_interval_id = sqti.id " +
52 "JOIN grain_fraction gf ON sl.grain_fraction_id = gf.id " + 64 "JOIN grain_fraction gf ON sl.grain_fraction_id = gf.id " +
53 "JOIN measurement_station ms ON slv.measurement_station_id = ms.id " +
54 "JOIN rivers r ON ms.river_id = r.id " + 65 "JOIN rivers r ON ms.river_id = r.id " +
55 "WHERE r.name = :river " + 66 "LEFT JOIN sediment_load_values slv " +
67 "ON ms.id=slv.measurement_station_id AND sl.id=slv.sediment_load_id " +
68 "WHERE (" +
69 "(ms.measurement_type='Geschiebe' AND gf.name IN " +
70 "('coarse', 'fine_middle', 'sand', 'susp_sand', " +
71 "'susp_sand_bed', 'bed_load', 'bed_load_susp_sand', " +
72 "'suspended_load', 'total')) " +
73 "OR " +
74 "(ms.measurement_type='Schwebstoff' AND gf.name IN " +
75 "('suspended_sediment', 'suspended_load', 'total'))) " +
76 "AND r.name = :river " +
56 "ORDER BY sl.id"; 77 "ORDER BY sl.id";
57 78
58 public static final SedimentLoadDataFactory INSTANCE = 79 public static final SedimentLoadDataFactory INSTANCE =
59 new SedimentLoadDataFactory(); 80 new SedimentLoadDataFactory();
60 81
130 151
131 List<Object[]> list = sqlQuery.list(); 152 List<Object[]> list = sqlQuery.list();
132 153
133 for (Object [] row: list) { 154 for (Object [] row: list) {
134 155
156 // Load
135 Integer sl_id = (Integer)row[0]; 157 Integer sl_id = (Integer)row[0];
136 Integer sl_kind = (Integer)row[1]; 158 Integer sl_kind = (Integer)row[1];
137 String sl_description = (String)row[2]; 159 String sl_description = (String)row[2];
138 Timestamp ti_start_time = (Timestamp)row[3]; 160 Timestamp ti_start_time = (Timestamp)row[3];
139 Timestamp ti_stop_time = (Timestamp)row[4]; 161 Timestamp ti_stop_time = (Timestamp)row[4];
140 Timestamp sq_start_time = (Timestamp)row[5]; 162 Timestamp sq_start_time = (Timestamp)row[5];
141 Timestamp sq_stop_time = (Timestamp)row[6]; 163 Timestamp sq_stop_time = (Timestamp)row[6];
142 Integer sq_id = (Integer)row[7]; 164 Integer sq_id = (Integer)row[7];
165
166 // Value
143 Double slv_value = (Double)row[8]; 167 Double slv_value = (Double)row[8];
144 String gf_name = (String)row[9]; 168 String gf_name = (String)row[9];
169
170 // Station
145 Integer ms_id = (Integer)row[10]; 171 Integer ms_id = (Integer)row[10];
146 Double ms_station = (Double)row[11]; 172 Double ms_station = (Double)row[11];
147 String ms_type = (String)row[12]; 173 String ms_type = (String)row[12];
148 174
149 if (load == null || load.getId() != sl_id) { 175 if (load == null || load.getId() != sl_id) {
179 id2station.put(ms_id, station); 205 id2station.put(ms_id, station);
180 } 206 }
181 207
182 station.addValue( 208 station.addValue(
183 grainFractionIndex, 209 grainFractionIndex,
184 new SedimentLoadData.Value(load, slv_value)); 210 new SedimentLoadData.Value(load, slv_value == null
211 ? Double.NaN
212 : slv_value));
185 } 213 }
186 214
187 SedimentLoadData sld = new SedimentLoadData(id2station.values(), 215 SedimentLoadData sld = new SedimentLoadData(id2station.values(),
188 RiverFactory.getRiver(river).getKmUp()); 216 RiverFactory.getRiver(river).getKmUp());
189 217
http://dive4elements.wald.intevation.org