changeset 8917:86395ab8ebc3

SINFO Flowdepth: d50 aggregation by median instead of arithmetic mean
author mschaefer
date Mon, 26 Feb 2018 18:56:05 +0100
parents 37ff7f435912
children 87aa705e1e99
files artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java
diffstat 1 files changed, 33 insertions(+), 131 deletions(-) [+]
line wrap: on
line diff
--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java	Fri Feb 23 09:30:24 2018 +0100
+++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/flowdepth/BedQualityD50KmValueFinder.java	Mon Feb 26 18:56:05 2018 +0100
@@ -10,7 +10,6 @@
 
 package org.dive4elements.river.artifacts.sinfo.flowdepth;
 
-import java.util.Date;
 import java.util.List;
 
 import org.apache.commons.lang.math.DoubleRange;
@@ -22,7 +21,6 @@
 import org.dive4elements.river.artifacts.model.DateRange;
 import org.dive4elements.river.backend.SedDBSessionHolder;
 import org.dive4elements.river.model.River;
-import org.dive4elements.river.utils.DoubleUtil;
 import org.hibernate.SQLQuery;
 import org.hibernate.Session;
 import org.hibernate.type.StandardBasicTypes;
@@ -39,99 +37,6 @@
  */
 public class BedQualityD50KmValueFinder {
 
-    /***** INNER CLASSES *****/
-    
-    /**
-     * A bed measurements aggregate with its d50 characteristic grain diameter 
-     */
-    private class D50Measurement {
-        private double km;
-        public double getKm() {
-            return km;
-        }
-        private Date mindate;
-        public Date getMinDate() {
-            return mindate;
-        }
-        private Date maxdate;
-        public Date getMaxDate() {
-            return maxdate;
-        }
-        private int cnt;
-        public int getCnt() {
-            return cnt;
-        }
-        private double mindepth;
-        public double getMinDepth() {
-            return mindepth;
-        }
-        private double maxdepth;
-        public double getMaxDepth() {
-            return maxdepth;
-        }
-        private double d50;
-        /**
-         * D50 in m
-         */
-        public double getD50() {
-            return d50;
-        }
-        /**
-         * Parameter constructor
-         */
-        public D50Measurement(double km, Date mindate, Date maxdate, int cnt, double mindepth, double maxdepth, double d50mm) {
-            this.km = km;
-            this.mindate = mindate;
-            this.maxdate = maxdate;
-            this.cnt = cnt;
-            this.mindepth = mindepth;
-            this.maxdepth = maxdepth;
-            this.d50 = d50mm / 1000;
-        }
-
-        /**
-         * Query result row constructor
-         */
-        public D50Measurement(Object[] tuple, String[] aliases) {
-            km = 0;
-            mindate = null;
-            maxdate = null;
-            cnt = 0;
-            mindepth = Double.NaN;
-            maxdepth = Double.NaN;
-            d50 = Double.NaN;
-            for (int i = 0; i < tuple.length; ++i) {
-                if (tuple[i] == null)
-                    continue;
-                switch (aliases[i]) {
-                case "km":
-                    km = ((Number) tuple[i]).doubleValue();
-                    break;
-                case "mindate":
-                    mindate = (Date) tuple[i];
-                    break;
-                case "maxdate":
-                    maxdate = (Date) tuple[i];
-                    break;
-                case "cnt":
-                     cnt = ((Number) tuple[i]).intValue();
-                   break;
-                case "mindepth":
-                     mindepth = ((Number) tuple[i]).doubleValue();
-                   break;
-                case "maxdepth":
-                    maxdepth = ((Number) tuple[i]).doubleValue();
-                    break;
-                case "d50":
-                    d50 = ((Number) tuple[i]).doubleValue() / 1000;     // mm to m
-                    break;
-                default:
-                    break;
-                }
-            }
-        }
-    }
-    
     /***** FIELDS *****/
     
     /**
@@ -140,28 +45,26 @@
     private static Logger log = Logger.getLogger(BedQualityD50KmValueFinder.class);
 
     /**
-     * Query that aggregates by km for a km range and a time period all sub layer bed measurements with their d50<br />
+     * Query selecting all sub layer bed measurements with their d50 for a km range and a time period<br />
      * <br />
      * A km may have bed measurements for multiple dates, multiple distances from the river bank, and multiple depth layers.
-     * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth).
-     * Those measurements are then grouped by km, and the D50 aggregated as average value.
+     * The query filters by km range, time period and layer (sub layer: below bed to max. 50 cm depth).<br />
+     * 
+     * If PostgreSQL would support a median aggregate function like Oracle does the aggregation could be placed into this query.
      */
     private static final String SQL_BED_D50_SUBLAYER_MEASUREMENT =
-            "SELECT t.km, MIN(t.datum) AS mindate, MAX(t.datum) AS maxdate, COUNT(*) AS cnt,"
-                + " MIN(p.tiefevon) AS mindepth, MAX(p.tiefebis) AS maxdepth, AVG(a.d50) AS d50"
-                + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid"
-                + "    INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid"
-                + "    INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid"
-                + "    INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid"
-                + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)"
-                + "    AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)"
-                + "    AND (t.datum BETWEEN :fromdate AND :todate)"
-                + " GROUP BY t.km"
-                + " ORDER BY t.km";
-    private static final String[] SQL_BED_D50_SELECT_ALIAS = {"km", "mindate", "maxdate", "cnt", "mindepth", "maxdepth", "d50"};
+    "SELECT t.km, t.datum, p.tiefevon, p.tiefebis, a.d50 AS d50"
+        + " FROM sohltest t INNER JOIN station s ON t.stationid = s.stationid"
+        + "    INNER JOIN gewaesser g ON s.gewaesserid = g.gewaesserid"
+        + "    INNER JOIN sohlprobe p ON t.sohltestid = p.sohltestid"
+        + "    INNER JOIN siebanalyse a ON p.sohlprobeid = a.sohlprobeid"
+        + " WHERE (g.name = :name) AND (s.km BETWEEN :fromkm - 0.0001 AND :tokm + 0.0001)"
+        + "    AND (p.tiefevon > 0.0) AND (p.tiefebis <= 0.5)"
+        + "    AND (t.datum BETWEEN :fromdate AND :todate)"
+        + " ORDER BY t.km ASC, a.d50 ASC";
 
     /**
-     * Real linear interpolator for kms and d50 values
+     * Real linear interpolator for kms and d50 values (m)
      */
     private PolynomialSplineFunction interpolator;
     
