--- android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java 2010/06/15 05:33:14 848 +++ android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java 2010/09/16 13:32:10 1060 @@ -5,14 +5,17 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; -import java.util.ArrayList; -import java.util.List; +import java.util.logging.Logger; + +import dk.thoerup.traininfoservice.StationBean.StationEntry; public class StationDAO { - final static int LOCATION_LIMIT = 5; + final static int LOCATION_LIMIT = 8; + static final Logger logger = Logger.getLogger(StationDAO.class.getName()); + - private StationBean convertSingleRow(ResultSet res) throws SQLException { - StationBean station = new StationBean(); + private StationEntry convertSingleRow(ResultSet res) throws SQLException { + StationEntry station = new StationEntry(); station.setId( res.getInt(1) ); station.setName( res.getString(2) ); @@ -23,28 +26,32 @@ station.setMetro( res.getString(7) ); station.setAddress( res.getString(8) ); station.setCalcdist( (int)res.getDouble(9) ); + + station.setIsRegional( station.getRegional() != null ); + station.setIsStrain( station.getStrain() != null ); + station.setIsMetro( station.getMetro() != null ); return station; } - private List convertResultset(ResultSet res) throws SQLException { - List stations = new ArrayList(); + private StationBean convertResultset(ResultSet res) throws SQLException { + StationBean stations = new StationBean(); while (res.next()) { - stations.add( convertSingleRow(res) ); + stations.entries.add( convertSingleRow(res) ); } return stations; } - public StationBean getById(int id) throws SQLException { + public StationEntry getById(int id) throws SQLException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " + "FROM trainstations WHERE id=" + id + " AND enabled=true"; Connection conn = null; Statement stmt = null; ResultSet res = null; - StationBean result; + StationEntry result; try { conn = DBConnection.getConnection(); @@ -71,14 +78,14 @@ * 'select $2 ilike $1' language sql strict immutable; * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~); */ - public List getByName(String name) throws SQLException { + public StationBean getByName(String name) throws SQLException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + "FROM trainstations " + "WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " + "ORDER BY name "; - List result; + StationBean result; Connection conn = null; PreparedStatement stmt = null; ResultSet res = null; @@ -103,17 +110,17 @@ return result; } - //the "hack" with max 1.0 degrees latitude and 2.0 degrees longitude is only valid since we only service danish trains - // in denmark 1.0dg latitude ~ 110km, 2.0dg longitude ~ 120km + //the "hack" with max 0.4 degrees latitude and 0.75 degrees longitude is only valid since we only service danish trains, + // in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km // the ultra fast method (and only slightly inaccurate as long as we only cover a limited geographically area) // is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to // calculate the distance: // sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist - public List getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException { + public StationBean getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException { - String limitExpression = geolimit == true ? "AND abs(latitude-?)<1.0 AND abs(longitude-?)<2.0 " : ""; + String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : ""; String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, " + "earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " + @@ -121,8 +128,10 @@ "WHERE enabled = true " + limitExpression + "ORDER BY calcdist ASC " + "LIMIT " + LOCATION_LIMIT; + + - List result; + StationBean result; Connection conn = null; PreparedStatement stmt = null; ResultSet res = null; @@ -137,7 +146,7 @@ } res = stmt.executeQuery(); result = convertResultset(res); - + } finally { if (res != null) res.close(); @@ -149,10 +158,12 @@ return result; } - public List getByLocation(double latitude, double longitude) throws SQLException { - List result = getByLocationWorker(latitude, longitude, true); + public StationBean getByLocation(double latitude, double longitude) throws SQLException { + StationBean result = getByLocationWorker(latitude, longitude, true); - if (result.size() < LOCATION_LIMIT) { //failover + if (result.entries.size() < LOCATION_LIMIT) { //failover + logger.info("getByLocation failover: " +latitude + "," + longitude); + result = getByLocationWorker(latitude, longitude, false); } @@ -161,7 +172,7 @@ - public List getByList(String list) throws SQLException { + public StationBean getByList(String list) throws SQLException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + "FROM trainstations " + "WHERE id IN " + list + " AND enabled = true " + @@ -170,7 +181,7 @@ Connection conn = null; Statement stmt = null; ResultSet res = null; - List result; + StationBean result; try { conn = DBConnection.getConnection(); @@ -218,7 +229,7 @@ "WHERE name = ? AND enabled = true " + "LIMIT 1 "; - List result; + StationBean result; Connection conn = null; PreparedStatement stmt = null; ResultSet res = null; @@ -240,8 +251,8 @@ conn.close(); } - if (result.size() == 1) { - return result.get(0).getId(); + if (result.entries.size() == 1) { + return result.entries.get(0).getId(); } else { return -1; }