--- dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2015/02/19 13:39:33 2328 +++ dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2015/07/28 08:08:22 2640 @@ -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,19 +27,25 @@ 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 ); @@ -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 9499 " + // 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,10 +197,10 @@ 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); @@ -204,10 +208,10 @@ saveStmt.setInt(10, bedsteAdresse.id); - saveStmt.setInt(11, bedsteAdresse.postnr); + saveStmt.setShort(11, bedsteAdresse.postnr); saveStmt.setString(12, bedsteAdresse.adresse); saveStmt.setInt(13, bedsteAdresse.gadeid); - saveStmt.setInt(14, bedsteAdresse.husnr); + saveStmt.setShort(14, bedsteAdresse.husnr); saveStmt.setString(15, bedsteAdresse.husnrbogstav); saveStmt.setDouble(16, bedsteAdresse.latitude); saveStmt.setDouble(17, bedsteAdresse.longitude); @@ -217,7 +221,7 @@ saveStmt.addBatch(); batchCount++; - if (batchCount >= 100) { + if (batchCount >= 1000) { saveStmt.executeBatch(); batchCount = 0; } @@ -242,31 +246,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); @@ -306,6 +302,32 @@ // ////////////////////////////////////////////////////////////////// + /* + public Queue hentIkkedaekkedeAdresserCache(short postnr) { + List postListe = ikkeDaekkedePrPost.get(postnr); + + return new ConcurrentLinkedQueue(postListe); + } + + public Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox, Adresse adresseListe[] ) { + long start = System.currentTimeMillis(); + ArrayList list = new ArrayList(); + for (Adresse a : adresseListe) { + 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 Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox) { + return hentDaekkedeAdresserCache(bbox, alleAdresser); + }*/ + + /* @Deprecated private BoundingBox getBoundingboxFromDb_old(String postnr) throws SQLException { String minPostnr = postnr.replace('x', '0'); @@ -401,7 +423,7 @@ "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 " + @@ -433,7 +455,7 @@ //list.add(8700); return list; - } + }*/ }