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: 
gernotbelger@8915: package org.dive4elements.river.artifacts.sinfo.tkhcalculation;
mschaefer@8898: 
gernotbelger@9522: import java.io.IOException;
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@8920: import org.dive4elements.river.artifacts.math.Utils;
gernotbelger@8964: import org.dive4elements.river.artifacts.model.Calculation;
gernotbelger@9522: import org.dive4elements.river.artifacts.model.DateRange;
gernotbelger@9522: import org.dive4elements.river.artifacts.sinfo.tkhstate.BedQualityD50TimeRangeConfig;
gernotbelger@9522: import org.dive4elements.river.artifacts.sinfo.tkhstate.TsvHelper.TsvReaderException;
mschaefer@8898: import org.dive4elements.river.backend.SedDBSessionHolder;
mschaefer@8898: import org.dive4elements.river.model.River;
mschaefer@8898: import org.hibernate.SQLQuery;
mschaefer@8898: import org.hibernate.Session;
mschaefer@8898: import org.hibernate.type.StandardBasicTypes;
mschaefer@8898: 
mschaefer@8920: import gnu.trove.TDoubleArrayList;
mschaefer@8920: 
mschaefer@8898: /**
gernotbelger@8915:  * Searchable sorted km array with parallel bed measurements value array and linear interpolation for km and d50 between
gernotbelger@8915:  * the array elements.<br />
mschaefer@8901:  * <br />
mschaefer@8901:  * See comment of SQL command on how the values are filtered and aggregated.
gernotbelger@8915:  *
mschaefer@8898:  * @author Matthias Schäfer
mschaefer@8898:  *
mschaefer@8898:  */
mschaefer@8920: public class BedQualityD50KmValueFinder {
gernotbelger@8915: 
mschaefer@8898:     /***** FIELDS *****/
gernotbelger@8915: 
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@8920:      * Query selecting all sub layer bed measurements with their d50 for a km range and a time period<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@8920:      * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth).<br />
gernotbelger@8964:      *
gernotbelger@8964:      * If PostgreSQL would support a median aggregate function like Oracle does, the aggregation could be placed into this
gernotbelger@8964:      * query.
mschaefer@8898:      */
mschaefer@9017:     private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT = //
gernotbelger@9522:             "SELECT s.km, t.datum, p.tiefevon, p.tiefebis, a.d50" //
gernotbelger@9522:                     + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid" //
gernotbelger@9522:                     + "    INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid" //
gernotbelger@9522:                     + "    INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid" //
gernotbelger@9522:                     + "    INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid" //
gernotbelger@9522:                     + " WHERE (g.name = :name)" //
gernotbelger@9522:                     + "    AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)" //
gernotbelger@9522:                     + "    AND (t.datum BETWEEN :fromdate AND :todate)" //
mschaefer@9665:                     + " ORDER BY s.km ASC, a.d50 ASC";
gernotbelger@8964: 
gernotbelger@9522:     public static final Date MIN_DATE = new Date(-10000000000000l); // Database does not cope with Long.MIN/Long.Max, so we go just a few hundred years
gernotbelger@9522:     // back/forward
gernotbelger@9522:     public static final Date MAX_DATE = new Date(10000000000000l);
mschaefer@9339: 
gernotbelger@8964:     private Calculation problems;
mschaefer@8898: 
mschaefer@8898:     /**
mschaefer@8920:      * Real linear interpolator for kms and d50 values (m)
mschaefer@8898:      */
gernotbelger@8915:     private final PolynomialSplineFunction interpolator;
gernotbelger@8915: 
mschaefer@8920:     /***** CONSTRUCTORS *****/
gernotbelger@8964: 
gernotbelger@8964:     private BedQualityD50KmValueFinder(final Calculation problems, final double[] kms, final double[] values) {
gernotbelger@8964:         this.problems = problems;
gernotbelger@8964: 
gernotbelger@8915:         this.interpolator = new LinearInterpolator().interpolate(kms, values);
gernotbelger@8915:     }
gernotbelger@8915: 
mschaefer@8920:     /***** METHODS *****/
gernotbelger@8964: 
mschaefer@8898:     /**
gernotbelger@8915:      * Sohlbeschaffenheit (D50 Korndurchmesser aus Seddb)
gernotbelger@8915:      * Abhängig von Peiljahr
gernotbelger@8964:      *
gernotbelger@8964:      * @param problems
gernotbelger@9522:      * @throws TsvReaderException
gernotbelger@9522:      * @throws IOException
mschaefer@8898:      */
gernotbelger@8964:     public static BedQualityD50KmValueFinder loadBedMeasurements(final Calculation problems, final River river, final DoubleRange kmRange,
gernotbelger@9532:             final int soundingYear) {
gernotbelger@8915: 
gernotbelger@9532:         final DateRange dateRange = BedQualityD50TimeRangeConfig.getDefaults(river, soundingYear, problems);
gernotbelger@9532:         if (dateRange == null)
gernotbelger@9532:             return null;
gernotbelger@9532: 
gernotbelger@9522:         return loadBedMeasurements(problems, river, kmRange, dateRange, soundingYear);
gernotbelger@9522:     }
gernotbelger@8915: 
gernotbelger@9522:     private static BedQualityD50KmValueFinder loadBedMeasurements(final Calculation problems, final River river, final DoubleRange kmRange,
gernotbelger@9522:             final DateRange dateRange, final int soundingYear) {
gernotbelger@8915: 
gernotbelger@9522:         final Date startTime = dateRange.getFrom();
gernotbelger@9522:         final Date endTime = dateRange.getTo();
gernotbelger@8915: 
mschaefer@9091:         final String seddbRiver = river.nameForSeddb();
mschaefer@9091:         log.debug(String.format("loadValues '%s' km %.3f - %.3f %tF - %tF", seddbRiver, kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), startTime,
mschaefer@9091:                 endTime));
gernotbelger@8915:         final Session session = SedDBSessionHolder.HOLDER.get();
gernotbelger@8915:         final SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_D50_SUBLAYER_MEASUREMENT).addScalar("km", StandardBasicTypes.DOUBLE)
gernotbelger@8964:                 .addScalar("datum", StandardBasicTypes.DATE).addScalar("tiefevon", StandardBasicTypes.DOUBLE).addScalar("tiefebis", StandardBasicTypes.DOUBLE)
gernotbelger@8964:                 .addScalar("d50", StandardBasicTypes.DOUBLE);
mschaefer@8898:         sqlQuery.setString("name", seddbRiver);
mschaefer@9339:         // sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble());
mschaefer@9339:         // sqlQuery.setDouble("tokm", kmRange.getMaximumDouble());
gernotbelger@8915:         sqlQuery.setDate("fromdate", startTime);
gernotbelger@8915:         sqlQuery.setDate("todate", endTime);
mschaefer@9665:         final List<Object[]> rows = sqlQuery.list();
gernotbelger@8915: 
mschaefer@9665:         // Aggregate d50 values for each km
mschaefer@9665:         // final TDoubleArrayList kmsm = new TDoubleArrayList();
mschaefer@9665:         // final TDoubleArrayList valuesm = new TDoubleArrayList();
mschaefer@8920:         final TDoubleArrayList kms = new TDoubleArrayList();
mschaefer@8920:         final TDoubleArrayList values = new TDoubleArrayList();
mschaefer@9081:         if (rows != null) {
mschaefer@9665:             // // median
mschaefer@9665:             // final TDoubleArrayList kmd50s = new TDoubleArrayList();
mschaefer@9665:             // for (int i = 0; i <= rows.size() - 1; i++) {
mschaefer@9665:             // log.trace("loadValues rows(" + i + ") " + rows.get(i)[0] + " " + rows.get(i)[1] + " " + rows.get(i)[2] + " " +
mschaefer@9665:             // rows.get(i)[3] + " "
mschaefer@9665:             // + rows.get(i)[4]);
mschaefer@9665:             // kmd50s.add((double) rows.get(i)[4]);
mschaefer@9665:             // if (((i == rows.size() - 1) || !Utils.epsilonEquals((double) rows.get(i)[0], (double) rows.get(i + 1)[0], 0.0001))) {
mschaefer@9665:             // final int k = kmd50s.size() / 2;
mschaefer@9665:             // valuesm.add(((k + k < kmd50s.size()) ? kmd50s.get(k) : (kmd50s.get(k - 1) + kmd50s.get(k)) / 2) / 1000);
mschaefer@9665:             // kmsm.add((double) rows.get(i)[0]);
mschaefer@9665:             // log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kmsm.get(kmsm.size() - 1),
mschaefer@9665:             // valuesm.get(valuesm.size() - 1) * 1000,
mschaefer@9665:             // kmd50s.size()));
mschaefer@9665:             // kmd50s.clear();
mschaefer@9665:             // }
mschaefer@9665:             // }
mschaefer@9665:             // arithmetic mean
mschaefer@9665:             double sum = 0;
mschaefer@9665:             int n = 0;
mschaefer@9081:             for (int i = 0; i <= rows.size() - 1; i++) {
mschaefer@9665:                 sum += (double) rows.get(i)[4];
mschaefer@9665:                 n++;
mschaefer@9081:                 if (((i == rows.size() - 1) || !Utils.epsilonEquals((double) rows.get(i)[0], (double) rows.get(i + 1)[0], 0.0001))) {
mschaefer@9081:                     kms.add((double) rows.get(i)[0]);
mschaefer@9665:                     values.add((sum / n) / 1000);
mschaefer@9665:                     log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms.get(kms.size() - 1), values.get(values.size() - 1) * 1000, n));
mschaefer@9665:                     sum = 0;
mschaefer@9665:                     n = 0;
mschaefer@9081:                 }
gernotbelger@8964:             }
mschaefer@8920:         }
mschaefer@9017:         if (kms.size() >= 1)
mschaefer@9017:             log.debug(String.format("loadValues: %d kms found from %.3f to %.3f", kms.size(), kms.get(0), kms.get(kms.size() - 1)));
mschaefer@9017:         else
mschaefer@9017:             log.debug("loadValues: no kms found");
gernotbelger@8964: 
gernotbelger@8964:         if (kms.size() < 2 || values.size() < 2) {
gernotbelger@9468:             problems.addProblem("bedqualityd50kmvaluefinder.empty", Integer.toString(soundingYear));
gernotbelger@8964:             return null;
mschaefer@8898:         }
gernotbelger@8964: 
mschaefer@8911:         try {
gernotbelger@8964:             return new BedQualityD50KmValueFinder(problems, kms.toNativeArray(), values.toNativeArray());
gernotbelger@8915:         }
gernotbelger@8915:         catch (final Exception e) {
gernotbelger@8915:             e.printStackTrace();
gernotbelger@8964:             problems.addProblem("bedqualityd50kmvaluefinder.error", e.getLocalizedMessage());
gernotbelger@8915:             return null;
mschaefer@8911:         }
mschaefer@8898:     }
mschaefer@8898: 
gernotbelger@8915:     /**
gernotbelger@8915:      * Returns the d50 value interpolated according to a km
gernotbelger@8915:      *
mschaefer@8920:      * @return d50 (m) of the km, or NaN
gernotbelger@8915:      */
gernotbelger@8964:     public double findD50(final double km) {
gernotbelger@8964:         try {
gernotbelger@8964:             return this.interpolator.value(km);
gernotbelger@8964:         }
mschaefer@9665:         catch (final ArgumentOutsideDomainException e) {
gernotbelger@9522:             // No stack trace because this might happen a lot (intended) and we produce an error message anyways.
gernotbelger@9086:             // e.printStackTrace();
gernotbelger@8964: 
gernotbelger@8964:             if (this.problems != null) {
gernotbelger@8964:                 this.problems.addProblem(km, "bedqualityd50kmvaluefinder.missing");
gernotbelger@8964:                 // Report only once
gernotbelger@8964:                 this.problems = null;
gernotbelger@8964:             }
gernotbelger@8964: 
gernotbelger@8964:             return Double.NaN;
gernotbelger@8964:         }
gernotbelger@8915:     }
gernotbelger@8915: }