diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index fc3721b..7aac330 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -1,5 +1,6 @@ package osm.jp.api; +import hayashi.yuu.tools.json.JsonTool; import java.io.*; import java.sql.Connection; import java.sql.PreparedStatement; @@ -21,6 +22,7 @@ import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.xml.sax.SAXException; +import osm.jp.coverage.busstop.DbBusstop; public abstract class Osmdb { public String TABLE_NAME = "EXIST_osm"; @@ -129,8 +131,7 @@ } - public int importExistingNode(Connection osmdb, ResultSet rset, int point) throws IOException, SQLException { - + public int importExistingNode(Connection osmdb, ResultSet rset, int point, boolean removed) throws IOException, SQLException { String osmidStr = rset.getString("osm_id"); String latStr = rset.getString("lat"); String lonStr = rset.getString("lon"); @@ -184,7 +185,7 @@ } } - System.out.println(tags); + //System.out.println(tags); } } } @@ -199,15 +200,26 @@ if (score > 0) { // idref と brandStr をデータベースに格納する - System.out.println("INSERT INTO "+ tableName +" (idref,lat,lon,score,name) VALUES ("+ osmidStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N"); + System.out.println( + "INSERT INTO "+ tableName + + " (idref,lat,lon,score,name,removed) VALUES (" + + osmidStr +"," + + latStr +"," + + lonStr +"," + + Integer.toString(score) +"," + + "'"+ nameStr +"'," + + "false" + + ")N" + ); try (PreparedStatement ps5 = hsqldb.prepareStatement( - "INSERT INTO "+ tableName +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)" + "INSERT INTO "+ tableName +" (idref,lat,lon,score,name,removed) VALUES (?,?,?,?,?,?)" )) { ps5.setString(1, osmidStr); ps5.setDouble(2, Double.parseDouble(latStr)); ps5.setDouble(3, Double.parseDouble(lonStr)); ps5.setInt(4, score); ps5.setString(5, nameStr); + ps5.setBoolean(6, removed); ps5.executeUpdate(); return 1; } @@ -228,14 +240,12 @@ * * @param sqlWhere POI条件 * @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name' + * @param removed * @throws Exception エラー */ - public void readExisting(String sqlWhere, int point) throws Exception { - Connection osmdb = null; + public void readExisting(String sqlWhere, int point, boolean removed) throws Exception { long counter = 0L; - try { - osmdb = DatabaseTool.openDb("osmdb"); - + try (Connection osmdb = DatabaseTool.openDb("osmdb")) { String sqlSelect = "osm_id," + "brand," + "disused," @@ -243,35 +253,31 @@ + "ST_Y(ST_Transform(way,4326)) as lat," + "ST_X(ST_Transform(way,4326)) as lon"; String sqlNode = String.format( - "SELECT %s FROM planet_osm_point %s", sqlSelect, sqlWhere + "SELECT %s FROM planet_osm_point %s", + sqlSelect, + sqlWhere ); - System.out.println(sqlNode); - PreparedStatement ps1 = osmdb.prepareStatement(sqlNode); try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { - counter += importExistingNode(osmdb, rset1, point); + counter += importExistingNode(osmdb, rset1, point, removed); } } - sqlSelect = "osm_id,brand,disused,name,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon"; String sqlArea = String.format( - "SELECT %s FROM planet_osm_polygon %s", sqlSelect, sqlWhere + "SELECT %s FROM planet_osm_polygon %s", + "osm_id,brand,disused,name,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon", + sqlWhere ); - System.out.println(sqlArea); - PreparedStatement ps2 = osmdb.prepareStatement(sqlArea); try (ResultSet rset2 = ps2.executeQuery()) { while (rset2.next()) { - counter += importExistingNode(osmdb, rset2, point); + counter += importExistingNode(osmdb, rset2, point, removed); } } System.out.println("Exists Node count = " + counter); } - finally { - DatabaseTool.closeDb(osmdb); - } } /** @@ -295,12 +301,16 @@ * @throws Exception */ public void readExistingSub(String kStr, String vStr, int point) throws Exception { + readExistingSub(kStr, vStr, point, true); + } + + public void readExistingSub(String kStr, String vStr, int point, boolean removed) throws Exception { JsonBuilderFactory factory = Json.createBuilderFactory(null); JsonObjectBuilder builder = factory.createObjectBuilder(); builder.add("k",kStr); builder.add("v", vStr); JsonObject tag = builder.build(); - readExistingSub(tag, point); + readExistingSub(tag, point, removed); } /** @@ -310,11 +320,19 @@ * @throws Exception */ public void readExistingSub(JsonObject tag,int point) throws Exception { - boolean removed = true; - Connection osmdb = null; + readExistingSub(tag, point, true); + } + + /** + * + * @param tag {k: string, v: string} + * @param point + * @param removed + * @throws Exception + */ + public void readExistingSub(JsonObject tag,int point, boolean removed) throws Exception { long counter = 0L; - try { - osmdb = DatabaseTool.openDb("osmdb"); + try (Connection osmdb = DatabaseTool.openDb("osmdb")) { String sqlSelect = "SELECT tags,id,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon"; String sqlWhere = String.format( "WHERE (osm_id=id AND '%s'=ANY(tags) AND '%s'=ANY(tags))", @@ -340,9 +358,6 @@ } System.out.println("Exists Node count = " + counter); } - finally { - DatabaseTool.closeDb(osmdb); - } } /** @@ -352,11 +367,19 @@ * @throws Exception */ public void readExistingSub(JsonArray array,int point) throws Exception { - boolean removed = true; - Connection osmdb = null; + readExistingSub(array,point, true); + } + + /** + * + * @param array tags: [{k: string, v: string}] + * @param point + * @param removed + * @throws Exception + */ + public void readExistingSub(JsonArray array,int point, boolean removed) throws Exception { long counter = 0L; - try { - osmdb = DatabaseTool.openDb("osmdb"); + try (Connection osmdb = DatabaseTool.openDb("osmdb")) { String sqlSelect = "SELECT " + "tags," + "id," @@ -392,9 +415,6 @@ } System.out.println("Exists Node count = " + counter); } - finally { - DatabaseTool.closeDb(osmdb); - } } int score(int point, String tags) { @@ -486,14 +506,10 @@ */ public int insertExistingNode(String idref, double lat, double lon, int score, String name, boolean removed) throws IOException, SQLException { // idref と brandStr をデータベースに格納する - String sql = String.format( - "INSERT INTO %s (idref,lat,lon,score,name) VALUES (%s,%2.7f,%3.7f,%d,%s)", - tableName, - idref, lat, lon, score, name - ); - System.out.println(sql); + /* + */ - sql = String.format("DELETE FROM %s WHERE idref=?", tableName); + String sql = String.format("DELETE FROM %s WHERE idref=?", tableName); try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) { ps5.setString(1, idref); ps5.executeUpdate(); @@ -506,6 +522,13 @@ } sql = String.format( + "INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (%s,%2.7f,%3.7f,%d,%s,%s)", + tableName, + idref, lat, lon, score, name, (removed ? "true":"false") + ); + System.out.println(sql); + + sql = String.format( "INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (?,?,?,?,?,?)", tableName ); @@ -528,6 +551,96 @@ return 0; } + /** + * REMOVEDファイルからデータを揉み込む + * + * @param revovedFile + * @throws FileNotFoundException + * @throws IOException + * @throws SQLException + */ + public void loadRemoved(File revovedFile) throws FileNotFoundException, IOException, SQLException { + if (revovedFile == null) { + throw new FileNotFoundException(); + } + if (!revovedFile.exists()) { + throw new FileNotFoundException(); + } + if (!revovedFile.isFile()) { + throw new FileNotFoundException(); + } + + try (PreparedStatement ps = hsqldb.prepareStatement( + "UPDATE " + + tableName + + " SET idref=?,removed=true,fixed1=100" + + " WHERE gmlid=? AND area=?" + )) { + LineNumberReader reader = new LineNumberReader( + new InputStreamReader(new FileInputStream(revovedFile)) + ); + while (reader.readLine() != null) { + String lineStr = reader.readLine(); + if (lineStr == null) { + continue; + } + if (lineStr.trim().length() < 3) { + continue; + } + + JsonObject json = JsonTool.parse(lineStr); + String gmlid = json.getString("gmlid"); + String idref = json.getString("idref"); + int area = json.getInt("area"); + + ps.setString(1, idref); + ps.setString(2, gmlid); + ps.setInt(3, area); + ps.executeUpdate(); + } + } + } + + /** + * removedデータをファイルに出力する + * + * @param removedFile + * @throws SQLException + * @throws java.io.IOException + */ + public void outputRemoved(File removedFile) throws SQLException, IOException { + String whereStr = "WHERE (removed=?)"; + String fromStr = "FROM "+ DbBusstop.TABLE_NAME; + String sortStr = "ORDER BY area,gmlid"; + String sql = String.format("SELECT * %s %s %s", fromStr, whereStr, sortStr); + + removedFile.deleteOnExit(); + try (FileWriter fw = new FileWriter(removedFile); + PreparedStatement ps1 = hsqldb.prepareStatement(sql)) + { + System.out.println(sql); + ps1.setBoolean(1, true); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + String gmlid = rset1.getString("gmlid"); + int area = rset1.getInt("area"); + String idref = rset1.getString("idref"); + + JsonObjectBuilder jsonBuilder = Json.createObjectBuilder(); + jsonBuilder.add("gmlid", gmlid); + jsonBuilder.add("idref", idref); + jsonBuilder.add("area", area); + JsonObject value = jsonBuilder.build(); + String line = value.toString(); + + fw.write(line); + fw.write("\n"); + fw.flush(); + } + } + } + } + boolean isNull(String vstr) { if (vstr == null) { return true; diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index f7127cd..7a3a11f 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -11,8 +11,6 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; -import java.util.logging.Level; -import java.util.logging.Logger; import jp.co.areaweb.tools.database.*; import osm.jp.api.Japan; @@ -28,8 +26,9 @@ * OSM_EXISTのscoreを集計して,BUSSTOPのfixedをUPDATE * * @param args + * @throws java.lang.Exception * */ - public static void main(String[] args) + public static void main(String[] args) throws Exception { // HSQLディレクトリがなければ作る File dbdir = new File("database"); @@ -37,39 +36,8 @@ dbdir.mkdir(); } - Connection con; - try { - con = DatabaseTool.openDb("database"); - try { - new Busstop(con); - } - catch (SQLException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); - ex.printStackTrace(); - } catch (ClassNotFoundException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); - ex.printStackTrace(); - } catch (IOException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); - ex.printStackTrace(); - } catch (ParserConfigurationException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); - ex.printStackTrace(); - } catch (SAXException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); - ex.printStackTrace(); - } catch (TransformerException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); - ex.printStackTrace(); - } finally { - DatabaseTool.closeDb(con); - } - } catch (ClassNotFoundException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); - } catch (SQLException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); - } catch (IOException ex) { - Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + try (Connection hsql = DatabaseTool.openDb("database")) { + new Busstop(hsql); } } @@ -86,20 +54,21 @@ * @throws TransformerException */ public Busstop(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ DbExistBusstop.EXIST_TABLE_NAME; + String sqlStr4 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"; + try (PreparedStatement ps4 = con.prepareStatement(sqlStr4)) { + System.out.println(sqlStr4); + ps4.executeUpdate(); + } + + String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ DbExistBusstop.TABLE_NAME; String sqlStr2 = "SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"; String sqlStr3 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?"; - String sqlStr4 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"; String sqlStr5 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; try ( PreparedStatement ps2 = con.prepareStatement(sqlStr2); PreparedStatement ps1 = con.prepareStatement(sqlStr1); PreparedStatement ps3 = con.prepareStatement(sqlStr3); - PreparedStatement ps4 = con.prepareStatement(sqlStr4); PreparedStatement ps5 = con.prepareStatement(sqlStr5)) { - System.out.println(sqlStr4); - ps4.executeUpdate(); - System.out.println(sqlStr1); try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index f32f7a3..8cc5da9 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -15,22 +15,39 @@ import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.xml.sax.SAXException; -import osm.jp.api.HttpPOST; +import osm.jp.api.Db; +import osm.jp.api.Osmdb; -public class DbBusstop { +public class DbBusstop extends Osmdb { + @SuppressWarnings("FieldNameHidesFieldInSuperclass") public static final String TABLE_NAME = "busstop"; + public static final String CLASS_NAME = "DbBusstop"; - File inputFile; - String filter = ""; - int iCounter = 0; - String urlStr = ""; - Connection con; - String timeStampStr = null; - File dir = null; + /** + * コマンド + */ + static void commandHelp() { + System.out.println("[Command Call]"); + System.out.println("> "+ CLASS_NAME +" -INIT"); + System.out.println(" Initillaize detabase 'table."+ TABLE_NAME +"'."); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -IMPORT "); + System.out.println(" Import from GML files."); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -REMOVED "); + System.out.println(" Import REMOVED json.text file."); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -EXPORT"); + System.out.println(" 'table."+ TABLE_NAME +"'の内容をCSV形式にして標準出力に出力する"); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -OUTPUT "); + System.out.println(" removedデータをファイルに追記する"); + System.out.println(); + } /** メイン * 動作条件; HSQLDBのフォルダを削除した状態で実行すること。 - * フォルダを削除しないで実行する場合は「-DROP」オプションを使うこと + * * @param args * @throws IOException * @throws SQLException @@ -38,54 +55,131 @@ * @throws FileNotFoundException * @throws javax.xml.parsers.ParserConfigurationException * @throws org.xml.sax.SAXException */ - public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException + public static void main(String[] args) throws Exception { + if (args.length < 1) { + commandHelp(); + throw new Exception( + String.format("[ERROR] Illegal command call '%s'", CLASS_NAME) + ); + } + // HSQLディレクトリがなければエラー File dbdir = new File("database"); if (!dbdir.isDirectory()) { throw new FileNotFoundException("Directory 'database' is not found."); } - Connection conHsql = null; - try { - conHsql = DatabaseTool.openDb("database"); - HttpPOST httpPOST = new HttpPOST(conHsql, TABLE_NAME); - httpPOST.sql("DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); - create(conHsql); - - /** - * 都道府県ごとのGMLディレクトリの処理 - */ - int fcounter = 0; - File dir = new File("GML_BUSSTOP"); - for (File gmldir : dir.listFiles()) { - if (checkGMLdir(gmldir)) { - // GMLディレクトリを処理する - int areacode = Integer.parseInt(gmldir.getName().substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)); - File[] files = gmldir.listFiles(); - for (File iFile : files) { - // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。 - if (checkFile(iFile, areacode)) { - importBusstop(conHsql, iFile, areacode); - fcounter++; + try (Connection hsqldb = DatabaseTool.openDb("database")) { + switch (args[0]) { + case "-INIT": + { + // DB.tableを作成(初期化) + DbBusstop db = new DbBusstop(hsqldb); + db.dropTable(); + db.create(); + break; + } + case "-IMPORT": + { + // GMLからの読み込み + if (args.length < 2) { + throw new Exception( + "[ERROR] Illegal command call '"+ CLASS_NAME +" -IMPORT '" + ); } + DbBusstop db = new DbBusstop(hsqldb); + db.importGmlfiles(args[1]); + break; + } + case "-REMOVED": + { + // REMOVEDファイルを読み込み + if (args.length < 2) { + throw new Exception( + "[ERROR] Illegal command call '"+ CLASS_NAME +" -REMOVED '" + ); + } + DbBusstop db = new DbBusstop(hsqldb); + db.loadRemoved(new File(args[1])); + break; + } + case "-EXPORT": + { + // 'table.FUEL'の内容をCSV形式にして標準出力に出力する + DbBusstop db = new DbBusstop(hsqldb); + db.export(); + break; + } + case "-OUTPUT": + { + // REMOVEDファイルを読み込み + if (args.length < 2) { + throw new Exception( + "[ERROR] Illegal command call '"+ CLASS_NAME +" -OUTPUT '" + ); + } + DbBusstop db = new DbBusstop(hsqldb); + db.outputRemoved(new File(args[1])); + break; + } + default: + commandHelp(); + throw new Exception("[ERROR] Illegal command call '"+ CLASS_NAME +"'"); + } + } + } + + + /** + * コンストラクタ + * + * @param hsqldb + */ + public DbBusstop(Connection hsqldb) { + super(hsqldb, TABLE_NAME); + this.hsqldb = hsqldb; + } + + /** + * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する + * + * @param gmlDirectory + * @throws ClassNotFoundException + * @throws SQLException + * @throws IOException + * @throws FileNotFoundException + * @throws ParserConfigurationException + * @throws SAXException + */ + void importGmlfiles(String gmlDirectory) throws ClassNotFoundException, SQLException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + File gmlDir = new File(gmlDirectory); + if (!gmlDir.exists()) { + throw new FileNotFoundException(gmlDirectory); + } + if (!gmlDir.isDirectory()) { + throw new FileNotFoundException( + String.format("'%s' is not Directory", gmlDirectory) + ); + } + + for (File gmldir : gmlDir.listFiles()) { + if (checkGMLdir(gmldir)) { + // GMLディレクトリを処理する + int areacode = Integer.parseInt(gmldir.getName().substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)); + File[] files = gmldir.listFiles(); + for (File iFile : files) { + // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。 + if (checkFile(iFile, areacode)) { + importBusstop(iFile, areacode); } } } - System.out.println("["+ fcounter +"]つのGMLファイルをインポートしました。"); - - DbBusstop.export(conHsql); - } - finally { - if (conHsql != null) { - DatabaseTool.closeDb(conHsql); - } } } /** * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する - * @param conHsql * @param iFile * @param areacode * @throws FileNotFoundException @@ -95,10 +189,8 @@ * @throws ParserConfigurationException * @throws SAXException */ - public static void importBusstop (Connection conHsql, File iFile, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; - String timeStampStr = null; - + public void importBusstop (File iFile, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + int iCounter_ = 0; String iStr = iFile.getName(); DocumentBuilderFactory factory; @@ -112,8 +204,8 @@ factory.setValidating(true); root = builder.parse(iFile); - iCounter += showNodes(conHsql, root, iStr.substring(0, iStr.length() - 4), areacode); - System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); + iCounter_ += showNodes(hsqldb, root, iStr.substring(0, iStr.length() - 4), areacode); + System.out.println("("+ areacode +") バス停数["+ iCounter_ +"]"); } /** @@ -182,7 +274,10 @@ latStr = str4Ary[0]; lonStr = str4Ary[1]; - try (PreparedStatement ps6 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (lat,lon,fixed,fixed1,area,gmlid,up) VALUES (?,?,0,0,?,?,2)")) { + String sql = "INSERT INTO "+ TABLE_NAME + + " (lat,lon,fixed,fixed1,area,gmlid,up)" + + " VALUES (?,?,0,0,?,?,2)"; + try (PreparedStatement ps6 = con.prepareStatement(sql)) { double lat = Double.parseDouble(latStr); double lon = Double.parseDouble(lonStr); System.out.println("INSERT INTO "+ TABLE_NAME +" (lat,lon,fixed,fixed1,area,gmlid,up) VALUES ('"+ latStr +"','"+ lonStr +"',0,0,"+ areacode +",'"+ idStr +"',2)"); @@ -329,30 +424,40 @@ /** * 'table.BUSSTOP'を新規に作る * 既にテーブルが存在する時には何もしない - * @param conHsql * @throws SQLException */ - public static void create(Connection conHsql) throws SQLException { - String createSt; - HttpPOST httpPOST = new HttpPOST(conHsql, TABLE_NAME); + @Override + public void create() throws SQLException { // 'table.BUSSTOP'を新規に作る - createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));"; - httpPOST.sql(createSt); + String createSt = "CREATE TABLE "+ TABLE_NAME +" (" + + " gmlid VARCHAR(12) NOT NULL, name VARCHAR(128)," + + " idref VARCHAR(12), " + + " lat DOUBLE," + + " lon DOUBLE," + + " fixed INT," + + " fixed1 INT," + + " area INT," + + " ifile VARCHAR(128)," + + " up INT," + + " removed BOOLEAN DEFAULT FALSE NOT NULL," + + " CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area)" + + ");"; + Db.updateSQL(hsqldb, createSt); createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);"; - httpPOST.sql(createSt); + Db.updateSQL(hsqldb, createSt); } /** * 'table.BUSSTOP'の内容をCSV形式にして標準出力に出力する - * @param con */ - public static void export(Connection con) { + @Override + public void export() { try { - System.out.println("TABLE: BUSSTOP"); - System.out.println("\"gmlid\",\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\""); - PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,name,lat,lon,fixed,ifile FROM "+ DbBusstop.TABLE_NAME); + System.out.println("TABLE: "+ tableName); + System.out.println("gmlid,name,lat,lon,fixed,ifile,area,idref,removed"); + PreparedStatement ps8 = hsqldb.prepareStatement("SELECT * FROM "+ tableName); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { String gmlid = rset8.getString("gmlid"); @@ -361,7 +466,20 @@ Double lon = rset8.getDouble("lon"); int fixed = rset8.getInt("fixed"); String ifile = rset8.getString("ifile"); - System.out.println("\""+ gmlid +"\",\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\""); + int area = rset8.getInt("area"); + String idref = rset8.getString("idref"); + boolean removed = rset8.getBoolean("removed"); + System.out.println( + gmlid +"," + + name +"," + + lat +"," + + lon +"," + + fixed +"," + + ifile +"," + + area +"," + + idref +"," + + removed + ); } } } diff --git a/src/osm/jp/coverage/busstop/DbExistBusstop.java b/src/osm/jp/coverage/busstop/DbExistBusstop.java index ffe9da7..1921e81 100644 --- a/src/osm/jp/coverage/busstop/DbExistBusstop.java +++ b/src/osm/jp/coverage/busstop/DbExistBusstop.java @@ -16,7 +16,9 @@ import osm.jp.api.Osmdb; public class DbExistBusstop extends Osmdb { - public static final String EXIST_TABLE_NAME = "BUSSTOP_EXIST"; + @SuppressWarnings("FieldNameHidesFieldInSuperclass") + public static final String TABLE_NAME = "BUSSTOP_EXIST"; + public static final String CLASS_NAME = "DbExistFuel"; /** * 既存のOSMバス停を読み込む @@ -39,21 +41,65 @@ * @throws ParserConfigurationException */ public static void main(String[] args) throws Exception { - try (Connection hsqldb = DatabaseTool.openDb("database")) - { - /** - * 既存のOSMバス停を読み込む - * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、 - * 「HSQLDB.BUSSTOP_EXIST」にSTOREする - */ - DbExistBusstop osmExist = new DbExistBusstop(hsqldb); - osmExist.create(); - osmExist.getJapanCapabilities(); + if (args.length < 1) { + commandHelp(); + throw new Exception( + String.format("[ERROR] Illegal command call '%s'", CLASS_NAME) + ); + } + + // HSQLディレクトリがなければエラー + File dbdir = new File("database"); + if (!dbdir.isDirectory()) { + throw new FileNotFoundException("Directory 'database' is not found."); + } + + try (Connection hsqldb = DatabaseTool.openDb("database")) { + switch (args[0]) { + case "-INIT": + { + // DB.tableを作成(初期化) + DbExistBusstop dbExist = new DbExistBusstop(hsqldb); + dbExist.create(); + break; + } + case "-IMPORT": + { + /** + * 既存のOSMバス停を読み込む + * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、 + * 「HSQLDB.BUSSTOP_EXIST」にSTOREする + */ + DbExistBusstop dbExist = new DbExistBusstop(hsqldb); + dbExist.getJapanCapabilities(); + break; + } + case "-EXPORT": + { + DbExistBusstop dbExist = new DbExistBusstop(hsqldb); + dbExist.export(); + break; + } + default: + commandHelp(); + throw new Exception("[ERROR] Illegal command call '"+ CLASS_NAME +"'"); + } } } + static void commandHelp() { + System.out.println("[Command Call]"); + System.out.println("> "+ CLASS_NAME +" -INIT"); + System.out.println(" Initillaize detabase table '"+ TABLE_NAME +"'."); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -IMPORT"); + System.out.println(" 既存のOSMデータを読み込む."); + System.out.println(); + } + + public DbExistBusstop(Connection hsqldb) { - super(hsqldb, DbExistBusstop.EXIST_TABLE_NAME); + super(hsqldb, DbExistBusstop.TABLE_NAME); } /** @@ -84,13 +130,14 @@ // 通常 → 50ポイント // NAMEなし → 1ポイント // FIXMEあり → 1ポイント - readExisting(null, POINT_NO_NAME | POINT_FIXME); + readExisting(null, POINT_NO_NAME | POINT_FIXME, false); + readExistingSub("public_transport", "platform", POINT_BUS_NO | POINT_NO_NAME | POINT_FIXME, false); + readExistingSub("disused:highway", "bus_stop", POINT_NO); readExistingSub("abandoned:highway", "bus_stop", POINT_NO); readExistingSub("removed:highway", "bus_stop", POINT_NO); readExistingSub("no:highway", "bus_stop", POINT_NO); - readExistingSub("public_transport", "platform", POINT_BUS_NO | POINT_NO_NAME | POINT_FIXME); readExistingSub("disused:public_transport", "platform", POINT_BUS_NO); readExistingSub("abandoned:public_transport", "platform", POINT_BUS_NO); readExistingSub("removed:public_transport", "platform", POINT_BUS_NO); @@ -111,50 +158,60 @@ * @throws Exception エラー */ @Override - public void readExisting(String sqlWhere, int point) throws Exception { + public void readExisting(String sqlWhere, int point, boolean removed) throws Exception { long counter = 0L; try (Connection osmdb = DatabaseTool.openDb("osmdb")) { - String sql = "select osm_id,brand,disused,name"; + String sql = "select osm_id,brand,disused,name,"; - StringBuilder whereText0 = new StringBuilder(); - whereText0.append("where (highway='disused:bus_stop'"); - whereText0.append(")"); - PreparedStatement ps0 = osmdb.prepareStatement(sql + ",ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + whereText0.toString()); + PreparedStatement ps0 = osmdb.prepareStatement( + sql + + "ST_Y(ST_Transform(way,4326)) as lat," + + "ST_X(ST_Transform(way,4326)) as lon" + + " from planet_osm_point " + + "where (highway='disused:bus_stop')" + ); try (ResultSet rset1 = ps0.executeQuery()) { while (rset1.next()) { - counter += importExistingNode(osmdb, rset1, 0); + counter += importExistingNode(osmdb, rset1, 0, removed); } } - StringBuilder whereText1 = new StringBuilder(); - whereText1.append("where (highway='bus_stop'"); - whereText1.append(" or amenity='bus_station'"); - whereText1.append(")"); - PreparedStatement ps1 = osmdb.prepareStatement(sql + ",ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + whereText1.toString()); + PreparedStatement ps1 = osmdb.prepareStatement( + sql + + "ST_Y(ST_Transform(way,4326)) as lat," + + "ST_X(ST_Transform(way,4326)) as lon" + + " from planet_osm_point " + + "where (highway='bus_stop' or amenity='bus_station')" + ); try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { - counter += importExistingNode(osmdb, rset1, point); + counter += importExistingNode(osmdb, rset1, point, removed); } } - StringBuilder whereText = new StringBuilder(); - whereText.append("where (public_transport='platform'"); - whereText.append(" or public_transport='stop_position'"); - whereText.append(")"); - PreparedStatement ps = osmdb.prepareStatement(sql + ",ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + whereText.toString()); + PreparedStatement ps = osmdb.prepareStatement( + sql + + "ST_Y(ST_Transform(way,4326)) as lat," + + "ST_X(ST_Transform(way,4326)) as lon" + + " from planet_osm_point " + + "where (public_transport='platform' or public_transport='stop_position')" + ); try (ResultSet rset1 = ps.executeQuery()) { while (rset1.next()) { - counter += importExistingNode(osmdb, rset1, point | POINT_BUS_NO); + counter += importExistingNode(osmdb, rset1, point | POINT_BUS_NO, removed); } } - StringBuilder whereText2 = new StringBuilder(); - whereText2.append("where (amenity='bus_station'"); - whereText2.append(")"); - PreparedStatement ps2 = osmdb.prepareStatement(sql +",ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon from planet_osm_polygon " + whereText2.toString()); + PreparedStatement ps2 = osmdb.prepareStatement( + sql + + "ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat," + + "ST_X(ST_Transform(ST_Centroid(way),4326)) as lon" + + " from planet_osm_polygon " + + "where (amenity='bus_station')" + ); try (ResultSet rset2 = ps2.executeQuery()) { while (rset2.next()) { - counter += importExistingNode(osmdb, rset2, point); + counter += importExistingNode(osmdb, rset2, point, removed); } } diff --git a/src/osm/jp/coverage/busstop/NagoyaBusstop.java b/src/osm/jp/coverage/busstop/NagoyaBusstop.java index 09425a5..2784e9a 100644 --- a/src/osm/jp/coverage/busstop/NagoyaBusstop.java +++ b/src/osm/jp/coverage/busstop/NagoyaBusstop.java @@ -385,9 +385,11 @@ System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。"); } - public static void initDb(Connection con) throws SQLException { - // 'table.BUSSTOP'を新規に作る - DbBusstop.create(con); + public static void initDb(Connection con) throws SQLException, ClassNotFoundException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + // DB.tableを作成(初期化) + DbBusstop db = new DbBusstop(con); + db.dropTable(); + db.create(); } diff --git a/src/osm/jp/coverage/fuel/DbExistFuel.java b/src/osm/jp/coverage/fuel/DbExistFuel.java index 4bfc2bc..7179c84 100644 --- a/src/osm/jp/coverage/fuel/DbExistFuel.java +++ b/src/osm/jp/coverage/fuel/DbExistFuel.java @@ -135,7 +135,7 @@ // 通常 → 50ポイント // BRANDなし → 1ポイント // FIXMEあり → 1ポイント - readExisting("where amenity='fuel'", POINT_NO_BRAND | POINT_FIXME); + readExisting("where amenity='fuel'", POINT_NO_BRAND | POINT_FIXME, false); readExistingSub("disused:amenity", "fuel", POINT_NO); readExistingSub("abandoned:amenity", "fuel", POINT_NO); readExistingSub("demolished:amenity", "fuel", POINT_NO); diff --git a/src/osm/jp/coverage/fuel/DbFuel.java b/src/osm/jp/coverage/fuel/DbFuel.java index eec0387..752388c 100644 --- a/src/osm/jp/coverage/fuel/DbFuel.java +++ b/src/osm/jp/coverage/fuel/DbFuel.java @@ -5,10 +5,6 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import javax.json.Json; -import javax.json.JsonObject; -import javax.json.JsonObjectBuilder; -import javax.json.JsonReader; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; @@ -349,92 +345,4 @@ } } } - - public void loadRemoved(File revovedFile) throws FileNotFoundException, IOException, SQLException { - if (revovedFile == null) { - throw new FileNotFoundException(); - } - if (!revovedFile.exists()) { - throw new FileNotFoundException(); - } - if (!revovedFile.isFile()) { - throw new FileNotFoundException(); - } - - try (PreparedStatement ps = hsqldb.prepareStatement( - "UPDATE " - + tableName - + " SET idref=?,removed=true,fixed1=100" - + " WHERE gmlid=? AND area=?" - )) { - LineNumberReader reader = new LineNumberReader( - new InputStreamReader(new FileInputStream(revovedFile)) - ); - while (reader.readLine() != null) { - String lineStr = reader.readLine(); - if (lineStr == null) { - continue; - } - if (lineStr.trim().length() < 3) { - continue; - } - - JsonObject json = parse(lineStr); - String gmlid = json.getString("gmlid"); - String idref = json.getString("idref"); - int area = json.getInt("area"); - - ps.setString(1, idref); - ps.setString(2, gmlid); - ps.setInt(3, area); - ps.executeUpdate(); - } - } - } - - /** - * removedデータをファイルに出力する - * - * @param removedFile - * @throws SQLException - * @throws java.io.IOException - */ - public void outputRemoved(File removedFile) throws SQLException, IOException { - String whereStr = "WHERE (removed=?)"; - String fromStr = "FROM "+ DbFuel.TABLE_NAME; - String sortStr = "ORDER BY area,gmlid"; - String sql = String.format("SELECT * %s %s %s", fromStr, whereStr, sortStr); - - removedFile.deleteOnExit(); - try (FileWriter fw = new FileWriter(removedFile); - PreparedStatement ps1 = hsqldb.prepareStatement(sql)) - { - System.out.println(sql); - ps1.setBoolean(1, true); - try (ResultSet rset1 = ps1.executeQuery()) { - while (rset1.next()) { - String gmlid = rset1.getString("gmlid"); - int area = rset1.getInt("area"); - String idref = rset1.getString("idref"); - - JsonObjectBuilder jsonBuilder = Json.createObjectBuilder(); - jsonBuilder.add("gmlid", gmlid); - jsonBuilder.add("idref", idref); - jsonBuilder.add("area", area); - JsonObject value = jsonBuilder.build(); - String line = value.toString(); - - fw.write(line); - fw.write("\n"); - fw.flush(); - } - } - } - } - - static public JsonObject parse(String jsonObjectString) { - JsonReader jsonReader = Json.createReader(new StringReader(jsonObjectString)); - JsonObject jsonObject = jsonReader.readObject(); - return jsonObject; - } } \ No newline at end of file diff --git a/src/osm/jp/coverage/police/DbExistPolice.java b/src/osm/jp/coverage/police/DbExistPolice.java index 3af05c4..dbe89e8 100644 --- a/src/osm/jp/coverage/police/DbExistPolice.java +++ b/src/osm/jp/coverage/police/DbExistPolice.java @@ -84,7 +84,7 @@ public void getJapanCapabilities() throws Exception { // 通常 → 50ポイント // FIXMEあり → 1ポイント - readExisting("where amenity='police'", POINT_FIXME); + readExisting("where amenity='police'", POINT_FIXME, false); readExistingSub("disused:amenity", "police", POINT_NO); readExistingSub("abandoned:amenity", "police", POINT_NO); readExistingSub("demolished:amenity", "police", POINT_NO); diff --git a/src/osm/jp/coverage/postoffice/DbExistPostoffice.java b/src/osm/jp/coverage/postoffice/DbExistPostoffice.java index f8a5bf3..97fe203 100644 --- a/src/osm/jp/coverage/postoffice/DbExistPostoffice.java +++ b/src/osm/jp/coverage/postoffice/DbExistPostoffice.java @@ -67,7 +67,7 @@ public void getJapanCapabilities() throws Exception { // 通常 → 50ポイント // FIXMEあり → 1ポイント - readExisting("where amenity='post_office'", POINT_FIXME); + readExisting("where amenity='post_office'", POINT_FIXME, false); readExistingSub("disused:amenity", "post_office", POINT_NO); readExistingSub("abandoned:amenity", "post_office", POINT_NO); readExistingSub("demolished:amenity", "post_office", POINT_NO); diff --git a/test/osm/jp/coverage/busstop/BusstopTest.java b/test/osm/jp/coverage/busstop/BusstopTest.java index 7320944..d4c7ec6 100644 --- a/test/osm/jp/coverage/busstop/BusstopTest.java +++ b/test/osm/jp/coverage/busstop/BusstopTest.java @@ -1,18 +1,15 @@ package osm.jp.coverage.busstop; import java.io.File; -import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; -import java.sql.SQLException; import java.text.MessageFormat; -import java.util.logging.Level; -import java.util.logging.Logger; import jp.co.areaweb.tools.database.DatabaseTool; import static org.hamcrest.CoreMatchers.is; import org.junit.*; import static org.junit.Assert.*; +import org.junit.runners.MethodSorters; import osm.jp.api.Japan; import osm.jp.api.RectArea; import osm.jp.coverage.PoiTest; @@ -22,6 +19,7 @@ * * @author yuu */ +@FixMethodOrder (MethodSorters.NAME_ASCENDING) public class BusstopTest extends PoiTest { @Before @@ -35,15 +33,19 @@ @Test public void test00Busstop_main() { String[] args = new String[0]; - Busstop.main(args); + try { + Busstop.main(args); + } catch (Exception ex) { + fail(); + } } @Test public void test01Busstop_dataread() { - Connection hsqldb = null; - try { - hsqldb = DatabaseTool.openDb("database"); - String sql = "SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > '34.69161765717579') and (lat < '34.697025680375795') and (lon > '135.79486712544522') and (lon < '135.80144696784524')"; + try (Connection hsqldb = DatabaseTool.openDb("database")) { + String sql = "SELECT * FROM " + + DbBusstop.TABLE_NAME + +" WHERE (lat > '34.69161765717579') and (lat < '34.697025680375795') and (lon > '135.79486712544522') and (lon < '135.80144696784524')"; PreparedStatement ps1 = hsqldb.prepareStatement(sql); try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { @@ -55,37 +57,20 @@ System.out.println(rset1.getDouble("lon")); } } - - } catch (ClassNotFoundException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (SQLException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (IOException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } finally { - DatabaseTool.closeDb(hsqldb); + } + catch (Exception ex) { + fail(ex.toString()); } } @Test public void test02Busstop_busstop() { - try { - String[] args = new String[0]; - Busstop.main(args); - } - catch (Exception ex) { - fail(ex.toString()); - } - File dir = new File("database"); assertTrue(dir.exists()); assertTrue(dir.isDirectory()); - Connection hsqldb = null; - try { - hsqldb = DatabaseTool.openDb("database"); - - PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST"); + try (Connection hsqldb = DatabaseTool.openDb("database")) { + PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM "+ DbExistBusstop.TABLE_NAME); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { long cnt = rset1.getLong(1); @@ -95,9 +80,23 @@ fail(); } } - - // ノード: 八幡前 (3152604023) 場所: 33.9808001, 133.3123441 fixme有り - ps1 = hsqldb.prepareStatement("SELECT * FROM BUSSTOP where name='八幡前' and area=38"); + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 八幡前 (3152604023) + * 場所: 33.9808001, 133.3123441 + * fixme有り + */ + @Test + public void test02Busstop_3152604023() { + try (Connection hsqldb = DatabaseTool.openDb("database")) { + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM "+ DbBusstop.TABLE_NAME +" where name='八幡前' and area=38" + ); try (ResultSet rset1 = ps1.executeQuery()) { boolean ari = false; while (rset1.next()) { @@ -109,79 +108,135 @@ int fixed1 = rset1.getInt("fixed1"); System.out.println(MessageFormat.format("gmlid: '{0}', name: '{1}', fixed: {2}, fixed1: {3}", gmlid, name, fixed, fixed1)); assertTrue(fixed < 50); + assertThat(rset1.getBoolean("removed"), is(false)); } if (!ari) { fail(); } } - - // ノード: 4940018338 場所: 35.5909251, 139.1498642 highway=bus_stop, name=null, bus=null,public_transport=platform - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='4940018338'"); + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 4940018338 + * 場所: 35.5909251, 139.1498642 + * highway=bus_stop, name=null, bus=yes, public_transport=platform, operator=神奈中 + */ + @Test + public void test02Busstop_4940018338() { + try (Connection hsqldb = DatabaseTool.openDb("database")) { + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM " + + DbExistBusstop.TABLE_NAME + +" where idref='4940018338'" + ); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { // nameなし - assertThat(rset1.getInt("score"), is(1)); + assertThat(rset1.getInt("score"), is(0)); assertThat(checkRenge(rset1, "35.5909251", "139.1498642"), is(true)); + assertThat(rset1.getBoolean("removed"), is(false)); } else { fail(); } } - - // ノード: 海老名高校前 (2043102034) 場所: 35.4435042, 139.3878934 highway=bus_stop - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='2043102034'"); + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 海老名高校前 (2043102034) + * 場所: 35.4435042, 139.3878934 + * highway=bus_stop + */ + @Test + public void test02Busstop_2043102034() { + try (Connection hsqldb = DatabaseTool.openDb("database")) { + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM "+ DbExistBusstop.TABLE_NAME +" where idref='2043102034'" + ); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); assertThat(checkRenge(rset1, "35.4435042", "139.3878934"), is(true)); + assertThat(rset1.getBoolean("removed"), is(true)); } else { fail(); } } - - // ノード: 厚木ナイロン (1995040609) 場所: 35.4433312, 139.3932098 public_transport=stop_position,bus=yes - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='1995040609'"); + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 厚木ナイロン (1995040609) + * 場所: 35.4433312, 139.3932098 + * public_transport=stop_position,bus=yes + */ + @Test + public void test02Busstop_1995040609() { + try (Connection hsqldb = DatabaseTool.openDb("database")) { + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM "+ DbExistBusstop.TABLE_NAME +" where idref='1995040609'" + ); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); assertThat(checkRenge(rset1, "35.4433312", "139.3932098"), is(true)); + assertThat(rset1.getBoolean("removed"), is(false)); } else { fail(); } } - - // ウェイ: 国分寺台第12 (154659062) bus_station - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='154659062'"); + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ウェイ: 国分寺台第12 (154659062) + * bus_station + */ + @Test + public void test02Busstop_154659062() { + try (Connection hsqldb = DatabaseTool.openDb("database")) { + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM "+ DbExistBusstop.TABLE_NAME +" where idref='154659062'" + ); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); + assertThat(rset1.getBoolean("removed"), is(false)); } else { fail(); } } - - } catch (ClassNotFoundException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (SQLException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (IOException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } finally { - DatabaseTool.closeDb(hsqldb); + } + catch (Exception ex) { + fail(ex.toString()); } } @Test public void test03Busstop_dataread() { - Connection hsqldb = null; - try { - hsqldb = DatabaseTool.openDb("database"); - String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ DbExistBusstop.EXIST_TABLE_NAME; - String sqlStr2 = "SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"; - String sqlStr3 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?"; + try (Connection hsqldb = DatabaseTool.openDb("database")) { + String sqlStr1 = "SELECT * FROM "+ DbExistBusstop.TABLE_NAME; + String sqlStr2 = "SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM "+ DbBusstop.TABLE_NAME + +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"; + String sqlStr3 = "UPDATE "+ DbBusstop.TABLE_NAME + +" SET fixed1=? WHERE gmlid=? and area=?"; System.out.println(sqlStr1); try ( PreparedStatement ps2 = hsqldb.prepareStatement(sqlStr2); PreparedStatement ps1 = hsqldb.prepareStatement(sqlStr1); @@ -247,18 +302,11 @@ } } } - } } - - } catch (ClassNotFoundException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (SQLException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (IOException ex) { - Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } finally { - DatabaseTool.closeDb(hsqldb); + } + catch (Exception ex) { + fail(ex.toString()); } } diff --git a/test/osm/jp/coverage/busstop/DbBusstopTest.java b/test/osm/jp/coverage/busstop/DbBusstopTest.java index 22b6f17..33b74c7 100644 --- a/test/osm/jp/coverage/busstop/DbBusstopTest.java +++ b/test/osm/jp/coverage/busstop/DbBusstopTest.java @@ -1,22 +1,194 @@ package osm.jp.coverage.busstop; -import java.io.FileNotFoundException; -import java.io.IOException; -import java.sql.SQLException; -import javax.xml.parsers.ParserConfigurationException; +import java.io.File; +import java.io.FileWriter; +import java.sql.Connection; +import jp.co.areaweb.tools.database.DatabaseTool; +import static org.hamcrest.CoreMatchers.is; +import static org.junit.Assert.assertThat; +import static org.junit.Assert.fail; +import org.junit.FixMethodOrder; import org.junit.Test; -import org.xml.sax.SAXException; +import org.junit.runners.MethodSorters; +import osm.jp.coverage.DbTest; -/** - * - * @author yuu - */ +@FixMethodOrder (MethodSorters.NAME_ASCENDING) public class DbBusstopTest { + String databaseName = "database"; + String tableName = "busstop"; + /** + * コマンド引数チェック + * + * @throws Exception + */ @Test - public void testDbBusstop_main() throws ClassNotFoundException, IOException, FileNotFoundException, SQLException, ParserConfigurationException, SAXException { - String[] args = new String[0]; - DbBusstop.main(args); + public void t00_main_null() throws Exception { + try { + String[] args = new String[]{}; + DbBusstop.main(args); + fail(); + } + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); + } } + @Test + public void t01_main_illiegal() throws Exception { + try { + String[] args = new String[]{"-"}; + DbBusstop.main(args); + fail(); + } + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); + } + } + + /** + * (1)テーブルを初期化する + * + * @throws Exception + */ + @Test + public void t10_main_init() throws Exception { + try { + String[] args = new String[]{"-INIT"}; + DbBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + DbTest.checkDatabase(databaseName); + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + assertThat(DbTest.isTable(hsqldb, tableName), is(true)); + assertThat(DbTest.getRecordCount(hsqldb, tableName), is(0)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * (2)GMLファイルを読み取る(国土数値情報の読み取り) + * + * @throws Exception + */ + @Test + public void t20_main_import_ng() throws Exception { + try { + String[] args = new String[]{"-IMPORT"}; + DbBusstop.main(args); + fail(); + } + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); + } + } + + @Test + public void t21_main_import_ng() throws Exception { + try { + String[] args = new String[]{"-IMPORT", "xxx"}; + DbBusstop.main(args); + fail(); + } + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); + } + } + + @Test + public void t25_main_import() throws Exception { + try { + String[] args = new String[]{"-IMPORT", "GML_BUSSTOP"}; + DbBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt = DbTest.getRecordCount(hsqldb, tableName); + System.out.println("'"+ tableName +"' table count = " + cnt); + assertThat(cnt > 100, is(true)); + + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'"+ tableName +" REMOVED=TRUE' table count = " + cnt); + assertThat(cnt, is(0)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * (3)DISUSEDなPOIを読み込む + * + * @throws Exception + */ + @Test + public void t30_main_removed() throws Exception { + File file = new File("GML_BUSSTOP", "P11-10.removed.json.txt"); + try (FileWriter fw = new FileWriter(file)) { + fw.write(""); + fw.flush(); + } + + try { + String[] args = new String[]{"-REMOVED", "GML_BUSSTOP/P11-10.removed.json.txt"}; + DbBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'FUEL REMOVED=TRUE' table count = " + cnt); + assertThat(cnt, is(0)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * (4)DISUSEDをファイルに出力する + * + * @throws Exception + */ + @Test + public void t40_main_output() throws Exception { + int cnt = 0; + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'BUSSTOP REMOVED=TRUE' table count = " + cnt); + } + catch (Exception e) { + fail(e.toString()); + } + + try { + String[] args = new String[]{"-OUTPUT", "GML_BUSSTOP/P11-10.removed.json.txt"}; + DbBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + File file = new File("GML_BUSSTOP", "P11-10.removed.json.txt"); + assertThat(file.exists(), is(true)); + + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt1 = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'BUSSTOP REMOVED=TRUE' table count = " + cnt1); + assertThat(cnt1, is(cnt)); + } + catch (Exception e) { + fail(e.toString()); + } + } } diff --git a/test/osm/jp/coverage/busstop/DbExistBusstopTest.java b/test/osm/jp/coverage/busstop/DbExistBusstopTest.java index 30132ff..d45d603 100644 --- a/test/osm/jp/coverage/busstop/DbExistBusstopTest.java +++ b/test/osm/jp/coverage/busstop/DbExistBusstopTest.java @@ -1,15 +1,10 @@ package osm.jp.coverage.busstop; -import java.io.File; -import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.util.logging.Level; -import java.util.logging.Logger; import javax.json.Json; -import javax.json.JsonArray; import javax.json.JsonArrayBuilder; import javax.json.JsonBuilderFactory; import javax.json.JsonObject; @@ -18,187 +13,201 @@ import static org.hamcrest.CoreMatchers.is; import org.junit.*; import static org.junit.Assert.*; +import org.junit.runners.MethodSorters; import static osm.jp.api.Osmdb.POINT_BUS_NO; import static osm.jp.api.Osmdb.POINT_FIXME; import static osm.jp.api.Osmdb.POINT_NO_NAME; +import osm.jp.coverage.DbTest; /** * * @author yuu */ +@FixMethodOrder (MethodSorters.NAME_ASCENDING) public class DbExistBusstopTest { + String databaseName = "database"; + String tableName = "BUSSTOP_EXIST"; - @BeforeClass - public static void setUp() throws Exception { - File dir = new File("database"); - if (dir.exists()) { - if (dir.isDirectory()) { - dir.deleteOnExit(); - } - else { - throw new Exception("'database' is not directory."); - } - } - - Connection con = null; + /** + * コマンド引数チェック + * + * @throws Exception + */ + @Test + public void t00_main_null() throws Exception { try { - // DB.tableを作成 - con = DatabaseTool.openDb("database"); - DbExistBusstop osmExist = new DbExistBusstop(con); - osmExist.create(); - } finally { - if (con != null) { - DatabaseTool.closeDb(con); - } + String[] args = new String[]{}; + DbExistBusstop.main(args); + fail(); } - } - - @After - public void tearDown() throws Exception { + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); + } } @Test - public void test01DbExistBusstopTest_hsqldbの生成確認() { - - File dir = new File("database"); - assertTrue(dir.exists()); - assertTrue(dir.isDirectory()); - - Connection hsqldb = null; + public void t01_main_illiegal() throws Exception { try { - hsqldb = DatabaseTool.openDb("database"); - - PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM AREA_NODE"); - try (ResultSet rset8 = ps8.executeQuery()) { - if (rset8.next()) { - long cnt = rset8.getLong(1); - assertThat(Long.toString(cnt), is("0")); - } - else { - fail(); - } - } - - PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST"); - try (ResultSet rset1 = ps1.executeQuery()) { - if (rset1.next()) { - long cnt = rset1.getLong(1); - //assertThat(Long.toString(cnt), is("0")); - } - else { - fail(); - } - } - - } catch (ClassNotFoundException ex) { - Logger.getLogger(DbExistBusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (SQLException ex) { - Logger.getLogger(DbExistBusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (IOException ex) { - Logger.getLogger(DbExistBusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } finally { - DatabaseTool.closeDb(hsqldb); + String[] args = new String[]{"-"}; + DbExistBusstop.main(args); + fail(); + } + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); } } - + + /** + * (1)テーブルを初期化する + * + * @throws Exception + */ @Test - public void test02DbExistBusstopTest_busstop() { + public void t10_main_init() throws Exception { try { - String[] args = new String[0]; + String[] args = new String[]{"-INIT"}; DbExistBusstop.main(args); } - catch (Exception ex) { - fail(ex.toString()); + catch(Exception e) { + fail(e.toString()); } - - File dir = new File("database"); - assertTrue(dir.exists()); - assertTrue(dir.isDirectory()); - Connection hsqldb = null; + DbTest.checkDatabase(databaseName); + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + assertThat(DbTest.isTable(hsqldb, tableName), is(true)); + assertThat(DbTest.getRecordCount(hsqldb, tableName), is(0)); + + assertThat(DbTest.isTable(hsqldb, "AREA_NODE"), is(true)); + assertThat(DbTest.getRecordCount(hsqldb, "AREA_NODE"), is(0)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * (2)GMLファイルを読み取る(国土数値情報の読み取り) + * + * @throws Exception + */ + @Test + public void t20_main_import() throws Exception { try { - hsqldb = DatabaseTool.openDb("database"); + String[] args = new String[]{"-IMPORT"}; + DbExistBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + } + + @Test + public void t21_main_import() { + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt = DbTest.getRecordCount(hsqldb, tableName); + System.out.println("'BUSSTOP' table count = " + cnt); + assertThat(cnt > 100, is(true)); - PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST"); + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'BUSSTOP REMOVED=TRUE' table count = " + cnt); + assertThat(cnt > 0, is(true)); + + String sql = "SELECT * FROM "+ tableName +" WHERE "; + + // REMOVED + PreparedStatement ps1 = hsqldb.prepareStatement(sql +"removed=true"); try (ResultSet rset1 = ps1.executeQuery()) { - if (rset1.next()) { - long cnt = rset1.getLong(1); - assertThat((cnt > 0), is(true)); - } - else { - fail(); + while (rset1.next()) { + String idref = rset1.getString("idref"); + int area = rset1.getInt("area"); + int score = rset1.getInt("score"); + System.out.println( + String.format("'BUSSTOP REMOVED'{idref='%s', area=%d, score=%d}", idref, area, score) + ); + //assertThat(score >= 50, is(true)); } } - - // ノード: 八幡前 (3152604023) 場所: 33.9808001, 133.3123441 fixme有り - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='3152604023'"); + + // ノード: 八幡前 (3152604023) + // 場所: 33.9808001, 133.3123441 + // fixme有り + ps1 = hsqldb.prepareStatement(sql +"idref='3152604023'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { // fixme有り assertThat(rset1.getInt("score"), is(1)); assertThat(checkRenge(rset1, "33.9808001", "133.3123441"), is(true)); + assertThat(rset1.getBoolean("removed"), is(false)); } else { fail(); } } - // ノード: 4940018338 場所: 35.5909251, 139.1498642 highway=bus_stop, name=null, bus=null,public_transport=platform - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='4940018338'"); + // ノード: 4940018338 + // 場所: 35.5909251, 139.1498642 + // highway=bus_stop, name=null, bus=yes,public_transport=platform + ps1 = hsqldb.prepareStatement(sql + "idref='4940018338'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { // nameなし assertThat(rset1.getInt("score"), is(1)); assertThat(checkRenge(rset1, "35.5909251", "139.1498642"), is(true)); + assertThat(rset1.getBoolean("removed"), is(false)); } else { fail(); } } - // ノード: 海老名高校前 (2043102034) 場所: 35.4435042, 139.3878934 highway=bus_stop - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='2043102034'"); + // ノード: 海老名高校前 (2043102034) + // 場所: 35.4435042, 139.3878934 + // disused:highway=bus_stop + // operator=海老名市コミュニティバス + // bench=yes + // ref=11 + ps1 = hsqldb.prepareStatement(sql + "idref='2043102034'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); assertThat(checkRenge(rset1, "35.4435042", "139.3878934"), is(true)); + assertThat(rset1.getBoolean("removed"), is(true)); } else { fail(); } } - // ノード: 厚木ナイロン (1995040609) 場所: 35.4433312, 139.3932098 public_transport=stop_position,bus=yes - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='1995040609'"); + // ノード: 厚木ナイロン (1995040609) + // 場所: 35.4433312, 139.3932098 + // public_transport=stop_position,bus=yes + ps1 = hsqldb.prepareStatement(sql + "idref='1995040609'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); assertThat(checkRenge(rset1, "35.4433312", "139.3932098"), is(true)); + assertThat(rset1.getBoolean("removed"), is(false)); } else { fail(); } } - // ウェイ: 国分寺台第12 (154659062) bus_station - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='154659062'"); + // ウェイ: 国分寺台第12 (154659062) + // bus_station + ps1 = hsqldb.prepareStatement(sql + "idref='154659062'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); + assertThat(rset1.getBoolean("removed"), is(false)); } else { fail(); } } - - } catch (ClassNotFoundException ex) { - Logger.getLogger(DbExistBusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (SQLException ex) { - Logger.getLogger(DbExistBusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } catch (IOException ex) { - Logger.getLogger(DbExistBusstopTest.class.getName()).log(Level.SEVERE, null, ex); - } finally { - DatabaseTool.closeDb(hsqldb); + } + catch (Exception e) { + fail(e.toString()); } } @@ -223,7 +232,7 @@ tag = builder.build(); abuilder.add(tag); - osmExist.readExistingSub(abuilder.build(), POINT_BUS_NO | POINT_NO_NAME | POINT_FIXME); + osmExist.readExistingSub(abuilder.build(), POINT_BUS_NO | POINT_NO_NAME | POINT_FIXME, false); } catch (Exception ex) { fail(ex.toString()); } finally { @@ -233,6 +242,22 @@ } } + + /** + * (3)'table.OSM_EXIST'の内容をCSV形式にして標準出力に出力する + * + * @throws Exception + */ + @Test + public void t30_main_expport() throws Exception { + try { + String[] args = new String[]{"-EXPORT"}; + DbExistBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + } boolean checkRenge(ResultSet rset, String latStr, String lonStr) throws SQLException { if (checkRenge(rset.getDouble("lat"), latStr)) {