Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/sinfo/tkhcalculation/FlowVelocityModelKmValueFinder.java @ 9095:1b24fdbf7fe2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed flow velocity query by distinct clause to avoid duplicates
author mschaefer
date Fri, 25 May 2018 16:03:15 +0200
parents 50cfc90ac5b0
children 9bd4505a20dc
comparison
equal deleted inserted replaced
9094:5488166d32ee 9095:1b24fdbf7fe2
50 /** 50 /**
51 * Query for a range of stations of a river with all their q, main-v and tau values.<br /> 51 * Query for a range of stations of a river with all their q, main-v and tau values.<br />
52 * (Might be several 10000 rows if many stations and large q range) 52 * (Might be several 10000 rows if many stations and large q range)
53 */ 53 */
54 private static final String SQL_SELECT_ALL = // 54 private static final String SQL_SELECT_ALL = //
55 "SELECT fvmv.station AS station, fvmv.q AS q, fvmv.main_channel AS vmain, fvmv.shear_stress AS tau" 55 "SELECT DISTINCT fvmv.station AS station, fvmv.q AS q, fvmv.main_channel AS vmain, fvmv.shear_stress AS tau"
56 + " FROM (discharge_zone dz INNER JOIN flow_velocity_model fvm ON dz.id = fvm.discharge_zone_id)" 56 + " FROM (discharge_zone dz INNER JOIN flow_velocity_model fvm ON dz.id = fvm.discharge_zone_id)"
57 + " INNER JOIN flow_velocity_model_values fvmv ON fvm.id = fvmv.flow_velocity_model_id" 57 + " INNER JOIN flow_velocity_model_values fvmv ON fvm.id = fvmv.flow_velocity_model_id"
58 + " WHERE (dz.river_id = :river_id) AND (fvmv.station BETWEEN :kmfrom - 0.0001 AND :kmto + 0.0001)" 58 + " WHERE (dz.river_id = :river_id) AND (fvmv.station BETWEEN :kmfrom - 0.0001 AND :kmto + 0.0001)"
59 /* + " WHERE (dz.river_id = :river_id) AND (fvmv.q BETWEEN :qmin AND :qmax)" */ 59 /* + " WHERE (dz.river_id = :river_id) AND (fvmv.q BETWEEN :qmin AND :qmax)" */
60 + " ORDER BY fvmv.station ASC, fvmv.q ASC"; 60 + " ORDER BY fvmv.station ASC, fvmv.q ASC";
61 61
62 /** 62 /**
63 * Query for a river's max km below a limit with all its q, main-v and tau values. 63 * Query for a river's max km below a limit with all its q, main-v and tau values.
64 */ 64 */
65 private static final String SQL_SELECT_KMLOWER = // 65 private static final String SQL_SELECT_KMLOWER = //
66 "SELECT fvmv.station AS station, fvmv.q AS q, fvmv.main_channel AS vmain, fvmv.shear_stress AS tau" + " FROM flow_velocity_model_values fvmv" 66 "SELECT DISTINCT fvmv.station AS station, fvmv.q AS q, fvmv.main_channel AS vmain, fvmv.shear_stress AS tau"
67 + " FROM flow_velocity_model_values fvmv"
67 + " INNER JOIN (SELECT MAX(fvmvi.station) AS kmmax" 68 + " INNER JOIN (SELECT MAX(fvmvi.station) AS kmmax"
68 + " FROM(discharge_zone dz INNER JOIN flow_velocity_model fvm ON dz.id = fvm.discharge_zone_id)" 69 + " FROM discharge_zone dz INNER JOIN flow_velocity_model fvm ON dz.id = fvm.discharge_zone_id"
69 + " INNER JOIN flow_velocity_model_values fvmvi ON fvm.id = fvmvi.flow_velocity_model_id" 70 + " INNER JOIN flow_velocity_model_values fvmvi ON fvm.id = fvmvi.flow_velocity_model_id"
70 + " WHERE (dz.river_id = :river_id) AND (fvmvi.station < :kmfrom - 0.0001)) finf ON fvmv.station = finf.kmmax" 71 + " WHERE (dz.river_id = :river_id) AND (fvmvi.station < :kmfrom - 0.0001)) finf ON fvmv.station = finf.kmmax"
71 + " ORDER BY fvmv.q ASC"; 72 + " ORDER BY fvmv.q ASC";
72 73
73 /** 74 /**
74 * Query for a river's min km above a limit with all its q, main-v and tau values. 75 * Query for a river's min km above a limit with all its q, main-v and tau values.
75 */ 76 */
76 private static final String SQL_SELECT_KMUPPER = // 77 private static final String SQL_SELECT_KMUPPER = //
77 "SELECT fvmv.station AS station, fvmv.q AS q, fvmv.main_channel AS vmain, fvmv.shear_stress AS tau" + " FROM flow_velocity_model_values fvmv" 78 "SELECT DISTINCT fvmv.station AS station, fvmv.q AS q, fvmv.main_channel AS vmain, fvmv.shear_stress AS tau"
79 + " FROM flow_velocity_model_values fvmv"
78 + " INNER JOIN (SELECT MIN(fvmvi.station) AS kmmin" 80 + " INNER JOIN (SELECT MIN(fvmvi.station) AS kmmin"
79 + " FROM(discharge_zone dz INNER JOIN flow_velocity_model fvm ON dz.id = fvm.discharge_zone_id)" 81 + " FROM discharge_zone dz INNER JOIN flow_velocity_model fvm ON dz.id = fvm.discharge_zone_id"
80 + " INNER JOIN flow_velocity_model_values fvmvi ON fvm.id = fvmvi.flow_velocity_model_id" 82 + " INNER JOIN flow_velocity_model_values fvmvi ON fvm.id = fvmvi.flow_velocity_model_id"
81 + " WHERE (dz.river_id = :river_id) AND (fvmvi.station > :kmto + 0.0001)) fsup ON fvmv.station = fsup.kmmin" 83 + " WHERE (dz.river_id = :river_id) AND (fvmvi.station > :kmto + 0.0001)) fsup ON fvmv.station = fsup.kmmin"
82 + " ORDER BY fvmv.q ASC"; 84 + " ORDER BY fvmv.q ASC";
83 85
84 // /** 86 // /**
http://dive4elements.wald.intevation.org