Mercurial > dive4elements > river

comparison flys-artifacts/src/main/java/de/intevation/flys/artifacts/model/sq/MeasurementFactory.java @ 3393:525e976102ac

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