Mercurial > dive4elements > river

comparison flys-artifacts/src/main/java/org/dive4elements/river/artifacts/model/sq/MeasurementFactory.java @ 5831:bd047b71ab37

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Repaired internal references
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 12:06:39 +0200
parents flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java@14db045d6368
children
comparison
equal deleted inserted replaced
5830:160f53ee0870 5831:bd047b71ab37
1 package org.dive4elements.river.artifacts.model.sq;
2
3 import java.util.ArrayList;
4 import java.util.Calendar;
5 import java.util.Collections;
6 import java.util.Date;
7 import java.util.HashMap;
8 import java.util.List;
9 import java.util.Map;
10 import java.util.TreeMap;
11
12 import org.apache.log4j.Logger;
13
14 import org.hibernate.SQLQuery;
15 import org.hibernate.Session;
16
17 import org.hibernate.transform.BasicTransformerAdapter;
18
19 import org.hibernate.type.StandardBasicTypes;
20
21 import org.dive4elements.river.artifacts.model.DateRange;
22
23 import org.dive4elements.river.backend.SedDBSessionHolder;
24
25 public class MeasurementFactory
26 {
27 private static final Logger log =
28 Logger.getLogger(MeasurementFactory.class);
29
30 public static final String SQL_TOTALS =
31 "SELECT " +
32 "m.Q_BPEGEL AS Q_BPEGEL,"+
33 "m.TSCHWEB AS TSCHWEB," +
34 "m.TSAND AS TSAND " +
35 "FROM MESSUNG m " +
36 "JOIN STATION s ON m.STATIONID = s.STATIONID " +
37 "JOIN GEWAESSER r ON s.GEWAESSERID = r.GEWAESSERID " +
38 "WHERE " +
39 "r.NAME = :river_name " +
40 "AND m.Q_BPEGEL IS NOT NULL " +
41 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " +
42 "AND m.DATUM BETWEEN :from AND :to " +
43 "AND m.DATUM IS NOT NULL";
44
45 public static final String SQL_FACTIONS =
46 "SELECT " +
47 "m.datum AS DATUM," +
48 "m.Q_BPEGEL AS Q_BPEGEL,"+
49 "g.GLOTRECHTEID AS GLOTRECHTEID," +
50 "gp.LFDNR AS LFDNR," +
51 "g.UFERABST AS UFERABST," +
52 "g.UFERABLINKS AS UFERABLINKS," +
53 "m.TSCHWEB AS TSCHWEB," +
54 "m.TSAND AS TSAND," +
55 "gp.GTRIEB_F AS GTRIEB," +
56 "m.TGESCHIEBE AS TGESCHIEBE," +
57 "si.SIEB01 AS SIEB01, si.SIEB02 AS SIEB02," +
58 "si.SIEB03 AS SIEB03, si.SIEB04 AS SIEB04," +
59 "si.SIEB05 AS SIEB05, si.SIEB06 AS SIEB06," +
60 "si.SIEB07 AS SIEB07, si.SIEB08 AS SIEB08," +
61 "si.SIEB09 AS SIEB09, si.SIEB10 AS SIEB10," +
62 "si.SIEB11 AS SIEB11, si.SIEB12 AS SIEB12," +
63 "si.SIEB13 AS SIEB13, si.SIEB14 AS SIEB14," +
64 "si.SIEB15 AS SIEB15, si.SIEB16 AS SIEB16," +
65 "si.SIEB17 AS SIEB17, si.SIEB18 AS SIEB18," +
66 "si.SIEB19 AS SIEB19, si.SIEB20 AS SIEB20," +
67 "si.SIEB21 AS SIEB21," +
68 "gs.RSIEB01 AS RSIEB01, gs.RSIEB02 AS RSIEB02," +
69 "gs.RSIEB03 AS RSIEB03, gs.RSIEB04 AS RSIEB04," +
70 "gs.RSIEB05 AS RSIEB05, gs.RSIEB06 AS RSIEB06," +
71 "gs.RSIEB07 AS RSIEB07, gs.RSIEB08 AS RSIEB08," +
72 "gs.RSIEB09 AS RSIEB09, gs.RSIEB10 AS RSIEB10," +
73 "gs.RSIEB11 AS RSIEB11, gs.RSIEB12 AS RSIEB12," +
74 "gs.RSIEB13 AS RSIEB13, gs.RSIEB14 AS RSIEB14," +
75 "gs.RSIEB15 AS RSIEB15, gs.RSIEB16 AS RSIEB16," +
76 "gs.RSIEB17 AS RSIEB17, gs.RSIEB18 AS RSIEB18," +
77 "gs.RSIEB19 AS RSIEB19, gs.RSIEB20 AS RSIEB20," +
78 "gs.RSIEB21 AS RSIEB21, gs.REST AS REST " +
79 "FROM MESSUNG m " +
80 "JOIN STATION s ON m.STATIONID = s.STATIONID " +
81 "JOIN GEWAESSER r ON s.GEWAESSERID = r.GEWAESSERID " +
82 "JOIN GLOTRECHTE g ON m.MESSUNGID = g.MESSUNGID " +
83 "JOIN GPROBE gp ON g.GLOTRECHTEID = gp.GLOTRECHTEID " +
84 "JOIN GSIEBUNG gs ON g.GLOTRECHTEID = gs.GLOTRECHTEID " +
85 "JOIN GSIEBSATZ si ON m.GSIEBSATZID = si.GSIEBSATZID " +
86 "WHERE " +
87 "r.NAME = :river_name " +
88 "AND m.Q_BPEGEL IS NOT NULL " +
89 "AND s.KM BETWEEN :location - 0.001 AND :location + 0.001 " +
90 "AND m.DATUM BETWEEN :from AND :to " +
91 "AND m.TGESCHIEBE IS NOT NULL " +
92 "AND m.DATUM IS NOT NULL " +
93 "AND (" +
94 "COALESCE(gs.RSIEB01, 0) + COALESCE(gs.RSIEB02, 0) +" +
95 "COALESCE(gs.RSIEB03, 0) + COALESCE(gs.RSIEB04, 0) +" +
96 "COALESCE(gs.RSIEB05, 0) + COALESCE(gs.RSIEB06, 0) +" +
97 "COALESCE(gs.RSIEB07, 0) + COALESCE(gs.RSIEB08, 0) +" +
98 "COALESCE(gs.RSIEB09, 0) + COALESCE(gs.RSIEB10, 0) +" +
99 "COALESCE(gs.RSIEB11, 0) + COALESCE(gs.RSIEB12, 0) +" +
100 "COALESCE(gs.RSIEB13, 0) + COALESCE(gs.RSIEB14, 0) +" +
101 "COALESCE(gs.RSIEB15, 0) + COALESCE(gs.RSIEB16, 0) +" +
102 "COALESCE(gs.RSIEB17, 0) + COALESCE(gs.RSIEB18, 0) +" +
103 "COALESCE(gs.RSIEB19, 0) + COALESCE(gs.RSIEB20, 0) +" +
104 "COALESCE(gs.RSIEB21, 0) + COALESCE(gs.REST, 0)) >= 0 " +
105 "ORDER BY " +
106 "m.DATUM, g.UFERABST, g.GLOTRECHTEID, gp.LFDNR";
107
108 public static final BasicTransformerAdapter TOTALS_TRANSFORMER =
109 new BasicTransformerAdapter() {
110 private static final long serialVersionUID = 1L;
111
112 @Override
113 public Object transformTuple(Object [] tuple, String [] aliases) {
114 Map<String, Object> map = new HashMap<String, Object>();
115 for (int i = 0; i < tuple.length; ++i) {
116 Object value = tuple[i];
117 if (value != null) {
118 map.put(aliases[i], value);
119 }
120 }
121 return new Measurement(map, Collections.<Sieve>emptyList());
122 }
123 };
124
125 private static final int index(String s) {
126 return Integer.parseInt(s.substring(s.length()-2))-1;
127 }
128
129 public static final BasicTransformerAdapter FRACTIONS_TRANSFORMER =
130 new BasicTransformerAdapter() {
131 private static final long serialVersionUID = 1L;
132
133 @Override
134 public Object transformTuple(Object [] tuple, String [] aliases) {
135 Map<String, Object> map = new HashMap<String, Object>();
136
137 Sieve [] sieves = new Sieve[21];
138
139 List<Sieve> validSieves = new ArrayList<Sieve>(21);
140
141 for (int i = 0; i < tuple.length; ++i) {
142 Object value = tuple[i];
143 if (value == null) {
144 continue;
145 }
146 String alias = aliases[i];
147 if (alias.startsWith("SIEB")
148 || alias.startsWith("RSIEB")) {
149 int idx = index(alias);
150 Sieve s = sieves[idx];
151 double v = (Double)value;
152 if (s == null) {
153 s = new Sieve();
154 sieves[idx] = s;
155 }
156 if (alias.startsWith("SIEB")) {
157 s.setDiameter(v);
158 }
159 else {
160 s.setLoad(v);
161 }
162 }
163 else if (alias.equals("REST")) {
164 Sieve s = new Sieve(0d, (Double)value);
165 validSieves.add(s);
166 }
167 else {
168 map.put(alias, value);
169 }
170
171 }
172 for (Sieve s: sieves) {
173 if (s != null) {
174 validSieves.add(s);
175 }
176 }
177 return new Measurement(map, validSieves);
178 }
179 };
180
181 private MeasurementFactory() {
182 }
183
184 public static Measurements getMeasurements(
185 String river,
186 double location,
187 DateRange dateRange
188 ) {
189 Session session = SedDBSessionHolder.HOLDER.get();
190 try {
191 List<Measurement> totals = loadTotals(
192 session, river, location, dateRange);
193
194 List<Measurement> accumulated = loadFractions(
195 session, river, location, dateRange);
196
197 return new Measurements(totals, accumulated);
198 }
199 finally {
200 session.close();
201 }
202 }
203
204 protected static List<Measurement> loadTotals(
205 Session session,
206 String river,
207 double location,
208 DateRange dateRange
209 ) {
210 SQLQuery query = session.createSQLQuery(SQL_TOTALS)
211 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE)
212 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE)
213 .addScalar("TSAND", StandardBasicTypes.DOUBLE);
214
215 query.setString("river_name", river);
216 query.setDouble("location", location);
217 query.setDate("from", dateRange.getFrom());
218 query.setDate("to", dateRange.getTo());
219
220 query.setResultTransformer(TOTALS_TRANSFORMER);
221
222 @SuppressWarnings("unchecked")
223 List<Measurement> result = (List<Measurement>)query.list();
224 return result;
225 }
226
227 protected static List<Measurement> loadFractions(
228 Session session,
229 String river,
230 double location,
231 DateRange dateRange
232 ) {
233 boolean debug = log.isDebugEnabled();
234
235 SQLQuery query = session.createSQLQuery(SQL_FACTIONS)
236 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE)
237 .addScalar("DATUM", StandardBasicTypes.DATE)
238 .addScalar("GLOTRECHTEID", StandardBasicTypes.INTEGER)
239 .addScalar("LFDNR", StandardBasicTypes.INTEGER)
240 .addScalar("UFERABST", StandardBasicTypes.DOUBLE)
241 .addScalar("UFERABLINKS", StandardBasicTypes.DOUBLE)
242 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE)
243 .addScalar("TSAND", StandardBasicTypes.DOUBLE)
244 .addScalar("GTRIEB", StandardBasicTypes.DOUBLE)
245 .addScalar("TGESCHIEBE", StandardBasicTypes.DOUBLE)
246 .addScalar("RSIEB01", StandardBasicTypes.DOUBLE)
247 .addScalar("RSIEB02", StandardBasicTypes.DOUBLE)
248 .addScalar("RSIEB03", StandardBasicTypes.DOUBLE)
249 .addScalar("RSIEB04", StandardBasicTypes.DOUBLE)
250 .addScalar("RSIEB05", StandardBasicTypes.DOUBLE)
251 .addScalar("RSIEB06", StandardBasicTypes.DOUBLE)
252 .addScalar("RSIEB07", StandardBasicTypes.DOUBLE)
253 .addScalar("RSIEB08", StandardBasicTypes.DOUBLE)
254 .addScalar("RSIEB09", StandardBasicTypes.DOUBLE)
255 .addScalar("RSIEB10", StandardBasicTypes.DOUBLE)
256 .addScalar("RSIEB11", StandardBasicTypes.DOUBLE)
257 .addScalar("RSIEB12", StandardBasicTypes.DOUBLE)
258 .addScalar("RSIEB13", StandardBasicTypes.DOUBLE)
259 .addScalar("RSIEB14", StandardBasicTypes.DOUBLE)
260 .addScalar("RSIEB15", StandardBasicTypes.DOUBLE)
261 .addScalar("RSIEB16", StandardBasicTypes.DOUBLE)
262 .addScalar("RSIEB17", StandardBasicTypes.DOUBLE)
263 .addScalar("RSIEB18", StandardBasicTypes.DOUBLE)
264 .addScalar("RSIEB19", StandardBasicTypes.DOUBLE)
265 .addScalar("RSIEB20", StandardBasicTypes.DOUBLE)
266 .addScalar("RSIEB21", StandardBasicTypes.DOUBLE)
267 .addScalar("REST", StandardBasicTypes.DOUBLE)
268 .addScalar("SIEB01", StandardBasicTypes.DOUBLE)
269 .addScalar("SIEB02", StandardBasicTypes.DOUBLE)
270 .addScalar("SIEB03", StandardBasicTypes.DOUBLE)
271 .addScalar("SIEB04", StandardBasicTypes.DOUBLE)
272 .addScalar("SIEB05", StandardBasicTypes.DOUBLE)
273 .addScalar("SIEB06", StandardBasicTypes.DOUBLE)
274 .addScalar("SIEB07", StandardBasicTypes.DOUBLE)
275 .addScalar("SIEB08", StandardBasicTypes.DOUBLE)
276 .addScalar("SIEB09", StandardBasicTypes.DOUBLE)
277 .addScalar("SIEB10", StandardBasicTypes.DOUBLE)
278 .addScalar("SIEB11", StandardBasicTypes.DOUBLE)
279 .addScalar("SIEB12", StandardBasicTypes.DOUBLE)
280 .addScalar("SIEB13", StandardBasicTypes.DOUBLE)
281 .addScalar("SIEB14", StandardBasicTypes.DOUBLE)
282 .addScalar("SIEB15", StandardBasicTypes.DOUBLE)
283 .addScalar("SIEB16", StandardBasicTypes.DOUBLE)
284 .addScalar("SIEB17", StandardBasicTypes.DOUBLE)
285 .addScalar("SIEB18", StandardBasicTypes.DOUBLE)
286 .addScalar("SIEB19", StandardBasicTypes.DOUBLE)
287 .addScalar("SIEB20", StandardBasicTypes.DOUBLE)
288 .addScalar("SIEB21", StandardBasicTypes.DOUBLE);
289
290 query.setString("river_name", river);
291 query.setDouble("location", location);
292 query.setDate("from", dateRange.getFrom());
293 query.setDate("to", dateRange.getTo());
294
295 query.setResultTransformer(FRACTIONS_TRANSFORMER);
296
297 @SuppressWarnings("unchecked")
298 List<Measurement> measuments = (List<Measurement>)query.list();
299
300 if (debug) {
301 log.debug("num fraction results: " + measuments.size());
302 }
303
304 List<Measurement> same = new ArrayList<Measurement>();
305
306 Integer lastLR = null;
307
308 List<Measurement> accumulated = new ArrayList<Measurement>();
309
310 for (Measurement m: measuments) {
311 Integer currentLR = (Integer)m.getData("GLOTRECHTEID");
312
313 boolean newDS = lastLR == null
314 || (currentLR != null && !lastLR.equals(currentLR));
315
316 if (newDS && !same.isEmpty()) {
317 accumulated.add(accumulate(same));
318 same.clear();
319 }
320
321 same.add(m);
322
323 lastLR = currentLR;
324 }
325
326 if (!same.isEmpty()) {
327 accumulated.add(accumulate(same));
328 }
329
330 if (debug) {
331 log.debug("Before date separation: " + accumulated.size());
332 }
333
334 accumulated = separateByDate(accumulated);
335
336 if (debug) {
337 log.debug("After date separation: " + accumulated.size());
338 }
339
340 return accumulated;
341 }
342
343 protected static List<Measurement> separateByDate(List<Measurement> measurements) {
344
345 List<Measurement> result = new ArrayList<Measurement>();
346
347 List<Measurement> same = new ArrayList<Measurement>();
348
349 Date lastDate = null;
350
351 for (Measurement m: measurements) {
352 Date currentDate = (Date)m.getData("DATUM");
353 if ((lastDate == null
354 || !equalDate(currentDate, lastDate))
355 && !same.isEmpty()
356 ) {
357 result.add(processSameDate(same));
358 same.clear();
359 }
360 same.add(m);
361 lastDate = currentDate;
362 }
363
364 if (!same.isEmpty()) {
365 result.add(processSameDate(same));
366 }
367
368 return result;
369 }
370
371
372 protected static Measurement processSameDate(List<Measurement> measurements) {
373 int N = measurements.size();
374
375 boolean debug = log.isDebugEnabled();
376 if (debug && N > 0) {
377 log.debug("process same date for Q: " + measurements.get(0).Q());
378 }
379 if (N == 1) {
380 Measurement current = measurements.get(0);
381 double left = current.get("UFERABLINKS");
382 double right = current.get("UFERABST");
383 current.set("EFFWIDTH", left + right);
384 }
385 else {
386 for (int i = 0; i < N; ++i) {
387 Measurement current = measurements.get(i);
388
389 if (i == 0) {
390 Measurement next = measurements.get(i+1);
391 double distCurrent = current.get("UFERABST");
392 double distNext = next.get("UFERABST");
393 current.set("EFFWIDTH", distNext - distCurrent);
394 }
395 else if (i == N-1) {
396 Measurement prev = measurements.get(i-1);
397 double distCurrent = current.get("UFERABST");
398 double distPrev = prev.get("UFERABST");
399 current.set("EFFWIDTH", distCurrent - distPrev);
400 }
401 else {
402 Measurement prev = measurements.get(i-1);
403 Measurement next = measurements.get(i+1);
404 double distPrev = prev.get("UFERABST");
405 double distNext = next.get("UFERABST");
406 current.set("EFFWIDTH", 0.5*(distNext - distPrev));
407 }
408 if (debug) {
409 log.debug("effective width: " + current.get("EFFWIDTH"));
410 }
411 }
412 }
413
414 double sumSandF = 0d;
415 double sumCoarseF = 0d;
416 double sumGravelF = 0d;
417 double sumNorm = 0d;
418
419 for (Measurement m: measurements) {
420 SieveArray sa = m.getSieveArray();
421 if (sa.totalLoad() < SieveArray.EPSILON) {
422 continue;
423 }
424 double sandF = sa.sandNormFraction();
425 double coarseF = sa.coarseNormFraction();
426 double gravelF = sa.gravelNormFraction();
427 double effWidth = m.get("EFFWIDTH");
428 double gt = m.get("GTRIEB");
429 double scale = effWidth*gt;
430 sumSandF += scale*sandF;
431 sumCoarseF += scale*coarseF;
432 sumGravelF += scale*gravelF;
433 sumNorm += scale;
434 if (debug) {
435 log.debug("fractions - s: " +
436 sandF + " c: " +
437 coarseF + " g: " +
438 gravelF);
439 log.debug("scale: " + scale + " = " + effWidth + " * " + gt);
440 }
441 }
442
443 Map<String, Object> data =
444 new HashMap<String, Object>(measurements.get(0).getData());
445
446 Measurement m = new Measurement(data, Collections.<Sieve>emptyList());
447
448 sumNorm = 1d/sumNorm;
449
450 m.set("BL_S", sumNorm*sumSandF);
451 m.set("BL_G", sumNorm*sumGravelF);
452 m.set("BL_C", sumNorm*sumCoarseF);
453 if (debug) {
454 log.debug(
455 "BL_S: " + m.get("BL_S") +
456 " BL_G: " + m.get("BL_G") +
457 " BL_C: " + m.get("BL_C"));
458 }
459 return m;
460 }
461
462
463 private static final boolean equalDate(Date a, Date b) {
464 Calendar ca = Calendar.getInstance();
465 Calendar cb = Calendar.getInstance();
466 ca.setTime(a);
467 cb.setTime(b);
468 return ca.get(Calendar.YEAR) == cb.get(Calendar.YEAR)
469 && ca.get(Calendar.MONTH) == cb.get(Calendar.MONTH)
470 && ca.get(Calendar.DAY_OF_MONTH) == cb.get(Calendar.DAY_OF_MONTH);
471 }
472
473
474 protected static Measurement accumulate(List<Measurement> measuments) {
475
476 int N = measuments.size();
477 if (N == 1) {
478 return measuments.get(0);
479 }
480 TreeMap<Double, double []> diameters =
481 new TreeMap<Double, double []>(Sieve.DIAMETER_CMP);
482
483 double sumGTrieb = 0d;
484 for (Measurement m: measuments) {
485 for (Sieve s: m.getSieves()) {
486 Double key = s.getDiameter();
487 double [] sum = diameters.get(key);
488 if (sum == null) {
489 sum = new double[1];
490 diameters.put(key, sum);
491 }
492 sum[0] += s.getLoad();
493 }
494 // calculate 'Geschiebetrieb'
495 sumGTrieb += m.get("GTRIEB");
496 }
497 List<Sieve> accumulatedSieves = new ArrayList<Sieve>(diameters.size());
498 for (Map.Entry<Double, double []> entry: diameters.entrySet()) {
499 accumulatedSieves.add(
500 new Sieve(entry.getKey(),
501 entry.getValue()[0]/N));
502 }
503 Map<String, Object> data =
504 new HashMap<String, Object>(measuments.get(0).getData());
505
506 data.put("GTRIEB", sumGTrieb/N);
507
508 return new Measurement(data, accumulatedSieves);
509 }
510 }
511 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :
http://dive4elements.wald.intevation.org