Mercurial > dive4elements > river
comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/FixingsOverview.java @ 2561:b3f6d49cdc80
Service to generate the data needed to build the "Fixerungen pro Fluss Uebersicht"
flys-artifacts/trunk@4087 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 19 Feb 2012 17:14:39 +0000 |
parents | |
children | ba35dfb7c09a |
comparison
equal
deleted
inserted
replaced
2560:0d8c97374dc9 | 2561:b3f6d49cdc80 |
---|---|
1 package de.intevation.flys.artifacts.model; | |
2 | |
3 import java.io.Serializable; | |
4 | |
5 import java.text.SimpleDateFormat; | |
6 | |
7 import java.util.ArrayList; | |
8 import java.util.Collections; | |
9 import java.util.Comparator; | |
10 import java.util.Date; | |
11 import java.util.HashMap; | |
12 import java.util.List; | |
13 import java.util.Map; | |
14 | |
15 import org.apache.log4j.Logger; | |
16 | |
17 import org.hibernate.SQLQuery; | |
18 import org.hibernate.Session; | |
19 | |
20 import org.hibernate.type.StandardBasicTypes; | |
21 | |
22 import org.w3c.dom.Document; | |
23 import org.w3c.dom.Element; | |
24 | |
25 public class FixingsOverview | |
26 implements Serializable | |
27 { | |
28 private static Logger log = Logger.getLogger(FixingsOverview.class); | |
29 | |
30 public static final double EPSILON = 1e-5; | |
31 public static final double EPSILON2 = 1e-1; | |
32 | |
33 public static final String SQL_RIVER_ID = | |
34 "SELECT" + | |
35 " id AS river_id," + | |
36 " km_up " + | |
37 "FROM rivers " + | |
38 "WHERE" + | |
39 " name = :name"; | |
40 | |
41 public static final String SQL_RIVER_EXTENT = | |
42 "SELECT" + | |
43 " MIN(wcv.position) AS start_km," + | |
44 " MAX(wcv.position) AS end_km " + | |
45 "FROM wst_column_values wcv" + | |
46 " JOIN wst_columns wc ON wcv.wst_column_id = wc.id " + | |
47 " JOIN wsts w ON wc.wst_id = w.id " + | |
48 "WHERE" + | |
49 " w.kind = 0 AND w.river_id = :river_id"; | |
50 | |
51 public static final String SQL_GAUGES = | |
52 "SELECT" + | |
53 " g.id AS gauge_id," + | |
54 " r.a AS a," + | |
55 " r.b AS b " + | |
56 "FROM gauges g" + | |
57 " JOIN ranges r ON g.range_id = r.id " + | |
58 "WHERE" + | |
59 " g.river_id = :river_id " + | |
60 "ORDER BY r.a"; | |
61 | |
62 public static final String SQL_DISCHARGE_SECTORS = | |
63 "SELECT" + | |
64 " g.id AS gauge_id," + | |
65 " nmv.name AS name," + | |
66 " CAST(mv.value AS NUMERIC(38,2)) AS value " + | |
67 "FROM gauges g" + | |
68 " JOIN main_values mv ON g.id = mv.gauge_id" + | |
69 " JOIN named_main_values nmv ON nmv.id = mv.named_value_id" + | |
70 " JOIN main_value_types mvt ON nmv.type_id = mvt.id " + | |
71 "WHERE" + | |
72 " mvt.name = 'Q' AND (" + | |
73 " nmv.name = 'MNQ' OR" + | |
74 " nmv.name LIKE 'MNQ(%' OR" + | |
75 " nmv.name = 'MQ' OR" + | |
76 " nmv.name LIKE 'MQ(%' OR" + | |
77 " nmv.name = 'MHQ' OR" + | |
78 " nmv.name LIKE 'MHQ(%' OR" + | |
79 " nmv.name = 'HQ5' OR" + | |
80 " nmv.name LIKE 'HQ5(%') AND" + | |
81 " g.river_id = :river_id " + | |
82 "ORDER BY" + | |
83 " g.id"; | |
84 | |
85 public static final String SQL_FIXINGS = | |
86 "SELECT" + | |
87 " id AS wst_id," + | |
88 " description " + | |
89 "FROM wsts " + | |
90 "WHERE" + | |
91 " river_id = :river_id AND kind = 2"; | |
92 | |
93 public static final String SQL_FIXING_COLUMNS = | |
94 "SELECT" + | |
95 " wc.id AS wst_column_id," + | |
96 " ti.start_time AS start_time," + | |
97 " wc.name AS name " + | |
98 "FROM wst_columns wc" + | |
99 " JOIN time_intervals ti ON wc.time_interval_id = ti.id " + | |
100 "WHERE" + | |
101 " wc.wst_id = :wst_id " + | |
102 "ORDER BY position"; | |
103 | |
104 public static final String SQL_FIXING_COLUMN_Q_RANGES = | |
105 "SELECT" + | |
106 " wqr.q AS q," + | |
107 " r.a AS start_km," + | |
108 " r.b AS stop_km " + | |
109 "FROM wst_column_q_ranges wcqr" + | |
110 " JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id" + | |
111 " JOIN ranges r ON wqr.range_id = r.id " + | |
112 "WHERE" + | |
113 " wcqr.wst_column_id = :column_id " + | |
114 "ORDER BY r.a"; | |
115 | |
116 public static final String SQL_FIXING_COLUMN_KM_RANGE = | |
117 "SELECT" + | |
118 " MIN(position) AS start_km," + | |
119 " MAX(position) AS stop_km " + | |
120 "FROM" + | |
121 " wst_column_values " + | |
122 "WHERE " + | |
123 " wst_column_id = :column_id"; | |
124 | |
125 public static class Range implements Serializable { | |
126 | |
127 protected double start; | |
128 protected double end; | |
129 | |
130 public Range() { | |
131 } | |
132 | |
133 public Range(double start, double end) { | |
134 this.start = start; | |
135 this.end = end; | |
136 } | |
137 | |
138 public boolean disjoint(Range other) { | |
139 return start > other.end || other.start > end; | |
140 } | |
141 | |
142 public boolean clip(Range other) { | |
143 if (disjoint(other)) return false; | |
144 | |
145 if (other.start > start) start = other.start; | |
146 if (other.end < end ) end = other.end; | |
147 | |
148 return true; | |
149 } | |
150 | |
151 public boolean inside(double x) { | |
152 return x > start-EPSILON && x < end+EPSILON; | |
153 } | |
154 } // class Range | |
155 | |
156 public static class GaugeRange extends Range { | |
157 | |
158 private static final class Sector implements Serializable { | |
159 | |
160 int sector; | |
161 double value; | |
162 | |
163 Sector(int sector, double value) { | |
164 this.sector = sector; | |
165 this.value = value; | |
166 } | |
167 | |
168 } // class Sector | |
169 | |
170 protected int gaugeId; | |
171 | |
172 protected Map<String, Double> mainValues; | |
173 protected List<Sector> sectors; | |
174 | |
175 public GaugeRange() { | |
176 } | |
177 | |
178 public GaugeRange(double start, double end, int gaugeId) { | |
179 super(start, end); | |
180 this.gaugeId = gaugeId; | |
181 mainValues = new HashMap<String, Double>(); | |
182 sectors = new ArrayList<Sector>(3); | |
183 } | |
184 | |
185 public void addMainValue(String label, Double value) { | |
186 int idx = label.indexOf('('); | |
187 if (idx >= 0) { | |
188 label = label.substring(0, idx); | |
189 } | |
190 mainValues.put(label, value); | |
191 } | |
192 | |
193 protected Double getMainValue(String label) { | |
194 Double v = mainValues.get(label); | |
195 if (v == null) { | |
196 log.warn("Missing main value '" | |
197 + label + "' for gauge " + gaugeId); | |
198 } | |
199 return v; | |
200 } | |
201 | |
202 public void buildClasses() { | |
203 Double mnq = getMainValue("MNQ"); | |
204 Double mq = getMainValue("MQ"); | |
205 Double mhq = getMainValue("MHQ"); | |
206 Double hq5 = getMainValue("HQ5"); | |
207 | |
208 Double [][] pairs = { | |
209 { mnq, mq }, | |
210 { mq, mhq }, | |
211 { hq5, hq5 } }; | |
212 | |
213 for (int c = 0; c < pairs.length; ++c) { | |
214 Double [] pair = pairs[c]; | |
215 if (pair[0] != null && pair[1] != null) { | |
216 double value = 0.5*(pair[0] + pair[1]); | |
217 sectors.add(new Sector(c, value)); | |
218 } | |
219 } | |
220 } | |
221 | |
222 public int classify(double value) { | |
223 for (Sector sector: sectors) { | |
224 if (value < sector.value) { | |
225 return sector.sector; | |
226 } | |
227 } | |
228 return sectors.size(); | |
229 } | |
230 } // class GaugeRange | |
231 | |
232 public static class QRange extends Range { | |
233 | |
234 protected double q; | |
235 | |
236 public QRange() { | |
237 } | |
238 | |
239 public QRange(double start, double end, double q) { | |
240 super(start, end); | |
241 this.q = q; | |
242 } | |
243 } // class QRange | |
244 | |
245 public static class SectorRange extends Range { | |
246 | |
247 protected int sector; | |
248 | |
249 public SectorRange() { | |
250 } | |
251 | |
252 public SectorRange(double start, double end, int sector) { | |
253 super(start, end); | |
254 this.sector = sector; | |
255 } | |
256 | |
257 public boolean enlarge(SectorRange other) { | |
258 if (sector == other.sector | |
259 && Math.abs(end-other.start) < EPSILON2) { | |
260 end = other.end; | |
261 return true; | |
262 } | |
263 return false; | |
264 } | |
265 } // class SectorRange | |
266 | |
267 public static class Fixing implements Serializable { | |
268 | |
269 public static final Comparator<Column> DATE_CMP = | |
270 new Comparator<Column>() { | |
271 @Override | |
272 public int compare(Column a, Column b) { | |
273 return a.startTime.compareTo(b.startTime); | |
274 } | |
275 }; | |
276 | |
277 public class Column extends Range { | |
278 | |
279 protected int columnId; | |
280 protected Date startTime; | |
281 protected String name; | |
282 | |
283 protected List<QRange> qRanges; | |
284 | |
285 public Column() { | |
286 } | |
287 | |
288 public Column(int columnId, Date startTime, String name) { | |
289 this.columnId = columnId; | |
290 this.startTime = startTime; | |
291 this.name = name; | |
292 | |
293 qRanges = new ArrayList<QRange>(); | |
294 } | |
295 | |
296 public Fixing getFixing() { | |
297 return Fixing.this; | |
298 } | |
299 | |
300 public List<SectorRange> classify(GaugeRange gauge) { | |
301 | |
302 List<SectorRange> sectors = new ArrayList<SectorRange>(); | |
303 | |
304 for (QRange qRange: qRanges) { | |
305 SectorRange sector = new SectorRange( | |
306 qRange.start, qRange.end, | |
307 gauge.classify(qRange.q)); | |
308 if (sectors.isEmpty() | |
309 || !sectors.get(sectors.size()-1).enlarge(sector)) { | |
310 sectors.add(sector); | |
311 } | |
312 } | |
313 | |
314 return sectors; | |
315 } | |
316 | |
317 public void loadKmRange(SQLQuery query) { | |
318 query.setInteger("column_id", columnId); | |
319 | |
320 List<Object []> kms = query.list(); | |
321 | |
322 if (kms.isEmpty()) { | |
323 log.warn("No km range for column " + columnId + "."); | |
324 } | |
325 else { | |
326 Object [] obj = kms.get(0); | |
327 start = (Double)obj[0]; | |
328 end = (Double)obj[1]; | |
329 } | |
330 } | |
331 | |
332 public void loadQRanges(SQLQuery query) { | |
333 query.setInteger("column_id", columnId); | |
334 List<Object []> list = query.list(); | |
335 | |
336 for (Object [] row: list) { | |
337 double q = (Double)row[0]; | |
338 double start = (Double)row[1]; | |
339 double end = (Double)row[2]; | |
340 QRange qRange = new QRange(start, end, q); | |
341 if (qRange.clip(this)) { | |
342 qRanges.add(qRange); | |
343 } | |
344 } | |
345 } | |
346 } // class Column | |
347 | |
348 protected int wstId; | |
349 protected String description; | |
350 protected List<Column> columns; | |
351 | |
352 public Fixing() { | |
353 } | |
354 | |
355 public Fixing(int wstId, String description) { | |
356 this.wstId = wstId; | |
357 this.description = description; | |
358 columns = new ArrayList<Column>(); | |
359 } | |
360 | |
361 public void loadColumns(SQLQuery query) { | |
362 query.setInteger("wst_id", wstId); | |
363 List<Object []> list = query.list(); | |
364 for (Object [] row: list) { | |
365 int columnId = (Integer)row[0]; | |
366 Date startTime = (Date) row[1]; | |
367 String name = (String) row[2]; | |
368 columns.add(new Column(columnId, startTime, name)); | |
369 } | |
370 } | |
371 | |
372 public void loadColumnsKmRange(SQLQuery query) { | |
373 for (Column column: columns) { | |
374 column.loadKmRange(query); | |
375 } | |
376 } | |
377 | |
378 public void loadColumnsQRanges(SQLQuery query) { | |
379 for (Column column: columns) { | |
380 column.loadQRanges(query); | |
381 } | |
382 } | |
383 | |
384 public void addAllColumns(List<Column> allColumns) { | |
385 allColumns.addAll(columns); | |
386 } | |
387 } // class Fixing | |
388 | |
389 | |
390 protected String riverName; | |
391 protected int riverId; | |
392 protected boolean isKmUp; | |
393 protected List<GaugeRange> gauges; | |
394 protected List<Fixing> fixings; | |
395 protected Range extent; | |
396 | |
397 public FixingsOverview() { | |
398 gauges = new ArrayList<GaugeRange>(); | |
399 fixings = new ArrayList<Fixing>(); | |
400 } | |
401 | |
402 public FixingsOverview(String riverName) { | |
403 this(); | |
404 this.riverName = riverName; | |
405 } | |
406 | |
407 protected boolean loadRiver(Session session) { | |
408 SQLQuery query = session.createSQLQuery(SQL_RIVER_ID) | |
409 .addScalar("river_id", StandardBasicTypes.INTEGER) | |
410 .addScalar("km_up", StandardBasicTypes.BOOLEAN); | |
411 | |
412 query.setString("name", riverName); | |
413 | |
414 List<Object []> list = query.list(); | |
415 | |
416 if (list.isEmpty()) { | |
417 log.warn("No river '" + riverName + "' found."); | |
418 return false; | |
419 } | |
420 | |
421 Object [] row = list.get(0); | |
422 | |
423 riverId = (Integer)row[0]; | |
424 isKmUp = (Boolean)row[1]; | |
425 | |
426 return true; | |
427 } | |
428 | |
429 protected boolean loadRiverExtent(Session session) { | |
430 SQLQuery query = session.createSQLQuery(SQL_RIVER_EXTENT) | |
431 .addScalar("start_km", StandardBasicTypes.DOUBLE) | |
432 .addScalar("end_km", StandardBasicTypes.DOUBLE); | |
433 | |
434 query.setInteger("river_id", riverId); | |
435 | |
436 List<Object []> list = query.list(); | |
437 | |
438 if (list.isEmpty()) { | |
439 log.warn("River " + riverId + " has no extent."); | |
440 return false; | |
441 } | |
442 | |
443 Object [] row = list.get(0); | |
444 extent = new Range((Double)row[0], (Double)row[1]); | |
445 | |
446 return true; | |
447 } | |
448 | |
449 | |
450 protected boolean loadGauges(Session session) { | |
451 SQLQuery query = session.createSQLQuery(SQL_GAUGES) | |
452 .addScalar("gauge_id", StandardBasicTypes.INTEGER) | |
453 .addScalar("a", StandardBasicTypes.DOUBLE) | |
454 .addScalar("b", StandardBasicTypes.DOUBLE); | |
455 | |
456 query.setInteger("river_id", riverId); | |
457 | |
458 List<Object []> list = query.list(); | |
459 | |
460 if (list.isEmpty()) { | |
461 log.warn("River " + riverId + " has no gauges."); | |
462 return false; | |
463 } | |
464 | |
465 for (Object [] row: list) { | |
466 int gaugeId = (Integer)row[0]; | |
467 double start = (Double) row[1]; | |
468 double end = (Double) row[2]; | |
469 GaugeRange gauge = new GaugeRange(start, end, gaugeId); | |
470 gauges.add(gauge); | |
471 } | |
472 | |
473 return true; | |
474 } | |
475 | |
476 protected GaugeRange findGaugeById(int gaugeId) { | |
477 for (GaugeRange gauge: gauges) { | |
478 if (gauge.gaugeId == gaugeId) { | |
479 return gauge; | |
480 } | |
481 } | |
482 return null; | |
483 } | |
484 | |
485 protected GaugeRange findGaugeByKm(double km) { | |
486 for (GaugeRange gauge: gauges) { | |
487 if (gauge.inside(km)) { | |
488 return gauge; | |
489 } | |
490 } | |
491 return null; | |
492 } | |
493 | |
494 protected boolean loadDischargeSectors(Session session) { | |
495 | |
496 SQLQuery query = session.createSQLQuery(SQL_DISCHARGE_SECTORS) | |
497 .addScalar("gauge_id", StandardBasicTypes.INTEGER) | |
498 .addScalar("name", StandardBasicTypes.STRING) | |
499 .addScalar("value", StandardBasicTypes.DOUBLE); | |
500 | |
501 query.setInteger("river_id", riverId); | |
502 | |
503 List<Object []> list = query.list(); | |
504 | |
505 if (list.isEmpty()) { | |
506 log.warn("River " + riverId + " has no discharge sectors."); | |
507 return false; | |
508 } | |
509 | |
510 GaugeRange gauge = null; | |
511 | |
512 for (Object [] row: list) { | |
513 int gaugeId = (Integer)row[0]; | |
514 String label = (String) row[1]; | |
515 Double value = (Double) row[2]; | |
516 | |
517 if (gauge == null || gauge.gaugeId != gaugeId) { | |
518 if ((gauge = findGaugeById(gaugeId)) == null) { | |
519 log.warn("Cannot find gauge for id " + gaugeId + "."); | |
520 continue; | |
521 } | |
522 } | |
523 | |
524 gauge.addMainValue(label, value); | |
525 } | |
526 | |
527 for (GaugeRange g: gauges) { | |
528 g.buildClasses(); | |
529 } | |
530 | |
531 return true; | |
532 } | |
533 | |
534 protected void loadFixings(Session session) { | |
535 SQLQuery query = session.createSQLQuery(SQL_FIXINGS) | |
536 .addScalar("wst_id", StandardBasicTypes.INTEGER) | |
537 .addScalar("description", StandardBasicTypes.STRING); | |
538 | |
539 query.setInteger("river_id", riverId); | |
540 | |
541 List<Object []> list = query.list(); | |
542 | |
543 if (list.isEmpty()) { | |
544 log.warn("River " + riverId + " has no fixings."); | |
545 // Its pretty fine to have no fixings. | |
546 } | |
547 | |
548 for (Object [] row: list) { | |
549 int wstId = (Integer)row[0]; | |
550 String description = (String) row[1]; | |
551 Fixing fixing = new Fixing(wstId, description); | |
552 fixings.add(fixing); | |
553 } | |
554 } | |
555 | |
556 protected void loadFixingsColumns(Session session) { | |
557 SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMNS) | |
558 .addScalar("wst_column_id", StandardBasicTypes.INTEGER) | |
559 .addScalar("start_time", StandardBasicTypes.DATE) | |
560 .addScalar("name", StandardBasicTypes.STRING); | |
561 | |
562 for (Fixing fixing: fixings) { | |
563 fixing.loadColumns(query); | |
564 } | |
565 } | |
566 | |
567 protected void loadFixingsColumnsKmRange(Session session) { | |
568 SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMN_KM_RANGE) | |
569 .addScalar("start_km", StandardBasicTypes.DOUBLE) | |
570 .addScalar("stop_km", StandardBasicTypes.DOUBLE); | |
571 | |
572 for (Fixing fixing: fixings) { | |
573 fixing.loadColumnsKmRange(query); | |
574 } | |
575 } | |
576 | |
577 protected void loadFixingsColumnsQRanges(Session session) { | |
578 SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMN_Q_RANGES) | |
579 .addScalar("q", StandardBasicTypes.DOUBLE) | |
580 .addScalar("start_km", StandardBasicTypes.DOUBLE) | |
581 .addScalar("stop_km", StandardBasicTypes.DOUBLE); | |
582 | |
583 for (Fixing fixing: fixings) { | |
584 fixing.loadColumnsQRanges(query); | |
585 } | |
586 } | |
587 | |
588 public boolean load(Session session) { | |
589 | |
590 if (!loadRiver(session)) { | |
591 return false; | |
592 } | |
593 | |
594 if (!loadRiverExtent(session)) { | |
595 return false; | |
596 } | |
597 | |
598 if (!loadGauges(session)) { | |
599 return false; | |
600 } | |
601 | |
602 if (!loadDischargeSectors(session)) { | |
603 return false; | |
604 } | |
605 | |
606 loadFixings(session); | |
607 loadFixingsColumns(session); | |
608 loadFixingsColumnsKmRange(session); | |
609 loadFixingsColumnsQRanges(session); | |
610 | |
611 return true; | |
612 } | |
613 | |
614 public void generateOverview(Document document) { | |
615 | |
616 List<Fixing.Column> allColumns = new ArrayList<Fixing.Column>(); | |
617 | |
618 for (Fixing fixing: fixings) { | |
619 fixing.addAllColumns(allColumns); | |
620 } | |
621 | |
622 Collections.sort(allColumns, Fixing.DATE_CMP); | |
623 | |
624 Element fixingsElement = document.createElement("fixings"); | |
625 | |
626 Element riverElement = document.createElement("river"); | |
627 | |
628 riverElement.setAttribute("from", String.valueOf(extent.start)); | |
629 riverElement.setAttribute("to", String.valueOf(extent.end)); | |
630 riverElement.setAttribute("rid", String.valueOf(riverId)); | |
631 | |
632 fixingsElement.appendChild(riverElement); | |
633 | |
634 SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm"); | |
635 | |
636 Element esE = document.createElement("events"); | |
637 | |
638 for (Fixing.Column column: allColumns) { | |
639 | |
640 double km = isKmUp ? column.start : column.end; | |
641 | |
642 GaugeRange gauge = findGaugeByKm(km); | |
643 | |
644 if (gauge == null) { | |
645 log.warn("Cannot find gauge for km " + km + "."); | |
646 continue; | |
647 } | |
648 | |
649 List<SectorRange> sectors = column.classify(gauge); | |
650 | |
651 if (!sectors.isEmpty()) { | |
652 Element eE = document.createElement("event"); | |
653 eE.setAttribute("name", | |
654 String.valueOf(column.getFixing().description)); | |
655 eE.setAttribute("cid", String.valueOf(column.columnId)); | |
656 eE.setAttribute("date", df.format(column.startTime)); | |
657 | |
658 for (SectorRange sector: sectors) { | |
659 Element sE = document.createElement("sector"); | |
660 | |
661 sE.setAttribute("from", String.valueOf(sector.start)); | |
662 sE.setAttribute("to", String.valueOf(sector.end)); | |
663 sE.setAttribute("class", String.valueOf(sector.sector)); | |
664 | |
665 eE.appendChild(sE); | |
666 } | |
667 | |
668 esE.appendChild(eE); | |
669 } | |
670 } | |
671 | |
672 fixingsElement.appendChild(esE); | |
673 | |
674 document.appendChild(fixingsElement); | |
675 } | |
676 } | |
677 // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : |