--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java	Thu Feb 22 12:03:31 2018 +0100
@@ -0,0 +1,209 @@
+/* Copyright (C) 2017 by Bundesanstalt für Gewässerkunde
+ * Software engineering by
+ *  Björnsen Beratende Ingenieure GmbH
+ *  Dr. Schumacher Ingenieurbüro für Wasser und Umwelt
+ *
+ * This file is Free Software under the GNU AGPL (>=v3)
+ * and comes with ABSOLUTELY NO WARRANTY! Check out the
+ * documentation coming with Dive4Elements River for details.
+ */
+
+package org.dive4elements.river.artifacts.sinfo.flowdepth;
+
+import java.util.Date;
+import java.util.List;
+
+import org.apache.commons.lang.math.DoubleRange;
+import org.apache.commons.math.ArgumentOutsideDomainException;
+import org.apache.commons.math.analysis.interpolation.LinearInterpolator;
+import org.apache.commons.math.analysis.polynomials.PolynomialSplineFunction;
+import org.apache.log4j.Logger;
+import org.dive4elements.river.artifacts.model.DateRange;
+import org.dive4elements.river.backend.SedDBSessionHolder;
+import org.dive4elements.river.model.River;
+import org.hibernate.SQLQuery;
+import org.hibernate.Session;
+import org.hibernate.type.StandardBasicTypes;
+
+/**
+ * Searchable sorted km array with parallel bed measurements value array and linear interpolation for km and d50 between the array elements.
+ * @author Matthias Schäfer
+ *
+ */
+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 *****/
+    
+    /**
+     * Private log to use here.
+     */
+    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<br />
+     * <br />
+     * 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.
+     */
+    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"};
+
+    /**
+     * Real linear interpolator for kms and d50 values
+     */
+    private PolynomialSplineFunction interpolator;
+    
+    /***** METHODS *****/
+    
+    /**
+     * Returns the d50 value interpolated according to a km
+     * @throws ArgumentOutsideDomainException
+     */
+    public double findD50(double km) throws ArgumentOutsideDomainException {
+        return interpolator.value(km);
+    }
+    
+    /**
+     * Loads the range of the river's kms with their associated values.
+     * @return Whether the load has been successful
+     */
+    public boolean loadValues(final River river, final DoubleRange kmRange, final DateRange dateRange) {
+        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);
+        String seddbRiver = river.nameForSeddb();
+        sqlQuery.setString("name", seddbRiver);
+        sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble());
+        sqlQuery.setDouble("tokm", kmRange.getMaximumDouble());
+        sqlQuery.setDate("fromdate", dateRange.getFrom());
+        sqlQuery.setDate("todate", dateRange.getTo());
+        @SuppressWarnings("unchecked")
+        final List<Object[]> 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()));
+        }
+        interpolator = new LinearInterpolator().interpolate(kms, values);
+        return true;
+    }
+
+}