diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index a592e2c..853633c 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -13,21 +13,24 @@ 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 { public String TABLE_NAME = "EXIST_osm"; - Connection hsqldb = null; // hsqldb DatabaseTool.openDb("database"); + public String tableName = TABLE_NAME; + public Connection hsqldb = null; // hsqldb DatabaseTool.openDb("database"); public Osmdb(Connection hsqldb, String tableName) { this.hsqldb = hsqldb; if (tableName != null) { - this.TABLE_NAME = tableName; + this.tableName = tableName; } } @@ -40,18 +43,36 @@ public void create() throws SQLException { String createSt; - sql("DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); - sql("DROP INDEX IF EXISTS "+ TABLE_NAME +"_index;"); + sql("DROP TABLE IF EXISTS "+ tableName +" CASCADE"); + sql("DROP INDEX IF EXISTS "+ tableName +"_index;"); sql("DROP TABLE IF EXISTS AREA_NODE CASCADE"); // 'table.OSM_EXIST'を新規に作る - createSt = "CREATE TABLE "+ TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, gmlid VARCHAR(12), area INT, PRIMARY KEY(idref), removed BOOLEAN DEFAULT FALSE NOT NULL);"; + createSt = "CREATE TABLE "+ tableName + + " (" + + "idref VARCHAR(12) NOT NULL, " + + "name VARCHAR(128), " + + "lat DOUBLE, " + + "lon DOUBLE, " + + "score INT, " + + "gmlid VARCHAR(12), " + + "area INT, " + + "PRIMARY KEY(idref), " + + "removed BOOLEAN DEFAULT FALSE NOT NULL" + + ");"; Db.updateSQL(hsqldb, createSt); - createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat, lon);"; + createSt = "CREATE INDEX "+ tableName +"_index ON "+ tableName + + " (lat, lon);"; Db.updateSQL(hsqldb, createSt); // 'table.AREA_NODE'を新規に作る - createSt = "CREATE TABLE AREA_NODE (idref VARCHAR(12) NOT NULL, pid VARCHAR(12), lat DOUBLE, lon DOUBLE);"; + createSt = "CREATE TABLE AREA_NODE " + + "(" + + "idref VARCHAR(12) NOT NULL, " + + "pid VARCHAR(12), " + + "lat DOUBLE, " + + "lon DOUBLE" + + ");"; Db.updateSQL(hsqldb, createSt); } @@ -86,9 +107,11 @@ */ public void export() throws SQLException { String header = "idref,lat,lon,score"; - System.out.println("TABLE: "+ TABLE_NAME); + System.out.println("TABLE: "+ tableName); System.out.println(header); - PreparedStatement ps8 = hsqldb.prepareStatement("SELECT idref,lat,lon,score,removed FROM "+ TABLE_NAME); + PreparedStatement ps8 = hsqldb.prepareStatement( + "SELECT idref,lat,lon,score,removed FROM "+ tableName + ); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { String idcode = rset8.getString(1); @@ -96,7 +119,10 @@ Double lon = rset8.getDouble(3); int score = rset8.getInt(4); boolean removed = rset8.getBoolean(5); - String out = String.format("OSM: %s,%2.5f,%3.5f,%d,%b", idcode, lat, lon, score, removed); + String out = String.format( + "OSM: %s,%2.5f,%3.5f,%d,%b", + idcode, lat, lon, score, removed + ); System.out.println(out); } } @@ -123,7 +149,9 @@ boolean fixme = false; boolean busYes = false; if (((point & (POINT_FIXME | POINT_BUS_NO)) != 0)) { - PreparedStatement ps8 = osmdb.prepareStatement("SELECT * FROM planet_osm_nodes where id='"+ osmidStr +"'"); + PreparedStatement ps8 = osmdb.prepareStatement( + "SELECT * FROM planet_osm_nodes where id='"+ osmidStr +"'" + ); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { String tags = rset8.getString("tags"); @@ -171,8 +199,10 @@ if (score > 0) { // idref と brandStr をデータベースに格納する - System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES ("+ osmidStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N"); - try (PreparedStatement ps5 = hsqldb.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) { + System.out.println("INSERT INTO "+ tableName +" (idref,lat,lon,score,name) VALUES ("+ osmidStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N"); + try (PreparedStatement ps5 = hsqldb.prepareStatement( + "INSERT INTO "+ tableName +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)" + )) { ps5.setString(1, osmidStr); ps5.setDouble(2, Double.parseDouble(latStr)); ps5.setDouble(3, Double.parseDouble(lonStr)); @@ -206,8 +236,15 @@ try { osmdb = DatabaseTool.openDb("osmdb"); - String sqlSelect = "osm_id,brand,disused,name,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); + String sqlSelect = "osm_id," + + "brand," + + "disused," + + "name," + + "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 + ); System.out.println(sqlNode); PreparedStatement ps1 = osmdb.prepareStatement(sqlNode); @@ -218,7 +255,9 @@ } 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); + String sqlArea = String.format( + "SELECT %s FROM planet_osm_polygon %s", sqlSelect, sqlWhere + ); System.out.println(sqlArea); PreparedStatement ps2 = osmdb.prepareStatement(sqlArea); @@ -277,8 +316,13 @@ try { 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))", tag.getString("k"), tag.getString("v")); - String sql = String.format("%s FROM planet_osm_point,planet_osm_nodes %s", sqlSelect, sqlWhere); + String sqlWhere = String.format( + "WHERE (osm_id=id AND '%s'=ANY(tags) AND '%s'=ANY(tags))", + tag.getString("k"), tag.getString("v") + ); + String sql = String.format( + "%s FROM planet_osm_point,planet_osm_nodes %s", sqlSelect, sqlWhere + ); System.out.println(sql); PreparedStatement ps1 = osmdb.prepareStatement(sql); @@ -313,14 +357,24 @@ long counter = 0L; try { 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 sqlSelect = "SELECT " + + "tags," + + "id," + + "ST_Y(ST_Transform(way,4326)) as lat," + + "ST_X(ST_Transform(way,4326)) as lon"; String sqlSS = ""; for (JsonValue v : array) { JsonObject tag = (JsonObject)v; - sqlSS += String.format(" AND ('%s'=ANY(tags) AND '%s'=ANY(tags))", tag.getString("k"), tag.getString("v")); + sqlSS += String.format( + " AND ('%s'=ANY(tags) AND '%s'=ANY(tags))", + tag.getString("k"), tag.getString("v") + ); } String sqlWhere = String.format("WHERE (osm_id=id %s)", sqlSS); - String sql = String.format("%s FROM planet_osm_point,planet_osm_nodes %s", sqlSelect, sqlWhere); + String sql = String.format( + "%s FROM planet_osm_point,planet_osm_nodes %s", + sqlSelect, sqlWhere + ); System.out.println(sql); PreparedStatement ps1 = osmdb.prepareStatement(sql); @@ -432,12 +486,14 @@ */ 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)", - TABLE_NAME, - idref, lat, lon, score, name); + 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=?", TABLE_NAME); + sql = String.format("DELETE FROM %s WHERE idref=?", tableName); try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) { ps5.setString(1, idref); ps5.executeUpdate(); @@ -449,7 +505,10 @@ // [SQLIntegrityConstraintViolationException]は、無視する } - sql = String.format("INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (?,?,?,?,?,?)", TABLE_NAME); + sql = String.format( + "INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (?,?,?,?,?,?)", + tableName + ); try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) { ps5.setString(1, idref); ps5.setDouble(2, lat); @@ -525,7 +584,9 @@ // idref と brandStr をデータベースに格納する System.out.println("insert into AREA_NODE(idref,lat,lon) values("+ idrefStr +","+ latStr +","+ lonStr+")"); - try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO AREA_NODE (idref,lat,lon) VALUES (?,?,?)")) { + try (PreparedStatement ps5 = con.prepareStatement( + "INSERT INTO AREA_NODE (idref,lat,lon) VALUES (?,?,?)" + )) { ps5.setString(1, idrefStr); ps5.setDouble(2, Double.parseDouble(latStr)); ps5.setDouble(3, Double.parseDouble(lonStr)); @@ -535,7 +596,9 @@ } static Position getNdPosition(Connection con, String idref) throws SQLException { - PreparedStatement ps8 = con.prepareStatement("SELECT lat,lon FROM AREA_NODE where idref=?"); + PreparedStatement ps8 = con.prepareStatement( + "SELECT lat,lon FROM AREA_NODE where idref=?" + ); ps8.setString(1, idref); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { @@ -546,4 +609,13 @@ } 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/fuel/DbFuel.java b/src/osm/jp/coverage/fuel/DbFuel.java index e45a559..194aa39 100644 --- a/src/osm/jp/coverage/fuel/DbFuel.java +++ b/src/osm/jp/coverage/fuel/DbFuel.java @@ -5,6 +5,10 @@ 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; @@ -15,14 +19,14 @@ import org.w3c.dom.NodeList; import org.xml.sax.SAXException; import osm.jp.api.Db; +import osm.jp.api.Osmdb; -public class DbFuel { +public class DbFuel extends Osmdb { + @SuppressWarnings("FieldNameHidesFieldInSuperclass") public static final String TABLE_NAME = "FUEL"; - Connection hsqldb = null; - //public static boolean UPDATE = false; - //public static boolean DROP = false; /** メイン + * * @param args * @throws IOException * @throws SQLException @@ -30,8 +34,13 @@ * @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("[ERROR] Illegal command call 'DbFuel'"); + } + // HSQLディレクトリがなければエラー File dbdir = new File("database"); if (!dbdir.isDirectory()) { @@ -40,21 +49,48 @@ Connection hsqldb = null; try { - // DB.tableを作成 hsqldb = DatabaseTool.openDb("database"); - DbFuel dbFuel = new DbFuel(hsqldb); - dbFuel.dropTable(); - dbFuel.create(); - - File gmlDir = new File("GML_FUEL"); - for (File gmlFile : gmlDir.listFiles()) { - if (checkGMLfile(gmlFile)) { - int index = "P07-15_".length(); - int areacode = Integer.parseInt(gmlFile.getName().substring(index, index+2)); - dbFuel.inputFile(gmlFile, areacode); - } + switch (args[0]) { + case "-INIT": + { + // DB.tableを作成(初期化) + DbFuel dbFuel = new DbFuel(hsqldb); + dbFuel.dropTable(); + dbFuel.create(); + break; + } + case "-IMPORT": + { + // GMLからの読み込み + if (args.length < 2) { + throw new Exception( + "[ERROR] Illegal command call 'DbFuel -IMPORT '" + ); + } + DbFuel dbFuel = new DbFuel(hsqldb); + dbFuel.importGmlfiles(args[1]); + break; + } + case "-REMOVED": + { + // REMOVEDファイルを読み込み + if (args.length < 2) { + throw new Exception( + "[ERROR] Illegal command call 'DbFuel -REMOVED '" + ); + } + DbFuel dbFuel = new DbFuel(hsqldb); + dbFuel.loadRemoved(new File(args[1])); + break; + } + case "-EXPORT": + // 'table.FUEL'の内容をCSV形式にして標準出力に出力する + DbFuel.export(hsqldb); + break; + default: + commandHelp(); + throw new Exception("[ERROR] Illegal command call 'DbFuel'"); } - DbFuel.export(hsqldb); } finally { if (hsqldb != null) { @@ -63,62 +99,58 @@ } } - public DbFuel(Connection hsqldb) { - this.hsqldb = hsqldb; - - } - - public void dropTable () throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - String sqlStr = "DROP TABLE IF EXISTS "+ DbFuel.TABLE_NAME; - try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) { - System.out.println(sqlStr); - ps.executeUpdate(); - } + static void commandHelp() { + System.out.println("[Command Call]"); + System.out.println("> DbFuel -INIT"); + System.out.println(" Initillaize detabase table 'FUEL'."); + System.out.println(); + System.out.println("> DbFuel -IMPORT "); + System.out.println(" Import from GML files."); + System.out.println(); + System.out.println("> DbFuel -REMOVED "); + System.out.println(" Import REMOVED json.text file."); + System.out.println(); + System.out.println("> DbFuel -EXPORT"); + System.out.println(" 'table.*'の内容をCSV形式にして標準出力に出力する"); + System.out.println(); } + public DbFuel(Connection hsqldb) { + super(hsqldb, TABLE_NAME); + this.hsqldb = hsqldb; + } + /** - * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM FUEL" - * POSTGIS "insert into t_FUEL(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));" + * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する * - * @param conHsql - * @param conPost - * @throws java.io.FileNotFoundException - * @throws java.lang.ClassNotFoundException - * @throws java.sql.SQLException - * @throws java.io.IOException - * @throws javax.xml.parsers.ParserConfigurationException - * @throws org.xml.sax.SAXException + * @param gmlDirectory + * @throws ClassNotFoundException + * @throws SQLException + * @throws IOException + * @throws FileNotFoundException + * @throws ParserConfigurationException + * @throws SAXException */ - /* - public static void transport (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - try (PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,idref,area,fixed,ST_Y(geom) AS lat,ST_X(geom) AS lon FROM t_FUEL")) { - try (ResultSet rset1 = ps1.executeQuery()) { - while (rset1.next()) { - long gid = rset1.getLong("gid"); - String gmlid = rset1.getString("idref"); - int area = rset1.getInt("area"); - int fixed = rset1.getInt("fixed"); - double lat = rset1.getDouble("lat"); - double lon = rset1.getDouble("lon"); - - String sqlStr = "INSERT INTO FUEL (gid,lat,lon,fixed,idref,area,up,fixed1) VALUES(?,?,?,?,?,?,0,0)"; - try (PreparedStatement ps = conHsql.prepareStatement(sqlStr)) { - ps.setLong(1, gid); - ps.setString(2, BigDecimal.valueOf(lat).toPlainString()); - ps.setString(3, BigDecimal.valueOf(lon).toPlainString()); - ps.setInt(4, fixed); - ps.setString(5, idref); - ps.setInt(6, area); - System.out.println(sqlStr); - ps.executeUpdate(); - } - } + 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 gmlFile : gmlDir.listFiles()) { + if (checkGMLfile(gmlFile)) { + int index = "P07-15_".length(); + int areacode = Integer.parseInt(gmlFile.getName().substring(index, index+2)); + inputFile(gmlFile, areacode); } } } - */ - /** * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する * @@ -225,14 +257,16 @@ } } - try (PreparedStatement ps = this.hsqldb.prepareStatement("INSERT INTO "+ TABLE_NAME +"(gid,lat,lon,fixed,gmlid,area,up,fixed1) VALUES(0,?,?,?,?,?,2,0)")) { + try (PreparedStatement ps = this.hsqldb.prepareStatement( + "INSERT INTO "+ TABLE_NAME + +"(gid,lat,lon,fixed,gmlid,area,up,fixed1) VALUES(0,?,?,0,?,?,2,0)") + ) { double lat = Double.parseDouble(latStr); double lon = Double.parseDouble(lonStr); ps.setDouble(1, lat); ps.setDouble(2, lon); - ps.setInt(3, 0); - ps.setString(4, idStr); - ps.setInt(5, areacode); + ps.setString(3, idStr); + ps.setInt(4, areacode); System.out.println("INSERT INTO "+ TABLE_NAME +"(gmlid="+ idStr +", lat="+ lat +", lon="+ lon +", fixed=0, area="+ areacode +", up=2, fixed1=0)"); ps.executeUpdate(); } @@ -259,23 +293,25 @@ * 既にテーブルが存在する時には何もしない * @throws SQLException */ + @Override public void create() throws SQLException { String createSt; - // 'table.FUEL'を新規に作る - //Db.drop(con, TABLE_NAME); - createSt = "CREATE TABLE "+ DbFuel.TABLE_NAME +" (gid INT, gmlid VARCHAR(12) NOT NULL, idref VARCHAR(12), lat DOUBLE, lon DOUBLE, fixed INT, area INT, up INT, fixed1 INT, removed BOOLEAN)"; + // drop(con, TABLE_NAME); + createSt = "CREATE TABLE "+ TABLE_NAME + + " (" + + "gid INT, " + + "gmlid VARCHAR(12) NOT NULL, " + + "idref VARCHAR(12), " + + "lat DOUBLE, " + + "lon DOUBLE, " + + "fixed INT, " + + "area INT, " + + "up INT, " + + "fixed1 INT, " + + "removed BOOLEAN DEFAULT FALSE NOT NULL" + + ")"; Db.updateSQL(this.hsqldb, createSt); - - /* - drop(con, "existing_data"); - createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));"; - Db.updateSQL(con, createSt); - - drop(con, "coverage"); - createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);"; - Db.updateSQL(con, createSt); - */ } /** @@ -285,9 +321,11 @@ */ public static void export(Connection con) throws SQLException { String header = "gmlid,idref,lat,lon,fixed"; - System.out.println("TABLE: "+ DbFuel.TABLE_NAME); + System.out.println("TABLE: "+ TABLE_NAME); System.out.println(header); - PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,idref,lat,lon,fixed,area FROM "+ DbFuel.TABLE_NAME); + PreparedStatement ps8 = con.prepareStatement( + "SELECT gmlid,idref,lat,lon,fixed,area FROM "+ DbFuel.TABLE_NAME + ); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { String gmlid = rset8.getString("gmlid"); @@ -300,4 +338,90 @@ } } } + + 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 sql = String.format("SELECT gmlid,area,idref %s %s", fromStr, whereStr); + + try (FileWriter fw = new FileWriter(removedFile, true); + 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/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index 0bba21b..d6da2f7 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -10,9 +10,6 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; -import javax.json.Json; -import javax.json.JsonObject; -import javax.json.JsonObjectBuilder; import jp.co.areaweb.tools.database.*; import osm.jp.api.Japan; @@ -48,15 +45,12 @@ dbdir.mkdir(); } - Connection con = DatabaseTool.openDb("database"); - try { - Fuel fuel = new Fuel(con); + try (Connection hsql = DatabaseTool.openDb("database")) { + new Fuel(hsql); + File removedFile = new File("GML_FUEL", "P07-15.removed.json.txt"); - fuel.delRemoved(removedFile); - fuel.outputRemoved(removedFile); - } - finally { - DatabaseTool.closeDb(con); + DbFuel dbFuel = new DbFuel(hsql); + dbFuel.outputRemoved(removedFile); } } @@ -75,14 +69,6 @@ public Fuel(Connection hsqldb) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { this.hsqldb = hsqldb; - String sql0 = "UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=?,removed=?"; - try (PreparedStatement ps0 = hsqldb.prepareStatement(sql0)) { - System.out.println(sql0+ " <-- 0, false"); - ps0.setInt(1, 0); - ps0.setBoolean(2, false); - ps0.executeUpdate(); - } - String sql1 = "SELECT gmlid,area,lat,lon,idref FROM "+ DbFuel.TABLE_NAME; try (PreparedStatement ps1 = hsqldb.prepareStatement(sql1)) { try (ResultSet rset1 = ps1.executeQuery()) { @@ -165,64 +151,4 @@ } } - /** - * removedファイルに書かれたデータをDBから削除する - * - * @param removedFile - * @throws java.io.IOException - */ - public void delRemoved(File removedFile) throws IOException { - if (removedFile.exists()) { - try (BufferedReader br = new BufferedReader(new FileReader(removedFile));) { - while (true) { - String line = br.readLine(); - if (line == null) { - break; - } - System.out.println(line); - - //JsonObject jsonObject = new JsonParser().parse(line).getAsJsonObject(); - - } - } - } - } - - /** - * 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 sql = String.format("SELECT gmlid,area,idref %s %s", fromStr, whereStr); - - try (FileWriter fw = new FileWriter(removedFile, true); - 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(); - } - } - } - } } \ No newline at end of file diff --git a/test/osm/jp/coverage/fuel/DbFuelTest.java b/test/osm/jp/coverage/fuel/DbFuelTest.java index bd24b51..7d5921a 100644 --- a/test/osm/jp/coverage/fuel/DbFuelTest.java +++ b/test/osm/jp/coverage/fuel/DbFuelTest.java @@ -1,16 +1,22 @@ package osm.jp.coverage.fuel; -import java.io.IOException; -import java.sql.SQLException; -import javax.xml.parsers.ParserConfigurationException; +import java.io.File; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import jp.co.areaweb.tools.database.DatabaseTool; +import static org.hamcrest.CoreMatchers.is; import org.junit.After; import org.junit.AfterClass; +import static org.junit.Assert.assertThat; import static org.junit.Assert.fail; import org.junit.Before; import org.junit.BeforeClass; +import org.junit.FixMethodOrder; import org.junit.Test; -import org.xml.sax.SAXException; +import org.junit.runners.MethodSorters; +@FixMethodOrder (MethodSorters.NAME_ASCENDING) public class DbFuelTest { @BeforeClass @@ -29,15 +35,246 @@ public void tearDown() throws Exception { } + String databaseName = "database"; + String tableName = "FUEL"; + + /** + * コマンド引数チェック + * + * @throws Exception + */ @Test - public void test00_main() throws Exception { + public void t00_main_null() throws Exception { try { - String[] args = new String[]{"-DROP"}; + String[] args = new String[]{}; + DbFuel.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[]{"-"}; + DbFuel.main(args); + fail(); + } + catch(Exception e) { + System.out.println("OK! --> "+ e.toString()); + } + } + + /** + * （１）テーブルを初期化する + * + * @throws Exception + */ + @Test + public void t10_main_init() throws Exception { + try { + String[] args = new String[]{"-INIT"}; DbFuel.main(args); } - catch(IOException | ClassNotFoundException | SQLException | ParserConfigurationException | SAXException e) { + catch(Exception e) { + fail(e.toString()); + } + + checkDatabase(databaseName); + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + assertThat(isTable(hsqldb, tableName), is(true)); + assertThat(getRecordCount(hsqldb, tableName), is(0)); + } + catch (Exception e) { fail(e.toString()); } } + /** + * （２）GMLファイルを読み取る(国土数値情報の読み取り) + * + * @throws Exception + */ + @Test + public void t20_main_import_ng() throws Exception { + try { + String[] args = new String[]{"-IMPORT"}; + DbFuel.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"}; + DbFuel.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_FUEL"}; + DbFuel.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt = getRecordCount(hsqldb, tableName); + System.out.println("'FUEL' table count = " + cnt); + assertThat(cnt > 100, is(true)); + + cnt = getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'FUEL REMOVED=TRUE' table count = " + cnt); + assertThat(cnt, is(0)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * （３）DISUSEDなPOIを読み込む + * + * @throws Exception + */ + @Test + public void t30_main_removed() throws Exception { + try { + String[] args = new String[]{"-REMOVED", "GML_FUEL/P07-15.removed.json.txt"}; + DbFuel.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt = getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'FUEL REMOVED=TRUE' table count = " + cnt); + assertThat(cnt > 10, is(true)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * テーブル内容を標準出力する + * + * @throws Exception + */ + @Test + public void t90_main_export() throws Exception { + try { + String[] args = new String[]{"-EXPORT"}; + DbFuel.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + } + + //-------------------------------------------------------- + // Test tools SQL + //-------------------------------------------------------- + + @SuppressWarnings("UseSpecificCatch") + void checkDatabase(String databaseName) { + File dbdir = new File(databaseName); + if (!dbdir.isDirectory()) { + fail("Directory 'database' is not found."); + } + + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb(databaseName); + } + catch (Exception e) { + fail(e.toString()); + } + finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + } + + /** + * データベースにテーブルが存在するかどうか + * + * @param con + * @param table + * @return + */ + @SuppressWarnings("UseSpecificCatch") + boolean isTable(Connection con, String table) { + try { + PreparedStatement ps = con.prepareStatement( + "SELECT count(*) FROM "+ table + ); + try (ResultSet rset = ps.executeQuery()) { + return rset.next(); + } + catch (Exception ex) { + return false; + } + } + catch (Exception ex) { + return false; + } + } + + /** + * データベースにテーブルが存在するかどうか + * + * @param con + * @param table + * @return + */ + @SuppressWarnings("UseSpecificCatch") + int getRecordCount(Connection con, String table) { + return getRecordCount(con, table, null); + } + + /** + * データベースにテーブルが存在するかどうか + * + * @param con + * @param table + * @return + */ + @SuppressWarnings("UseSpecificCatch") + int getRecordCount(Connection con, String table, String where) { + try { + PreparedStatement ps = con.prepareStatement( + "SELECT count(*) FROM "+ table + + (where != null ? " WHERE "+ where : "") + ); + try (ResultSet rset = ps.executeQuery()) { + if (rset.next()) { + return rset.getInt(1); + } + fail(); + } + catch (Exception ex) { + fail(); + } + } + catch (Exception ex) { + fail(); + } + return -1; + } } diff --git a/test/osm/jp/coverage/fuel/OsmExistTest.java b/test/osm/jp/coverage/fuel/OsmExistTest.java new file mode 100644 index 0000000..6c477d5 --- /dev/null +++ b/test/osm/jp/coverage/fuel/OsmExistTest.java @@ -0,0 +1,72 @@ +package osm.jp.coverage.fuel; + +import java.sql.Connection; +import java.util.ArrayList; +import jp.co.areaweb.tools.database.DatabaseTool; +import org.junit.*; +import org.junit.runners.MethodSorters; +import osm.jp.api.Feature; +import osm.jp.api.Japan; +import static osm.jp.api.Osmdb.POINT_FIXME; +import static osm.jp.api.Osmdb.POINT_NO; +import static osm.jp.api.Osmdb.POINT_NO_BRAND; + +@FixMethodOrder (MethodSorters.NAME_ASCENDING) +public class OsmExistTest { + public void init() throws Exception { + } + + + @Before + public void setUp() throws Exception { + } + + @After + public void tearDown() throws Exception { + } + + @Test + public void test02_main() throws Exception { + Connection hsqldb = null; + try { + // DB.tableを作成 + hsqldb = DatabaseTool.openDb("database"); + + ArrayList features = new ArrayList<>(); + features.add(new Feature(Feature.NODE, "amenity", "fuel", POINT_NO_BRAND | POINT_FIXME)); + features.add(new Feature(Feature.NODE, "disused:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "abandoned:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "demolished:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "historic:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "was:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "removed:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "no:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "amenity", "fuel", POINT_NO_BRAND | POINT_FIXME)); + features.add(new Feature(Feature.AREA, "disused:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "abandoned:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "demolished:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "historic:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "was:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "removed:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "no:amenity", "fuel", POINT_NO)); + + /** + * 既存のOSMガソリンスタンドを読み込む + * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする + */ + OsmExistFuel osmExistFuel = new OsmExistFuel(hsqldb); + osmExistFuel.getJapanCapabilities(features, testBoxes); + osmExistFuel.export(); + } + finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + } + + public static Japan[] testBoxes = new Japan[] { + // 神奈川県東部 + new Japan(138D, 34D, 2D) + }; +}