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