# HG changeset patch
# User mschaefer
# Date 1519667765 -3600
# Node ID 86395ab8ebc381d8128f4a6c6324c3c14c8922f5
# Parent 37ff7f43591222df1152f715af27ee958516229c
SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
diff -r 37ff7f435912 -r 86395ab8ebc3 artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java
--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java Fri Feb 23 09:30:24 2018 +0100
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java Mon Feb 26 18:56:05 2018 +0100
@@ -10,7 +10,6 @@
package org.dive4elements.river.artifacts.sinfo.flowdepth;
-import java.util.Date;
import java.util.List;
import org.apache.commons.lang.math.DoubleRange;
@@ -22,7 +21,6 @@
import org.dive4elements.river.artifacts.model.DateRange;
import org.dive4elements.river.backend.SedDBSessionHolder;
import org.dive4elements.river.model.River;
-import org.dive4elements.river.utils.DoubleUtil;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.type.StandardBasicTypes;
@@ -39,99 +37,6 @@
*/
public class BedQualityD50KmValueFinder {
- /***** INNER CLASSES *****/
-
- /**
- * A bed measurements aggregate with its d50 characteristic grain diameter
- */
- private class D50Measurement {
- private double km;
- public double getKm() {
- return km;
- }
- private Date mindate;
- public Date getMinDate() {
- return mindate;
- }
- private Date maxdate;
- public Date getMaxDate() {
- return maxdate;
- }
- private int cnt;
- public int getCnt() {
- return cnt;
- }
- private double mindepth;
- public double getMinDepth() {
- return mindepth;
- }
- private double maxdepth;
- public double getMaxDepth() {
- return maxdepth;
- }
- private double d50;
- /**
- * D50 in m
- */
- public double getD50() {
- return d50;
- }
- /**
- * Parameter constructor
- */
- public D50Measurement(double km, Date mindate, Date maxdate, int cnt, double mindepth, double maxdepth, double d50mm) {
- this.km = km;
- this.mindate = mindate;
- this.maxdate = maxdate;
- this.cnt = cnt;
- this.mindepth = mindepth;
- this.maxdepth = maxdepth;
- this.d50 = d50mm / 1000;
- }
-
- /**
- * Query result row constructor
- */
- public D50Measurement(Object[] tuple, String[] aliases) {
- km = 0;
- mindate = null;
- maxdate = null;
- cnt = 0;
- mindepth = Double.NaN;
- maxdepth = Double.NaN;
- d50 = Double.NaN;
- for (int i = 0; i < tuple.length; ++i) {
- if (tuple[i] == null)
- continue;
- switch (aliases[i]) {
- case "km":
- km = ((Number) tuple[i]).doubleValue();
- break;
- case "mindate":
- mindate = (Date) tuple[i];
- break;
- case "maxdate":
- maxdate = (Date) tuple[i];
- break;
- case "cnt":
- cnt = ((Number) tuple[i]).intValue();
- break;
- case "mindepth":
- mindepth = ((Number) tuple[i]).doubleValue();
- break;
- case "maxdepth":
- maxdepth = ((Number) tuple[i]).doubleValue();
- break;
- case "d50":
- d50 = ((Number) tuple[i]).doubleValue() / 1000; // mm to m
- break;
- default:
- break;
- }
- }
- }
- }
-
/***** FIELDS *****/
/**
@@ -140,28 +45,26 @@
private static Logger log = Logger.getLogger(BedQualityD50KmValueFinder.class);
/**
- * Query that aggregates by km for a km range and a time period all sub layer bed measurements with their d50
+ * Query selecting all sub layer bed measurements with their d50 for a km range and a time period
*
* A km may have bed measurements for multiple dates, multiple distances from the river bank, and multiple depth layers.
- * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth).
- * Those measurements are then grouped by km, and the D50 aggregated as average value.
+ * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth).
+ *
+ * If PostgreSQL would support a median aggregate function like Oracle does the aggregation could be placed into this query.
*/
private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT =
- "SELECT t.km, MIN(t.datum) AS mindate, MAX(t.datum) AS maxdate, COUNT(*) AS cnt,"
- + " MIN(p.tiefevon) AS mindepth, MAX(p.tiefebis) AS maxdepth, AVG(a.d50) AS d50"
- + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid"
- + " INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid"
- + " INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid"
- + " INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid"
- + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)"
- + " AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)"
- + " AND (t.datum BETWEEN :fromdate AND :todate)"
- + " GROUP BY t.km"
- + " ORDER BY t.km";
- private static final String[] SQL_BED_D50_SELECT_ALIAS = {"km", "mindate", "maxdate", "cnt", "mindepth", "maxdepth", "d50"};
+ "SELECT t.km, t.datum, p.tiefevon, p.tiefebis, a.d50 AS d50"
+ + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid"
+ + " INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid"
+ + " INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid"
+ + " INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid"
+ + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)"
+ + " AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)"
+ + " AND (t.datum BETWEEN :fromdate AND :todate)"
+ + " ORDER BY t.km ASC, a.d50 ASC";
/**
- * Real linear interpolator for kms and d50 values
+ * Real linear interpolator for kms and d50 values (m)
*/
private PolynomialSplineFunction interpolator;
@@ -170,7 +73,7 @@
/**
* Returns the d50 value interpolated according to a km
- * @return d50 (mm) of the km, or NaN
+ * @return d50 (m) of the km, or NaN
*/
public double findD50(double km) throws ArgumentOutsideDomainException {
return interpolator.value(km);
@@ -197,13 +100,11 @@
log.debug(String.format("loadValues km %.3f - %.3f %tF - %tF", kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), dateRange.getFrom(), dateRange.getTo()));
Session session = SedDBSessionHolder.HOLDER.get();
SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_D50_SUBLAYER_MEASUREMENT)
- .addScalar("km", StandardBasicTypes.DOUBLE)
- .addScalar("mindate", StandardBasicTypes.DATE)
- .addScalar("maxdate", StandardBasicTypes.DATE)
- .addScalar("cnt", StandardBasicTypes.INTEGER)
- .addScalar("mindepth", StandardBasicTypes.DOUBLE)
- .addScalar("maxdepth", StandardBasicTypes.DOUBLE)
- .addScalar("d50", StandardBasicTypes.DOUBLE);
+ .addScalar("km", StandardBasicTypes.DOUBLE)
+ .addScalar("datum", StandardBasicTypes.DATE)
+ .addScalar("tiefevon", StandardBasicTypes.DOUBLE)
+ .addScalar("tiefebis", StandardBasicTypes.DOUBLE)
+ .addScalar("d50", StandardBasicTypes.DOUBLE);
String seddbRiver = river.nameForSeddb();
sqlQuery.setString("name", seddbRiver);
sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble());
@@ -212,24 +113,25 @@
sqlQuery.setDate("todate", dateRange.getTo());
@SuppressWarnings("unchecked")
final List rows = sqlQuery.list();
- final double[] kms = new double[rows.size()];
- final double[] values = new double[rows.size()];
- D50Measurement measurement;
- int i = -1;
- for (Object[] row : rows) {
- measurement = new D50Measurement(row, SQL_BED_D50_SELECT_ALIAS);
- i++;
- kms[i] = measurement.getKm();
- values[i] = measurement.getD50();
- log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms[i], values[i], measurement.getCnt()));
+ final TDoubleArrayList kms = new TDoubleArrayList();
+ final TDoubleArrayList values = new TDoubleArrayList();
+ final TDoubleArrayList kmd50s = new TDoubleArrayList();
+ for (int i = 0; i <= rows.size() - 1; i++) {
+ kmd50s.add((double) rows.get(i)[4]);
+ if (((i == rows.size() - 1) || !Utils.epsilonEquals((double) rows.get(i)[0], (double) rows.get(i+1)[0], 0.0001))) {
+ int k = kmd50s.size() / 2;
+ values.add(((k + k < kmd50s.size()) ? kmd50s.get(k) : (kmd50s.get(k-1) + kmd50s.get(k)) / 2) / 1000);
+ kms.add((double) rows.get(i)[0]);
+ log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms.get(kms.size()-1), values.get(values.size()-1), kmd50s.size()));
+ kmd50s.clear();
+ }
}
try {
- interpolator = new LinearInterpolator().interpolate(kms, values);
+ interpolator = new LinearInterpolator().interpolate(kms.toNativeArray(), values.toNativeArray());
return true;
} catch (Exception e) {
interpolator = null;
return false;
}
}
-
}