@@ -170,7 +73,7 @@
     
     /**
      * Returns the d50 value interpolated according to a km
-     * @return d50 (mm) of the km, or NaN
+     * @return d50 (m) of the km, or NaN
      */
     public double findD50(double km) throws ArgumentOutsideDomainException {
         return interpolator.value(km);
@@ -197,13 +100,11 @@
         log.debug(String.format("loadValues km %.3f - %.3f %tF - %tF", kmRange.getMinimumDouble(), kmRange.getMaximumDouble(), dateRange.getFrom(), dateRange.getTo()));
         Session session = SedDBSessionHolder.HOLDER.get();
         SQLQuery sqlQuery = session.createSQLQuery(SQL_BED_D50_SUBLAYER_MEASUREMENT)
-                .addScalar("km", StandardBasicTypes.DOUBLE)
-                .addScalar("mindate", StandardBasicTypes.DATE)
-                .addScalar("maxdate", StandardBasicTypes.DATE)
-                .addScalar("cnt", StandardBasicTypes.INTEGER)
-                .addScalar("mindepth", StandardBasicTypes.DOUBLE)
-                .addScalar("maxdepth", StandardBasicTypes.DOUBLE)
-                .addScalar("d50", StandardBasicTypes.DOUBLE);
+            .addScalar("km", StandardBasicTypes.DOUBLE)
+            .addScalar("datum", StandardBasicTypes.DATE)
+            .addScalar("tiefevon", StandardBasicTypes.DOUBLE)
+            .addScalar("tiefebis", StandardBasicTypes.DOUBLE)
+            .addScalar("d50", StandardBasicTypes.DOUBLE);
         String seddbRiver = river.nameForSeddb();
         sqlQuery.setString("name", seddbRiver);
         sqlQuery.setDouble("fromkm", kmRange.getMinimumDouble());
@@ -212,24 +113,25 @@
         sqlQuery.setDate("todate", dateRange.getTo());
         @SuppressWarnings("unchecked")
         final List<Object[]> rows = sqlQuery.list();
-        final double[] kms = new double[rows.size()];
-        final double[] values = new double[rows.size()];
-        D50Measurement measurement;
-        int i = -1;
-        for (Object[] row : rows) {
-            measurement = new D50Measurement(row, SQL_BED_D50_SELECT_ALIAS);
-            i++;
-            kms[i] = measurement.getKm();
-            values[i] = measurement.getD50();
-            log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms[i], values[i], measurement.getCnt()));
+        final TDoubleArrayList kms = new TDoubleArrayList();
+        final TDoubleArrayList values = new TDoubleArrayList();
+        final TDoubleArrayList kmd50s = new TDoubleArrayList();
+        for (int i = 0; i <= rows.size() - 1; i++) {
+            kmd50s.add((double) rows.get(i)[4]);
+            if (((i == rows.size() - 1) || !Utils.epsilonEquals((double) rows.get(i)[0], (double) rows.get(i+1)[0], 0.0001))) {
+                int k = kmd50s.size() / 2;
+                values.add(((k + k < kmd50s.size()) ? kmd50s.get(k) : (kmd50s.get(k-1) + kmd50s.get(k)) / 2) / 1000);
+                kms.add((double) rows.get(i)[0]);
+                log.debug(String.format("loadValues km %.3f d50(mm) %.1f count %d", kms.get(kms.size()-1), values.get(values.size()-1), kmd50s.size()));
+                kmd50s.clear();
+            }
         }
         try {
-            interpolator = new LinearInterpolator().interpolate(kms, values);
+            interpolator = new LinearInterpolator().interpolate(kms.toNativeArray(), values.toNativeArray());
             return true;
         } catch (Exception e) {
             interpolator = null;
             return false;
         }
     }
-
 }

http://dive4elements.wald.intevation.org