diff --git a/src/osm/jp/api/DbCommand.java b/src/osm/jp/api/DbCommand.java new file mode 100644 index 0000000..d68b385 --- /dev/null +++ b/src/osm/jp/api/DbCommand.java @@ -0,0 +1,30 @@ +package osm.jp.api; + +import java.sql.Connection; +import java.sql.PreparedStatement; + +public class DbCommand { + Connection hsqldb; + String tableName; + boolean verbose; + + public DbCommand(Connection hsqldb, String tableName) { + this.hsqldb = hsqldb; + this.tableName = tableName; + this.verbose = false; + } + + public DbCommand setVerbose(boolean verbose) { + this.verbose = verbose; + return this; + } + + public DbCommand dropTable() throws Exception { + String sqlStr = "DROP TABLE IF EXISTS "+ tableName; + try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) { + if (verbose) System.out.println(sqlStr); + ps.executeUpdate(); + } + return this; + } +} diff --git a/src/osm/jp/api/OsmExist.java b/src/osm/jp/api/OsmExist.java index dbac962..d98d978 100644 --- a/src/osm/jp/api/OsmExist.java +++ b/src/osm/jp/api/OsmExist.java @@ -14,7 +14,6 @@ public abstract class OsmExist extends Osmdb { public ArrayList nodelist = new ArrayList<>(); - public OsmExist(Connection hsqldb, String tableName) { super(hsqldb, tableName); } @@ -230,6 +229,17 @@ /** * + * @param gmlDirectory + * @throws Exception + */ + public void importGmlfiles(String gmlDirectory) throws Exception { + throw new Exception( + String.format("Illiegal method. 'importGmlfiles(%s)'", gmlDirectory) + ); + } + + /** + * * @param point * @param tags * @return diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index 40520bc..b23e2b2 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -14,17 +14,15 @@ import javax.json.JsonObject; import javax.json.JsonObjectBuilder; import javax.json.JsonValue; -import javax.xml.parsers.ParserConfigurationException; import jp.co.areaweb.tools.csv.CsvRecord; import jp.co.areaweb.tools.database.DatabaseTool; import org.hsqldb.HsqlException; import org.w3c.dom.NamedNodeMap; import org.w3c.dom.Node; import org.w3c.dom.NodeList; -import org.xml.sax.SAXException; public abstract class Osmdb { - + boolean verbose; public String tableName; public Connection hsqldb = null; // hsqldb DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB); @@ -35,7 +33,32 @@ abstract public void create() throws SQLException; - void sql(String sql) throws SQLException { + /** + * + * @param gmlDirectory + * @throws Exception + */ + abstract public void importGmlfiles(String gmlDirectory) throws Exception; + + public Osmdb setVerbose(boolean verbose) { + this.verbose = verbose; + return this; + } + + public boolean getVerbose() { + return verbose; + } + + public Osmdb dropTable() throws Exception { + String sqlStr = "DROP TABLE IF EXISTS "+ tableName; + try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) { + if (verbose) System.out.println(sqlStr); + ps.executeUpdate(); + } + return this; + } + + public void sql(String sql) throws SQLException { System.out.println(sql); try (PreparedStatement ps = hsqldb.prepareStatement(sql)) { ps.executeUpdate(); @@ -65,9 +88,11 @@ * @throws java.sql.SQLException */ public void export() throws SQLException { - String header = "idref,lat,lon,score"; - System.out.println("TABLE: "+ tableName); - System.out.println(header); + if (getVerbose()) { + String header = "idref,lat,lon,score"; + System.out.println("TABLE: "+ tableName); + System.out.println(header); + } PreparedStatement ps8 = hsqldb.prepareStatement( "SELECT idref,lat,lon,score,removed FROM "+ tableName ); @@ -82,19 +107,17 @@ "OSM: %s,%2.5f,%3.5f,%d,%b", idcode, lat, lon, score, removed ); - System.out.println(out); + if (getVerbose()) System.out.println(out); } } } - 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"); String brandStr = rset.getString("brand"); String nameStr = rset.getString("name"); - String fixmeStr = ""; int score = 50; if (((point & POINT_NO_BRAND) != 0) && isNull(brandStr)) { @@ -680,12 +703,4 @@ return null; } - public void dropTable () throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - String sqlStr = "DROP TABLE IF EXISTS "+ tableName; - try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) { - System.out.println(sqlStr); - ps.executeUpdate(); - } - } - } diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index 429d627..0ddc117 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -1,17 +1,10 @@ package osm.jp.coverage.busstop; 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.Coverage; import osm.jp.api.Japan; @@ -19,12 +12,13 @@ public class Busstop { // 近くのバス停を探す範囲（バス停を中心としたNEER×2ｍ四方の領域 static final int NEER = 150; // 150m - public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); /** * メイン * OSM_EXISTのscoreを集計して,BUSSTOPのfixedをUPDATE + * OPTION: + * -v verbose * * @param args * @throws java.lang.Exception @@ -32,6 +26,13 @@ @SuppressWarnings("ResultOfObjectAllocationIgnored") public static void main(String[] args) throws Exception { + boolean verbose = false; + for (String arg : args) { + if (arg.toUpperCase().equals("-V")) { + verbose = true; + } + } + // HSQLディレクトリがなければ作る File dbdir = new File(Coverage.DB_PORP_LOCALDB); if (!dbdir.isDirectory()) { @@ -39,26 +40,25 @@ } try (Connection hsql = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { - new Busstop(hsql); + new Busstop(hsql, verbose); } } + + public boolean verbose = false; /** * OSM_EXISTのscoreを集計して,BUSSTOPのfixedをUPDATE * * @param con - * @throws SQLException - * @throws FileNotFoundException - * @throws ClassNotFoundException - * @throws IOException - * @throws ParserConfigurationException - * @throws SAXException - * @throws TransformerException + * @param verbose + * @throws Exception */ - public Busstop(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { + public Busstop(Connection con, boolean verbose) throws Exception { + this.verbose = verbose; + String sqlStr0 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"; try (PreparedStatement ps0 = con.prepareStatement(sqlStr0)) { - System.out.println(sqlStr0); + if (verbose) System.out.println(sqlStr0); ps0.executeUpdate(); } @@ -70,7 +70,7 @@ PreparedStatement ps2 = con.prepareStatement(sqlStr2); PreparedStatement ps3 = con.prepareStatement(sqlStr3)) { - System.out.println(sqlStr1); + if (verbose) System.out.println(sqlStr1); try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { double lat = rset1.getDouble("lat"); @@ -91,29 +91,30 @@ ps2.setDouble(4, rect.maxlon); try (ResultSet rset2 = ps2.executeQuery()) { while (rset2.next()) { - System.out.print("{"); gmlid = rset2.getString("gmlid"); - System.out.print("gmlid:"+gmlid +", "); area = rset2.getInt("area"); - System.out.print("area:"+area +", "); fixed1 = rset2.getInt("fixed1"); fixed1 += score; - System.out.print("fixed1:"+fixed1 +", "); String ksjName = rset2.getString("name"); - System.out.print("ksjName:"+ksjName +", "); double lat2 = rset2.getDouble("lat"); - System.out.print("lat2:"+lat2 +", "); double lon2 = rset2.getDouble("lon"); - System.out.print("lon2:"+lon2 +", "); - double dd = Japan.distanceKm(lat,lon,lat2,lon2); - System.out.print("dd:"+dd +" "); - System.out.println("}"); - System.out.println("distance() -> "+ dd +"(km)"); + if (verbose) { + System.out.print("{"); + System.out.print("gmlid:"+gmlid +", "); + System.out.print("area:"+area +", "); + System.out.print("fixed1:"+fixed1 +", "); + System.out.print("ksjName:"+ksjName +", "); + System.out.print("lat2:"+lat2 +", "); + System.out.print("lon2:"+lon2 +", "); + System.out.print("dd:"+dd +" "); + System.out.println("}"); + System.out.println("distance() -> "+ dd +"(km)"); + } if ( ((dd * 1000.0D) < NEER) || (((dd * 1000.0D) < NEER*2) && (name != null) && (name.equals(ksjName)))) { - System.out.println(sqlStr3 +" ["+ fixed1 +", "+ removed +", "+ gmlid +", "+ area +"]"); + if (verbose) System.out.println(sqlStr3 +" ["+ fixed1 +", "+ removed +", "+ gmlid +", "+ area +"]"); ps3.setInt(1, fixed1); ps3.setBoolean(2, removed); ps3.setString(3, gmlid); @@ -127,7 +128,7 @@ } String sqlStr5 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; - System.out.println(sqlStr5); + if (verbose) System.out.println(sqlStr5); try (PreparedStatement ps5 = con.prepareStatement(sqlStr5)) { ps5.executeUpdate(); } diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index 5055005..d364117 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -5,7 +5,6 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.util.ArrayList; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; @@ -46,16 +45,13 @@ System.out.println(); } - /** メイン + /** + * メイン * 動作条件； HSQLDBのフォルダを削除した状態で実行すること。 * * @param args - * @throws IOException - * @throws SQLException - * @throws ClassNotFoundException - * @throws FileNotFoundException - * @throws javax.xml.parsers.ParserConfigurationException - * @throws org.xml.sax.SAXException */ + * @throws Exception + */ public static void main(String[] args) throws Exception { if (args.length < 1) { @@ -153,7 +149,8 @@ * @throws ParserConfigurationException * @throws SAXException */ - void importGmlfiles(String gmlDirectory) throws ClassNotFoundException, SQLException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + @Override + public void importGmlfiles(String gmlDirectory) throws Exception { File gmlDir = new File(gmlDirectory); if (!gmlDir.exists()) { throw new FileNotFoundException(gmlDirectory); @@ -332,7 +329,6 @@ String nameStr = ""; String sql = String.format("UPDATE %s SET name=?,ifile=? WHERE (gmlid=? and area=?)", DbBusstop.TABLE_NAME); try (PreparedStatement ps2 = con.prepareStatement(sql)) { - ArrayList bris = new ArrayList<>(); NodeList nodes = node.getChildNodes(); for (int i=0; i < nodes.getLength(); i++) { Node node2 = nodes.item(i); @@ -355,7 +351,6 @@ case "ksj:busRouteInformation": String[] rtn = anaCommJGD(node2); if (rtn != null) { - bris.add(rtn); } break; default: break; diff --git a/src/osm/jp/coverage/busstop/NagoyaBusstop.java b/src/osm/jp/coverage/busstop/NagoyaBusstop.java index f03d6b2..424a49e 100644 --- a/src/osm/jp/coverage/busstop/NagoyaBusstop.java +++ b/src/osm/jp/coverage/busstop/NagoyaBusstop.java @@ -387,7 +387,7 @@ System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。"); } - public static void initDb(Connection con) throws SQLException, ClassNotFoundException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + public static void initDb(Connection con) throws SQLException, ClassNotFoundException, IOException, FileNotFoundException, ParserConfigurationException, SAXException, Exception { // DB.tableを作成(初期化) DbBusstop db = new DbBusstop(con); db.dropTable(); diff --git a/src/osm/jp/coverage/fuel/DbFuel.java b/src/osm/jp/coverage/fuel/DbFuel.java index 3aa7a2f..1602243 100644 --- a/src/osm/jp/coverage/fuel/DbFuel.java +++ b/src/osm/jp/coverage/fuel/DbFuel.java @@ -132,14 +132,10 @@ * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する * * @param gmlDirectory - * @throws ClassNotFoundException - * @throws SQLException - * @throws IOException - * @throws FileNotFoundException - * @throws ParserConfigurationException - * @throws SAXException + * @throws Exception */ - void importGmlfiles(String gmlDirectory) throws ClassNotFoundException, SQLException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + @Override + public void importGmlfiles(String gmlDirectory) throws Exception { File gmlDir = new File(gmlDirectory); if (!gmlDir.exists()) { throw new FileNotFoundException(gmlDirectory); diff --git a/src/osm/jp/coverage/postoffice/CoveragePostoffice.java b/src/osm/jp/coverage/postoffice/CoveragePostoffice.java index 955ba0d..10b3072 100644 --- a/src/osm/jp/coverage/postoffice/CoveragePostoffice.java +++ b/src/osm/jp/coverage/postoffice/CoveragePostoffice.java @@ -10,9 +10,8 @@ throw new Exception("args[0] <--- 'gisdb'"); } - output( - new File("GML_POSTOFFICE"), - DbPostoffice.TABLE_NAME, + output(new File("GML_POSTOFFICE"), + DbPostoffice.TABLE_NAME1, "国土数値情報 郵便局データ（平成25年）", args[0] ); diff --git a/src/osm/jp/coverage/postoffice/DbPostoffice.java b/src/osm/jp/coverage/postoffice/DbPostoffice.java index 7209b52..b2cb879 100644 --- a/src/osm/jp/coverage/postoffice/DbPostoffice.java +++ b/src/osm/jp/coverage/postoffice/DbPostoffice.java @@ -16,52 +16,138 @@ import org.xml.sax.SAXException; import osm.jp.api.Coverage; import osm.jp.api.Db; +import osm.jp.api.Osmdb; -public class DbPostoffice { - public static final String TABLE_NAME = "POSTOFFICE"; +public class DbPostoffice extends Osmdb { + public static final String CLASS_NAME = "DbPostoffice"; + public static final String TABLE_NAME1 = "POSTOFFICE"; public static final String TABLE_NAME2 = "POSTOFFICE2"; + + /** + * コマンド + */ + static void commandHelp() { + System.out.println("[Command Call]"); + System.out.println("> "+ CLASS_NAME +" -INIT"); + System.out.println(" Initillaize detabase 'table."+ TABLE_NAME1 +"'."); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -IMPORT "); + System.out.println(" Import from GML files."); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -MERGE"); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -EXPORT"); + System.out.println(" 'table."+ TABLE_NAME1 +"'の内容をCSV形式にして標準出力に出力する"); + System.out.println(); + System.out.println("> "+ CLASS_NAME +" -OUTPUT "); + System.out.println(" removedデータをファイルに追記する"); + System.out.println(); + } - - /** メイン + /** + * メイン + * (1) java DbPostoffice -v -INIT + * (2) java DbPostoffice -IMPORT [gml directory] + * (3) java DbPostoffice -MERGE + * (4) java DbPostoffice -EXPORT + * * @param args - * @throws IOException - * @throws SQLException - * @throws ClassNotFoundException - * @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 + * @throws Exception + */ + 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(Coverage.DB_PORP_LOCALDB); if (!dbdir.isDirectory()) { throw new FileNotFoundException("Directory 'database' is not found."); } - Connection con = null; - try { - // DB.tableを作成 - con = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB); - create(con); - - File gmlDir = new File("GML_POSTOFFICE"); - for (File gmlFile : gmlDir.listFiles()) { - if (checkGMLfile(gmlFile)) { - inputPostOffice(con, gmlFile); + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + boolean verbose = false; + String command = null; + String option = null; + for (String arg : args) { + if (arg.equals("-v")){ + verbose = true; + } + else { + if (arg.startsWith("-")) { + command = arg; + } + else { + option = arg; + } } } - DbPostoffice.margeDb(con); - DbPostoffice.export(con); - } - finally { - if (con != null) { - DatabaseTool.closeDb(con); + if (command == null) { + commandHelp(); + return; + } + switch (command) { + case "-INIT": + { + // DB.tableを作成(初期化) + DbPostoffice db = new DbPostoffice(hsqldb); + db.setVerbose(verbose); + db.dropTable(); + db.create(); + break; + } + case "-IMPORT": + { + // GMLからの読み込み + if (option == null) { + commandHelp(); + throw new Exception( + "[ERROR] Illegal command call '"+ CLASS_NAME +" -IMPORT '" + ); + } + DbPostoffice db = new DbPostoffice(hsqldb); + db.setVerbose(verbose); + db.importGmlfiles(option); + break; + } + case "-MERGE": + { + DbPostoffice db = new DbPostoffice(hsqldb); + db.setVerbose(verbose); + db.margeDb(); + break; + } + case "-EXPORT": + { + // 'table.FUEL'の内容をCSV形式にして標準出力に出力する + DbPostoffice db = new DbPostoffice(hsqldb); + db.setVerbose(verbose); + db.export(); + break; + } + default: + commandHelp(); + throw new Exception("[ERROR] Illegal command call '"+ CLASS_NAME +"'"); } } } /** + * コンストラクタ + * + * @param hsqldb + */ + public DbPostoffice(Connection hsqldb) { + super(hsqldb, TABLE_NAME1); + this.hsqldb = hsqldb; + } + + /** * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する * @param con * @param iFile @@ -234,7 +320,7 @@ } } - try (PreparedStatement ps = con.prepareStatement("INSERT INTO "+ TABLE_NAME +"(gid,fixed,idref,area,up,fixed1) VALUES(?,?,?,?,?,?)")) { + try (PreparedStatement ps = con.prepareStatement("INSERT INTO "+ TABLE_NAME1 +"(gid,fixed,idref,area,up,fixed1) VALUES(?,?,?,?,?,?)")) { int s = areaStr.length() - 3; ps.setInt(1, 0); // gid ps.setInt(2, 0); // fixed @@ -242,7 +328,7 @@ ps.setInt(4, Integer.parseInt(areaStr.substring(0, s))); // 都道府県コード ps.setInt(5, 2); // up ps.setInt(6, 0); // fixed1 - System.out.println("INSERT INTO "+ TABLE_NAME +"(gid=0, fixed=0, idref='"+ idStr +"', area="+ 0 +", up=2, fixed1=0)"); + System.out.println("INSERT INTO "+ TABLE_NAME1 +"(gid=0, fixed=0, idref='"+ idStr +"', area="+ 0 +", up=2, fixed1=0)"); ps.executeUpdate(); } } @@ -263,43 +349,92 @@ /** * 'table.FUEL'を新規に作る * 既にテーブルが存在する時には何もしない - * @param con * @throws SQLException */ - public static void create(Connection con) throws SQLException { + @Override + public void create() throws SQLException { String createSt; // 'table.FUEL'を新規に作る //Db.drop(con, TABLE_NAME); createSt = "CREATE TABLE "+ TABLE_NAME2 +" (idref VARCHAR(12) NOT NULL, lat DOUBLE, lon DOUBLE)"; - Db.updateSQL(con, createSt); + Db.updateSQL(hsqldb, createSt); createSt = "CREATE UNIQUE INDEX id2 ON "+ TABLE_NAME2 +" (idref)"; - Db.updateSQL(con, createSt); + Db.updateSQL(hsqldb, createSt); - createSt = "CREATE TABLE "+ TABLE_NAME +" (gid INT, idref VARCHAR(12) NOT NULL, area INT, lat DOUBLE, lon DOUBLE, up INT, fixed INT, fixed1 INT)"; - Db.updateSQL(con, createSt); + createSt = "CREATE TABLE "+ TABLE_NAME1 +" (gid INT, idref VARCHAR(12) NOT NULL, area INT, lat DOUBLE, lon DOUBLE, up INT, fixed INT, fixed1 INT)"; + Db.updateSQL(hsqldb, createSt); - createSt = "CREATE INDEX id1 ON "+ TABLE_NAME +" (idref, lat, lon)"; - Db.updateSQL(con, createSt); + createSt = "CREATE INDEX id1 ON "+ TABLE_NAME1 +" (idref, lat, lon)"; + Db.updateSQL(hsqldb, createSt); + } + + @Override + public DbPostoffice dropTable() throws Exception { + String sqlStr = "DROP TABLE IF EXISTS "+ TABLE_NAME2; + try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) { + if (getVerbose()) System.out.println(sqlStr); + ps.executeUpdate(); + } + sqlStr = "DROP TABLE IF EXISTS "+ TABLE_NAME1; + try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) { + if (getVerbose()) System.out.println(sqlStr); + ps.executeUpdate(); + } + + sqlStr = "DROP INDEX IF EXISTS id2"; + try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) { + if (getVerbose()) System.out.println(sqlStr); + ps.executeUpdate(); + } + sqlStr = "DROP INDEX IF EXISTS id1"; + try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) { + if (getVerbose()) System.out.println(sqlStr); + ps.executeUpdate(); + } + return this; + } + + /** + * + * @param gmlDirectory + * @throws Exception + */ + @Override + public void importGmlfiles(String gmlDirectory) throws Exception { + 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 gmlFile : gmlDir.listFiles()) { + if (checkGMLfile(gmlFile)) { + inputPostOffice(hsqldb, gmlFile); + } + } } /** * UPDATE TABLE_NAME SET column = Expression [, ...] [WHERE Expression]; * - * @param con * @throws java.sql.SQLException */ - public static void margeDb(Connection con) throws SQLException { - PreparedStatement ps8 = con.prepareStatement("SELECT idref,lat,lon FROM "+ TABLE_NAME2); + public void margeDb() throws SQLException { + PreparedStatement ps8 = hsqldb.prepareStatement("SELECT idref,lat,lon FROM "+ TABLE_NAME2); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { String idref = rset8.getString(1); Double lat = rset8.getDouble(2); Double lon = rset8.getDouble(3); - String updateSt = "UPDATE "+ TABLE_NAME +" SET lat=?, lon=? WHERE idref=?"; - try (PreparedStatement ps = con.prepareStatement(updateSt)) { + String updateSt = "UPDATE "+ TABLE_NAME1 +" SET lat=?, lon=? WHERE idref=?"; + try (PreparedStatement ps = hsqldb.prepareStatement(updateSt)) { ps.setDouble(1, lat); ps.setDouble(2, lon); ps.setString(3, idref); @@ -311,14 +446,16 @@ /** * 'table.POSTOFFICE'の内容をCSV形式にして標準出力に出力する - * @param con * @throws java.sql.SQLException */ - public static void export(Connection con) throws SQLException { - String header = "idref,lat,lon,fixed,area"; - System.out.println("TABLE: "+ TABLE_NAME); - System.out.println(header); - PreparedStatement ps8 = con.prepareStatement("SELECT idref,lat,lon,fixed,area FROM "+ TABLE_NAME); + @Override + public void export() throws SQLException { + if (getVerbose()) { + String header = "idref,lat,lon,fixed,area"; + System.out.println("TABLE: "+ TABLE_NAME1); + System.out.println(header); + } + PreparedStatement ps8 = hsqldb.prepareStatement("SELECT idref,lat,lon,fixed,area FROM "+ TABLE_NAME1); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { String name = rset8.getString(1); @@ -326,7 +463,7 @@ Double lon = rset8.getDouble(3); int fixed = rset8.getInt(4); int area = rset8.getInt(5); - System.out.println(name +","+ lat +","+ lon +","+ fixed+","+ area); + if (getVerbose()) System.out.println(name +","+ lat +","+ lon +","+ fixed+","+ area); } } } diff --git a/src/osm/jp/coverage/postoffice/Postoffice.java b/src/osm/jp/coverage/postoffice/Postoffice.java index 52e6160..a2e06ed 100644 --- a/src/osm/jp/coverage/postoffice/Postoffice.java +++ b/src/osm/jp/coverage/postoffice/Postoffice.java @@ -1,17 +1,10 @@ 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.Coverage; @@ -31,21 +24,24 @@ /** * メイン - * + * * java -cp .:osmCoverage.jar:hsqldb_2.2.9.jar osm.jp.coverage.opstoffice.PostOffice [option] + * option: + * -v verbose * * @param args - * @throws IOException - * @throws SQLException - * @throws ClassNotFoundException - * @throws FileNotFoundException - * @throws TransformerException - * @throws SAXException - * @throws ParserConfigurationException + * @throws Exception */ @SuppressWarnings("ResultOfObjectAllocationIgnored") public static void main(String[] args) throws Exception { + boolean verbose = false; + for (String arg : args) { + if (arg.toUpperCase().equals("-V")) { + verbose = true; + } + } + // HSQLディレクトリがなければ作る File dbdir = new File(Coverage.DB_PORP_LOCALDB); if (!dbdir.isDirectory()) { @@ -53,37 +49,35 @@ } try (Connection hsql = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { - new Postoffice(hsql); + new Postoffice(hsql, verbose); } } + public boolean verbose = false; + /** * 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 + * @param verbose + * @throws Exception */ - public Postoffice(Connection con) throws Exception { - String sql4 = "UPDATE "+ DbPostoffice.TABLE_NAME +" SET fixed1=0"; + public Postoffice(Connection con, boolean verbose) throws Exception { + this.verbose = verbose; + + String sqlStr0 = "UPDATE "+ DbPostoffice.TABLE_NAME1 +" SET fixed1=0"; + try (PreparedStatement ps0 = con.prepareStatement(sqlStr0)) { + if (verbose) System.out.println(sqlStr0); + ps0.executeUpdate(); + } + String sql1 = "SELECT idref,lat,lon,score FROM "+ DbExistPostoffice.EXIST_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)"; + String sql2 = "SELECT idref,lat,lon,fixed, area FROM "+ DbPostoffice.TABLE_NAME1 +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)"; + String sql3 = "UPDATE "+ DbPostoffice.TABLE_NAME1 +" SET fixed1=? WHERE idref=? and area=?"; 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)) + PreparedStatement ps3 = con.prepareStatement(sql3)) { - System.out.println(sql4); - ps4.executeUpdate(); - try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { String osmid = rset1.getString("idref"); @@ -113,19 +107,22 @@ } } if (idref != null) { - System.out.println("UPDATE "+ DbPostoffice.TABLE_NAME +" SET fixed1="+ score +" WHERE idref="+ idref +" and area=" + area); + if (verbose) System.out.println("UPDATE "+ DbPostoffice.TABLE_NAME1 +" 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); + if (verbose) System.out.println("NOT FOUND! idref="+ osmid); } } } - - System.out.println("UPDATE "+ DbPostoffice.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"); + } + + String sqlStr5 = "UPDATE "+ DbPostoffice.TABLE_NAME1 +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; + if (verbose) System.out.println(sqlStr5); + try (PreparedStatement ps5 = con.prepareStatement(sqlStr5)) { ps5.executeUpdate(); } } diff --git a/src/osm/jp/coverage/postoffice/ToGeoJSON.java b/src/osm/jp/coverage/postoffice/ToGeoJSON.java index c4da8ae..c0437bf 100644 --- a/src/osm/jp/coverage/postoffice/ToGeoJSON.java +++ b/src/osm/jp/coverage/postoffice/ToGeoJSON.java @@ -12,10 +12,10 @@ * fixed OSMの周辺に存在するかどうか、存在しない場合は0,存在する場合は50 * geom PostGIS形式の位置情報(4612:) * SELECT row_to_json(feature) FROM ( -select 'Feature' As type, ST_AsGeoJSON(ST_Transform(t_POSTOFFICE.geom,4326))::json As geometry, row_to_json(( + select 'Feature' As type, ST_AsGeoJSON(ST_Transform(t_POSTOFFICE.geom,4326))::json As geometry, row_to_json(( SELECT p FROM (SELECT t_POSTOFFICE.idref, t_POSTOFFICE.fixed) AS p)) AS properties From t_POSTOFFICE) As feature -* - * @author yuu + * + * @author haya4 * */ public class ToGeoJSON { @@ -27,11 +27,15 @@ slim = true; } else { - gisdb = argv[0]; + gisdb = arg; } } - osm.jp.postgis.ToGeoJSON obj = new osm.jp.postgis.ToGeoJSON(DbPostoffice.TABLE_NAME); + if (gisdb == null) { + throw new Exception("Illiegal command call."); + } + + osm.jp.postgis.ToGeoJSON obj = new osm.jp.postgis.ToGeoJSON(DbPostoffice.TABLE_NAME1); Connection con = DatabaseTool.openDb(gisdb); try { obj.outputDb(con, "", new File("GML_POSTOFFICE", "postoffice.json"), false); diff --git a/src/osm/jp/coverage/postoffice/ToPostgis.java b/src/osm/jp/coverage/postoffice/ToPostgis.java index 1ce9f10..5ab808b 100644 --- a/src/osm/jp/coverage/postoffice/ToPostgis.java +++ b/src/osm/jp/coverage/postoffice/ToPostgis.java @@ -34,7 +34,7 @@ } public ToPostgis() { - super(DbPostoffice.TABLE_NAME); + super(DbPostoffice.TABLE_NAME1); this.items = new PostgisItems(); items.add(new PostgisItem("gmlid", "idref")); diff --git a/src/osm/jp/postgis/Kml.java b/src/osm/jp/postgis/Kml.java index e657075..ffb8708 100644 --- a/src/osm/jp/postgis/Kml.java +++ b/src/osm/jp/postgis/Kml.java @@ -60,7 +60,7 @@ else if (dbname.equals(osm.jp.coverage.police.DbPolice.TABLE_NAME)) { this.type = new osm.jp.coverage.police.ToPostgis(); } - else if (dbname.equals(osm.jp.coverage.postoffice.DbPostoffice.TABLE_NAME)) { + else if (dbname.equals(osm.jp.coverage.postoffice.DbPostoffice.TABLE_NAME1)) { this.type = new osm.jp.coverage.postoffice.ToPostgis(); } diff --git a/test/osm/jp/coverage/postoffice/DbPostofficeTest.java b/test/osm/jp/coverage/postoffice/DbPostofficeTest.java index b8e785d..d0247d4 100644 --- a/test/osm/jp/coverage/postoffice/DbPostofficeTest.java +++ b/test/osm/jp/coverage/postoffice/DbPostofficeTest.java @@ -62,7 +62,7 @@ } } - PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM "+ DbPostoffice.TABLE_NAME); + PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM "+ DbPostoffice.TABLE_NAME1); try (ResultSet rset8 = ps8.executeQuery()) { if (rset8.next()) { long cnt = rset8.getLong(1); @@ -74,9 +74,9 @@ } // gml:id="po1" n1 name=那覇新都心郵便局 場所: 26.226808 127.692199 - String sql = "SELECT "+ DbPostoffice.TABLE_NAME +".idref, area, "+ DbPostoffice.TABLE_NAME2 +".lat, "+ DbPostoffice.TABLE_NAME2 +".lon" - + " FROM "+ DbPostoffice.TABLE_NAME +","+ DbPostoffice.TABLE_NAME2 - + " WHERE ("+ DbPostoffice.TABLE_NAME +".idref="+ DbPostoffice.TABLE_NAME2 +".idref) and ("+ DbPostoffice.TABLE_NAME +".idref='n1')"; + String sql = "SELECT "+ DbPostoffice.TABLE_NAME1 +".idref, area, "+ DbPostoffice.TABLE_NAME2 +".lat, "+ DbPostoffice.TABLE_NAME2 +".lon" + + " FROM "+ DbPostoffice.TABLE_NAME1 +","+ DbPostoffice.TABLE_NAME2 + + " WHERE ("+ DbPostoffice.TABLE_NAME1 +".idref="+ DbPostoffice.TABLE_NAME2 +".idref) and ("+ DbPostoffice.TABLE_NAME1 +".idref='n1')"; ps1 = hsqldb.prepareStatement(sql); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { @@ -91,9 +91,9 @@ } // gml:id="po68" n68 name=浦添西原郵便局 場所: 26.255802 127.740007 - sql = "SELECT "+ DbPostoffice.TABLE_NAME +".idref, area, "+ DbPostoffice.TABLE_NAME2 +".lat, "+ DbPostoffice.TABLE_NAME2 +".lon" - + " FROM "+ DbPostoffice.TABLE_NAME +","+ DbPostoffice.TABLE_NAME2 - + " WHERE ("+ DbPostoffice.TABLE_NAME +".idref="+ DbPostoffice.TABLE_NAME2 +".idref) and ("+ DbPostoffice.TABLE_NAME +".idref='n68')"; + sql = "SELECT "+ DbPostoffice.TABLE_NAME1 +".idref, area, "+ DbPostoffice.TABLE_NAME2 +".lat, "+ DbPostoffice.TABLE_NAME2 +".lon" + + " FROM "+ DbPostoffice.TABLE_NAME1 +","+ DbPostoffice.TABLE_NAME2 + + " WHERE ("+ DbPostoffice.TABLE_NAME1 +".idref="+ DbPostoffice.TABLE_NAME2 +".idref) and ("+ DbPostoffice.TABLE_NAME1 +".idref='n68')"; ps1 = hsqldb.prepareStatement(sql); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { @@ -108,9 +108,9 @@ } // gml:id="po24526" name=岬町簡易郵便局 場所: 44.105486 145.246918 - sql = "SELECT "+ DbPostoffice.TABLE_NAME +".idref, area, "+ DbPostoffice.TABLE_NAME2 +".lat, "+ DbPostoffice.TABLE_NAME2 +".lon" - + " FROM "+ DbPostoffice.TABLE_NAME +","+ DbPostoffice.TABLE_NAME2 - + " WHERE ("+ DbPostoffice.TABLE_NAME +".idref="+ DbPostoffice.TABLE_NAME2 +".idref) and ("+ DbPostoffice.TABLE_NAME +".idref='n24526')"; + sql = "SELECT "+ DbPostoffice.TABLE_NAME1 +".idref, area, "+ DbPostoffice.TABLE_NAME2 +".lat, "+ DbPostoffice.TABLE_NAME2 +".lon" + + " FROM "+ DbPostoffice.TABLE_NAME1 +","+ DbPostoffice.TABLE_NAME2 + + " WHERE ("+ DbPostoffice.TABLE_NAME1 +".idref="+ DbPostoffice.TABLE_NAME2 +".idref) and ("+ DbPostoffice.TABLE_NAME1 +".idref='n24526')"; ps1 = hsqldb.prepareStatement(sql); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { diff --git a/test/osm/jp/coverage/postoffice/PostofficeTest.java b/test/osm/jp/coverage/postoffice/PostofficeTest.java index 7293a0e..b8e0542 100644 --- a/test/osm/jp/coverage/postoffice/PostofficeTest.java +++ b/test/osm/jp/coverage/postoffice/PostofficeTest.java @@ -38,7 +38,7 @@ public void test01_dataread() { double lat = 35.4316208D; // 35.43035 35.4316208, 139.4094861 double lon = 139.4094861D; //139.36622 - String idrefs = getRefid(DbPostoffice.TABLE_NAME, lat, lon, 50); + String idrefs = getRefid(DbPostoffice.TABLE_NAME1, lat, lon, 50); System.out.println(idrefs); } @@ -50,7 +50,7 @@ public void test92_normal() { double lat = 44.105486D; double lon = 145.246918D; - String idrefs = getRefid(DbPostoffice.TABLE_NAME, lat, lon, 0); + String idrefs = getRefid(DbPostoffice.TABLE_NAME1, lat, lon, 0); System.out.println(idrefs); }