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@8915: import java.util.Calendar;
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;
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.
mschaefer@8901: *
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
mschaefer@8898: *
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).
mschaefer@8920: *
mschaefer@8920: * If PostgreSQL would support a median aggregate function like Oracle does, the aggregation could be placed into this query.
mschaefer@8898: */
mschaefer@8898: private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT =
mschaefer@8920: "SELECT t.km, t.datum, p.tiefevon, p.tiefebis, a.d50"
mschaefer@8920: + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid"
mschaefer@8920: + " INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid"
mschaefer@8920: + " INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid"
mschaefer@8920: + " INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid"
mschaefer@8920: + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)"
mschaefer@8920: + " AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)"
mschaefer@8920: + " AND (t.datum BETWEEN :fromdate AND :todate)"
mschaefer@8920: + " ORDER BY t.km ASC, a.d50 ASC";
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:
gernotbelger@8915:
mschaefer@8920: /***** CONSTRUCTORS *****/
mschaefer@8920:
gernotbelger@8915: private BedQualityD50KmValueFinder(final double[] kms, final double[] values) {
gernotbelger@8915: this.interpolator = new LinearInterpolator().interpolate(kms, values);
gernotbelger@8915: }
gernotbelger@8915:
mschaefer@8920: /***** METHODS *****/
mschaefer@8920:
mschaefer@8898: /**
gernotbelger@8915: * Sohlbeschaffenheit (D50 Korndurchmesser aus Seddb)
gernotbelger@8915: * Abhängig von Peiljahr
mschaefer@8898: */
gernotbelger@8915: public static BedQualityD50KmValueFinder loadBedMeasurements(final River river, final DoubleRange kmRange, final int soundingYear, final int validYears) {
gernotbelger@8915:
gernotbelger@8915: /* construct valid measurement time range */
gernotbelger@8915: final Calendar cal = Calendar.getInstance();
gernotbelger@8915: cal.clear();
gernotbelger@8915:
gernotbelger@8915: cal.set(soundingYear - validYears, 0, 1);
gernotbelger@8915: final Date startTime = cal.getTime();
gernotbelger@8915:
gernotbelger@8915: cal.set(soundingYear + validYears, 11, 31);
gernotbelger@8915: final Date endTime = cal.getTime();
gernotbelger@8915:
gernotbelger@8915: log.debug(String.format("loadValues km %.3f - %.3f %tF - %tF", kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), startTime, 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)
mschaefer@8920: .addScalar("datum", StandardBasicTypes.DATE).addScalar("tiefevon", StandardBasicTypes.DOUBLE)
mschaefer@8920: .addScalar("tiefebis", StandardBasicTypes.DOUBLE).addScalar("d50", StandardBasicTypes.DOUBLE);
gernotbelger@8915: final String seddbRiver = river.nameForSeddb();
mschaefer@8898: sqlQuery.setString("name", seddbRiver);
mschaefer@8898: sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble());
mschaefer@8898: sqlQuery.setDouble("tokm", kmRange.getMaximumDouble());
gernotbelger@8915: sqlQuery.setDate("fromdate", startTime);
gernotbelger@8915: sqlQuery.setDate("todate", endTime);
gernotbelger@8915:
mschaefer@8898: final List