# HG changeset patch # User mschaefer # Date 1519667917 -3600 # Node ID 87aa705e1e991a2c538cb0f9ce036a2cccda9c0d # Parent 924cd99433378d42128cd3a34c265ac679ef881a# Parent 86395ab8ebc381d8128f4a6c6324c3c14c8922f5 Merge diff -r 924cd9943337 -r 87aa705e1e99 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 Mon Feb 26 18:10:03 2018 +0100 +++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java Mon Feb 26 18:58:37 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; } } - }