Mercurial > dive4elements > river

comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/HYKFactory.java @ 2419:98a350bb91a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed broken HYKs in crosssection charts for river Mosel. flys-artifacts/trunk@4053 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Thu, 16 Feb 2012 06:47:46 +0000
parents 8c9c40459d8f
children ac528b883b47
comparison
equal deleted inserted replaced
2418:899ca89f497e 2419:98a350bb91a9
12 12
13 import org.apache.log4j.Logger; 13 import org.apache.log4j.Logger;
14 14
15 import org.hibernate.Query; 15 import org.hibernate.Query;
16 import org.hibernate.Session; 16 import org.hibernate.Session;
17 import org.hibernate.SQLQuery;
18 import org.hibernate.type.StandardBasicTypes;
17 19
18 import de.intevation.flys.model.HYK; 20 import de.intevation.flys.model.HYK;
19 import de.intevation.flys.model.HYKFormation; 21 import de.intevation.flys.model.HYKFormation;
20 import de.intevation.flys.model.HYKFlowZone; 22 import de.intevation.flys.model.HYKFlowZone;
21 23
92 * @param hykid ID of the 'main' HYK to query. 94 * @param hykid ID of the 'main' HYK to query.
93 * @param km for which to get the hyk-zones. 95 * @param km for which to get the hyk-zones.
94 * @return according zones. 96 * @return according zones.
95 */ 97 */
96 public static List<Zone> getZonesUncached(int hykid, double km) { 98 public static List<Zone> getZonesUncached(int hykid, double km) {
97
98 if (logger.isDebugEnabled()) { 99 if (logger.isDebugEnabled()) {
99 logger.debug("HYKFactory.getZoneUncached " + hykid + " km " + km); 100 logger.debug("HYKFactory.getZoneUncached " + hykid + " km " + km);
100 } 101 }
101 102
102 Session session = SessionHolder.HOLDER.get(); 103 Session session = SessionHolder.HOLDER.get();
105 // OPTIMIZE: 1) query kmUp directly 2) merge queries. 106 // OPTIMIZE: 1) query kmUp directly 2) merge queries.
106 Query rQuery = session.createQuery("from HYK where id = :hykid"); 107 Query rQuery = session.createQuery("from HYK where id = :hykid");
107 rQuery.setParameter("hykid", hykid); 108 rQuery.setParameter("hykid", hykid);
108 rQuery.setMaxResults(1); 109 rQuery.setMaxResults(1);
109 HYK hyk = (HYK) rQuery.uniqueResult(); 110 HYK hyk = (HYK) rQuery.uniqueResult();
110 BigDecimal flowDir = (hyk.getRiver().getKmUp()) ? new BigDecimal(1) : new BigDecimal(-1); 111
111 112 double flowDir = hyk.getRiver().getKmUp() ? 1 : -1;
112 // Query HYKFormations in range. 113
113 Query query = session.createQuery( 114 List<HYKFormation> forms = getHYKFormations(hykid, km, flowDir);
114 "from HYKFormation where entry.HYK.id = :hykid " + 115 List<Zone> zones = new ArrayList<Zone>();
115 " and :km between entry.km and " +
116 " entry.km + :flowDir * "+
117 " cast(distance_vl/1000.0 + 0.001 as big_decimal)" +
118 " order by entry.km asc");
119 query.setParameter("hykid", hykid);
120 query.setParameter("km", new BigDecimal(km));
121 query.setParameter("flowDir", flowDir);
122 query.setMaxResults(1);
123
124 List<HYKFormation> forms = query.list();
125
126 List<Zone> zones = new ArrayList<Zone>();
127 116
128 // Take the first one. 117 // Take the first one.
129 if (forms.size() >= 1) { 118 if (forms.size() >= 1) {
130 HYKFormation form = forms.get(0); 119 HYKFormation form = forms.get(0);
131 // Create respective zones. 120 // Create respective zones.
138 } 127 }
139 128
140 return zones; 129 return zones;
141 } 130 }
142 131
132
133 protected static List<HYKFormation> getHYKFormations(
134 int hykid,
135 double km,
136 double flowDir
137 ) {
138 Session session = SessionHolder.HOLDER.get();
139
140 String SQL = "SELECT " +
141 " f.id AS FID, " +
142 " f.distance_vl AS DIST, " +
143 " e.hyk_id AS HID, " +
144 " e.km AS KM " +
145 " FROM hyk_formations f INNER JOIN hyk_entries e " +
146 " ON e.id = f.hyk_entry_id " +
147 " WHERE e.hyk_id = :hykid " +
148 " AND :km between " +
149 " LEAST(e.km, e.km + :flowDir*(f.distance_vl/1000.0+0.001)) " +
150 " AND " +
151 " GREATEST(e.km, e.km + :flowDir*(f.distance_vl/1000.0+0.001))";
152
153 SQLQuery sqlQuery = session.createSQLQuery(SQL)
154 .addScalar("FID", StandardBasicTypes.INTEGER)
155 .addScalar("DIST", StandardBasicTypes.DOUBLE)
156 .addScalar("HID", StandardBasicTypes.INTEGER)
157 .addScalar("KM", StandardBasicTypes.DOUBLE);
158
159 sqlQuery.setInteger("hykid", hykid);
160 sqlQuery.setDouble("flowDir", flowDir);
161 sqlQuery.setDouble("km", km);
162
163 logger.debug("HYK SQL: " + sqlQuery.getQueryString());
164
165 List<Object[]> results = sqlQuery.list();
166
167 logger.debug("Found " + results.size() + " HYKFormation IDs in DB.");
168
169 if (results == null || results.isEmpty()) {
170 logger.debug("No HYK found for ID " + hykid + " at km " + km);
171 return new ArrayList<HYKFormation>();
172 }
173
174 Object[] resultSet = results.get(0);
175 Integer hykFormationId = (Integer) resultSet[0];
176
177 Query query = session.createQuery("from HYKFormation where id = :id");
178 query.setParameter("id", hykFormationId);
179 query.setMaxResults(1);
180
181 return query.list();
182 }
183
184
143 /** Labelled section. */ 185 /** Labelled section. */
144 public static class Zone implements Serializable { 186 public static class Zone implements Serializable {
145 /** Lower end of segment. */ 187 /** Lower end of segment. */
146 protected double from; 188 protected double from;
147 /** Upper end of segment. */ 189 /** Upper end of segment. */
http://dive4elements.wald.intevation.org