diff --git a/.gitignore b/.gitignore index c32a11a..558220c 100644 --- a/.gitignore +++ b/.gitignore @@ -31,5 +31,4 @@ [Tt]humbs.db **/nbproject/private/ /build/** -!/build/ /dist/** \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java index 53e615c..5ac21d5 100644 --- a/src/osm/jp/coverage/fuel/DbExist.java +++ b/src/osm/jp/coverage/fuel/DbExist.java @@ -7,20 +7,12 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.SQLIntegrityConstraintViolationException; import javax.xml.parsers.ParserConfigurationException; import jp.co.areaweb.tools.database.*; -import org.hsqldb.HsqlException; -import org.w3c.dom.NamedNodeMap; -import org.w3c.dom.Node; -import org.w3c.dom.NodeList; import org.xml.sax.SAXException; import osm.jp.api.HttpPOST; import osm.jp.api.Japan; -import osm.jp.api.OsmnodeNd; -import osm.jp.api.OsmnodeTag; -import osm.jp.api.Position; public class DbExist extends HttpPOST { diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index bd97d3a..93501ee 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -14,6 +14,7 @@ import java.text.SimpleDateFormat; import jp.co.areaweb.tools.database.*; import osm.jp.api.HttpPOST; +import osm.jp.api.Japan; public class Fuel { @@ -73,13 +74,18 @@ * @throws TransformerException */ public Fuel(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - 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 "+ HttpPOST.TABLE_NAME); - 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)" )) + String sql4 = "UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=0"; + String sql1 = "SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME; + 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); + PreparedStatement ps4 = con.prepareStatement(sql4); + PreparedStatement ps5 = con.prepareStatement(sql5)) { - System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=0"); + System.out.println(sql4); ps4.executeUpdate(); try (ResultSet rset1 = ps1.executeQuery()) { @@ -102,7 +108,7 @@ while (rset2.next()) { double lat2 = rset2.getDouble("lat"); double lon2 = rset2.getDouble("lon"); - double dd = distance(lat,lat2,lon,lon2); + double dd = Japan.distanceKm(lat,lat2,lon,lon2); if (dd < distance) { distance = dd; idref = rset2.getString("idref"); @@ -125,15 +131,6 @@ System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"); ps5.executeUpdate(); - } } - - public static final double ONE_KM_LAT = 0.009013372D; - public static final double ONE_KM_LON = 0.010966404D; - static double distance(double lat1, double lon1, double lat2, double lon2) { - double dlat = Math.abs(lat1 - lat2) / ONE_KM_LAT / 1000D; - double dlon = Math.abs(lon1 - lon2) / ONE_KM_LON / 1000D; - return Math.sqrt(dlat*dlat + dlon*dlon); - } } \ No newline at end of file