--- dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2015/02/19 13:39:33 2328 +++ dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2015/08/14 10:56:32 2643 @@ -1,5 +1,7 @@ package dk.daoas.fulddaekning; +import geocode.GeoPoint; + import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; @@ -11,10 +13,8 @@ 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; @@ -27,25 +27,31 @@ Connection conn; PreparedStatement saveStmt; - Adresse alleAdresser[]; + List alleAdresser; Adresse alleIkkeDaekkede[]; + Map> daekkedeAdresserHO = new HashMap>(); + + + + + DeduplicateHelper husnrbogstavCache = new DeduplicateHelper(); DeduplicateHelper ruteCache = new DeduplicateHelper(); - Set postnumre = new TreeSet(); + Set postnumre = new TreeSet(); - Map> ikkeDaekkedePrPost = new HashMap>(); + //Map> ikkeDaekkedePrPost = new HashMap>(); - private HashMap bbCache = new HashMap(); + private HashMap bbCache = new HashMap(); public Database(SafeProperties conf) throws SQLException,IOException { this.conn = getConnection( conf ); - String sql = "INSERT INTO fulddaekning.afstand_anden_rute_ny (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgLatitude,orgLongitude,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,afstand,`timestamp`) "+ - "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; + String sql = "INSERT INTO fulddaekning.afstand_anden_rute_ny (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,rute,afstand,`timestamp`) "+ + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; saveStmt = conn.prepareStatement(sql); @@ -70,24 +76,24 @@ conn.createStatement().executeUpdate(sql); logger.info("Rename tables"); - sql = "RENAME TABLE fulddaekning.afstand_anden_rute" + ext + " TO fulddaekning.afstand_anden_rute_old" + ext + ", fulddaekning.afstand_anden_rute_ny TO fulddaekning.afstand_anden_rute" + ext; + String sql2 = "RENAME TABLE fulddaekning.afstand_anden_rute" + ext + " TO fulddaekning.afstand_anden_rute_old" + ext + ", fulddaekning.afstand_anden_rute_ny TO fulddaekning.afstand_anden_rute" + ext; - logger.info("Executing: " + sql); - conn.createStatement().executeUpdate(sql); + logger.info("Executing: " + sql2); + conn.createStatement().executeUpdate(sql2); } - public BoundingBox getBoundingbox(int postnr) { + public BoundingBox getBoundingbox(short postnr) { BoundingBox bb = bbCache.get(postnr); return bb.clone();//never return the original / cached object } - public Set hentPostnumreCache() { + public Set hentPostnumreCache() { return postnumre; } - public void hentAlleIkkedaekkedeAdresser(int minPostnr, int maxPostnr) throws SQLException { + public Adresse[] hentAlleIkkedaekkedeAdresser(int minPostnr, int maxPostnr) throws SQLException { logger.info("Henter alle IKKE-daekkede adresser"); @@ -96,6 +102,8 @@ "LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + "WHERE rute IS NULL " + //Ingen dækning "AND a.postnr BETWEEN ? AND ? " + + "AND a.postnr NOT BETWEEN 3900 and 3999 " + //Skip greenland + "AND a.postnr NOT BETWEEN 9000 AND 9549 " + // Skip nordjylland (DAO) "AND latitude IS NOT NULL " + "AND longitude IS NOT NULL " + "AND gadeid IS NOT NULL " + @@ -111,22 +119,18 @@ for (Adresse a : alleIkkeDaekkede) { - List postListe; BoundingBox bbox; if (! postnumre.contains(a.postnr )) { postnumre.add( a.postnr ); - bbox = new BoundingBox(); - postListe = new ArrayList(); + bbox = new BoundingBox(); 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); @@ -134,33 +138,19 @@ bbox.longitudeMax = Math.max(bbox.longitudeMax, a.longitude); bbox.longitudeMin = Math.min(bbox.longitudeMin, a.longitude); - postListe.add(a); } + return alleIkkeDaekkede; } - public Queue hentIkkedaekkedeAdresserCache(int postnr) { - List postListe = ikkeDaekkedePrPost.get(postnr); - - return new ConcurrentLinkedQueue(postListe); - } - public Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox) { - long start = System.currentTimeMillis(); - ArrayList list = new ArrayList(); - for (Adresse a : alleAdresser) { - if ( a.latitude > bbox.latitudeMin && a.latitude< bbox.latitudeMax && a.longitude> bbox.longitudeMin && a.longitude < bbox.longitudeMax) { - list.add(a); - } - } - long stop = System.currentTimeMillis(); - logger.info("Elapsed cache: " + (stop - start)); - return list.toArray( new Adresse[ list.size() ] ); - } + public Map> getDaekkedeAdresserHO() { + return daekkedeAdresserHO; + } - public Adresse[] hentAlleDaekkedeAdresser() throws SQLException { + public int hentAlleDaekkedeAdresser() throws SQLException { if ( alleAdresser == null ) { String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + "FROM fulddaekning.adressetabel a " + @@ -172,16 +162,30 @@ // 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.setString(1, LookupMain.distributor); List list = hentAdresseListe( stmt ); - alleAdresser = list.toArray( new Adresse[ list.size() ] ); + + alleAdresser = new ArrayList(); + alleAdresser.addAll(list); + + for(Adresse addr : list) { + short ho = addr.ho; + List hoListe = daekkedeAdresserHO.get(ho); + if ( hoListe == null) { + hoListe = new ArrayList(); + daekkedeAdresserHO.put(ho, hoListe); + } + + hoListe.add(addr); + + } } - return alleAdresser; + + return alleAdresser.size(); } @@ -193,31 +197,27 @@ PreparedStatement saveStmt = conn.prepareStatement(sql);*/ saveStmt.setInt(1, orgAdresse.id); - saveStmt.setInt(2, orgAdresse.postnr); + saveStmt.setShort(2, orgAdresse.postnr); saveStmt.setString(3, orgAdresse.adresse); saveStmt.setInt(4, orgAdresse.gadeid); - saveStmt.setInt(5, orgAdresse.husnr); + saveStmt.setShort(5, orgAdresse.husnr); saveStmt.setString(6, orgAdresse.husnrbogstav); - saveStmt.setDouble(7, orgAdresse.latitude); - saveStmt.setDouble(8, orgAdresse.longitude); - saveStmt.setString(9, orgAdresse.rute); - - - saveStmt.setInt(10, bedsteAdresse.id); - saveStmt.setInt(11, bedsteAdresse.postnr); - saveStmt.setString(12, bedsteAdresse.adresse); - saveStmt.setInt(13, bedsteAdresse.gadeid); - saveStmt.setInt(14, bedsteAdresse.husnr); - saveStmt.setString(15, bedsteAdresse.husnrbogstav); - saveStmt.setDouble(16, bedsteAdresse.latitude); - saveStmt.setDouble(17, bedsteAdresse.longitude); - saveStmt.setString(18, bedsteAdresse.rute); + saveStmt.setString(7, orgAdresse.rute); - saveStmt.setDouble(19, bedsteAfstand); + + saveStmt.setInt(8, bedsteAdresse.id); + saveStmt.setShort(9, bedsteAdresse.postnr); + saveStmt.setString(10, bedsteAdresse.adresse); + saveStmt.setInt(11, bedsteAdresse.gadeid); + saveStmt.setShort(12, bedsteAdresse.husnr); + saveStmt.setString(13, bedsteAdresse.husnrbogstav); + saveStmt.setString(14, bedsteAdresse.rute); + + saveStmt.setDouble(15, bedsteAfstand); saveStmt.addBatch(); batchCount++; - if (batchCount >= 100) { + if (batchCount >= 1000) { saveStmt.executeBatch(); batchCount = 0; } @@ -242,31 +242,23 @@ //logger.info("Starting exec query done"); while (res.next()) { - Adresse adr = new Adresse(); + + double latitude = res.getDouble(7); + double longitude = res.getDouble(8); + + + Adresse adr = new Adresse(latitude,longitude); - /* - adr.id = res.getInt("id"); - adr.postnr = res.getInt("postnr"); - adr.adresse = res.getString("adresse"); - adr.gadeid = res.getInt("gadeid"); - adr.husnr = res.getInt("husnr"); - adr.husnrbogstav = res.getString("husnrbogstav"); - adr.latitude = res.getDouble("latitude"); - adr.longitude = res.getDouble("longitude"); - adr.rute = res.getString("rute"); - adr.ho = res.getInt("ho"); - */ - adr.id = res.getInt(1); - adr.postnr = res.getInt(2); + adr.postnr = res.getShort(2); adr.adresse = res.getString(3); adr.gadeid = res.getInt(4); - adr.husnr = res.getInt(5); + adr.husnr = res.getShort(5); adr.husnrbogstav = husnrbogstavCache.getInstance( res.getString(6) ); - adr.latitude = res.getDouble(7); - adr.longitude = res.getDouble(8); + //adr.latitude = res.getDouble(7); + //adr.longitude = res.getDouble(8); adr.rute = ruteCache.getInstance( res.getString(9) ); - adr.ho = res.getInt(10); + adr.ho = res.getShort(10); list.add(adr); @@ -303,137 +295,5 @@ 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; - } - - }