Mercurial > dive4elements > river
comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/HYKFactory.java @ 2419:98a350bb91a9
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. */ |