diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index d37e197..703a4fc 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -57,7 +57,7 @@ DatabaseTool.closeDb(con); } } - + /** * 個別の都道府県「GMLディレクトリ」を処理 * @@ -76,9 +76,7 @@ String sql2 = "SELECT idref,lat,lon,fixed, area FROM "+ DbFuel.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)"; String sql3 = "UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=? WHERE idref=? and area=?"; String sql5 = "UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; - try ( PreparedStatement ps2 = con.prepareStatement(sql2); - PreparedStatement ps1 = con.prepareStatement(sql1); - PreparedStatement ps3 = con.prepareStatement(sql3); + try ( PreparedStatement ps1 = con.prepareStatement(sql1); PreparedStatement ps4 = con.prepareStatement(sql4); PreparedStatement ps5 = con.prepareStatement(sql5)) { @@ -91,38 +89,40 @@ double lat = rset1.getDouble("lat"); double lon = rset1.getDouble("lon"); int score = rset1.getInt("score"); - int area = 0; - - // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 - RectArea rect = new RectArea(lat, lon, NEER); // 100m 四方 - ps2.setDouble(1, rect.minlat); - ps2.setDouble(2, rect.maxlat); - ps2.setDouble(3, rect.minlon); - ps2.setDouble(4, rect.maxlon); - try (ResultSet rset2 = ps2.executeQuery()) { - String idref = null; - double distance = 999999.9D; - while (rset2.next()) { - double lat2 = rset2.getDouble("lat"); - double lon2 = rset2.getDouble("lon"); - double dd = Japan.distanceKm(lat,lat2,lon,lon2); - if (dd < distance) { - distance = dd; - idref = rset2.getString("idref"); - area = rset2.getInt("area"); + + try (PreparedStatement ps2 = con.prepareStatement(sql2)) { + // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 + RectArea rect = new RectArea(lat, lon, NEER); // 100m 四方 + ps2.setDouble(1, rect.minlat); + ps2.setDouble(2, rect.maxlat); + ps2.setDouble(3, rect.minlon); + ps2.setDouble(4, rect.maxlon); + try (ResultSet rset2 = ps2.executeQuery()) { + String idref = null; + double distance = 999999.9D; + while (rset2.next()) { + double lat2 = rset2.getDouble("lat"); + double lon2 = rset2.getDouble("lon"); + double dd = Japan.distanceKm(lat,lat2,lon,lon2); + if (dd < distance) { + distance = dd; + idref = rset2.getString("idref"); + area = rset2.getInt("area"); + } } - } - if (idref != null) { - System.out.println(sql3); - System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1="+ score +" WHERE idref="+ idref +" and area=" + area); - ps3.setInt(1, score); - ps3.setString(2, idref); - ps3.setInt(3, area); - ps3.executeUpdate(); - } - else { - System.out.println("NOT FOUND! idref="+ osmid); + if (idref != null) { + try (PreparedStatement ps3 = con.prepareStatement(sql3)) { + System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1="+ score +" WHERE idref="+ idref +" and area=" + area); + ps3.setInt(1, score); + ps3.setString(2, idref); + ps3.setInt(3, area); + ps3.executeUpdate(); + } + } + else { + System.out.println("NOT FOUND! idref="+ osmid); + } } } }