Mercurial > dive4elements > river
comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java @ 3393:525e976102ac
S/Q relation: Out commented the LEFT JOINs which produce a lot of empty and
redundant data.
flys-artifacts/trunk@5021 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 17 Jul 2012 07:22:17 +0000 |
parents | 816ceb0418b9 |
children | 3bffbf670b49 |
comparison
equal
deleted
inserted
replaced
3392:56f62b5209f5 | 3393:525e976102ac |
---|---|
42 public static final String SQL_MEASSURE = | 42 public static final String SQL_MEASSURE = |
43 ("SELECT " + | 43 ("SELECT " + |
44 "m.TSAND AS TSAND," + | 44 "m.TSAND AS TSAND," + |
45 "m.TSCHWEB AS TSCHWEB," + | 45 "m.TSCHWEB AS TSCHWEB," + |
46 "m.CSCHWEB AS CSCHWEB," + | 46 "m.CSCHWEB AS CSCHWEB," + |
47 "m.Q_BPEGEL AS Q," + | 47 "m.Q_BPEGEL AS Q_BPEGEL " + |
48 "%GSIEBSATZ%," + | 48 // ", %GSIEBSATZ% " + |
49 "%SSIEBUNG% " + | 49 // "%SSIEBUNG% " + |
50 "FROM messung m " + | 50 "FROM messung m " + |
51 "JOIN station s ON m.stationid = s.stationid " + | 51 "JOIN station s ON m.stationid = s.stationid " + |
52 "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " + | 52 "JOIN gewaesser g ON s.gewaesserid = g.gewaesserid " + |
53 "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gsiebsatzid " + | 53 // "LEFT JOIN GSIEBSATZ gs ON m.gsiebsatzid = gs.gsiebsatzid " + |
54 "LEFT JOIN SSIEBUNG ss ON gs.gsiebsatzid = ss.gsiebsatzid " + | 54 // "LEFT JOIN SSIEBUNG ss ON gs.gsiebsatzid = ss.gsiebsatzid " + |
55 "WHERE " + | 55 "WHERE " + |
56 "g.name = :river_name AND " + | 56 "g.name = :river_name " + |
57 "s.km BETWEEN :location - 0.001 AND :location + 0.001 AND " + | 57 "AND m.Q_BPEGEL IS NOT NULL " + |
58 "m.datum BETWEEN :from AND :to AND " + | 58 "AND s.km BETWEEN :location - 0.001 AND :location + 0.001 " + |
59 "m.Q_BPEGEL IS NOT NULL") | 59 "AND m.datum BETWEEN :from AND :to "); |
60 .replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ)) | 60 //.replace("%GSIEBSATZ%", projection("gs", GSIEBSATZ)); |
61 .replace("%SSIEBUNG%", projection("ss", SSIEBUNG)); | 61 //.replace("%SSIEBUNG%", projection("ss", SSIEBUNG)); |
62 | 62 |
63 private static final String projection( | 63 private static final String projection( |
64 String prefix, | 64 String prefix, |
65 String [] columnNames | 65 String [] columnNames |
66 ) { | 66 ) { |
113 if (debug) { | 113 if (debug) { |
114 log.debug(SQL_MEASSURE); | 114 log.debug(SQL_MEASSURE); |
115 } | 115 } |
116 | 116 |
117 SQLQuery query = session.createSQLQuery(SQL_MEASSURE) | 117 SQLQuery query = session.createSQLQuery(SQL_MEASSURE) |
118 .addScalar("TSAND", StandardBasicTypes.DOUBLE) | 118 .addScalar("TSAND", StandardBasicTypes.DOUBLE) |
119 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) | 119 .addScalar("TSCHWEB", StandardBasicTypes.DOUBLE) |
120 .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) | 120 .addScalar("CSCHWEB", StandardBasicTypes.DOUBLE) |
121 .addScalar("Q", StandardBasicTypes.DOUBLE); | 121 .addScalar("Q_BPEGEL", StandardBasicTypes.DOUBLE); |
122 | 122 |
123 /* | |
123 for (String siebsatz: GSIEBSATZ) { | 124 for (String siebsatz: GSIEBSATZ) { |
124 query.addScalar(siebsatz, StandardBasicTypes.DOUBLE); | 125 query.addScalar(siebsatz, StandardBasicTypes.DOUBLE); |
125 } | 126 } |
126 | 127 |
127 for (String siebung: SSIEBUNG) { | 128 for (String siebung: SSIEBUNG) { |
128 query.addScalar(siebung, StandardBasicTypes.DOUBLE); | 129 query.addScalar(siebung, StandardBasicTypes.DOUBLE); |
129 } | 130 } |
131 */ | |
130 | 132 |
131 query.setString("river_name", river); | 133 query.setString("river_name", river); |
132 query.setDouble("location", location); | 134 query.setDouble("location", location); |
133 query.setDate("from", dateRange.getFrom()); | 135 query.setDate("from", dateRange.getFrom()); |
134 query.setDate("to", dateRange.getTo()); | 136 query.setDate("to", dateRange.getTo()); |