--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/common/WQBaseTableFinder.java	Mon Jun 25 19:21:11 2018 +0200
@@ -0,0 +1,185 @@
+/** 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.common;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map.Entry;
+import java.util.NavigableMap;
+import java.util.TreeMap;
+
+import org.apache.commons.math.FunctionEvaluationException;
+import org.apache.commons.math.analysis.interpolation.LinearInterpolator;
+import org.apache.commons.math.analysis.polynomials.PolynomialSplineFunction;
+import org.dive4elements.river.artifacts.math.Linear;
+import org.dive4elements.river.artifacts.model.Calculation;
+import org.dive4elements.river.backend.SessionHolder;
+import org.dive4elements.river.model.River;
+import org.hibernate.SQLQuery;
+import org.hibernate.Session;
+import org.hibernate.type.StandardBasicTypes;
+
+import gnu.trove.TDoubleArrayList;
+
+/**
+ * Loading and search/interpolation of a W/Q base table of a river
+ *
+ * @author Matthias Schäfer
+ *
+ */
+public final class WQBaseTableFinder {
+
+    /***** FIELDS *****/
+
+    // private static Logger log = Logger.getLogger(WQTableFinder.class);
+
+    private final River river;
+
+    private Calculation problems;
+
+    private final List<String> columnNames;
+
+    private final NavigableMap<Double, PolynomialSplineFunction> kmWs;
+
+    private final NavigableMap<Double, PolynomialSplineFunction> kmQs;
+
+    private static final String SQLCOLUMNS = "SELECT wc.position AS colindex, wc.name AS qzone"
+            + " FROM wsts w"
+            + "  INNER JOIN wst_columns wc ON w.id=wc.wst_id"
+            + " WHERE w.river_id = :river_id"
+            + "  AND w.kind = 0"
+            + " ORDER BY wc.position ASC";
+
+    private static final String SQLMAIN = "SELECT wcv.position AS station, wc.position AS colindex, wcv.w, wqr.q"
+            + " FROM wsts w"
+            + "  INNER JOIN wst_columns wc ON w.id=wc.wst_id"
+            + "  INNER JOIN wst_column_values wcv ON wc.id=wcv.wst_column_id"
+            + "  INNER JOIN wst_column_q_ranges wcqr ON wc.id=wcqr.wst_column_id"
+            + "  INNER JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id=wqr.id"
+            + "  INNER JOIN ranges r ON wqr.range_id=r.id AND wcv.position BETWEEN r.a AND r.b"
+            + " WHERE w.river_id = :river_id"
+            + "  AND w.kind = 0"
+            + "  AND r.river_id = :river_id"
+            + "  AND wcv.position BETWEEN :kmfrom - 1 AND :kmto + 1" // some tolerance for start and end of list
+            + " ORDER BY wcv.position ASC, "
+            + "  wc.position ASC";
+
+
+    /***** CONSTRUCTORS *****/
+
+    private WQBaseTableFinder(final River river, final Calculation problems, final List<Object[]> colnames, final List<Object[]> rows) {
+        this.river = river;
+        this.problems = problems;
+        this.columnNames = new ArrayList<>();
+        for (final Object[] colname : colnames)
+            this.columnNames.add(colname[1].toString());
+        this.kmWs = new TreeMap<>();
+        this.kmQs = new TreeMap<>();
+        final TDoubleArrayList ws = new TDoubleArrayList();
+        final TDoubleArrayList qs = new TDoubleArrayList();
+        double km = Double.NaN;
+        for (int i = 0; i <= rows.size() - 1; i++) {
+            if (ws.isEmpty() || ((double) rows.get(i)[0] <= km + 0.0001)) {
+                if (ws.isEmpty())
+                    km = (double) rows.get(i)[0];
+                ws.add((double) rows.get(i)[2]);
+                qs.add((double) rows.get(i)[3]);
+            }
+            if ((i == rows.size() - 1) || ((double) rows.get(i)[0] > km + 0.0001)) {
+                try {
+                    this.kmWs.put(km, new LinearInterpolator().interpolate(ws.toNativeArray(), qs.toNativeArray()));
+                    this.kmQs.put(km, new LinearInterpolator().interpolate(qs.toNativeArray(), ws.toNativeArray()));
+                }
+                catch (final Exception e) {
+                    if (this.problems != null) {
+                        this.problems.addProblem(km, "wq_base_data.missing");
+                        // Report only once
+                        this.problems = null;
+                    }
+                }
+                ws.clear();
+                qs.clear();
+                km = (double) rows.get(i)[0];
+                ws.add((double) rows.get(i)[2]);
+                qs.add((double) rows.get(i)[3]);
+            }
+        }
+    }
+
+
+    /***** METHODS *****/
+
+    /**
+     * Loads the the W/Q tables of a km range of a river
+     *
+     * @return The W/Q table finder of the river, or null
+     */
+    public static WQBaseTableFinder loadValues(final River river, final double fromKm, final double toKm, final Calculation problems) {
+        final Session session = SessionHolder.HOLDER.get();
+        final SQLQuery colQuery = session.createSQLQuery(SQLCOLUMNS)
+                .addScalar("colindex", StandardBasicTypes.INTEGER)
+                .addScalar("qzone", StandardBasicTypes.STRING);
+        colQuery.setParameter("river_id", river.getId());
+        final List<Object[]> colnames = colQuery.list();
+        if ((colnames == null) || colnames.isEmpty()) {
+            problems.addProblem("wq_table.missing");
+            return null;
+        }
+        final SQLQuery wqQuery = session.createSQLQuery(SQLMAIN)
+                .addScalar("station", StandardBasicTypes.DOUBLE)
+                .addScalar("colindex", StandardBasicTypes.INTEGER)
+                .addScalar("w", StandardBasicTypes.DOUBLE)
+                .addScalar("q", StandardBasicTypes.DOUBLE);
+        wqQuery.setParameter("river_id", river.getId());
+        wqQuery.setParameter("kmfrom", fromKm);
+        wqQuery.setParameter("kmto", toKm);
+        final List<Object[]> rows = wqQuery.list();
+        if ((rows != null) && !rows.isEmpty())
+            return new WQBaseTableFinder(river, problems, colnames, rows);
+        else {
+            problems.addProblem("wq_table.missing");
+            return null;
+        }
+    }
+
+    /**
+     * If this provider may return valid data at all.
+     */
+    public boolean isValid() {
+        return (this.kmWs != null);
+    }
+
+    /**
+     * Discharge for a W
+     *
+     * @param station
+     *            station to find or interpolate
+     * @param w
+     *            W in m+NN or m+NHN
+     * @return Q, or NegInf for w less than all, or PosInf for w greater then all, or NaN in case of exception
+     */
+    public double getDischarge(final double station, final double w) {
+        if (this.kmWs == null)
+            return Double.NaN;
+        final Entry<Double, PolynomialSplineFunction> lowerEntry = this.kmWs.floorEntry(Double.valueOf(station));
+        final Entry<Double, PolynomialSplineFunction> upperEntry = this.kmWs.ceilingEntry(Double.valueOf(station));
+        if ((lowerEntry == null) || (upperEntry == null))
+            return Double.NaN;
+        try {
+            final double lowerQ = lowerEntry.getValue().value(w);
+            final double upperQ = upperEntry.getValue().value(w);
+            return Linear.linear(station, lowerEntry.getKey().doubleValue(), upperEntry.getKey().doubleValue(), lowerQ, upperQ);
+        }
+        catch (@SuppressWarnings("unused") final FunctionEvaluationException e) {
+            // ignore exception because this can/will happen regularly
+            return Double.NaN;
+        }
+    }
+}
\ No newline at end of file