/[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 1513 - (hide annotations) (download)
Thu Jun 9 10:02:31 2011 UTC (12 years, 11 months ago) by torben
File size: 10383 byte(s)
remove . on name before fuzzyname 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     logger.info("getByName failover: " + name);
153 torben 1513 name = name.replace(".", ""); //remove any .'s before fuzzy search
154 torben 1507 stations = getByNameFuzzy(name);
155     }
156     return stations;
157     }
158    
159    
160    
161 torben 1254 //Latitude (horizonal), longitude(vertical) so
162     // 1 degree latitude is ~ 111320 meters, since the distance between the horizonal lines is always the same
163     // 1 degree longitude is ~111320 meters at equator but gets shorter as we get closer to the poles.
164 torben 1511 // so 1 degree longitude is 64.5 km at denmarks southern point (gedser=54.55,11.95)
165     // and is 59.4km at northern point (skagen = 57.75,10.65)
166     // The "hack" with max 0.4 degrees latitude and 0.75 degrees longitude is only valid since we only service danish trains,
167 torben 928 // in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km
168 torben 836
169 torben 741 // the ultra fast method (and only slightly inaccurate as long as we only cover a limited geographically area)
170     // is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to
171     // calculate the distance:
172 torben 836 // sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist
173    
174 torben 1504 public StationBean getByLocationWorker(final double latitude, final double longitude, final boolean geolimit) throws SQLException {
175 torben 841
176 torben 929 String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : "";
177 torben 841
178     String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, " +
179     "earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " +
180     "FROM trainstations " +
181     "WHERE enabled = true " + limitExpression +
182     "ORDER BY calcdist ASC " +
183     "LIMIT " + LOCATION_LIMIT;
184 torben 849
185 torben 841
186 torben 1504 class LatlongSetter implements StatementParamSetter {
187     @Override
188     public void setParams(PreparedStatement stmt) throws SQLException {
189     stmt.setDouble(1, latitude);
190     stmt.setDouble(2, longitude);
191     if (geolimit == true) {
192     stmt.setDouble(3, latitude);
193     stmt.setDouble(4, longitude);
194     }
195     }
196     }
197 torben 849
198 torben 1504 return fetchStations(SQL, new LatlongSetter() );
199 torben 588 }
200 torben 841
201 torben 1060 public StationBean getByLocation(double latitude, double longitude) throws SQLException {
202     StationBean result = getByLocationWorker(latitude, longitude, true);
203 torben 841
204 torben 1060 if (result.entries.size() < LOCATION_LIMIT) { //failover
205 torben 894 logger.info("getByLocation failover: " +latitude + "," + longitude);
206    
207 torben 841 result = getByLocationWorker(latitude, longitude, false);
208     }
209    
210     return result;
211     }
212    
213    
214 torben 836
215 torben 1060 public StationBean getByList(String list) throws SQLException {
216 torben 836 String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " +
217     "FROM trainstations " +
218     "WHERE id IN " + list + " AND enabled = true " +
219     "ORDER BY name ";
220 torben 1504
221     return fetchStations(SQL, new NullSetter() );
222     }
223    
224 torben 836
225 torben 1504
226     public StationEntry getSimpleByName(final String name) throws SQLException {
227     String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
228     "FROM trainstations " +
229     "WHERE name = ? AND enabled = true " +
230     "LIMIT 1 ";
231 torben 836
232 torben 1504 class NameSetter implements StatementParamSetter {
233     @Override
234     public void setParams(PreparedStatement stmt) throws SQLException {
235     stmt.setString(1, name );
236     }
237 torben 836 }
238 torben 1504
239     StationBean stations = fetchStations(SQL, new NameSetter() );
240    
241     if (stations.entries.size() == 1) {
242     return stations.entries.get(0);
243     } else {
244     return null;
245     }
246     }
247 torben 1505
248     //used to create full dump in order to populate Google Appengine DB
249     @Deprecated
250     public StationBean dumpAll() throws SQLException {
251    
252     String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0,aliases " +
253     "FROM trainstations WHERE enabled = true ORDER BY id";
254    
255     Connection conn = null;
256     Statement stmt = null;
257     ResultSet res = null;
258 torben 836
259 torben 1505
260     try {
261     conn = DBConnection.getConnection();
262    
263     stmt = conn.createStatement();
264     res = stmt.executeQuery(SQL);
265    
266     // Does mostly the same as convertResultset()
267     StationBean stations = new StationBean();
268     while (res.next()) {
269     StationEntry entry = convertSingleRow(res);
270    
271     Array arr = res.getArray(10);
272     if (arr != null) {
273     String[] aliases = (String[]) arr.getArray();
274     entry.setAliases(aliases);
275     }
276    
277     stations.entries.add( entry );
278    
279     }
280     return stations;
281    
282    
283     } finally {
284     if (res != null)
285     res.close();
286     if (stmt != null)
287     stmt.close();
288     if (conn != null)
289     conn.close();
290     }
291    
292     }
293    
294 torben 1417 @Deprecated
295 torben 836 public static String getStationName(int stationID) {
296     String station = "";
297    
298     Connection conn = null;
299     try {
300     conn = DBConnection.getConnection();
301     Statement stmt = conn.createStatement();
302     ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID);
303     if (rs.next()) {
304     station = rs.getString(1);
305     }
306    
307     } catch (Exception e) {
308     } finally {
309 torben 588 try {
310 torben 836 if (conn != null && !conn.isClosed())
311 torben 588 conn.close();
312 torben 836 } catch (Exception e) {}
313     }
314 torben 650
315 torben 836 return station;
316     }
317 torben 1417
318 torben 1504
319    
320 torben 1417 @Deprecated
321 torben 1504 public int getIdByName(final String name) throws SQLException {
322 torben 836 String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
323     "FROM trainstations " +
324     "WHERE name = ? AND enabled = true " +
325     "LIMIT 1 ";
326 torben 650
327 torben 1504 class NameSetter implements StatementParamSetter {
328     @Override
329     public void setParams(PreparedStatement stmt) throws SQLException {
330     stmt.setString(1, name );
331     }
332 torben 836 }
333 torben 1504
334     StationBean stations = fetchStations(SQL, new NameSetter() );
335    
336     if (stations.entries.size() == 1) {
337     return stations.entries.get(0).getId();
338 torben 836 } else {
339     return -1;
340     }
341     }
342 torben 588 }

  ViewVC Help
Powered by ViewVC 1.1.20