Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/model/minfo/SedimentLoadFactory.java @ 6392:9a0f8d532797

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Load sedimentload fraction with sedimentload factory and add new facets to artifact.
author Raimund Renkert <rrenkert@intevation.de>
date Fri, 21 Jun 2013 15:35:44 +0200
parents fa8425c8694c
children 24b4819ef514
comparison
equal deleted inserted replaced
6391:ec537afacac2 6392:9a0f8d532797
11 import gnu.trove.TDoubleArrayList; 11 import gnu.trove.TDoubleArrayList;
12 12
13 import java.util.Calendar; 13 import java.util.Calendar;
14 import java.util.Date; 14 import java.util.Date;
15 import java.util.List; 15 import java.util.List;
16 import java.util.Map;
17 import java.util.TreeMap; 16 import java.util.TreeMap;
18 17
19 import net.sf.ehcache.Cache; 18 import net.sf.ehcache.Cache;
20 import net.sf.ehcache.Element; 19 import net.sf.ehcache.Element;
21 20
22 import org.apache.log4j.Logger; 21 import org.apache.log4j.Logger;
23 import org.hibernate.SQLQuery;
24 import org.hibernate.Session;
25 import org.hibernate.type.StandardBasicTypes;
26
27 import org.dive4elements.river.model.MeasurementStation;
28
29 import org.dive4elements.river.artifacts.cache.CacheFactory; 22 import org.dive4elements.river.artifacts.cache.CacheFactory;
30
31 import org.dive4elements.river.artifacts.model.Range; 23 import org.dive4elements.river.artifacts.model.Range;
32 import org.dive4elements.river.artifacts.model.RiverFactory; 24 import org.dive4elements.river.artifacts.model.RiverFactory;
33 import org.dive4elements.river.artifacts.model.StaticSedimentLoadCacheKey; 25 import org.dive4elements.river.artifacts.model.StaticSedimentLoadCacheKey;
34 import org.dive4elements.river.backend.SessionHolder; 26 import org.dive4elements.river.backend.SessionHolder;
27 import org.dive4elements.river.model.MeasurementStation;
28 import org.hibernate.SQLQuery;
29 import org.hibernate.Session;
30 import org.hibernate.type.StandardBasicTypes;
35 31
36 /** Pull Sediment Loads out of db. */ 32 /** Pull Sediment Loads out of db. */
37 public class SedimentLoadFactory 33 public class SedimentLoadFactory
38 { 34 {
39 /** Private logger to use here. */ 35 /** Private logger to use here. */
58 /** Query to get name for wst_id and column_pos. */ 54 /** Query to get name for wst_id and column_pos. */
59 public static final String SQL_SELECT_EPOCHS = 55 public static final String SQL_SELECT_EPOCHS =
60 "SELECT DISTINCT " + 56 "SELECT DISTINCT " +
61 " sy.description AS description, " + 57 " sy.description AS description, " +
62 " ti.start_time AS start, " + 58 " ti.start_time AS start, " +
63 " ti.stop_time AS end " + 59 " ti.stop_time AS end, " +
60 " u.name AS unit" +
64 " FROM sediment_yield sy " + 61 " FROM sediment_yield sy " +
65 " JOIN rivers r ON sy.river_id = r.id " + 62 " JOIN rivers r ON sy.river_id = r.id " +
66 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 63 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
67 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 64 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
65 " JOIN units u ON u.id = sy.unit_id " +
68 " WHERE r.name = :name " + 66 " WHERE r.name = :name " +
69 " AND ti.stop_time IS NOT NULL " + 67 " AND ti.stop_time IS NOT NULL " +
70 " AND syv.station BETWEEN :startKm AND :endKm"; 68 " AND syv.station BETWEEN :startKm AND :endKm";
71 69
72 public static final String SQL_SELECT_SINGLES_DATA = 70 public static final String SQL_SELECT_SINGLES_DATA =
73 "SELECT" + 71 "SELECT" +
74 " sy.description AS description, " + 72 " sy.description AS description, " +
75 " ti.start_time AS year, " + 73 " ti.start_time AS year, " +
76 " syv.value AS load, " + 74 " syv.value AS load, " +
77 " syv.station AS km " + 75 " syv.station AS km, " +
76 " u.name AS unit " +
78 " FROM sediment_yield sy " + 77 " FROM sediment_yield sy " +
79 " JOIN rivers r ON sy.river_id = r.id " + 78 " JOIN rivers r ON sy.river_id = r.id " +
80 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 79 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
81 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 80 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
82 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + 81 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
82 " JOIN units u ON u.id = sy.unit_id" +
83 " WHERE r.name = :name " + 83 " WHERE r.name = :name " +
84 " AND ti.start_time BETWEEN :begin AND :end " + 84 " AND ti.start_time BETWEEN :begin AND :end " +
85 " AND ti.stop_time IS NULL " + 85 " AND ti.stop_time IS NULL " +
86 " AND gf.name = :grain " + 86 " AND gf.name = :grain " +
87 " AND syv.station BETWEEN :startKm AND :endKm"; 87 " AND syv.station BETWEEN :startKm AND :endKm " +
88 " ORDER BY syv.station";
89
90 public static final String SQL_SELECT_UNKNOWN_DATA =
91 "SELECT" +
92 " sy.description AS description, " +
93 " ti.start_time AS start, " +
94 " ti.stop_time AS end, " +
95 " syv.value AS load, " +
96 " syv.station AS km, " +
97 " u.name AS unit " +
98 " FROM sediment_yield sy " +
99 " JOIN rivers r ON sy.river_id = r.id " +
100 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
101 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
102 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
103 " JOIN units u ON u.id = sy.unit_id" +
104 " WHERE r.name = :name " +
105 " AND sy.description = :descr " +
106 " AND gf.name = 'unknown' " +
107 " ORDER BY syv.station";
108
109 public static final String SQL_SELECT_UNKNOWN =
110 "SELECT DISTINCT " +
111 " sy.description AS description, " +
112 " ti.start_time AS start, " +
113 " ti.stop_time AS end " +
114 "FROM sediment_yield sy " +
115 " JOIN rivers r ON sy.river_id = r.id " +
116 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
117 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
118 "WHERE r.name = :river" +
119 " AND gf.name = 'unknown'";
88 120
89 public static final String SQL_SELECT_EPOCHS_DATA = 121 public static final String SQL_SELECT_EPOCHS_DATA =
90 "SELECT" + 122 "SELECT" +
91 " sy.description AS description, " + 123 " sy.description AS description, " +
92 " ti.start_time AS startYear, " + 124 " ti.start_time AS startYear, " +
93 " syv.value AS load, " + 125 " syv.value AS load, " +
94 " syv.station AS km," + 126 " syv.station AS km," +
95 " ti.stop_time AS endYear " + 127 " ti.stop_time AS endYear, " +
128 " u.name AS unit" +
96 " FROM sediment_yield sy " + 129 " FROM sediment_yield sy " +
97 " JOIN rivers r ON sy.river_id = r.id " + 130 " JOIN rivers r ON sy.river_id = r.id " +
98 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " + 131 " JOIN time_intervals ti ON sy.time_interval_id = ti.id " +
99 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " + 132 " JOIN sediment_yield_values syv ON sy.id = syv.sediment_yield_id " +
100 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " + 133 " JOIN grain_fraction gf ON sy.grain_fraction_id = gf.id " +
134 " JOIN units u ON u.id = sy.unit_id " +
101 " WHERE r.name = :name " + 135 " WHERE r.name = :name " +
102 " AND ti.start_time BETWEEN :sbegin AND :send " + 136 " AND ti.start_time BETWEEN :sbegin AND :send " +
103 " AND ti.stop_time BETWEEN :ebegin AND :eend " + 137 " AND ti.stop_time BETWEEN :ebegin AND :eend " +
104 " AND gf.name = :grain " + 138 " AND gf.name = :grain " +
105 " AND syv.station BETWEEN :startKm AND :endKm"; 139 " AND syv.station BETWEEN :startKm AND :endKm " +
140 " ORDER BY syv.station";
106 141
107 private SedimentLoadFactory() { 142 private SedimentLoadFactory() {
108 } 143 }
109 144
110 /** 145 /**
223 Object[] row = results.get(i); 258 Object[] row = results.get(i);
224 loads[i] = new SedimentLoad( 259 loads[i] = new SedimentLoad(
225 (String) row[0], 260 (String) row[0],
226 (Date) row[1], 261 (Date) row[1],
227 null, 262 null,
228 false); 263 false,
264 "");
229 } 265 }
230 return loads; 266 return loads;
231 } 267 }
232 else if (type.equals("epoch")) { 268 else if (type.equals("epoch")) {
233 sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS) 269 sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS)
244 Object[] row = results.get(i); 280 Object[] row = results.get(i);
245 loads[i] = new SedimentLoad( 281 loads[i] = new SedimentLoad(
246 (String) row[0], 282 (String) row[0],
247 (Date) row[1], 283 (Date) row[1],
248 (Date) row[2], 284 (Date) row[2],
249 true); 285 true,
286 "");
250 } 287 }
251 return loads; 288 return loads;
252 } 289 }
253 return new SedimentLoad[0]; 290 return new SedimentLoad[0];
254 } 291 }
295 if (type.equals("year") || type.equals("epoch")) { 332 if (type.equals("year") || type.equals("epoch")) {
296 sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES_DATA) 333 sqlQuery = session.createSQLQuery(SQL_SELECT_SINGLES_DATA)
297 .addScalar("description", StandardBasicTypes.STRING) 334 .addScalar("description", StandardBasicTypes.STRING)
298 .addScalar("year", StandardBasicTypes.DATE) 335 .addScalar("year", StandardBasicTypes.DATE)
299 .addScalar("load", StandardBasicTypes.DOUBLE) 336 .addScalar("load", StandardBasicTypes.DOUBLE)
300 .addScalar("km", StandardBasicTypes.DOUBLE); 337 .addScalar("km", StandardBasicTypes.DOUBLE)
338 .addScalar("unit", StandardBasicTypes.STRING);
301 sqlQuery.setString("name", river); 339 sqlQuery.setString("name", river);
302 sqlQuery.setDouble("startKm", startKm); 340 sqlQuery.setDouble("startKm", startKm);
303 sqlQuery.setDouble("endKm", endKm); 341 sqlQuery.setDouble("endKm", endKm);
304 sqlQuery.setDate("begin", start.getTime()); 342 sqlQuery.setDate("begin", start.getTime());
305 sqlQuery.setDate("end", end.getTime()); 343 sqlQuery.setDate("end", end.getTime());
309 Object[] row = results.get(0); 347 Object[] row = results.get(0);
310 load = new SedimentLoad( 348 load = new SedimentLoad(
311 (String) row[0], 349 (String) row[0],
312 (Date) row[1], 350 (Date) row[1],
313 null, 351 null,
314 false); 352 false,
353 (String) row[4]);
315 getValues("coarse", sqlQuery, load, floatStations); 354 getValues("coarse", sqlQuery, load, floatStations);
316 getValues("fine_middle", sqlQuery, load, floatStations); 355 getValues("fine_middle", sqlQuery, load, floatStations);
317 getValues("sand", sqlQuery, load, floatStations); 356 getValues("sand", sqlQuery, load, floatStations);
318 getValues("suspended_sediment", sqlQuery, load, suspStations); 357 getValues("suspended_sediment", sqlQuery, load, suspStations);
319 getValues("susp_sand_bed", sqlQuery, load, suspStations); 358 getValues("susp_sand_bed", sqlQuery, load, suspStations);
329 sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS_DATA) 368 sqlQuery = session.createSQLQuery(SQL_SELECT_EPOCHS_DATA)
330 .addScalar("description", StandardBasicTypes.STRING) 369 .addScalar("description", StandardBasicTypes.STRING)
331 .addScalar("startYear", StandardBasicTypes.DATE) 370 .addScalar("startYear", StandardBasicTypes.DATE)
332 .addScalar("load", StandardBasicTypes.DOUBLE) 371 .addScalar("load", StandardBasicTypes.DOUBLE)
333 .addScalar("km", StandardBasicTypes.DOUBLE) 372 .addScalar("km", StandardBasicTypes.DOUBLE)
334 .addScalar("endYear", StandardBasicTypes.DATE); 373 .addScalar("endYear", StandardBasicTypes.DATE)
374 .addScalar("unit", StandardBasicTypes.STRING);
335 sqlQuery.setString("name", river); 375 sqlQuery.setString("name", river);
336 sqlQuery.setDouble("startKm", startKm); 376 sqlQuery.setDouble("startKm", startKm);
337 sqlQuery.setDouble("endKm", endKm); 377 sqlQuery.setDouble("endKm", endKm);
338 sqlQuery.setDate("sbegin", start.getTime()); 378 sqlQuery.setDate("sbegin", start.getTime());
339 sqlQuery.setDate("send", end.getTime()); 379 sqlQuery.setDate("send", end.getTime());
348 Object[] row = results.get(0); 388 Object[] row = results.get(0);
349 load = new SedimentLoad( 389 load = new SedimentLoad(
350 (String) row[0], 390 (String) row[0],
351 (Date) row[1], 391 (Date) row[1],
352 (Date) row[4], 392 (Date) row[4],
353 true); 393 true,
394 (String)row[5]);
354 TDoubleArrayList kms = new TDoubleArrayList(); 395 TDoubleArrayList kms = new TDoubleArrayList();
355 for (int i = 0; i < results.size(); i++) { 396 for (int i = 0; i < results.size(); i++) {
356 row = results.get(i); 397 row = results.get(i);
357 kms.add((Double)row[3]); 398 kms.add((Double)row[3]);
358 load.setLoadTotal((Double)row[3], (Double)row[2]); 399 load.setLoadTotal((Double)row[3], (Double)row[2]);
428 else if (fraction.equals("susp_sand_bed")) { 469 else if (fraction.equals("susp_sand_bed")) {
429 load.setSuspSandBed(km, v, range); 470 load.setSuspSandBed(km, v, range);
430 } 471 }
431 } 472 }
432 } 473 }
474
475 public static SedimentLoad getLoadUnknown(
476 String river,
477 String description
478 ) {
479 log.debug("SedimentLoadFactory.getLoadWithData");
480 Cache cache = CacheFactory.getCache(LOAD_DATA_CACHE_NAME);
481
482 if (cache == null) {
483 log.debug("Cache not configured.");
484 return getSedimentLoadUnknownUncached(river, description);
485 }
486
487 StaticSedimentLoadCacheKey key =
488 new StaticSedimentLoadCacheKey(river, 0d, 0d, 0, 0);
489
490 Element element = cache.get(key);
491
492 if (element != null) {
493 log.debug("SedimentLoad found in cache");
494 return (SedimentLoad)element.getValue();
495 }
496
497 SedimentLoad values = getSedimentLoadUnknownUncached(river, description);
498
499 if (values != null && key != null) {
500 log.debug("Store static bed height values in cache.");
501 element = new Element(key, values);
502 cache.put(element);
503 }
504 return values;
505 }
506
507 /**
508 * Get sediment loads with fraction 'unknown' from db.
509 * @param river the river
510 * @param type the sediment load type (year or epoch)
511 * @return according sediment loads.
512 */
513 public static SedimentLoad getSedimentLoadUnknownUncached(
514 String river,
515 String description
516 ) {
517 log.debug("SedimentLoadFactory.getSedimentLoadWithDataUncached");
518 Session session = SessionHolder.HOLDER.get();
519 SQLQuery sqlQuery = null;
520
521 sqlQuery = session.createSQLQuery(SQL_SELECT_UNKNOWN_DATA)
522 .addScalar("description", StandardBasicTypes.STRING)
523 .addScalar("start", StandardBasicTypes.DATE)
524 .addScalar("end", StandardBasicTypes.DATE)
525 .addScalar("load", StandardBasicTypes.DOUBLE)
526 .addScalar("km", StandardBasicTypes.DOUBLE)
527 .addScalar("unit", StandardBasicTypes.STRING);
528 sqlQuery.setString("name", river);
529 sqlQuery.setString("descr", description);
530 List<Object []> results = sqlQuery.list();
531 SedimentLoad load = new SedimentLoad();
532 if (results.isEmpty()) {
533 return new SedimentLoad();
534 }
535 Object[] row = results.get(0);
536 load = new SedimentLoad(
537 (String) row[0],
538 (Date) row[1],
539 (Date) row[2],
540 false,
541 (String)row[5]);
542
543 for (int i = 0; i < results.size(); i++) {
544 row = results.get(i);
545 SedimentLoadFraction fraction = new SedimentLoadFraction();
546 fraction.setUnknown((Double)row[3]);
547 load.addKm((Double)row[4], fraction);
548 }
549 return load;
550 }
551
552 public static SedimentLoad[] getSedimentLoadUnknown(String river) {
553 Session session = SessionHolder.HOLDER.get();
554 SQLQuery sqlQuery = session.createSQLQuery(SQL_SELECT_UNKNOWN)
555 .addScalar("description", StandardBasicTypes.STRING)
556 .addScalar("start", StandardBasicTypes.DATE)
557 .addScalar("end", StandardBasicTypes.DATE);
558 sqlQuery.setString("river", river);
559 List<Object[]> results = sqlQuery.list();
560 SedimentLoad[] loads = new SedimentLoad[results.size()];
561 int counter = 0;
562 for (Object[] row: results) {
563 loads[counter] = new SedimentLoad(
564 (String)row[0], (Date)row[1], (Date)row[2], false, "");
565 counter++;
566 }
567 return loads;
568 }
433 } 569 }
434 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 : 570 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf-8 :
http://dive4elements.wald.intevation.org