Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/tkhcalculation/BedQualityD50KmValueFinder.java @ 8920:29442c03c6e3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
d50 aggregation by median instead of arithmetic mean, negative tkh replaced by 0
author mschaefer
date Thu, 01 Mar 2018 09:01:58 +0100
parents d9dbf0b74bc2
children 48d5812e8fd5
comparison
equal deleted inserted replaced
8916:5d5d0051723f 8920:29442c03c6e3
17 import org.apache.commons.lang.math.DoubleRange; 17 import org.apache.commons.lang.math.DoubleRange;
18 import org.apache.commons.math.ArgumentOutsideDomainException; 18 import org.apache.commons.math.ArgumentOutsideDomainException;
19 import org.apache.commons.math.analysis.interpolation.LinearInterpolator; 19 import org.apache.commons.math.analysis.interpolation.LinearInterpolator;
20 import org.apache.commons.math.analysis.polynomials.PolynomialSplineFunction; 20 import org.apache.commons.math.analysis.polynomials.PolynomialSplineFunction;
21 import org.apache.log4j.Logger; 21 import org.apache.log4j.Logger;
22 import org.dive4elements.river.artifacts.math.Utils;
22 import org.dive4elements.river.backend.SedDBSessionHolder; 23 import org.dive4elements.river.backend.SedDBSessionHolder;
23 import org.dive4elements.river.model.River; 24 import org.dive4elements.river.model.River;
24 import org.hibernate.SQLQuery; 25 import org.hibernate.SQLQuery;
25 import org.hibernate.Session; 26 import org.hibernate.Session;
26 import org.hibernate.type.StandardBasicTypes; 27 import org.hibernate.type.StandardBasicTypes;
28
29 import gnu.trove.TDoubleArrayList;
27 30
28 /** 31 /**
29 * Searchable sorted km array with parallel bed measurements value array and linear interpolation for km and d50 between 32 * Searchable sorted km array with parallel bed measurements value array and linear interpolation for km and d50 between
30 * the array elements.<br /> 33 * the array elements.<br />
31 * <br /> 34 * <br />
32 * See comment of SQL command on how the values are filtered and aggregated. 35 * See comment of SQL command on how the values are filtered and aggregated.
33 * 36 *
34 * @author Matthias Schäfer 37 * @author Matthias Schäfer
35 * 38 *
36 */ 39 */
37 final class BedQualityD50KmValueFinder { 40 public class BedQualityD50KmValueFinder {
38
39 /***** INNER CLASSES *****/
40
41 /**
42 * A bed measurements aggregate with its d50 characteristic grain diameter
43 */
44 private static class D50Measurement {
45 private double km;
46
47 public double getKm() {
48 return this.km;
49 }
50
51 // private Date mindate;
52
53 // public Date getMinDate() {
54 // return this.mindate;
55 // }
56
57 // private Date maxdate;
58
59 // public Date getMaxDate() {
60 // return this.maxdate;
61 // }
62
63 private int cnt;
64
65 public int getCnt() {
66 return this.cnt;
67 }
68
69 // private double mindepth;
70
71 // public double getMinDepth() {
72 // return this.mindepth;
73 // }
74
75 // private double maxdepth;
76
77 // public double getMaxDepth() {
78 // return this.maxdepth;
79 // }
80
81 private double d50;
82
83 /**
84 * D50 in m
85 */
86 public double getD50() {
87 return this.d50;
88 }
89
90 // /**
91 // * Parameter constructor
92 // */
93 // public D50Measurement(final double km, final Date mindate, final Date maxdate, final int cnt, final double mindepth,
94 // final double maxdepth,
95 // final double d50mm) {
96 // this.km = km;
97 // this.mindate = mindate;
98 // this.maxdate = maxdate;
99 // this.cnt = cnt;
100 // this.mindepth = mindepth;
101 // this.maxdepth = maxdepth;
102 // this.d50 = d50mm / 1000;
103 // }
104
105 /**
106 * Query result row constructor
107 */
108 public D50Measurement(final Object[] tuple, final String[] aliases) {
109 this.km = 0;
110 // this.mindate = null;
111 // this.maxdate = null;
112 this.cnt = 0;
113 // this.mindepth = Double.NaN;
114 // this.maxdepth = Double.NaN;
115 this.d50 = Double.NaN;
116 for (int i = 0; i < tuple.length; ++i) {
117 if (tuple[i] == null)
118 continue;
119 switch (aliases[i]) {
120 case "km":
121 this.km = ((Number) tuple[i]).doubleValue();
122 break;
123 // case "mindate":
124 // this.mindate = (Date) tuple[i];
125 // break;
126 // case "maxdate":
127 // this.maxdate = (Date) tuple[i];
128 // break;
129 case "cnt":
130 this.cnt = ((Number) tuple[i]).intValue();
131 break;
132 // case "mindepth":
133 // this.mindepth = ((Number) tuple[i]).doubleValue();
134 // break;
135 // case "maxdepth":
136 // this.maxdepth = ((Number) tuple[i]).doubleValue();
137 // break;
138 case "d50":
139 this.d50 = ((Number) tuple[i]).doubleValue() / 1000; // mm to m
140 break;
141 default:
142 break;
143 }
144 }
145 }
146 }
147 41
148 /***** FIELDS *****/ 42 /***** FIELDS *****/
149 43
150 /** 44 /**
151 * Private log to use here. 45 * Private log to use here.
152 */ 46 */
153 private static Logger log = Logger.getLogger(BedQualityD50KmValueFinder.class); 47 private static Logger log = Logger.getLogger(BedQualityD50KmValueFinder.class);
154 48
155 /** 49 /**
156 * Query that aggregates by km for a km range and a time period all sub layer bed measurements with their d50<br /> 50 * Query selecting all sub layer bed measurements with their d50 for a km range and a time period<br />
157 * <br /> 51 * <br />
158 * A km may have bed measurements for multiple dates, multiple distances from the river bank, and multiple depth layers. 52 * A km may have bed measurements for multiple dates, multiple distances from the river bank, and multiple depth layers.
159 * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth). 53 * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth).<br />
160 * Those measurements are then grouped by km, and the D50 aggregated as average value. 54 *
55 * If PostgreSQL would support a median aggregate function like Oracle does, the aggregation could be placed into this query.
161 */ 56 */
162 private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT = 57 private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT =
163 "SELECT t.km, MIN(t.datum) AS mindate, MAX(t.datum) AS maxdate, COUNT(*) AS cnt," // 58 "SELECT t.km, t.datum, p.tiefevon, p.tiefebis, a.d50"
164 + " MIN(p.tiefevon) AS mindepth, MAX(p.tiefebis) AS maxdepth, AVG(a.d50) AS d50" // 59 + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid"
165 + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid" // 60 + " INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid"
166 + " INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid" // 61 + " INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid"
167 + " INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid" // 62 + " INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid"
168 + " INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid" // 63 + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)"
169 + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)" // 64 + " AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)"
170 + " AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)" // 65 + " AND (t.datum BETWEEN :fromdate AND :todate)"
171 + " AND (t.datum BETWEEN :fromdate AND :todate)" // 66 + " ORDER BY t.km ASC, a.d50 ASC";
172 + " GROUP BY t.km" //
173 + " ORDER BY t.km"; //
174
175 private static final String[] SQL_BED_D50_SELECT_ALIAS = { "km", "mindate", "maxdate", "cnt", "mindepth", "maxdepth", "d50" };
176 67
177 /** 68 /**
178 * Real linear interpolator for kms and d50 values 69 * Real linear interpolator for kms and d50 values (m)
179 */ 70 */
180 private final PolynomialSplineFunction interpolator; 71 private final PolynomialSplineFunction interpolator;
181 72
182 /***** METHODS *****/
183 73
74 /***** CONSTRUCTORS *****/
75
184 private BedQualityD50KmValueFinder(final double[] kms, final double[] values) { 76 private BedQualityD50KmValueFinder(final double[] kms, final double[] values) {
185 this.interpolator = new LinearInterpolator().interpolate(kms, values); 77 this.interpolator = new LinearInterpolator().interpolate(kms, values);
186 } 78 }
187 79
80 /***** METHODS *****/
81
188 /** 82 /**
189 * Sohlbeschaffenheit (D50 Korndurchmesser aus Seddb) 83 * Sohlbeschaffenheit (D50 Korndurchmesser aus Seddb)
190 * Abhängig von Peiljahr 84 * Abhängig von Peiljahr
191 */ 85 */
192 public static BedQualityD50KmValueFinder loadBedMeasurements(final River river, final DoubleRange kmRange, final int soundingYear, final int validYears) { 86 public static BedQualityD50KmValueFinder loadBedMeasurements(final River river, final DoubleRange kmRange, final int soundingYear, final int validYears) {
202 final Date endTime = cal.getTime(); 96 final Date endTime = cal.getTime();
203 97
204 log.debug(String.format("loadValues km %.3f - %.3f %tF - %tF", kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), startTime, endTime)); 98 log.debug(String.format("loadValues km %.3f - %.3f %tF - %tF", kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), startTime, endTime));
205 final Session session = SedDBSessionHolder.HOLDER.get(); 99 final Session session = SedDBSessionHolder.HOLDER.get();
206 final SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_D50_SUBLAYER_MEASUREMENT).addScalar("km", StandardBasicTypes.DOUBLE) 100 final SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_D50_SUBLAYER_MEASUREMENT).addScalar("km", StandardBasicTypes.DOUBLE)
207 .addScalar("mindate", StandardBasicTypes.DATE).addScalar("maxdate", StandardBasicTypes.DATE).addScalar("cnt", StandardBasicTypes.INTEGER) 101 .addScalar("datum", StandardBasicTypes.DATE).addScalar("tiefevon", StandardBasicTypes.DOUBLE)
208 .addScalar("mindepth", StandardBasicTypes.DOUBLE).addScalar("maxdepth", StandardBasicTypes.DOUBLE).addScalar("d50", StandardBasicTypes.DOUBLE); 102 .addScalar("tiefebis", StandardBasicTypes.DOUBLE).addScalar("d50", StandardBasicTypes.DOUBLE);
209 final String seddbRiver = river.nameForSeddb(); 103 final String seddbRiver = river.nameForSeddb();
210 sqlQuery.setString("name", seddbRiver); 104 sqlQuery.setString("name", seddbRiver);
211 sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble()); 105 sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble());
212 sqlQuery.setDouble("tokm", kmRange.getMaximumDouble()); 106 sqlQuery.setDouble("tokm", kmRange.getMaximumDouble());
213 sqlQuery.setDate("fromdate", startTime); 107 sqlQuery.setDate("fromdate", startTime);
214 sqlQuery.setDate("todate", endTime); 108 sqlQuery.setDate("todate", endTime);
215 109
216 final List<Object[]> rows = sqlQuery.list(); 110 final List<Object[]> rows = sqlQuery.list();
217 final double[] kms = new double[rows.size()]; 111 final TDoubleArrayList kms = new TDoubleArrayList();
218 final double[] values = new double[rows.size()]; 112 final TDoubleArrayList values = new TDoubleArrayList();
219 D50Measurement measurement; 113 final TDoubleArrayList kmd50s = new TDoubleArrayList();
220 int i = -1; 114 for (int i = 0; i <= rows.size() - 1; i++) {
221 for (final Object[] row : rows) { 115 kmd50s.add((double) rows.get(i)[4]);
222 measurement = new D50Measurement(row, SQL_BED_D50_SELECT_ALIAS); 116 if (((i == rows.size() - 1) || !Utils.epsilonEquals((double) rows.get(i)[0], (double) rows.get(i+1)[0], 0.0001))) {
223 i++; 117 int k = kmd50s.size() / 2;
224 kms[i] = measurement.getKm(); 118 values.add(((k + k < kmd50s.size()) ? kmd50s.get(k) : (kmd50s.get(k-1) + kmd50s.get(k)) / 2) / 1000);
225 values[i] = measurement.getD50(); 119 kms.add((double) rows.get(i)[0]);
226 log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms[i], values[i], measurement.getCnt())); 120 log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms.get(kms.size()-1), values.get(values.size()-1), kmd50s.size()));
121 kmd50s.clear();
122 }
227 } 123 }
228 try { 124 try {
229 return new BedQualityD50KmValueFinder(kms, values); 125 return new BedQualityD50KmValueFinder(kms.toNativeArray(), values.toNativeArray());
230 } 126 }
231 catch (final Exception e) { 127 catch (final Exception e) {
232 e.printStackTrace(); 128 e.printStackTrace();
233 return null; 129 return null;
234 } 130 }
235 } 131 }
236 132
237 /** 133 /**
238 * Returns the d50 value interpolated according to a km 134 * Returns the d50 value interpolated according to a km
239 * 135 *
240 * @return d50 (mm) of the km, or NaN 136 * @return d50 (m) of the km, or NaN
241 */ 137 */
242 public double findD50(final double km) throws ArgumentOutsideDomainException { 138 public double findD50(final double km) throws ArgumentOutsideDomainException {
243 return this.interpolator.value(km); 139 return this.interpolator.value(km);
244 /* 140 /*
245 * ohne interpolation: 141 * ohne interpolation:
http://dive4elements.wald.intevation.org