Mercurial > dive4elements > river

comparison backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionAggregateValue.java @ 9160:38e8febd11b6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added methods to select collision values and collision counts
author mschaefer
date Tue, 19 Jun 2018 14:24:41 +0200
parents 8d4e7e08dbc0
children
comparison
equal deleted inserted replaced
9159:64e56a51db3f 9160:38e8febd11b6
14 import java.util.ArrayList; 14 import java.util.ArrayList;
15 import java.util.Date; 15 import java.util.Date;
16 import java.util.List; 16 import java.util.List;
17 17
18 import org.dive4elements.river.backend.SessionHolder; 18 import org.dive4elements.river.backend.SessionHolder;
19 import org.dive4elements.river.backend.utils.DateUtil;
20 import org.dive4elements.river.model.River;
19 import org.hibernate.SQLQuery; 21 import org.hibernate.SQLQuery;
20 import org.hibernate.Session; 22 import org.hibernate.Session;
21 import org.hibernate.type.StandardBasicTypes; 23 import org.hibernate.type.StandardBasicTypes;
22 24
23 25
94 public Double getGaugeW() { 96 public Double getGaugeW() {
95 return this.gaugeW; 97 return this.gaugeW;
96 } 98 }
97 99
98 /** 100 /**
99 * Selects the collision aggregate values of a data series in a km range from the database 101 * Selects from the database the collision aggregate values of a data series in a km range
100 */ 102 */
101 public static List<CollisionAggregateValue> getValuesByKm(final Collision parent, final double kmLo, final double kmHi) { 103 public static List<CollisionAggregateValue> getValuesByKm(final Collision parent, final double kmLo, final double kmHi) {
102 final Session session = SessionHolder.HOLDER.get(); 104 final Session session = SessionHolder.HOLDER.get();
103 final SQLQuery query = session.createSQLQuery("SELECT FLOOR(station+0.4999) AS station, COUNT(*) AS count," 105 final SQLQuery query = session.createSQLQuery("SELECT FLOOR(station+0.4999) AS station, COUNT(*) AS count,"
104 + " MIN(event_date) AS date_min, MAX(event_date) AS date_max," 106 + " MIN(event_date) AS date_min, MAX(event_date) AS date_max,"
105 + " AVG(gauge_w) AS gauge_w" 107 + " AVG(gauge_w) AS gauge_w"
106 + " FROM collision_values v INNER JOIN collision s ON v.collision_id=s.id" 108 + " FROM collision_values v INNER JOIN collision s ON v.collision_id=s.id"
107 + " WHERE (collision_id=:parent)" 109 + " WHERE (collision_id=:parent)"
108 + " AND (station BETWEEN (:kmLo - 0.0001) AND (:kmHi + 0.0001))" 110 + " AND (station BETWEEN (:kmLo - 0.0001) AND (:kmHi + 0.0001))"
109 + " GROUP BY FLOOR(station+0.4999)").addScalar("station", StandardBasicTypes.DOUBLE) 111 + " GROUP BY FLOOR(station+0.4999)"
110 .addScalar("count", StandardBasicTypes.INTEGER).addScalar("date_min", StandardBasicTypes.DATE) 112 + " ORDER BY FLOOR(station+0.4999)")
111 .addScalar("date_max", StandardBasicTypes.DATE).addScalar("gauge_w", StandardBasicTypes.DOUBLE); 113 .addScalar("station", StandardBasicTypes.DOUBLE).addScalar("count", StandardBasicTypes.INTEGER)
114 .addScalar("date_min", StandardBasicTypes.DATE).addScalar("date_max", StandardBasicTypes.DATE)
115 .addScalar("gauge_w", StandardBasicTypes.DOUBLE);
112 query.setInteger("parent", parent.getId()); 116 query.setInteger("parent", parent.getId());
113 query.setDouble("kmLo", new Double(kmLo)); 117 query.setDouble("kmLo", new Double(kmLo));
114 query.setDouble("kmHi", new Double(kmHi)); 118 query.setDouble("kmHi", new Double(kmHi));
115 final List<CollisionAggregateValue> values = new ArrayList(); 119 final List<CollisionAggregateValue> values = new ArrayList();
116 final List<Object[]> rows = query.list(); 120 final List<Object[]> rows = query.list();
120 (int) rows.get(i)[1], (double) rows.get(i)[4])); 124 (int) rows.get(i)[1], (double) rows.get(i)[4]));
121 } 125 }
122 } 126 }
123 return values; 127 return values;
124 } 128 }
129
130 /**
131 * Selects from the database the collision aggregate values of a km range of a river and a range of years
132 */
133 public static List<CollisionAggregateValue> getValuesByKm(final River river, final double kmLo, final double kmHi, final int fromYear, final int toYear) {
134 final Session session = SessionHolder.HOLDER.get();
135 final SQLQuery query = session.createSQLQuery("SELECT FLOOR(station+0.4999) AS station, COUNT(*) AS count,"
136 + " MIN(event_date) AS date_min, MAX(event_date) AS date_max,"
137 + " AVG(gauge_w) AS gauge_w"
138 + " FROM collision_values v INNER JOIN collision s ON v.collision_id=s.id"
139 + " WHERE (s.river_id=:river_id)"
140 + " AND (station BETWEEN (:kmLo - 0.0001) AND (:kmHi + 0.0001))"
141 + " AND (event_date BETWEEN :fromDate AND :toDate)"
142 + " GROUP BY FLOOR(station+0.4999)"
143 + " ORDER BY FLOOR(station+0.4999)")
144 .addScalar("station", StandardBasicTypes.DOUBLE).addScalar("count", StandardBasicTypes.INTEGER)
145 .addScalar("date_min", StandardBasicTypes.DATE).addScalar("date_max", StandardBasicTypes.DATE)
146 .addScalar("gauge_w", StandardBasicTypes.DOUBLE);
147 query.setInteger("river_id", river.getId());
148 query.setDouble("kmLo", new Double(kmLo));
149 query.setDouble("kmHi", new Double(kmHi));
150 query.setDate("fromDate", DateUtil.getStartDateFromYear(fromYear));
151 query.setDate("toDate", DateUtil.getEndDateFromYear(toYear));
152 final List<CollisionAggregateValue> values = new ArrayList();
153 final List<Object[]> rows = query.list();
154 if (rows != null) {
155 for (int i = 0; i <= rows.size() - 1; i++) {
156 values.add(new CollisionAggregateValue(null, (double) rows.get(i)[0], (Date) rows.get(i)[2], (Date) rows.get(i)[3],
157 (int) rows.get(i)[1], (double) rows.get(i)[4]));
158 }
159 }
160 return values;
161 }
125 } 162 }
http://dive4elements.wald.intevation.org