--- dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2015/02/19 10:36:40 2327 +++ dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2015/02/19 13:39:33 2328 @@ -9,8 +9,11 @@ import java.util.ArrayList; import java.util.HashMap; import java.util.List; +import java.util.Map; import java.util.Properties; import java.util.Queue; +import java.util.Set; +import java.util.TreeSet; import java.util.concurrent.ConcurrentLinkedQueue; import java.util.logging.Logger; @@ -25,11 +28,18 @@ PreparedStatement saveStmt; Adresse alleAdresser[]; + Adresse alleIkkeDaekkede[]; + DeduplicateHelper husnrbogstavCache = new DeduplicateHelper(); DeduplicateHelper ruteCache = new DeduplicateHelper(); - private HashMap bbCache = new HashMap(); + Set postnumre = new TreeSet(); + + Map> ikkeDaekkedePrPost = new HashMap>(); + + + private HashMap bbCache = new HashMap(); public Database(SafeProperties conf) throws SQLException,IOException { this.conn = getConnection( conf ); @@ -66,53 +76,20 @@ conn.createStatement().executeUpdate(sql); } - public BoundingBox getBoundingbox(String postnr) throws SQLException { + public BoundingBox getBoundingbox(int postnr) { BoundingBox bb = bbCache.get(postnr); - if ( bb == null ) { - bb = getBoundingboxFromDb(postnr); - bbCache.put(postnr, bb); - } else { - logger.info("Serving BB from cache"); - } - return bb.clone();//never return the original / cached object } - - private BoundingBox getBoundingboxFromDb(String postnr) throws SQLException { - String minPostnr = postnr.replace('x', '0'); - String maxPostnr = postnr.replace('x', '9'); - - String sql = - "SELECT max(latitude) latmax, min(latitude) latmin, max(longitude) lngmax,min(longitude) lngmin " + - "FROM fulddaekning.adressetabel WHERE postnr BETWEEN ? and ? and rute is null;"; - - PreparedStatement stmt = conn.prepareStatement(sql); - stmt.setString(1, minPostnr); - stmt.setString(2, maxPostnr); - - ResultSet res = stmt.executeQuery(); - res.next(); //query returnerer altid 1 række - - BoundingBox bbox = new BoundingBox(); - bbox.latitudeMax = res.getDouble("latmax"); - bbox.latitudeMin = res.getDouble("latmin"); - bbox.longitudeMax = res.getDouble("lngmax"); - bbox.longitudeMin = res.getDouble("lngmin"); - - res.close(); - stmt.close(); - - return bbox; - } + public Set hentPostnumreCache() { + return postnumre; + } - public Queue hentIkkedaekkedeAdresser(String postnr) throws SQLException { - - String minPostnr = postnr.replace('x', '0'); - String maxPostnr = postnr.replace('x', '9'); + + public void hentAlleIkkedaekkedeAdresser(int minPostnr, int maxPostnr) throws SQLException { - ConcurrentLinkedQueue queue = new ConcurrentLinkedQueue(); + logger.info("Henter alle IKKE-daekkede adresser"); String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + "FROM fulddaekning.adressetabel a " + @@ -124,87 +101,50 @@ "AND gadeid IS NOT NULL " + "AND (a.distributor IS NULL OR a.distributor<>'LUKKET') "; PreparedStatement stmt = conn.prepareStatement(sql); - stmt.setString(1, minPostnr); - stmt.setString(2, maxPostnr); - - queue.addAll( hentAdresseListe( stmt ) ); - return queue; - } - - public List hentPostnumre() throws SQLException { - ArrayList list = new ArrayList(); - - Constants consts = Constants.getInstance(); + stmt.setInt(1, minPostnr); + stmt.setInt(2, maxPostnr); - /* - String sql = "SELECT postnr " + - "FROM fulddaekning.adressetabel " + - "WHERE postnr BETWEEN ? AND ? " + - "AND rute is null " + // Træk kun liste på postnumre hvor der er ikke-dækkede adresser - "GROUP BY postnr " + - "ORDER by postnr"; - */ - + List list = hentAdresseListe( stmt ); + alleIkkeDaekkede = list.toArray( new Adresse[ list.size() ] ); - String sql = "SELECT rpad(left(postnr,?),'4', 'x') as postnr2 " + - "FROM fulddaekning.adressetabel " + - "WHERE postnr BETWEEN ? AND ? " + - "AND rute is null " + // Trae kun liste paa postnumre hvor der er ikke-daekede adresser - "AND (postnr NOT BETWEEN 3900 and 3999) " + //Skip alle groenlandske postnumre - "GROUP BY postnr2 " + - "ORDER by postnr2 "; + logger.info("Analyserer ikke-daekkede adresser"); - - - PreparedStatement stmt = conn.prepareStatement(sql); - //stmt.setString(1, Lookup.distributor ); - - stmt.setInt(1, consts.getPostnrGroup() ); - - stmt.setInt(2, consts.getMinPostnr()); - stmt.setInt(3, consts.getMaxPostnr()); - ResultSet res = stmt.executeQuery(); - - while (res.next()) { - String postnr = res.getString("postnr2"); - list.add(postnr); + for (Adresse a : alleIkkeDaekkede) { + + List postListe; + + BoundingBox bbox; + + if (! postnumre.contains(a.postnr )) { + postnumre.add( a.postnr ); + + bbox = new BoundingBox(); + postListe = new ArrayList(); + + bbCache.put( a.postnr, bbox); + ikkeDaekkedePrPost.put(a.postnr, postListe); + + } else { + bbox = bbCache.get( a.postnr); + postListe = ikkeDaekkedePrPost.get(a.postnr); + } + + bbox.latitudeMax = Math.max(bbox.latitudeMax, a.latitude); + bbox.latitudeMin = Math.min(bbox.latitudeMin, a.latitude); + bbox.longitudeMax = Math.max(bbox.longitudeMax, a.longitude); + bbox.longitudeMin = Math.min(bbox.longitudeMin, a.longitude); + + postListe.add(a); + } - res.close(); - stmt.close(); - - //list.add(8700); - - return list; } - - @Deprecated - public Adresse[] hentDaekkedeAdresser( BoundingBox bbox) throws SQLException { - long start = System.currentTimeMillis(); - String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + - "FROM fulddaekning.adressetabel a " + - "LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + - "WHERE rute IS NOT NULL " + - "AND latitude BETWEEN ? AND ? " + - "AND longitude BETWEEN ? AND ? " + - "AND a.distributor = ? "; - - // Forward only + concur_read_only + fetchsize tvinger driver til at hente en række af gangen (bedre performance ved store result sets) - // Se http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html - //PreparedStatement stmt = conn.prepareStatement(sql); - PreparedStatement stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); - stmt.setFetchSize(Integer.MIN_VALUE); - - stmt.setDouble(1, bbox.latitudeMin); - stmt.setDouble(2, bbox.latitudeMax); - stmt.setDouble(3, bbox.longitudeMin); - stmt.setDouble(4, bbox.longitudeMax); - stmt.setString(5, LookupMain.distributor); - - List list = hentAdresseListe( stmt ); - long stop = System.currentTimeMillis(); - logger.info("Elapsed DB: " + (stop - start)); - return list.toArray( new Adresse[ list.size() ] ); + + public Queue hentIkkedaekkedeAdresserCache(int postnr) { + List postListe = ikkeDaekkedePrPost.get(postnr); + + return new ConcurrentLinkedQueue(postListe); } + public Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox) { long start = System.currentTimeMillis(); @@ -363,4 +303,137 @@ return conn; } + + // ////////////////////////////////////////////////////////////////// + + @Deprecated + private BoundingBox getBoundingboxFromDb_old(String postnr) throws SQLException { + String minPostnr = postnr.replace('x', '0'); + String maxPostnr = postnr.replace('x', '9'); + + String sql = + "SELECT max(latitude) latmax, min(latitude) latmin, max(longitude) lngmax,min(longitude) lngmin " + + "FROM fulddaekning.adressetabel WHERE postnr BETWEEN ? and ? and rute is null;"; + + PreparedStatement stmt = conn.prepareStatement(sql); + stmt.setString(1, minPostnr); + stmt.setString(2, maxPostnr); + + ResultSet res = stmt.executeQuery(); + res.next(); //query returnerer altid 1 række + + BoundingBox bbox = new BoundingBox(); + bbox.latitudeMax = res.getDouble("latmax"); + bbox.latitudeMin = res.getDouble("latmin"); + bbox.longitudeMax = res.getDouble("lngmax"); + bbox.longitudeMin = res.getDouble("lngmin"); + + res.close(); + stmt.close(); + + return bbox; + } + + @Deprecated + public Adresse[] hentDaekkedeAdresser_old( BoundingBox bbox) throws SQLException { + long start = System.currentTimeMillis(); + String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + + "FROM fulddaekning.adressetabel a " + + "LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + + "WHERE rute IS NOT NULL " + + "AND latitude BETWEEN ? AND ? " + + "AND longitude BETWEEN ? AND ? " + + "AND a.distributor = ? "; + + // Forward only + concur_read_only + fetchsize tvinger driver til at hente en række af gangen (bedre performance ved store result sets) + // Se http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html + //PreparedStatement stmt = conn.prepareStatement(sql); + PreparedStatement stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); + stmt.setFetchSize(Integer.MIN_VALUE); + + stmt.setDouble(1, bbox.latitudeMin); + stmt.setDouble(2, bbox.latitudeMax); + stmt.setDouble(3, bbox.longitudeMin); + stmt.setDouble(4, bbox.longitudeMax); + stmt.setString(5, LookupMain.distributor); + + List list = hentAdresseListe( stmt ); + long stop = System.currentTimeMillis(); + logger.info("Elapsed DB: " + (stop - start)); + return list.toArray( new Adresse[ list.size() ] ); + } + + @Deprecated + public Queue hentIkkedaekkedeAdresser_old(String postnr) throws SQLException { + + String minPostnr = postnr.replace('x', '0'); + String maxPostnr = postnr.replace('x', '9'); + + ConcurrentLinkedQueue queue = new ConcurrentLinkedQueue(); + + String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + + "FROM fulddaekning.adressetabel a " + + "LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + + "WHERE rute IS NULL " + //Ingen dækning + "AND a.postnr BETWEEN ? AND ? " + + "AND latitude IS NOT NULL " + + "AND longitude IS NOT NULL " + + "AND gadeid IS NOT NULL " + + "AND (a.distributor IS NULL OR a.distributor<>'LUKKET') "; + PreparedStatement stmt = conn.prepareStatement(sql); + stmt.setString(1, minPostnr); + stmt.setString(2, maxPostnr); + + queue.addAll( hentAdresseListe( stmt ) ); + return queue; + } + + @Deprecated + public List hentPostnumre_old() throws SQLException { + ArrayList list = new ArrayList(); + + Constants consts = Constants.getInstance(); + + /* + String sql = "SELECT postnr " + + "FROM fulddaekning.adressetabel " + + "WHERE postnr BETWEEN ? AND ? " + + "AND rute is null " + // Træk kun liste på postnumre hvor der er ikke-dækkede adresser + "GROUP BY postnr " + + "ORDER by postnr"; + */ + + + String sql = "SELECT rpad(left(postnr,?),'4', 'x') as postnr2 " + + "FROM fulddaekning.adressetabel " + + "WHERE postnr BETWEEN ? AND ? " + + "AND rute is null " + // Trae kun liste paa postnumre hvor der er ikke-daekede adresser + "AND (postnr NOT BETWEEN 3900 and 3999) " + //Skip alle groenlandske postnumre + "GROUP BY postnr2 " + + "ORDER by postnr2 "; + + + + PreparedStatement stmt = conn.prepareStatement(sql); + //stmt.setString(1, Lookup.distributor ); + + stmt.setInt(1, consts.getPostnrGroup() ); + + stmt.setInt(2, consts.getMinPostnr()); + stmt.setInt(3, consts.getMaxPostnr()); + ResultSet res = stmt.executeQuery(); + + while (res.next()) { + String postnr = res.getString("postnr2"); + list.add(postnr); + } + res.close(); + stmt.close(); + + //list.add(8700); + + return list; + } + + }