Mercurial > dive4elements > river

comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/FixingsOverview.java @ 2561:b3f6d49cdc80

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 :
http://dive4elements.wald.intevation.org