diff --git a/src/osm/jp/coverage/fuel/DbFuel.java b/src/osm/jp/coverage/fuel/DbFuel.java index ed35c05..82ace0b 100644 --- a/src/osm/jp/coverage/fuel/DbFuel.java +++ b/src/osm/jp/coverage/fuel/DbFuel.java @@ -20,6 +20,7 @@ public class DbFuel { public static final String TABLE_NAME = "FUEL"; public static boolean UPDATE = false; + public static boolean DROP = false; /** メイン * @param args @@ -35,6 +36,9 @@ if (arg.toUpperCase().equals("-UPDATE")) { UPDATE = true; } + if (arg.toUpperCase().equals("-DROP")) { + DROP = true; + } } // HSQLディレクトリがなければエラー @@ -48,6 +52,12 @@ try { // DB.tableを作成 con = DatabaseTool.openDb("database"); + if (DROP) { + dropTable(con); + if (UPDATE) { + create(con); + } + } if (!UPDATE) { create(con); } @@ -83,6 +93,14 @@ } } } + + public static void dropTable (Connection conHsql) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + String sqlStr = "DROP TABLE FUEL"; + try (PreparedStatement ps = conHsql.prepareStatement(sqlStr)) { + System.out.println(sqlStr); + ps.executeUpdate(); + } + } /** * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM FUEL" @@ -108,7 +126,7 @@ double lat = rset1.getDouble("lat"); double lon = rset1.getDouble("lon"); - String sqlStr = "INSERT INTO FUEL (gid,lat,lon,fixed,idref,area,up,fixed1) VALUES(?,?,?,?,?,?,2,0)"; + String sqlStr = "INSERT INTO FUEL (gid,lat,lon,fixed,idref,area,up,fixed1) VALUES(?,?,?,?,?,?,0,0)"; try (PreparedStatement ps = conHsql.prepareStatement(sqlStr)) { ps.setLong(1, gid); ps.setString(2, BigDecimal.valueOf(lat).toPlainString()); diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index 8d3b1cb..c9cff87 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -22,7 +22,7 @@ public static final boolean DB_INIT = false; // 近くのノードを探す範囲(KJS2を中心としたNEER×2(m)四方の領域 - static final int NEER = 100; // 100m + static final int NEER = 200; // 200m x 2 = 400m四方 public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); @@ -74,11 +74,16 @@ public Fuel(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { try ( PreparedStatement ps2 = con.prepareStatement("SELECT idref,lat,lon,fixed FROM "+ DbFuel.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)"); PreparedStatement ps1 = con.prepareStatement("SELECT idref,lat,lon,score FROM "+ DbExist.TABLE_NAME); - PreparedStatement ps3 = con.prepareStatement("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=? WHERE idref=?");) + PreparedStatement ps3 = con.prepareStatement("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=? WHERE idref=?"); + PreparedStatement ps4 = con.prepareStatement("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=0"); + PreparedStatement ps5 = con.prepareStatement("UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)" )) { + System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=0"); + ps4.executeUpdate(); + try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { - //String osmid = rset1.getString("idref"); + String osmid = rset1.getString("idref"); double lat = rset1.getDouble("lat"); double lon = rset1.getDouble("lon"); int score = rset1.getInt("score"); @@ -86,7 +91,7 @@ int fixed = 0; // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 - RectArea rect = new RectArea(lat, lon, NEER); // 200m 四方 + RectArea rect = new RectArea(lat, lon, NEER); // 300m 四方 ps2.setDouble(1, rect.minlat); ps2.setDouble(2, rect.maxlat); ps2.setDouble(3, rect.minlon); @@ -110,10 +115,14 @@ ps3.executeUpdate(); } else { - System.out.println("NOT FOUND! idref="+ idref); + System.out.println("NOT FOUND! idref="+ osmid); } } } + + System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"); + ps5.executeUpdate(); + } }