Mercurial > dive4elements > river
comparison artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/tkhcalculation/BedQualityD50KmValueFinder.java @ 8920:29442c03c6e3
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: |