diff --git a/src/ImportGML_FUEL_update.sh b/src/ImportGML_FUEL_update.sh index 4cfbc95..86647cd 100644 --- a/src/ImportGML_FUEL_update.sh +++ b/src/ImportGML_FUEL_update.sh @@ -1,6 +1,6 @@ rm -r database mkdir database -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbFuel -update +java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbFuel -update -drop java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbExist java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Fuel java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToPostgis diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index c9cff87..084d9ba 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -72,9 +72,9 @@ * @throws TransformerException */ 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)"); + try ( PreparedStatement ps2 = con.prepareStatement("SELECT idref,lat,lon,fixed, area 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=? and area=?"); 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)" )) { @@ -88,6 +88,7 @@ double lon = rset1.getDouble("lon"); int score = rset1.getInt("score"); String idref = null; + int area = 0; int fixed = 0; // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 @@ -105,13 +106,15 @@ if (dd < distance) { distance = dd; idref = rset2.getString("idref"); + area = rset2.getInt("area"); } } } if (idref != null) { - System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1="+ score +" WHERE idref="+ idref); + 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 { diff --git a/src/osm/jp/coverage/fuel/ToPostgis.java b/src/osm/jp/coverage/fuel/ToPostgis.java index ff35238..51fd4e0 100644 --- a/src/osm/jp/coverage/fuel/ToPostgis.java +++ b/src/osm/jp/coverage/fuel/ToPostgis.java @@ -78,15 +78,15 @@ } } - try ( PreparedStatement ps1 = conHsql.prepareStatement("SELECT idref,fixed FROM FUEL where up=1"); - PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_FUEL set fixed=? WHERE idref=?")) { + try ( PreparedStatement ps1 = conHsql.prepareStatement("SELECT gid,fixed FROM FUEL where up=1"); + PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_FUEL set fixed=? WHERE gid=?")) { try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { - String idref = rset1.getString("idref"); + long gid = rset1.getLong("gid"); int fixed = rset1.getInt("fixed"); ps2.setInt(1, fixed); - ps2.setString(2, idref); - System.out.println("UPDATE t_FUEL set fixed="+ fixed +" WHERE idstr="+ idref); + ps2.setLong(2, gid); + System.out.println("UPDATE t_FUEL set fixed="+ fixed +" WHERE gid="+ gid); ps2.executeUpdate(); } }