Mercurial > dive4elements > river
annotate artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java @ 8917:86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
| author | mschaefer |
|---|---|
| date | Mon, 26 Feb 2018 18:56:05 +0100 |
| parents | 37ff7f435912 |
| children |
| rev | line source |
|---|---|
| 8898 | 1 /* Copyright (C) 2017 by Bundesanstalt für Gewässerkunde |
| 2 * Software engineering by | |
| 3 * Björnsen Beratende Ingenieure GmbH | |
| 4 * Dr. Schumacher Ingenieurbüro für Wasser und Umwelt | |
| 5 * | |
| 6 * This file is Free Software under the GNU AGPL (>=v3) | |
| 7 * and comes with ABSOLUTELY NO WARRANTY! Check out the | |
| 8 * documentation coming with Dive4Elements River for details. | |
| 9 */ | |
| 10 | |
| 11 package org.dive4elements.river.artifacts.sinfo.flowdepth; | |
| 12 | |
| 13 import java.util.List; | |
| 14 | |
| 15 import org.apache.commons.lang.math.DoubleRange; | |
| 16 import org.apache.commons.math.ArgumentOutsideDomainException; | |
| 17 import org.apache.commons.math.analysis.interpolation.LinearInterpolator; | |
| 18 import org.apache.commons.math.analysis.polynomials.PolynomialSplineFunction; | |
| 19 import org.apache.log4j.Logger; | |
| 8901 | 20 import org.dive4elements.river.artifacts.math.Utils; |
| 8898 | 21 import org.dive4elements.river.artifacts.model.DateRange; |
| 22 import org.dive4elements.river.backend.SedDBSessionHolder; | |
| 23 import org.dive4elements.river.model.River; | |
| 24 import org.hibernate.SQLQuery; | |
| 25 import org.hibernate.Session; | |
| 26 import org.hibernate.type.StandardBasicTypes; | |
| 27 | |
| 8901 | 28 import gnu.trove.TDoubleArrayList; |
| 29 | |
| 8898 | 30 /** |
| 8901 | 31 * Searchable sorted km array with parallel bed measurements value array and linear interpolation for km and d50 between the array elements.<br /> |
| 32 * <br /> | |
| 33 * See comment of SQL command on how the values are filtered and aggregated. | |
| 34 * | |
| 8898 | 35 * @author Matthias Schäfer |
| 36 * | |
| 37 */ | |
| 38 public class BedQualityD50KmValueFinder { | |
| 39 | |
| 40 /***** FIELDS *****/ | |
| 41 | |
| 42 /** | |
| 43 * Private log to use here. | |
| 44 */ | |
| 45 private static Logger log = Logger.getLogger(BedQualityD50KmValueFinder.class); | |
| 46 | |
| 47 /** | |
|
8917
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
48 * Query selecting all sub layer bed measurements with their d50 for a km range and a time period<br /> |
| 8898 | 49 * <br /> |
| 50 * A km may have bed measurements for multiple dates, multiple distances from the river bank, and multiple depth layers. | |
|
8917
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
51 * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth).<br /> |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
52 * |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
53 * If PostgreSQL would support a median aggregate function like Oracle does the aggregation could be placed into this query. |
| 8898 | 54 */ |
| 55 private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT = | |
|
8917
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
56 "SELECT t.km, t.datum, p.tiefevon, p.tiefebis, a.d50 AS d50" |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
57 + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid" |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
58 + " INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid" |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
59 + " INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid" |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
60 + " INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid" |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
61 + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)" |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
62 + " AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)" |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
63 + " AND (t.datum BETWEEN :fromdate AND :todate)" |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
64 + " ORDER BY t.km ASC, a.d50 ASC"; |
| 8898 | 65 |
| 66 /** | |
|
8917
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
67 * Real linear interpolator for kms and d50 values (m) |
| 8898 | 68 */ |
|
8911
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
69 private PolynomialSplineFunction interpolator; |
| 8901 | 70 |
| 8898 | 71 |
| 72 /***** METHODS *****/ | |
| 73 | |
| 74 /** | |
| 75 * Returns the d50 value interpolated according to a km | |
|
8917
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
76 * @return d50 (m) of the km, or NaN |
| 8898 | 77 */ |
| 78 public double findD50(double km) throws ArgumentOutsideDomainException { | |
|
8911
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
79 return interpolator.value(km); |
|
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
80 /* ohne interpolation: |
| 8901 | 81 if ((kms == null) || (kms.size() == 0)) |
| 82 return Double.NaN; | |
| 83 int i = kms.binarySearch(km); | |
| 84 if (i >= 0) | |
| 85 return values.get(i); | |
| 86 i = -i - 1; | |
| 87 if ((i - 1 >= 0) && Utils.epsilonEquals(km, kms.get(i - 1), 0.0001)) | |
| 88 return values.get(i - 1); | |
| 89 else if ((i >= 0) && (i <= kms.size() - 1) && Utils.epsilonEquals(km, kms.get(i), 0.0001)) | |
| 90 return values.get(i); | |
| 91 else | |
|
8911
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
92 return Double.NaN; */ |
| 8898 | 93 } |
| 94 | |
| 95 /** | |
| 96 * Loads the range of the river's kms with their associated values. | |
| 97 * @return Whether the load has been successful | |
| 98 */ | |
| 99 public boolean loadValues(final River river, final DoubleRange kmRange, final DateRange dateRange) { | |
| 100 log.debug(String.format("loadValues km %.3f - %.3f %tF - %tF", kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), dateRange.getFrom(), dateRange.getTo())); | |
| 101 Session session = SedDBSessionHolder.HOLDER.get(); | |
| 102 SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_D50_SUBLAYER_MEASUREMENT) | |
|
8917
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
103 .addScalar("km", StandardBasicTypes.DOUBLE) |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
104 .addScalar("datum", StandardBasicTypes.DATE) |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
105 .addScalar("tiefevon", StandardBasicTypes.DOUBLE) |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
106 .addScalar("tiefebis", StandardBasicTypes.DOUBLE) |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
107 .addScalar("d50", StandardBasicTypes.DOUBLE); |
| 8898 | 108 String seddbRiver = river.nameForSeddb(); |
| 109 sqlQuery.setString("name", seddbRiver); | |
| 110 sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble()); | |
| 111 sqlQuery.setDouble("tokm", kmRange.getMaximumDouble()); | |
| 112 sqlQuery.setDate("fromdate", dateRange.getFrom()); | |
| 113 sqlQuery.setDate("todate", dateRange.getTo()); | |
| 114 @SuppressWarnings("unchecked") | |
| 115 final List<Object[]> rows = sqlQuery.list(); | |
|
8917
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
116 final TDoubleArrayList kms = new TDoubleArrayList(); |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
117 final TDoubleArrayList values = new TDoubleArrayList(); |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
118 final TDoubleArrayList kmd50s = new TDoubleArrayList(); |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
119 for (int i = 0; i <= rows.size() - 1; i++) { |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
120 kmd50s.add((double) rows.get(i)[4]); |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
121 if (((i == rows.size() - 1) || !Utils.epsilonEquals((double) rows.get(i)[0], (double) rows.get(i+1)[0], 0.0001))) { |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
122 int k = kmd50s.size() / 2; |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
123 values.add(((k + k < kmd50s.size()) ? kmd50s.get(k) : (kmd50s.get(k-1) + kmd50s.get(k)) / 2) / 1000); |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
124 kms.add((double) rows.get(i)[0]); |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
125 log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms.get(kms.size()-1), values.get(values.size()-1), kmd50s.size())); |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
126 kmd50s.clear(); |
|
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
127 } |
| 8898 | 128 } |
|
8911
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
129 try { |
|
8917
86395ab8ebc3
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
mschaefer
parents:
8911
diff
changeset
|
130 interpolator = new LinearInterpolator().interpolate(kms.toNativeArray(), values.toNativeArray()); |
|
8911
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
131 return true; |
|
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
132 } catch (Exception e) { |
|
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
133 interpolator = null; |
|
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
134 return false; |
|
37ff7f435912
SINFO Flowdepth: more error checks, d50 interpolation, avoid negative tkh
mschaefer
parents:
8901
diff
changeset
|
135 } |
| 8898 | 136 } |
| 137 } |