--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/tkhcalculation/SoilKindKmValueFinder.java	Fri Mar 16 18:08:38 2018 +0100
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/tkhcalculation/SoilKindKmValueFinder.java	Mon Mar 19 09:12:17 2018 +0100
@@ -10,27 +10,55 @@
 
 package org.dive4elements.river.artifacts.sinfo.tkhcalculation;
 
-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.lang.math.DoubleRange;
 import org.apache.commons.math.ArgumentOutsideDomainException;
+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;
 
 /**
  * @author Matthias Schäfer
  */
 final class SoilKindKmValueFinder {
-    // /**
-    // * Private log to use here.
-    // */
+
+    /***** FIELDS *****/
+
     // private static Logger log = Logger.getLogger(SoilKindKmValueFinder.class);
 
-    private final TDoubleArrayList kms = new TDoubleArrayList();
+    /**
+     * Query selecting the bed mobility attribute for a range of stations of a river
+     */
+    private static final String SQL_BED_MOBILITY = "SELECT bmv.station, bmv.moving"
+            + " FROM bed_mobility bm INNER JOIN bed_mobility_values bmv ON bm.id = bmv.bed_mobility_id"
+            + " WHERE (bm.river_id=:river_id) AND (bmv.station BETWEEN (:fromkm-0.0001) AND (:tokm+0.0001))"
+            + " ORDER BY bmv.station ASC";
 
-    private final List<SoilKind> values = new ArrayList<>();
+    private final NavigableMap<Double, SoilKind> kmMobility;
+
+
+    /***** CONSTRUCTORS *****/
+
+    private SoilKindKmValueFinder() {
+        /* only instantiate me via static constructor */
+        this.kmMobility = null;
+    }
+
+    private SoilKindKmValueFinder(final List<Object[]> queryRows) {
+        this.kmMobility = new TreeMap<>();
+        for (int i = 0; i <= queryRows.size() - 1; i++) {
+            this.kmMobility.put(Double.valueOf((double) queryRows.get(i)[0]), (((int) queryRows.get(i)[1]) == 1) ? SoilKind.mobil : SoilKind.starr);
+        }
+    }
+
+    /***** METHODS *****/
 
     /**
      * Loads the range of the river's kms with their soil kind.
@@ -38,43 +66,17 @@
      * @return Whether the load has been successful
      */
     public static SoilKindKmValueFinder loadValues(final River river, final DoubleRange kmRange) {
-
-        final SoilKindKmValueFinder instance = new SoilKindKmValueFinder();
-
-        // FIXME Echte Daten aus der Datenbank abfragen
-        instance.addKmKind(0, SoilKind.starr);
-        instance.addKmKind(15.7, SoilKind.mobil);
-        instance.addKmKind(15.8, SoilKind.mobil);
-        instance.addKmKind(15.9, SoilKind.starr);
-        instance.addKmKind(108.7, SoilKind.mobil);
-        instance.addKmKind(108.8, SoilKind.mobil);
-        instance.addKmKind(108.9, SoilKind.starr);
-        instance.addKmKind(119.1, SoilKind.mobil);
-        instance.addKmKind(119.4, SoilKind.mobil);
-        instance.addKmKind(119.5, SoilKind.starr);
-        instance.addKmKind(128.3, SoilKind.mobil);
-        instance.addKmKind(128.9, SoilKind.mobil);
-        instance.addKmKind(129, SoilKind.starr);
-        instance.addKmKind(133.1, SoilKind.mobil);
-        instance.addKmKind(135.9, SoilKind.mobil);
-        instance.addKmKind(136, SoilKind.starr);
-        instance.addKmKind(136.5, SoilKind.mobil);
-        instance.addKmKind(139.9, SoilKind.mobil);
-        instance.addKmKind(140, SoilKind.starr);
-        instance.addKmKind(140.5, SoilKind.mobil);
-        instance.addKmKind(165, SoilKind.mobil);
-        instance.addKmKind(165.1, SoilKind.starr);
-        instance.addKmKind(165.9, SoilKind.mobil);
-        instance.addKmKind(180.8, SoilKind.mobil);
-        instance.addKmKind(180.9, SoilKind.starr);
-        instance.addKmKind(182, SoilKind.mobil);
-        instance.addKmKind(221.3, SoilKind.mobil);
-
-        return instance;
-    }
-
-    private SoilKindKmValueFinder() {
-        /* only instantiate me via static constructor */
+        final Session session = SessionHolder.HOLDER.get();
+        final SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_MOBILITY).addScalar("station", StandardBasicTypes.DOUBLE)
+                .addScalar("moving", StandardBasicTypes.INTEGER);
+        sqlQuery.setInteger("river_id", river.getId().intValue());
+        sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble());
+        sqlQuery.setDouble("tokm", kmRange.getMaximumDouble());
+        final List<Object[]> rows = sqlQuery.list();
+        if (rows.size() >= 1)
+            return new SoilKindKmValueFinder(rows);
+        else
+            return null;
     }
 
     /***** METHODS *****/
@@ -83,36 +85,25 @@
      * Searches a km with its soil kind
      */
     public SoilKind findSoilKind(final double km) throws ArgumentOutsideDomainException {
-        if ((this.kms == null) || (this.kms.size() == 0))
+        if ((this.kmMobility == null) || this.kmMobility.isEmpty())
             throw new ArgumentOutsideDomainException(km, Double.NaN, Double.NaN);
-
-        // TODO: Voraussetzung für die binäre suche ist, dass nach km sortiert ist.
-        // In diesem Fall könnte man ggf. auch gleich eine bessere Datenklasse benutzen, z.B. eine TreeMap<Double, SoilKind>
-        // (also station -> art), und deren funktionen zum finden verwenden:
-        // final double station = 0.0;
-        // final NavigableMap<Double, SoilKind> data = new TreeMap<>();
-        // data.ceilingEntry(station);
-        // data.floorEntry(station);
-
-        int i = this.kms.binarySearch(km);
-        if (i >= 0) {
-            // Exact km match
-            return this.values.get(i);
-        } else {
-            // Out of range or within km interval
-            if (i < 0)
-                i = -i - 1;
-            if ((i <= 0) || (i >= this.kms.size()))
+        else if (this.kmMobility.containsKey(Double.valueOf(km)))
+            return this.kmMobility.get(Double.valueOf(km));
+        else {
+            // FIXME: Assert minimum distance between neighbouring stations and candidate km?
+            final Entry<Double, SoilKind> streamUp = this.kmMobility.floorEntry(Double.valueOf(km));
+            if (streamUp == null)
                 throw new ArgumentOutsideDomainException(km, Double.NaN, Double.NaN);
-            if (km <= ((this.kms.get(i - 1) + this.kms.get(i)) / 2))
-                return this.values.get(i - 1);
-            else
-                return this.values.get(i);
+            else {
+                // Return the soil kind of the neighbouring station with the shorter distance to the candidate.
+                final Entry<Double, SoilKind> streamDown = this.kmMobility.ceilingEntry(Double.valueOf(km));
+                if (streamDown == null)
+                    return streamUp.getValue();
+                else if ((streamUp.getKey().doubleValue() + streamDown.getKey().doubleValue()) / 2 <= km)
+                    return streamUp.getValue();
+                else
+                    return streamDown.getValue();
+            }
         }
     }
-
-    private void addKmKind(final double km, final SoilKind kind) {
-        this.kms.add(km);
-        this.values.add(kind);
-    }
 }
\ No newline at end of file