diff --git a/src/osm/jp/api/OsmExist.java b/src/osm/jp/api/OsmExist.java index d98d978..e26f9ed 100644 --- a/src/osm/jp/api/OsmExist.java +++ b/src/osm/jp/api/OsmExist.java @@ -232,12 +232,20 @@ * @param gmlDirectory * @throws Exception */ + @Override public void importGmlfiles(String gmlDirectory) throws Exception { throw new Exception( String.format("Illiegal method. 'importGmlfiles(%s)'", gmlDirectory) ); } + @Override + public Osmdb dropTable() throws Exception { + throw new Exception( + String.format("Illiegal method. 'dropTable()'") + ); + } + /** * * @param point diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index b23e2b2..fa6f77b 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -57,7 +57,7 @@ } return this; } - + public void sql(String sql) throws SQLException { System.out.println(sql); try (PreparedStatement ps = hsqldb.prepareStatement(sql)) { diff --git a/src/osm/jp/coverage/police/CoveragePolice2.java b/src/osm/jp/coverage/police/CoveragePolice2.java index 30e519d..083c0a9 100644 --- a/src/osm/jp/coverage/police/CoveragePolice2.java +++ b/src/osm/jp/coverage/police/CoveragePolice2.java @@ -13,9 +13,8 @@ public static void main(String[] args) throws Exception { - output( - new File("GML_POLICE"), - DbPolice.TABLE_NAME, + output(new File("GML_POLICE"), + DbPolice.TABLE_NAME1, "police2", "国土数値情報 警察署データ(平成24年:2012)2:警察署", "code=2", diff --git a/src/osm/jp/coverage/police/CoveragePolice4.java b/src/osm/jp/coverage/police/CoveragePolice4.java index 102747c..5033612 100644 --- a/src/osm/jp/coverage/police/CoveragePolice4.java +++ b/src/osm/jp/coverage/police/CoveragePolice4.java @@ -12,9 +12,8 @@ public static void main(String[] args) throws Exception { - output( - new File("GML_POLICE"), - DbPolice.TABLE_NAME, + output(new File("GML_POLICE"), + DbPolice.TABLE_NAME1, "police4", "国土数値情報 警察署データ(平成24年:2012)4:交番", "code=4", diff --git a/src/osm/jp/coverage/police/CoveragePolice5.java b/src/osm/jp/coverage/police/CoveragePolice5.java index 0b65924..79e79df 100644 --- a/src/osm/jp/coverage/police/CoveragePolice5.java +++ b/src/osm/jp/coverage/police/CoveragePolice5.java @@ -11,9 +11,8 @@ public class CoveragePolice5 extends Coverage { public static void main(String[] args) throws Exception { - output( - new File("GML_POLICE"), - DbPolice.TABLE_NAME, + output(new File("GML_POLICE"), + DbPolice.TABLE_NAME1, "police5", "国土数値情報 警察署データ(平成24年:2012)5:駐在所", "code=5", diff --git a/src/osm/jp/coverage/police/CoveragePolice6.java b/src/osm/jp/coverage/police/CoveragePolice6.java index 93b3dd6..cf59fc6 100644 --- a/src/osm/jp/coverage/police/CoveragePolice6.java +++ b/src/osm/jp/coverage/police/CoveragePolice6.java @@ -12,9 +12,8 @@ { public static void main(String[] args) throws Exception { - output( - new File("GML_POLICE"), - DbPolice.TABLE_NAME, + output(new File("GML_POLICE"), + DbPolice.TABLE_NAME1, "police6", "国土数値情報 警察署データ(平成24年:2012)6:派出所", "code=6", diff --git a/src/osm/jp/coverage/police/DbPolice.java b/src/osm/jp/coverage/police/DbPolice.java index a7d95d1..7134ddd 100644 --- a/src/osm/jp/coverage/police/DbPolice.java +++ b/src/osm/jp/coverage/police/DbPolice.java @@ -7,20 +7,36 @@ import java.sql.SQLException; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; -import javax.xml.parsers.ParserConfigurationException; import jp.co.areaweb.tools.database.*; import org.w3c.dom.NamedNodeMap; import org.w3c.dom.Node; import org.w3c.dom.NodeList; -import org.xml.sax.SAXException; import osm.jp.api.Coverage; import osm.jp.api.Db; +import osm.jp.api.Osmdb; -public class DbPolice { - public static final String TABLE_NAME = "POLICE"; +public class DbPolice extends Osmdb { + public static final String CLASS_NAME = "DbPolice"; + public static final String TABLE_NAME1 = "POLICE"; public static final String TABLE_NAME2 = "POLICE2"; + /** + * コマンド + */ + 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 +" -EXPORT"); + System.out.println(" 'table."+ TABLE_NAME1 +"'の内容をCSV形式にして標準出力に出力する"); + System.out.println(); + } + File inputFile; String filter = ""; int iCounter = 0; @@ -29,31 +45,95 @@ String timeStampStr = null; File dir = null; - /** メイン + /** + * メイン + * 1. java DbPolice -v -INIT + * 2. java DbPolice -v -IMPORT [GML directory] + * 3. java DbPolice -v -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 'Coverage.DB_PORP_LOCALDB' is not found."); } + 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; + } + } + } + + if (command == null) { + commandHelp(); + return; + } + switch (command) { + case "-INIT": + { + // DB.tableを作成(初期化) + DbPolice db = new DbPolice(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 '" + ); + } + DbPolice db = new DbPolice(hsqldb); + db.setVerbose(verbose); + db.importGmlfiles(option); + break; + } + case "-EXPORT": + { + // 'table.FUEL'の内容をCSV形式にして標準出力に出力する + DbPolice db = new DbPolice(hsqldb); + db.setVerbose(verbose); + db.export(); + break; + } + default: + commandHelp(); + throw new Exception("[ERROR] Illegal command call '"+ CLASS_NAME +"'"); + } + } + /* Connection conHsql = null; try { // DB.tableを作成 conHsql = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB); create(conHsql); - /** - * 都道府県ごとのGMLディレクトリの処理 - */ int fcounter = 0; File folder = new File("GML_POLICE"); for (File xmlfile : folder.listFiles()) { @@ -77,6 +157,17 @@ DatabaseTool.closeDb(conHsql); } } + */ + } + + /** + * コンストラクタ + * + * @param hsqldb + */ + public DbPolice(Connection hsqldb) { + super(hsqldb, TABLE_NAME1); + this.hsqldb = hsqldb; } /** @@ -84,18 +175,9 @@ * @param conHsql * @param iFile * @param areacode - * @throws FileNotFoundException - * @throws ClassNotFoundException - * @throws SQLException - * @throws IOException - * @throws ParserConfigurationException - * @throws SAXException + * @throws Exception */ - public static void importPOI (Connection conHsql, File iFile, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; - - String iStr = iFile.getName(); - + public void importPOI (Connection conHsql, File iFile, int areacode) throws Exception { DocumentBuilderFactory factory; DocumentBuilder builder; Node root; @@ -107,8 +189,7 @@ factory.setValidating(true); root = builder.parse(iFile); - iCounter += showNodes(conHsql, root, areacode); - System.out.println("("+ areacode +") POI数["+ iCounter +"]"); + showNodes(conHsql, root, areacode); } /** @@ -116,12 +197,10 @@ * @param con * @param node * @param areacode - * @return * @throws IOException * @throws SQLException */ - public static int showNodes(Connection con, Node node, int areacode) throws IOException, SQLException { - int iCounter = 0; + public void showNodes(Connection con, Node node, int areacode) throws IOException, SQLException { NodeList nodes = node.getChildNodes(); for (int i=0; i ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)", DbPolice.TABLE_NAME); - String sql3 = String.format("UPDATE %s SET fixed1=? WHERE idref=? and area=?", DbPolice.TABLE_NAME); + String sql2 = String.format("SELECT idref,lat,lon,fixed,area FROM %s WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)", DbPolice.TABLE_NAME1); + String sql3 = String.format("UPDATE %s SET fixed1=? WHERE idref=? and area=?", DbPolice.TABLE_NAME1); System.out.println(sql1); System.out.println(sql2); System.out.println(sql3); @@ -145,7 +145,7 @@ } } - String sql5 = String.format("UPDATE %s SET up=1 WHERE (fixed<>fixed1) and (up=0)", DbPolice.TABLE_NAME); + String sql5 = String.format("UPDATE %s SET up=1 WHERE (fixed<>fixed1) and (up=0)", DbPolice.TABLE_NAME1); try (PreparedStatement ps5 = con.prepareStatement(sql5)) { System.out.println(sql5); diff --git a/src/osm/jp/coverage/police/ToGeoJSON.java b/src/osm/jp/coverage/police/ToGeoJSON.java index 0518ceb..185069c 100644 --- a/src/osm/jp/coverage/police/ToGeoJSON.java +++ b/src/osm/jp/coverage/police/ToGeoJSON.java @@ -33,7 +33,7 @@ } File dir = new File("GML_POLICE"); - osm.jp.postgis.ToGeoJSON obj = new osm.jp.postgis.ToGeoJSON(DbPolice.TABLE_NAME); + osm.jp.postgis.ToGeoJSON obj = new osm.jp.postgis.ToGeoJSON(DbPolice.TABLE_NAME1); Connection con = DatabaseTool.openDb(gisdb); try { obj.outputDb(con, "", new File(dir, "police.json"), false); diff --git a/src/osm/jp/coverage/police/ToPostgis.java b/src/osm/jp/coverage/police/ToPostgis.java index 8e6e267..f7da582 100644 --- a/src/osm/jp/coverage/police/ToPostgis.java +++ b/src/osm/jp/coverage/police/ToPostgis.java @@ -34,7 +34,7 @@ } public ToPostgis() { - super(DbPolice.TABLE_NAME); + super(DbPolice.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 ffb8708..d50a30f 100644 --- a/src/osm/jp/postgis/Kml.java +++ b/src/osm/jp/postgis/Kml.java @@ -57,7 +57,7 @@ else if (dbname.equals(osm.jp.coverage.fuel.DbFuel.TABLE_NAME)) { this.type = new osm.jp.coverage.fuel.ToPostgis(); } - else if (dbname.equals(osm.jp.coverage.police.DbPolice.TABLE_NAME)) { + else if (dbname.equals(osm.jp.coverage.police.DbPolice.TABLE_NAME1)) { this.type = new osm.jp.coverage.police.ToPostgis(); } else if (dbname.equals(osm.jp.coverage.postoffice.DbPostoffice.TABLE_NAME1)) { diff --git a/test/osm/jp/coverage/police/DbPoliceTest.java b/test/osm/jp/coverage/police/DbPoliceTest.java index eb9fdf0..1422a29 100644 --- a/test/osm/jp/coverage/police/DbPoliceTest.java +++ b/test/osm/jp/coverage/police/DbPoliceTest.java @@ -47,7 +47,7 @@ try { hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB); - PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM "+ DbPolice.TABLE_NAME); + PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM "+ DbPolice.TABLE_NAME1); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { long cnt = rset1.getLong(1); @@ -108,11 +108,11 @@ void checkSql(Connection hsqldb, String idref, int area, int code, String lat, String lon) throws SQLException { String sql = String.format("SELECT %s.idref, %s.area, %s.code, %s.lat, %s.lon", - DbPolice.TABLE_NAME, DbPolice.TABLE_NAME, DbPolice.TABLE_NAME, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME2) - + String.format(" FROM %s,%s", DbPolice.TABLE_NAME, DbPolice.TABLE_NAME2) + DbPolice.TABLE_NAME1, DbPolice.TABLE_NAME1, DbPolice.TABLE_NAME1, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME2) + + String.format(" FROM %s,%s", DbPolice.TABLE_NAME1, DbPolice.TABLE_NAME2) + String.format(" WHERE (%s.idref=%s.idref) and (%s.area=%s.area) and (%s.idref='%s') and (%s.area=%d)", - DbPolice.TABLE_NAME, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME, - idref, DbPolice.TABLE_NAME, area); + DbPolice.TABLE_NAME1, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME1, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME1, + idref, DbPolice.TABLE_NAME1, area); System.out.println(sql); PreparedStatement ps1 = hsqldb.prepareStatement(sql); try (ResultSet rset1 = ps1.executeQuery()) { diff --git a/test/osm/jp/coverage/police/PoliceTest.java b/test/osm/jp/coverage/police/PoliceTest.java index 266e90d..fef12ff 100644 --- a/test/osm/jp/coverage/police/PoliceTest.java +++ b/test/osm/jp/coverage/police/PoliceTest.java @@ -73,7 +73,7 @@ public void test92_normal() { double lat = 35.4214195D; double lon = 139.3600994D; - String idrefs = getRefid(DbPolice.TABLE_NAME, lat, lon, 50); + String idrefs = getRefid(DbPolice.TABLE_NAME1, lat, lon, 50); System.out.println(idrefs); } @@ -97,7 +97,7 @@ area, String.valueOf(lat-0.001D), String.valueOf(lat+0.001D), String.valueOf(lon-0.001D), String.valueOf(lon+0.001D)); - String sql = String.format("SELECT * FROM %s WHERE %s", DbPolice.TABLE_NAME, where); + String sql = String.format("SELECT * FROM %s WHERE %s", DbPolice.TABLE_NAME1, where); System.out.println(sql); PreparedStatement ps1 = hsqldb.prepareStatement(sql); try (ResultSet rset1 = ps1.executeQuery()) {