diff --git a/src/osm/jp/coverage/busstop/DbExistBusstop.java b/src/osm/jp/coverage/busstop/DbExistBusstop.java index 0c9f915..23c34ee 100644 --- a/src/osm/jp/coverage/busstop/DbExistBusstop.java +++ b/src/osm/jp/coverage/busstop/DbExistBusstop.java @@ -1,21 +1,17 @@ package osm.jp.coverage.busstop; + import osm.jp.api.HttpPOST; import osm.jp.api.Japan; import javax.xml.parsers.*; import javax.xml.transform.TransformerException; - -import org.w3c.dom.*; import org.xml.sax.*; import java.io.*; import java.net.MalformedURLException; import java.net.ProtocolException; import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.Statement; import java.text.SimpleDateFormat; import jp.co.areaweb.tools.database.*; @@ -26,7 +22,6 @@ String urlStr = ""; public static final boolean DB_INIT = false; - public static final String TABLE_NAME = "BUSSTOP_EXIST"; public static boolean DROP = false; // 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域 @@ -67,12 +62,9 @@ try { conHsql = DatabaseTool.openDb("database"); if (DROP) { - DbBusstop.drop(conHsql, TABLE_NAME); + DbBusstop.drop(conHsql, HttpPOST.TABLE_NAME); } - create(conHsql); - try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) { - ps.executeUpdate(); - } + HttpPOST.create(conHsql); /** * 既存のOSMバス停を読み込む @@ -80,20 +72,6 @@ * 「HSQLDB.BUSSTOP_EXIST」にSTOREする */ getJapanCapabilities(conHsql); - - /** - * 既存のOSMバス停を読み込む - * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、 - * 「HSQLDB.BUSSTOP_EXIST」にSTOREする - */ - /* - if (DbExistBusstop.update && !DbExistBusstop.noget) { - DbExistBusstop.initDb(conHsql); - File existingFile = new File(HttpPOST.EXIST_FILE); - getJapanCapabilities(conHsql, existingFile); - } - - */ } finally { if (conHsql != null) { @@ -140,380 +118,7 @@ queryText.append(");"); queryText.append("out;"); HttpPOST.getQuery(queryText.toString()); - readExistingFile(conHsql); + HttpPOST.readExistingFile(conHsql); } } - /* - public static void getJapanCapabilities(Connection con, File oFile) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { - for (Japan all1 : Japan.all) { - HttpPOST.getCapabilities("highway", "bus_stop", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - HttpPOST.getCapabilities("highway", "disused:bus_stop", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - HttpPOST.getCapabilities("amenity", "bus_station", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - HttpPOST.getCapabilities("public_transport", "platform", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - HttpPOST.getCapabilities("public_transport", "stop_position", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - } - } - */ - - - /** - * 個別の都道府県「GMLディレクトリ」を処理 - * - * @param con - * @param gmldir - * @param areacode = Integer.parseInt(gmldir.getName().substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)); - * @throws SQLException - * @throws FileNotFoundException - * @throws ClassNotFoundException - * @throws IOException - * @throws ParserConfigurationException - * @throws SAXException - * @throws TransformerException - */ - public DbExistBusstop(Connection con, File gmldir, int areacode) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - BufferedWriter ow = null; - ToPostgis postgis = null; - Connection conPost = null; - - File outFile = new File(gmldir, "update.sql"); - ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), "UTF-8")); - - if (DbExistBusstop.update) { - conPost = DatabaseTool.openDb("postgis"); - } - - File[] files = gmldir.listFiles(); - try { - for (File iFile : files) { - /* - // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。 - if (!checkFile(iFile, areacode)) { - continue; - } - - //Busstop.clearDb(con); - if (!Busstop.update) { - //inputFile(con, iFile, areacode); - } - */ - if (true) { - - PreparedStatement ps1; - if (DbExistBusstop.update) { - ps1 = conPost.prepareStatement("SELECT gid,name,ST_Y(geom) lat,ST_X(geom) lon,fixed FROM t_busstop WHERE area=?"); - } - else { - ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop WHERE area=?"); - } - try (PreparedStatement ps2 = con.prepareStatement("SELECT SUM(score) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)")) { - PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?"); - PreparedStatement ps4 = con.prepareStatement("SELECT SUM(score) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (name = ?)"); - ps1.setInt(1, areacode); - ResultSet rset1 = ps1.executeQuery(); - while (rset1.next()) { - String idref = rset1.getString(1); - String name = rset1.getString("name"); - Double lat = rset1.getDouble("lat"); - Double lon = rset1.getDouble("lon"); - int fixed = rset1.getInt("fixed"); - - // 指定の緯度経度を中心とする半径150x2m四方の矩形領域 - System.out.print(idref + "("+ name + ") ...."); - RectArea rect = new RectArea(lat, lon, NEER); // 300m 四方 - 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()) { - if (rset2.next()) { - int score = rset2.getInt(1); - if (score > 0) { - System.out.println("."+ score); - if (DbExistBusstop.update) { - if (fixed != score) { - String osm_node = "UPDATE t_busstop SET fixed="+ score +" WHERE gid="+ idref +";"; - System.out.println(osm_node); - ow.write(osm_node); - ow.newLine(); - } - } - else { - ps3.setInt(1, score); - ps3.setString(2, idref); - ps3.executeUpdate(); - } - } - else { - // 指定の緯度経度を中心とする半径150x4m四方の矩形領域 - System.out.print("***"); - rect = new RectArea(lat, lon, NEER*2); // 600m 四方 - ps4.setDouble(1, rect.minlat); - ps4.setDouble(2, rect.maxlat); - ps4.setDouble(3, rect.minlon); - ps4.setDouble(4, rect.maxlon); - ps4.setString(5, name); - try (ResultSet rset4 = ps4.executeQuery()) { - if (rset4.next()) { - score = rset4.getInt(1); - System.out.println(".."+ score); - if (score != fixed) { - if (DbExistBusstop.update) { - String osm_node = "UPDATE t_busstop SET fixed="+ score +" WHERE gid="+ idref +";"; - System.out.println(osm_node); - ow.write(osm_node); - ow.newLine(); - } - else { - ps3.setInt(1, score); - ps3.setString(2, idref); - ps3.executeUpdate(); - } - } - } - } - } - } - } - } - rset1.close(); - ps3.close(); - } - ps1.close(); - } - - // ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する - if (!DbExistBusstop.update) { - postgis = new ToPostgis(gmldir); - postgis.outputDb(con); - postgis.close(); - } - } - } - finally { - if (conPost != null) { - DatabaseTool.closeDb(conPost); - } - ow.flush(); - ow.close(); - } - } - - static String[] shiftArgs(String[] args) { - String[] values = new String[args.length - 1]; - for (int i=1; i < args.length; i++) { - values[i - 1] = args[i]; - } - return values; - } - - public static void readExistingFile (Connection con) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; - - DocumentBuilderFactory factory; - DocumentBuilder builder; - Node root; - - iCounter = 0; - factory = DocumentBuilderFactory.newInstance(); - builder = factory.newDocumentBuilder(); - factory.setIgnoringElementContentWhitespace(true); - factory.setIgnoringComments(true); - factory.setValidating(true); - File existingFile = new File(HttpPOST.EXIST_FILE); - root = builder.parse(existingFile); - - iCounter += readExistingNodes(con, root); - System.out.println("既存バス停数["+ iCounter +"]"); - } - - static int readExistingNodes(Connection con, Node node) throws IOException, SQLException { - int iCounter = 0; - - NodeList nodes = node.getChildNodes(); - for (int i = 0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - if (node2.getNodeName().equals("node")) { - iCounter++; - importExistingNode(con, node2); - } - else { - iCounter += readExistingNodes(con, node2); - } - } - return iCounter; - } - - static void importExistingNode(Connection con, Node node) throws IOException, SQLException { - String idrefStr = ""; - String latStr = ""; - String lonStr = ""; - String nameStr = ""; - String fixmeStr = ""; - int score = 0; - - NamedNodeMap nodeMap = node.getAttributes(); - if (null != nodeMap) { - for (int j=0; j < nodeMap.getLength(); j++) { - switch (nodeMap.item(j).getNodeName()) { - case "id": - idrefStr = nodeMap.item(j).getNodeValue(); - break; - case "lat": - latStr = nodeMap.item(j).getNodeValue(); - break; - case "lon": - lonStr = nodeMap.item(j).getNodeValue(); - break; - default: - break; - } - } - - NodeList nodes = node.getChildNodes(); - for (int i = 0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - if (node2.getNodeName().equals("tag")) { - NamedNodeMap nodeMap2 = node2.getAttributes(); - if (null != nodeMap2) { - String key = null; - String value = null; - for (int j=0; j < nodeMap2.getLength(); j++) { - if (nodeMap2.item(j).getNodeName().equals("k")) { - key = nodeMap2.item(j).getNodeValue(); - } - else if (nodeMap2.item(j).getNodeName().equals("v")) { - value = nodeMap2.item(j).getNodeValue(); - } - } - - if ((key != null) && key.toLowerCase().equals("name") && (value != null)) { - nameStr = value; - } - if ((key != null) && key.toLowerCase().equals("fixme") && (value != null)) { - fixmeStr = value; - } - } - } - } - - score = 50; - if (nameStr.equals("")) { - score = 1; - } - if (!fixmeStr.equals("")) { - score = 1; - } - - // idref と nameStr をデータベースに格納する - try (PreparedStatement ps1 = con.prepareStatement("SELECT name,score FROM existing_data WHERE idref=?")) { - ps1.setString(1, idrefStr); - ResultSet rset1 = ps1.executeQuery(); - if (rset1.next()) { - int fixed = rset1.getInt("score"); - if (fixed < score) { - System.out.println("update existing_data : [id:"+ idrefStr +"] score="+ Integer.toString(score) +" "+ nameStr); - try (PreparedStatement ps5 = con.prepareStatement("UPDATE existing_data SET score=? WHERE idref=?")) { - ps5.setInt(1, score); - ps5.setString(2, idrefStr); - ps5.executeUpdate(); - } - } - } - else { - System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")["+ Integer.toString(score) +"]"+ nameStr); - try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name, score) VALUES (?,?,?,?,?)")) { - ps5.setString(1, idrefStr); - ps5.setDouble(2, Double.parseDouble(latStr)); - ps5.setDouble(3, Double.parseDouble(lonStr)); - ps5.setString(4, nameStr); - ps5.setInt(5, score); - ps5.executeUpdate(); - } - } - rset1.close(); - } - } - } - - /** - * - * @param conHsql - * @param conPost - * @param areacode - * @throws java.io.FileNotFoundException - * @throws java.lang.ClassNotFoundException - * @throws java.sql.SQLException - * @throws java.io.IOException - * @throws javax.xml.parsers.ParserConfigurationException - * @throws org.xml.sax.SAXException - */ - public static void updateFile (Connection conHsql, Connection conPost, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; - PreparedStatement ps2; - try (PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) LON, ST_Y(geom) LAT FROM t_busstop WHERE area=?")) { - ps2 = conHsql.prepareStatement("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES (?,?,?,?,?,?)"); - // idref と nameStr をデータベースに格納する - ps1.setInt(1, areacode); - ResultSet rset1 = ps1.executeQuery(); - while (rset1.next()) { - iCounter++; - int idref = rset1.getInt("gid"); - String nameStr = rset1.getString("name"); - int fixed = rset1.getInt("fixed"); - double lat = rset1.getDouble(4); - double lon = rset1.getDouble(5); - - ps2.setInt(1, idref); - ps2.setString(2, nameStr); - ps2.setInt(3, fixed); - ps2.setInt(4, areacode); - ps2.setDouble(5, lon); - ps2.setDouble(6, lat); - System.out.println("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES ("+ idref +",'"+ nameStr +"',"+ fixed +","+ areacode +","+ lat +","+ lon +")"); - ps2.executeUpdate(); - } - rset1.close(); - } - ps2.close(); - - System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); - } - - /** - * 'table.BUSSTOP_EXIST'を新規に作る - * @param conHsql - * @throws SQLException - */ - public static void create(Connection conHsql) throws SQLException { - String createSt = "CREATE TABLE BUSSTOP_EXIST (osmid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(osmid, lat, lon));"; - DbBusstop.create(conHsql, createSt); - } - - public static void clearDb(Connection con) throws SQLException { - try (Statement stmt = con.createStatement()) { - long count = stmt.executeUpdate("delete from bus_stop"); - System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。"); - - count = stmt.executeUpdate("delete from existing_data"); - System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。"); - } - } - - public static void initDb(Connection con) throws SQLException { - // 'table.BUS_STOP'を新規に作る - DbBusstop.create(con); - } - - - - - - - - } \ No newline at end of file