/[projects]/android/TrainInfoService/src/dk/thoerup/traininfoservice/db/StationDAO.java
ViewVC logotype

Annotation of /android/TrainInfoService/src/dk/thoerup/traininfoservice/db/StationDAO.java

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1515 - (hide annotations) (download)
Fri Jun 10 08:25:43 2011 UTC (12 years, 11 months ago) by torben
File size: 10428 byte(s)
also log whether we actually found any with fuzzy str search
1 torben 1255 package dk.thoerup.traininfoservice.db;
2 torben 588
3 torben 1082 import java.sql.Array;
4 torben 588 import java.sql.Connection;
5     import java.sql.PreparedStatement;
6     import java.sql.ResultSet;
7     import java.sql.SQLException;
8     import java.sql.Statement;
9 torben 895 import java.util.logging.Logger;
10 torben 588
11 torben 1061 import dk.thoerup.android.traininfo.common.StationBean;
12 torben 1409 import dk.thoerup.android.traininfo.common.StationEntry;
13 torben 1060
14 torben 588 public class StationDAO {
15 torben 1145
16 torben 1504 private interface StatementParamSetter {
17     public void setParams(PreparedStatement stmt) throws SQLException ;
18     }
19     private class NullSetter implements StatementParamSetter {
20     @Override
21     public void setParams(PreparedStatement stmt) throws SQLException {}
22     }
23    
24 torben 1145 public static class NostationException extends Exception {
25     private static final long serialVersionUID = 1L;
26     }
27    
28 torben 997 final static int LOCATION_LIMIT = 8;
29 torben 895 static final Logger logger = Logger.getLogger(StationDAO.class.getName());
30 torben 841
31 torben 895
32 torben 1060 private StationEntry convertSingleRow(ResultSet res) throws SQLException {
33     StationEntry station = new StationEntry();
34 torben 836
35 torben 588 station.setId( res.getInt(1) );
36     station.setName( res.getString(2) );
37     station.setLatitude( res.getDouble(3) );
38     station.setLongitude( res.getDouble(4) );
39     station.setRegional( res.getString(5) );
40     station.setStrain( res.getString(6) );
41     station.setMetro( res.getString(7) );
42     station.setAddress( res.getString(8) );
43     station.setCalcdist( (int)res.getDouble(9) );
44 torben 1060
45     station.setIsRegional( station.getRegional() != null );
46     station.setIsStrain( station.getStrain() != null );
47     station.setIsMetro( station.getMetro() != null );
48 torben 836
49 torben 588 return station;
50     }
51 torben 836
52 torben 1060 private StationBean convertResultset(ResultSet res) throws SQLException {
53     StationBean stations = new StationBean();
54 torben 588 while (res.next()) {
55 torben 1060 stations.entries.add( convertSingleRow(res) );
56 torben 588 }
57     return stations;
58 torben 836
59 torben 588 }
60 torben 836
61 torben 1504 private StationBean fetchStations(String SQL, StatementParamSetter setter) throws SQLException {
62     StationBean stations;
63 torben 588 Connection conn = null;
64 torben 1504 PreparedStatement stmt = null;
65 torben 589 ResultSet res = null;
66 torben 588 try {
67     conn = DBConnection.getConnection();
68 torben 1504 stmt = conn.prepareStatement(SQL);
69    
70     setter.setParams(stmt);
71 torben 836
72 torben 1504 res = stmt.executeQuery();
73     stations = convertResultset(res);
74    
75 torben 588 } finally {
76 torben 589 if (res != null)
77     res.close();
78     if (stmt != null)
79     stmt.close();
80 torben 1504 if (conn!= null)
81 torben 588 conn.close();
82     }
83 torben 1504 return stations;
84    
85     }
86 torben 836
87 torben 1504 public StationEntry getById(int id) throws SQLException,NostationException {
88     String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " +
89     "FROM trainstations WHERE id=" + id + " AND enabled=true";
90    
91     StationBean stations = fetchStations(SQL, new NullSetter() );
92    
93     if (stations.entries.size() > 0 ) {
94     return stations.entries.get(0);
95     } else {
96     throw new NostationException();
97     }
98 torben 588 }
99 torben 1082
100 torben 836
101 torben 1082
102 torben 714 /*
103     * this code requires theses statements are run on database in order to do ILIKE searches against aliases (which is defines as array of varchar(64) )
104     * create function rlike(text,text) returns bool as
105     * 'select $2 ilike $1' language sql strict immutable;
106 torben 836 * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~);
107 torben 714 */
108 torben 1507 public StationBean getByNameNormal(final String name) throws SQLException {
109 torben 588 String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
110     "FROM trainstations " +
111 torben 714 "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " +
112 torben 588 "ORDER BY name ";
113    
114 torben 1504 class NameSetter implements StatementParamSetter {
115     @Override
116     public void setParams(PreparedStatement stmt) throws SQLException {
117     stmt.setString(1, name + "%");
118     stmt.setString(2, name + "%");
119     }
120 torben 588 }
121 torben 1504
122     return fetchStations(SQL, new NameSetter() );
123 torben 588 }
124 torben 1507
125    
126     public StationBean getByNameFuzzy(final String name) throws SQLException {
127 torben 1512 String SQL = "SELECT * FROM (" +
128     " SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0, " +
129     " levenshtein(lower(name),lower(?) ) as leven " +
130     " FROM trainstations " +
131     " WHERE enabled = true ) as lev2 " +
132     "WHERE (leven <= 3) " +
133     "ORDER BY leven " +
134     "LIMIT 1";
135 torben 836
136 torben 1507 class NameSetter implements StatementParamSetter {
137     @Override
138     public void setParams(PreparedStatement stmt) throws SQLException {
139     stmt.setString(1, name );
140     }
141     }
142    
143     StationBean stations = fetchStations(SQL, new NameSetter() );
144     stations.fuzzystrmatch = true;
145     return stations;
146     }
147    
148 torben 1513 public StationBean getByName(String name) throws SQLException {
149 torben 1507 StationBean stations = getByNameNormal(name);
150    
151     if (stations.entries.size() == 0) {
152 torben 1513 name = name.replace(".", ""); //remove any .'s before fuzzy search
153 torben 1507 stations = getByNameFuzzy(name);
154 torben 1515
155     logger.info("getByName failover: " + name + "(" + (stations.entries.size() >0) + ")" );
156 torben 1507 }
157     return stations;
158     }
159    
160    
161    
162 torben 1254 //Latitude (horizonal), longitude(vertical) so
163     // 1 degree latitude is ~ 111320 meters, since the distance between the horizonal lines is always the same
164     // 1 degree longitude is ~111320 meters at equator but gets shorter as we get closer to the poles.
165 torben 1511 // so 1 degree longitude is 64.5 km at denmarks southern point (gedser=54.55,11.95)
166     // and is 59.4km at northern point (skagen = 57.75,10.65)
167     // The "hack" with max 0.4 degrees latitude and 0.75 degrees longitude is only valid since we only service danish trains,
168 torben 928 // in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km
169 torben 836
170 torben 741 // the ultra fast method (and only slightly inaccurate as long as we only cover a limited geographically area)
171     // is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to
172     // calculate the distance:
173 torben 836 // sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist
174    
175 torben 1504 public StationBean getByLocationWorker(final double latitude, final double longitude, final boolean geolimit) throws SQLException {
176 torben 841
177 torben 929 String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : "";
178 torben 841
179     String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, " +
180     "earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " +
181     "FROM trainstations " +
182     "WHERE enabled = true " + limitExpression +
183     "ORDER BY calcdist ASC " +
184     "LIMIT " + LOCATION_LIMIT;
185 torben 849
186 torben 841
187 torben 1504 class LatlongSetter implements StatementParamSetter {
188     @Override
189     public void setParams(PreparedStatement stmt) throws SQLException {
190     stmt.setDouble(1, latitude);
191     stmt.setDouble(2, longitude);
192     if (geolimit == true) {
193     stmt.setDouble(3, latitude);
194     stmt.setDouble(4, longitude);
195     }
196     }
197     }
198 torben 849
199 torben 1504 return fetchStations(SQL, new LatlongSetter() );
200 torben 588 }
201 torben 841
202 torben 1060 public StationBean getByLocation(double latitude, double longitude) throws SQLException {
203     StationBean result = getByLocationWorker(latitude, longitude, true);
204 torben 841
205 torben 1060 if (result.entries.size() < LOCATION_LIMIT) { //failover
206 torben 894 logger.info("getByLocation failover: " +latitude + "," + longitude);
207    
208 torben 841 result = getByLocationWorker(latitude, longitude, false);
209     }
210    
211     return result;
212     }
213    
214    
215 torben 836
216 torben 1060 public StationBean getByList(String list) throws SQLException {
217 torben 836 String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " +
218     "FROM trainstations " +
219     "WHERE id IN " + list + " AND enabled = true " +
220     "ORDER BY name ";
221 torben 1504
222     return fetchStations(SQL, new NullSetter() );
223     }
224    
225 torben 836
226 torben 1504
227     public StationEntry getSimpleByName(final String name) throws SQLException {
228     String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
229     "FROM trainstations " +
230     "WHERE name = ? AND enabled = true " +
231     "LIMIT 1 ";
232 torben 836
233 torben 1504 class NameSetter implements StatementParamSetter {
234     @Override
235     public void setParams(PreparedStatement stmt) throws SQLException {
236     stmt.setString(1, name );
237     }
238 torben 836 }
239 torben 1504
240     StationBean stations = fetchStations(SQL, new NameSetter() );
241    
242     if (stations.entries.size() == 1) {
243     return stations.entries.get(0);
244     } else {
245     return null;
246     }
247     }
248 torben 1505
249     //used to create full dump in order to populate Google Appengine DB
250     @Deprecated
251     public StationBean dumpAll() throws SQLException {
252    
253     String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0,aliases " +
254     "FROM trainstations WHERE enabled = true ORDER BY id";
255    
256     Connection conn = null;
257     Statement stmt = null;
258     ResultSet res = null;
259 torben 836
260 torben 1505
261     try {
262     conn = DBConnection.getConnection();
263    
264     stmt = conn.createStatement();
265     res = stmt.executeQuery(SQL);
266    
267     // Does mostly the same as convertResultset()
268     StationBean stations = new StationBean();
269     while (res.next()) {
270     StationEntry entry = convertSingleRow(res);
271    
272     Array arr = res.getArray(10);
273     if (arr != null) {
274     String[] aliases = (String[]) arr.getArray();
275     entry.setAliases(aliases);
276     }
277    
278     stations.entries.add( entry );
279    
280     }
281     return stations;
282    
283    
284     } finally {
285     if (res != null)
286     res.close();
287     if (stmt != null)
288     stmt.close();
289     if (conn != null)
290     conn.close();
291     }
292    
293     }
294    
295 torben 1417 @Deprecated
296 torben 836 public static String getStationName(int stationID) {
297     String station = "";
298    
299     Connection conn = null;
300     try {
301     conn = DBConnection.getConnection();
302     Statement stmt = conn.createStatement();
303     ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID);
304     if (rs.next()) {
305     station = rs.getString(1);
306     }
307    
308     } catch (Exception e) {
309     } finally {
310 torben 588 try {
311 torben 836 if (conn != null && !conn.isClosed())
312 torben 588 conn.close();
313 torben 836 } catch (Exception e) {}
314     }
315 torben 650
316 torben 836 return station;
317     }
318 torben 1417
319 torben 1504
320    
321 torben 1417 @Deprecated
322 torben 1504 public int getIdByName(final String name) throws SQLException {
323 torben 836 String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
324     "FROM trainstations " +
325     "WHERE name = ? AND enabled = true " +
326     "LIMIT 1 ";
327 torben 650
328 torben 1504 class NameSetter implements StatementParamSetter {
329     @Override
330     public void setParams(PreparedStatement stmt) throws SQLException {
331     stmt.setString(1, name );
332     }
333 torben 836 }
334 torben 1504
335     StationBean stations = fetchStations(SQL, new NameSetter() );
336    
337     if (stations.entries.size() == 1) {
338     return stations.entries.get(0).getId();
339 torben 836 } else {
340     return -1;
341     }
342     }
343 torben 588 }

  ViewVC Help
Powered by ViewVC 1.1.20