mschaefer@8898: /* Copyright (C) 2017 by Bundesanstalt für Gewässerkunde
mschaefer@8898:  * Software engineering by
mschaefer@8898:  *  Björnsen Beratende Ingenieure GmbH
mschaefer@8898:  *  Dr. Schumacher Ingenieurbüro für Wasser und Umwelt
mschaefer@8898:  *
mschaefer@8898:  * This file is Free Software under the GNU AGPL (>=v3)
mschaefer@8898:  * and comes with ABSOLUTELY NO WARRANTY! Check out the
mschaefer@8898:  * documentation coming with Dive4Elements River for details.
mschaefer@8898:  */
mschaefer@8898: 
mschaefer@8898: package org.dive4elements.river.artifacts.sinfo.flowdepth;
mschaefer@8898: 
mschaefer@8898: import java.util.Date;
mschaefer@8898: import java.util.List;
mschaefer@8898: 
mschaefer@8898: import org.apache.commons.lang.math.DoubleRange;
mschaefer@8898: import org.apache.commons.math.ArgumentOutsideDomainException;
mschaefer@8898: import org.apache.commons.math.analysis.interpolation.LinearInterpolator;
mschaefer@8898: import org.apache.commons.math.analysis.polynomials.PolynomialSplineFunction;
mschaefer@8898: import org.apache.log4j.Logger;
mschaefer@8901: import org.dive4elements.river.artifacts.math.Utils;
mschaefer@8898: import org.dive4elements.river.artifacts.model.DateRange;
mschaefer@8898: import org.dive4elements.river.backend.SedDBSessionHolder;
mschaefer@8898: import org.dive4elements.river.model.River;
mschaefer@8901: import org.dive4elements.river.utils.DoubleUtil;
mschaefer@8898: import org.hibernate.SQLQuery;
mschaefer@8898: import org.hibernate.Session;
mschaefer@8898: import org.hibernate.type.StandardBasicTypes;
mschaefer@8898: 
mschaefer@8901: import gnu.trove.TDoubleArrayList;
mschaefer@8901: 
mschaefer@8898: /**
mschaefer@8901:  * Searchable sorted km array with parallel bed measurements value array and linear interpolation for km and d50 between the array elements.<br />
mschaefer@8901:  * <br />
mschaefer@8901:  * See comment of SQL command on how the values are filtered and aggregated.
mschaefer@8901:  * 
mschaefer@8898:  * @author Matthias Schäfer
mschaefer@8898:  *
mschaefer@8898:  */
mschaefer@8898: public class BedQualityD50KmValueFinder {
mschaefer@8898: 
mschaefer@8898:     /***** INNER CLASSES *****/
mschaefer@8898:     
mschaefer@8898:     /**
mschaefer@8898:      * A bed measurements aggregate with its d50 characteristic grain diameter 
mschaefer@8898:      */
mschaefer@8898:     private class D50Measurement {
mschaefer@8898:         private double km;
mschaefer@8898:         public double getKm() {
mschaefer@8898:             return km;
mschaefer@8898:         }
mschaefer@8898:         private Date mindate;
mschaefer@8898:         public Date getMinDate() {
mschaefer@8898:             return mindate;
mschaefer@8898:         }
mschaefer@8898:         private Date maxdate;
mschaefer@8898:         public Date getMaxDate() {
mschaefer@8898:             return maxdate;
mschaefer@8898:         }
mschaefer@8898:         private int cnt;
mschaefer@8898:         public int getCnt() {
mschaefer@8898:             return cnt;
mschaefer@8898:         }
mschaefer@8898:         private double mindepth;
mschaefer@8898:         public double getMinDepth() {
mschaefer@8898:             return mindepth;
mschaefer@8898:         }
mschaefer@8898:         private double maxdepth;
mschaefer@8898:         public double getMaxDepth() {
mschaefer@8898:             return maxdepth;
mschaefer@8898:         }
mschaefer@8898:         private double d50;
mschaefer@8898:         /**
mschaefer@8898:          * D50 in m
mschaefer@8898:          */
mschaefer@8898:         public double getD50() {
mschaefer@8898:             return d50;
mschaefer@8898:         }
mschaefer@8898:         /**
mschaefer@8898:          * Parameter constructor
mschaefer@8898:          */
mschaefer@8898:         public D50Measurement(double km, Date mindate, Date maxdate, int cnt, double mindepth, double maxdepth, double d50mm) {
mschaefer@8898:             this.km = km;
mschaefer@8898:             this.mindate = mindate;
mschaefer@8898:             this.maxdate = maxdate;
mschaefer@8898:             this.cnt = cnt;
mschaefer@8898:             this.mindepth = mindepth;
mschaefer@8898:             this.maxdepth = maxdepth;
mschaefer@8898:             this.d50 = d50mm / 1000;
mschaefer@8898:         }
mschaefer@8898: 
mschaefer@8898:         /**
mschaefer@8898:          * Query result row constructor
mschaefer@8898:          */
mschaefer@8898:         public D50Measurement(Object[] tuple, String[] aliases) {
mschaefer@8898:             km = 0;
mschaefer@8898:             mindate = null;
mschaefer@8898:             maxdate = null;
mschaefer@8898:             cnt = 0;
mschaefer@8898:             mindepth = Double.NaN;
mschaefer@8898:             maxdepth = Double.NaN;
mschaefer@8898:             d50 = Double.NaN;
mschaefer@8898:             for (int i = 0; i < tuple.length; ++i) {
mschaefer@8898:                 if (tuple[i] == null)
mschaefer@8898:                     continue;
mschaefer@8898:                 switch (aliases[i]) {
mschaefer@8898:                 case "km":
mschaefer@8898:                     km = ((Number) tuple[i]).doubleValue();
mschaefer@8898:                     break;
mschaefer@8898:                 case "mindate":
mschaefer@8898:                     mindate = (Date) tuple[i];
mschaefer@8898:                     break;
mschaefer@8898:                 case "maxdate":
mschaefer@8898:                     maxdate = (Date) tuple[i];
mschaefer@8898:                     break;
mschaefer@8898:                 case "cnt":
mschaefer@8898:                      cnt = ((Number) tuple[i]).intValue();
mschaefer@8898:                    break;
mschaefer@8898:                 case "mindepth":
mschaefer@8898:                      mindepth = ((Number) tuple[i]).doubleValue();
mschaefer@8898:                    break;
mschaefer@8898:                 case "maxdepth":
mschaefer@8898:                     maxdepth = ((Number) tuple[i]).doubleValue();
mschaefer@8898:                     break;
mschaefer@8898:                 case "d50":
mschaefer@8898:                     d50 = ((Number) tuple[i]).doubleValue() / 1000;     // mm to m
mschaefer@8898:                     break;
mschaefer@8898:                 default:
mschaefer@8898:                     break;
mschaefer@8898:                 }
mschaefer@8898:             }
mschaefer@8898:         }
mschaefer@8898:     }
mschaefer@8898:     
mschaefer@8898:     /***** FIELDS *****/
mschaefer@8898:     
mschaefer@8898:     /**
mschaefer@8898:      * Private log to use here.
mschaefer@8898:      */
mschaefer@8898:     private static Logger log = Logger.getLogger(BedQualityD50KmValueFinder.class);
mschaefer@8898: 
mschaefer@8898:     /**
mschaefer@8898:      * Query that aggregates by km for a km range and a time period all sub layer bed measurements with their d50<br />
mschaefer@8898:      * <br />
mschaefer@8898:      * A km may have bed measurements for multiple dates, multiple distances from the river bank, and multiple depth layers.
mschaefer@8898:      * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth).
mschaefer@8898:      * Those measurements are then grouped by km, and the D50 aggregated as average value.
mschaefer@8898:      */
mschaefer@8898:     private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT =
mschaefer@8898:             "SELECT t.km, MIN(t.datum) AS mindate, MAX(t.datum) AS maxdate, COUNT(*) AS cnt,"
mschaefer@8898:                 + " MIN(p.tiefevon) AS mindepth, MAX(p.tiefebis) AS maxdepth, AVG(a.d50) AS d50"
mschaefer@8898:                 + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid"
mschaefer@8898:                 + "    INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid"
mschaefer@8898:                 + "    INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid"
mschaefer@8898:                 + "    INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid"
mschaefer@8898:                 + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)"
mschaefer@8898:                 + "    AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)"
mschaefer@8898:                 + "    AND (t.datum BETWEEN :fromdate AND :todate)"
mschaefer@8898:                 + " GROUP BY t.km"
mschaefer@8898:                 + " ORDER BY t.km";
mschaefer@8898:     private static final String[] SQL_BED_D50_SELECT_ALIAS = {"km", "mindate", "maxdate", "cnt", "mindepth", "maxdepth", "d50"};
mschaefer@8898: 
mschaefer@8898:     /**
mschaefer@8911:      * Real linear interpolator for kms and d50 values
mschaefer@8898:      */
mschaefer@8911:     private PolynomialSplineFunction interpolator;
mschaefer@8901:     
mschaefer@8898:     
mschaefer@8898:     /***** METHODS *****/
mschaefer@8898:     
mschaefer@8898:     /**
mschaefer@8898:      * Returns the d50 value interpolated according to a km
mschaefer@8901:      * @return d50 (mm) of the km, or NaN
mschaefer@8898:      */
mschaefer@8898:     public double findD50(double km) throws ArgumentOutsideDomainException {
mschaefer@8911:         return interpolator.value(km);
mschaefer@8911:         /* ohne interpolation:
mschaefer@8901:         if ((kms == null) || (kms.size() == 0))
mschaefer@8901:             return Double.NaN;
mschaefer@8901:         int i = kms.binarySearch(km);
mschaefer@8901:         if (i >= 0)
mschaefer@8901:             return values.get(i);
mschaefer@8901:         i = -i - 1;
mschaefer@8901:         if ((i - 1 >= 0) && Utils.epsilonEquals(km, kms.get(i - 1), 0.0001))
mschaefer@8901:             return values.get(i - 1);
mschaefer@8901:         else if ((i >= 0) && (i <= kms.size() - 1) && Utils.epsilonEquals(km, kms.get(i), 0.0001))
mschaefer@8901:             return values.get(i);
mschaefer@8901:         else
mschaefer@8911:             return Double.NaN; */
mschaefer@8898:     }
mschaefer@8898:     
mschaefer@8898:     /**
mschaefer@8898:      * Loads the range of the river's kms with their associated values.
mschaefer@8898:      * @return Whether the load has been successful
mschaefer@8898:      */
mschaefer@8898:     public boolean loadValues(final River river, final DoubleRange kmRange, final DateRange dateRange) {
mschaefer@8898:         log.debug(String.format("loadValues km %.3f - %.3f %tF - %tF", kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), dateRange.getFrom(), dateRange.getTo()));
mschaefer@8898:         Session session = SedDBSessionHolder.HOLDER.get();
mschaefer@8898:         SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_D50_SUBLAYER_MEASUREMENT)
mschaefer@8898:                 .addScalar("km", StandardBasicTypes.DOUBLE)
mschaefer@8898:                 .addScalar("mindate", StandardBasicTypes.DATE)
mschaefer@8898:                 .addScalar("maxdate", StandardBasicTypes.DATE)
mschaefer@8898:                 .addScalar("cnt", StandardBasicTypes.INTEGER)
mschaefer@8898:                 .addScalar("mindepth", StandardBasicTypes.DOUBLE)
mschaefer@8898:                 .addScalar("maxdepth", StandardBasicTypes.DOUBLE)
mschaefer@8898:                 .addScalar("d50", StandardBasicTypes.DOUBLE);
mschaefer@8898:         String seddbRiver = river.nameForSeddb();
mschaefer@8898:         sqlQuery.setString("name", seddbRiver);
mschaefer@8898:         sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble());
mschaefer@8898:         sqlQuery.setDouble("tokm", kmRange.getMaximumDouble());
mschaefer@8898:         sqlQuery.setDate("fromdate", dateRange.getFrom());
mschaefer@8898:         sqlQuery.setDate("todate", dateRange.getTo());
mschaefer@8898:         @SuppressWarnings("unchecked")
mschaefer@8898:         final List<Object[]> rows = sqlQuery.list();
mschaefer@8911:         final double[] kms = new double[rows.size()];
mschaefer@8911:         final double[] values = new double[rows.size()];
mschaefer@8898:         D50Measurement measurement;
mschaefer@8911:         int i = -1;
mschaefer@8898:         for (Object[] row : rows) {
mschaefer@8898:             measurement = new D50Measurement(row, SQL_BED_D50_SELECT_ALIAS);
mschaefer@8911:             i++;
mschaefer@8911:             kms[i] = measurement.getKm();
mschaefer@8911:             values[i] = measurement.getD50();
mschaefer@8911:             log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms[i], values[i], measurement.getCnt()));
mschaefer@8898:         }
mschaefer@8911:         try {
mschaefer@8911:             interpolator = new LinearInterpolator().interpolate(kms, values);
mschaefer@8911:             return true;
mschaefer@8911:         } catch (Exception e) {
mschaefer@8911:             interpolator = null;
mschaefer@8911:             return false;
mschaefer@8911:         }
mschaefer@8898:     }
mschaefer@8898: 
mschaefer@8898: }