|
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.Date; |
|
|
14 import java.util.List; |
|
|
15 |
|
|
16 import org.apache.commons.lang.math.DoubleRange; |
|
|
17 import org.apache.commons.math.ArgumentOutsideDomainException; |
|
|
18 import org.apache.commons.math.analysis.interpolation.LinearInterpolator; |
|
|
19 import org.apache.commons.math.analysis.polynomials.PolynomialSplineFunction; |
|
|
20 import org.apache.log4j.Logger; |
|
8901
|
21 import org.dive4elements.river.artifacts.math.Utils; |
|
8898
|
22 import org.dive4elements.river.artifacts.model.DateRange; |
|
|
23 import org.dive4elements.river.backend.SedDBSessionHolder; |
|
|
24 import org.dive4elements.river.model.River; |
|
8901
|
25 import org.dive4elements.river.utils.DoubleUtil; |
|
8898
|
26 import org.hibernate.SQLQuery; |
|
|
27 import org.hibernate.Session; |
|
|
28 import org.hibernate.type.StandardBasicTypes; |
|
|
29 |
|
8901
|
30 import gnu.trove.TDoubleArrayList; |
|
|
31 |
|
8898
|
32 /** |
|
8901
|
33 * Searchable sorted km array with parallel bed measurements value array and linear interpolation for km and d50 between the array elements.<br /> |
|
|
34 * <br /> |
|
|
35 * See comment of SQL command on how the values are filtered and aggregated. |
|
|
36 * |
|
8898
|
37 * @author Matthias Schäfer |
|
|
38 * |
|
|
39 */ |
|
|
40 public class BedQualityD50KmValueFinder { |
|
|
41 |
|
|
42 /***** INNER CLASSES *****/ |
|
|
43 |
|
|
44 /** |
|
|
45 * A bed measurements aggregate with its d50 characteristic grain diameter |
|
|
46 */ |
|
|
47 private class D50Measurement { |
|
|
48 private double km; |
|
|
49 public double getKm() { |
|
|
50 return km; |
|
|
51 } |
|
|
52 private Date mindate; |
|
|
53 public Date getMinDate() { |
|
|
54 return mindate; |
|
|
55 } |
|
|
56 private Date maxdate; |
|
|
57 public Date getMaxDate() { |
|
|
58 return maxdate; |
|
|
59 } |
|
|
60 private int cnt; |
|
|
61 public int getCnt() { |
|
|
62 return cnt; |
|
|
63 } |
|
|
64 private double mindepth; |
|
|
65 public double getMinDepth() { |
|
|
66 return mindepth; |
|
|
67 } |
|
|
68 private double maxdepth; |
|
|
69 public double getMaxDepth() { |
|
|
70 return maxdepth; |
|
|
71 } |
|
|
72 private double d50; |
|
|
73 /** |
|
|
74 * D50 in m |
|
|
75 */ |
|
|
76 public double getD50() { |
|
|
77 return d50; |
|
|
78 } |
|
|
79 /** |
|
|
80 * Parameter constructor |
|
|
81 */ |
|
|
82 public D50Measurement(double km, Date mindate, Date maxdate, int cnt, double mindepth, double maxdepth, double d50mm) { |
|
|
83 this.km = km; |
|
|
84 this.mindate = mindate; |
|
|
85 this.maxdate = maxdate; |
|
|
86 this.cnt = cnt; |
|
|
87 this.mindepth = mindepth; |
|
|
88 this.maxdepth = maxdepth; |
|
|
89 this.d50 = d50mm / 1000; |
|
|
90 } |
|
|
91 |
|
|
92 /** |
|
|
93 * Query result row constructor |
|
|
94 */ |
|
|
95 public D50Measurement(Object[] tuple, String[] aliases) { |
|
|
96 km = 0; |
|
|
97 mindate = null; |
|
|
98 maxdate = null; |
|
|
99 cnt = 0; |
|
|
100 mindepth = Double.NaN; |
|
|
101 maxdepth = Double.NaN; |
|
|
102 d50 = Double.NaN; |
|
|
103 for (int i = 0; i < tuple.length; ++i) { |
|
|
104 if (tuple[i] == null) |
|
|
105 continue; |
|
|
106 switch (aliases[i]) { |
|
|
107 case "km": |
|
|
108 km = ((Number) tuple[i]).doubleValue(); |
|
|
109 break; |
|
|
110 case "mindate": |
|
|
111 mindate = (Date) tuple[i]; |
|
|
112 break; |
|
|
113 case "maxdate": |
|
|
114 maxdate = (Date) tuple[i]; |
|
|
115 break; |
|
|
116 case "cnt": |
|
|
117 cnt = ((Number) tuple[i]).intValue(); |
|
|
118 break; |
|
|
119 case "mindepth": |
|
|
120 mindepth = ((Number) tuple[i]).doubleValue(); |
|
|
121 break; |
|
|
122 case "maxdepth": |
|
|
123 maxdepth = ((Number) tuple[i]).doubleValue(); |
|
|
124 break; |
|
|
125 case "d50": |
|
|
126 d50 = ((Number) tuple[i]).doubleValue() / 1000; // mm to m |
|
|
127 break; |
|
|
128 default: |
|
|
129 break; |
|
|
130 } |
|
|
131 } |
|
|
132 } |
|
|
133 } |
|
|
134 |
|
|
135 /***** FIELDS *****/ |
|
|
136 |
|
|
137 /** |
|
|
138 * Private log to use here. |
|
|
139 */ |
|
|
140 private static Logger log = Logger.getLogger(BedQualityD50KmValueFinder.class); |
|
|
141 |
|
|
142 /** |
|
|
143 * Query that aggregates by km for a km range and a time period all sub layer bed measurements with their d50<br /> |
|
|
144 * <br /> |
|
|
145 * A km may have bed measurements for multiple dates, multiple distances from the river bank, and multiple depth layers. |
|
|
146 * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth). |
|
|
147 * Those measurements are then grouped by km, and the D50 aggregated as average value. |
|
|
148 */ |
|
|
149 private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT = |
|
|
150 "SELECT t.km, MIN(t.datum) AS mindate, MAX(t.datum) AS maxdate, COUNT(*) AS cnt," |
|
|
151 + " MIN(p.tiefevon) AS mindepth, MAX(p.tiefebis) AS maxdepth, AVG(a.d50) AS d50" |
|
|
152 + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid" |
|
|
153 + " INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid" |
|
|
154 + " INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid" |
|
|
155 + " INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid" |
|
|
156 + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)" |
|
|
157 + " AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)" |
|
|
158 + " AND (t.datum BETWEEN :fromdate AND :todate)" |
|
|
159 + " GROUP BY t.km" |
|
|
160 + " ORDER BY t.km"; |
|
|
161 private static final String[] SQL_BED_D50_SELECT_ALIAS = {"km", "mindate", "maxdate", "cnt", "mindepth", "maxdepth", "d50"}; |
|
|
162 |
|
|
163 /** |
|
8901
|
164 * Kms of the loaded river range |
|
8898
|
165 */ |
|
8901
|
166 private TDoubleArrayList kms; |
|
|
167 |
|
|
168 /** |
|
|
169 * D50 for each km in kms |
|
|
170 */ |
|
|
171 private TDoubleArrayList values; |
|
|
172 |
|
8898
|
173 |
|
|
174 /***** METHODS *****/ |
|
|
175 |
|
|
176 /** |
|
|
177 * Returns the d50 value interpolated according to a km |
|
8901
|
178 * @return d50 (mm) of the km, or NaN |
|
8898
|
179 */ |
|
|
180 public double findD50(double km) throws ArgumentOutsideDomainException { |
|
8901
|
181 if ((kms == null) || (kms.size() == 0)) |
|
|
182 return Double.NaN; |
|
|
183 int i = kms.binarySearch(km); |
|
|
184 if (i >= 0) |
|
|
185 return values.get(i); |
|
|
186 i = -i - 1; |
|
|
187 if ((i - 1 >= 0) && Utils.epsilonEquals(km, kms.get(i - 1), 0.0001)) |
|
|
188 return values.get(i - 1); |
|
|
189 else if ((i >= 0) && (i <= kms.size() - 1) && Utils.epsilonEquals(km, kms.get(i), 0.0001)) |
|
|
190 return values.get(i); |
|
|
191 else |
|
|
192 return Double.NaN; |
|
8898
|
193 } |
|
|
194 |
|
|
195 /** |
|
|
196 * Loads the range of the river's kms with their associated values. |
|
|
197 * @return Whether the load has been successful |
|
|
198 */ |
|
|
199 public boolean loadValues(final River river, final DoubleRange kmRange, final DateRange dateRange) { |
|
|
200 log.debug(String.format("loadValues km %.3f - %.3f %tF - %tF", kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), dateRange.getFrom(), dateRange.getTo())); |
|
|
201 Session session = SedDBSessionHolder.HOLDER.get(); |
|
|
202 SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_D50_SUBLAYER_MEASUREMENT) |
|
|
203 .addScalar("km", StandardBasicTypes.DOUBLE) |
|
|
204 .addScalar("mindate", StandardBasicTypes.DATE) |
|
|
205 .addScalar("maxdate", StandardBasicTypes.DATE) |
|
|
206 .addScalar("cnt", StandardBasicTypes.INTEGER) |
|
|
207 .addScalar("mindepth", StandardBasicTypes.DOUBLE) |
|
|
208 .addScalar("maxdepth", StandardBasicTypes.DOUBLE) |
|
|
209 .addScalar("d50", StandardBasicTypes.DOUBLE); |
|
|
210 String seddbRiver = river.nameForSeddb(); |
|
|
211 sqlQuery.setString("name", seddbRiver); |
|
|
212 sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble()); |
|
|
213 sqlQuery.setDouble("tokm", kmRange.getMaximumDouble()); |
|
|
214 sqlQuery.setDate("fromdate", dateRange.getFrom()); |
|
|
215 sqlQuery.setDate("todate", dateRange.getTo()); |
|
|
216 @SuppressWarnings("unchecked") |
|
|
217 final List<Object[]> rows = sqlQuery.list(); |
|
8901
|
218 kms = new TDoubleArrayList(); |
|
|
219 values = new TDoubleArrayList(); |
|
8898
|
220 D50Measurement measurement; |
|
|
221 for (Object[] row : rows) { |
|
|
222 measurement = new D50Measurement(row, SQL_BED_D50_SELECT_ALIAS); |
|
8901
|
223 kms.add(measurement.getKm()); |
|
|
224 values.add(measurement.getD50()); |
|
|
225 log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms.get(kms.size()-1), values.get(values.size()-1), measurement.getCnt())); |
|
8898
|
226 } |
|
|
227 return true; |
|
|
228 } |
|
|
229 |
|
|
230 } |
