--- android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java 2010/05/19 09:53:25 739 +++ android/TrainInfoService/src/dk/thoerup/traininfoservice/db/StationDAO.java 2011/06/08 15:38:11 1504 @@ -1,17 +1,37 @@ -package dk.thoerup.traininfoservice; +package dk.thoerup.traininfoservice.db; +import java.sql.Array; import java.sql.Connection; import java.sql.PreparedStatement; 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.android.traininfo.common.StationBean; +import dk.thoerup.android.traininfo.common.StationEntry; public class StationDAO { - private StationBean convertSingleRow(ResultSet res) throws SQLException { - StationBean station = new StationBean(); - + + private interface StatementParamSetter { + public void setParams(PreparedStatement stmt) throws SQLException ; + } + private class NullSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException {} + } + + public static class NostationException extends Exception { + private static final long serialVersionUID = 1L; + } + + final static int LOCATION_LIMIT = 8; + static final Logger logger = Logger.getLogger(StationDAO.class.getName()); + + + private StationEntry convertSingleRow(ResultSet res) throws SQLException { + StationEntry station = new StationEntry(); + station.setId( res.getInt(1) ); station.setName( res.getString(2) ); station.setLatitude( res.getDouble(3) ); @@ -22,35 +42,94 @@ 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; + + } + + private StationBean fetchStations(String SQL, StatementParamSetter setter) throws SQLException { + StationBean stations; + Connection conn = null; + PreparedStatement stmt = null; + ResultSet res = null; + try { + conn = DBConnection.getConnection(); + stmt = conn.prepareStatement(SQL); + + setter.setParams(stmt); + + res = stmt.executeQuery(); + stations = convertResultset(res); + + } finally { + if (res != null) + res.close(); + if (stmt != null) + stmt.close(); + if (conn!= null) + conn.close(); } return stations; } - - - public StationBean getById(int id) throws SQLException { + + public StationEntry getById(int id) throws SQLException,NostationException { String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " + - "FROM trainstations WHERE id=" + id + " AND enabled=true"; + "FROM trainstations WHERE id=" + id + " AND enabled=true"; + + StationBean stations = fetchStations(SQL, new NullSetter() ); + + if (stations.entries.size() > 0 ) { + return stations.entries.get(0); + } else { + throw new NostationException(); + } + } + + public StationBean dumpAll() throws SQLException { + + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0,aliases " + + "FROM trainstations WHERE enabled = true ORDER BY id"; Connection conn = null; Statement stmt = null; - ResultSet res = null; - StationBean result; + ResultSet res = null; + try { conn = DBConnection.getConnection(); - + stmt = conn.createStatement(); res = stmt.executeQuery(SQL); - res.next(); - result = convertSingleRow(res); + + // Does mostly the same as convertResultset() + StationBean stations = new StationBean(); + while (res.next()) { + StationEntry entry = convertSingleRow(res); + + Array arr = res.getArray(10); + if (arr != null) { + String[] aliases = (String[]) arr.getArray(); + entry.setAliases(aliases); + } + + stations.entries.add( entry ); + + } + return stations; + + } finally { if (res != null) res.close(); @@ -60,129 +139,162 @@ conn.close(); } - return result; } - + /* * 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) ) * create function rlike(text,text) returns bool as * 'select $2 ilike $1' language sql strict immutable; - * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~); + * create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~); */ - public List getByName(String name) throws SQLException { + public StationBean getByName(final 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 "; + class NameSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setString(1, name + "%"); + stmt.setString(2, name + "%"); + } + } - List result; - Connection conn = null; - PreparedStatement stmt = null; - ResultSet res = null; - try { - conn = DBConnection.getConnection(); - stmt = conn.prepareStatement(SQL); - - stmt.setString(1, name + "%"); - stmt.setString(2, name + "%"); - - res = stmt.executeQuery(); - result = convertResultset(res); + return fetchStations(SQL, new NameSetter() ); + } + + //Latitude (horizonal), longitude(vertical) so + // 1 degree latitude is ~ 111320 meters, since the distance between the horizonal lines is always the same + // 1 degree longitude is ~111320 meters at equator but gets shorter as we get closer to the poles. + // 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 StationBean getByLocationWorker(final double latitude, final double longitude, final boolean geolimit) throws SQLException { + + 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 " + + "FROM trainstations " + + "WHERE enabled = true " + limitExpression + + "ORDER BY calcdist ASC " + + "LIMIT " + LOCATION_LIMIT; + + + class LatlongSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setDouble(1, latitude); + stmt.setDouble(2, longitude); + if (geolimit == true) { + stmt.setDouble(3, latitude); + stmt.setDouble(4, longitude); + } + } + } + + return fetchStations(SQL, new LatlongSetter() ); + } + + public StationBean getByLocation(double latitude, double longitude) throws SQLException { + StationBean result = getByLocationWorker(latitude, longitude, true); + + if (result.entries.size() < LOCATION_LIMIT) { //failover + logger.info("getByLocation failover: " +latitude + "," + longitude); - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); + result = getByLocationWorker(latitude, longitude, false); } + return result; } - - //the "hack" with max 1.5 degrees latitude and 2.5 degrees longitude is only valid since we only service danish trains - // in denmark 1.5dg latitude ~ 165km, 2.5dg longitude ~ 155km - public List getByLocation(double latitude, double longitude) throws SQLException { - String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," + - " earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " + - "FROM trainstations " + - "WHERE enabled = true AND abs(latitude-?)<1.5 AND abs(longitude-?)<2.5 " + - "ORDER BY calcdist ASC " + - "LIMIT 4 "; - List result; + + + + 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 " + + "ORDER BY name "; + + return fetchStations(SQL, new NullSetter() ); + } + + + + public StationEntry getSimpleByName(final String name) throws SQLException { + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + + "FROM trainstations " + + "WHERE name = ? AND enabled = true " + + "LIMIT 1 "; + + class NameSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setString(1, name ); + } + } + + StationBean stations = fetchStations(SQL, new NameSetter() ); + + if (stations.entries.size() == 1) { + return stations.entries.get(0); + } else { + return null; + } + } + + @Deprecated + public static String getStationName(int stationID) { + String station = ""; + Connection conn = null; - PreparedStatement stmt = null; - ResultSet res = null; try { conn = DBConnection.getConnection(); - stmt = conn.prepareStatement(SQL); - stmt.setDouble(1, latitude); - stmt.setDouble(2, longitude); - stmt.setDouble(3, latitude); - stmt.setDouble(4, longitude); - res = stmt.executeQuery(); - result = convertResultset(res); - + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID); + if (rs.next()) { + station = rs.getString(1); + } + + } catch (Exception e) { } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) - conn.close(); - } - return result; - } - - public List 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 " + - "ORDER BY name "; - - Connection conn = null; - Statement stmt = null; - ResultSet res = null; - List result; - try { - conn = DBConnection.getConnection(); - stmt = conn.createStatement(); - res = stmt.executeQuery(SQL); - result = convertResultset(res); - } finally { - if (res != null) - res.close(); - if (stmt != null) - stmt.close(); - if (conn!= null) + if (conn != null && !conn.isClosed()) conn.close(); - } - - return result; - - } - public static String getStationName(int stationID) { - String station = ""; - - Connection conn = null; - try { - conn = DBConnection.getConnection(); - Statement stmt = conn.createStatement(); - ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID); - if (rs.next()) { - station = rs.getString(1); - } - - } catch (Exception e) { - } finally { - try { - if (conn != null && !conn.isClosed()) - conn.close(); - } catch (Exception e) {} - } + } catch (Exception e) {} + } - return station; - } + return station; + } + + + + @Deprecated + public int getIdByName(final String name) throws SQLException { + String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + + "FROM trainstations " + + "WHERE name = ? AND enabled = true " + + "LIMIT 1 "; + + class NameSetter implements StatementParamSetter { + @Override + public void setParams(PreparedStatement stmt) throws SQLException { + stmt.setString(1, name ); + } + } + + StationBean stations = fetchStations(SQL, new NameSetter() ); + + if (stations.entries.size() == 1) { + return stations.entries.get(0).getId(); + } else { + return -1; + } + } }