diff --git a/src/osm/jp/coverage/postoffice/Postoffice.java b/src/osm/jp/coverage/postoffice/Postoffice.java new file mode 100644 index 0000000..9194e79 --- /dev/null +++ b/src/osm/jp/coverage/postoffice/Postoffice.java @@ -0,0 +1,129 @@ +package osm.jp.coverage.postoffice; + +import osm.jp.api.RectArea; + +import javax.xml.parsers.*; +import javax.xml.transform.TransformerException; + +import org.xml.sax.*; + +import java.io.*; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.SimpleDateFormat; +import jp.co.areaweb.tools.database.*; +import osm.jp.api.HttpPOST; +import osm.jp.api.Japan; + +public class Postoffice { + + String filter = ""; + String urlStr = ""; + + public static final boolean DB_INIT = false; + + // 近くのノードを探す範囲(KJS2を中心としたNEER×2(m)四方の領域 + static final int NEER = 200; // 200m x 2 = 400m四方 + + public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); + + /** + * メイン + * + * java -cp .:osmCoverage.jar:hsqldb_2.2.9.jar osm.jp.coverage.opstoffice.PostOffice [option] + * + * @param args + * @throws IOException + * @throws SQLException + * @throws ClassNotFoundException + * @throws FileNotFoundException + * @throws TransformerException + * @throws SAXException + * @throws ParserConfigurationException + */ + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException + { + Connection con = DatabaseTool.openDb("database"); + try { + new Postoffice(con); + } + finally { + DatabaseTool.closeDb(con); + } + } + + /** + * HSQL_DB.OSM_EXITのscoreを集計して、HSQL_DB.POSTOFFICEのfixedに反映させる + * + * @param con + * @throws SQLException + * @throws FileNotFoundException + * @throws ClassNotFoundException + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + * @throws TransformerException + */ + public Postoffice(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { + String sql4 = "UPDATE "+ DbPostoffice.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 "+ DbPostoffice.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)"; + String sql3 = "UPDATE "+ DbPostoffice.TABLE_NAME +" SET fixed1=? WHERE idref=? and area=?"; + String sql5 = "UPDATE "+ DbPostoffice.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(sql4); + ps4.executeUpdate(); + + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + String osmid = rset1.getString("idref"); + double lat = rset1.getDouble("lat"); + double lon = rset1.getDouble("lon"); + int score = rset1.getInt("score"); + String idref = null; + int area = 0; + + // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 + RectArea rect = new RectArea(lat, lon, NEER); // 400m 四方 + 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()) { + 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("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); + } + } + } + + System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"); + ps5.executeUpdate(); + } + } +} \ No newline at end of file