diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java new file mode 100644 index 0000000..bc47ffc --- /dev/null +++ b/src/osm/jp/coverage/fuel/DbExist.java @@ -0,0 +1,246 @@ +package osm.jp.coverage.fuel; + +import java.io.*; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +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.Db; + +public class DbFuel { + public static final String TABLE_NAME = "FUEL"; + + /** メイン + * @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 + { + // HSQLディレクトリがなければ作る + File dbdir = new File("database"); + if (!dbdir.isDirectory()) { + dbdir.mkdir(); + } + Connection con = null; + try { + // DB.tableを作成 + con = DatabaseTool.openDb("database"); + create(con); + + // + 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)); + inputFile(con, gmlFile, areacode); + } + } + + DbFuel.export(con); + } + finally { + if (con != null) { + DatabaseTool.closeDb(con); + } + } + } + + /** + * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する + * @param con + * @param iFile + * @param areacode + * @throws FileNotFoundException + * @throws ClassNotFoundException + * @throws SQLException + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + */ + public static void inputFile (Connection con, File iFile, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + DocumentBuilderFactory factory; + DocumentBuilder builder; + + factory = DocumentBuilderFactory.newInstance(); + builder = factory.newDocumentBuilder(); + factory.setIgnoringElementContentWhitespace(true); + factory.setIgnoringComments(true); + factory.setValidating(true); + + // Root node --> + Node root = builder.parse(iFile); + NodeList nodes = root.getChildNodes(); + for (int i=0; i < nodes.getLength(); i++) { + Node nodePoint = nodes.item(i); + if (nodePoint.getNodeName().equals("ksj:Dataset")) { + int iCounter = showNodes(con, nodePoint, areacode); + System.out.println("("+ areacode +") データ数["+ iCounter +"]"); + } + } + + } + + /** + * node : + * + * + * 43.03442215 141.47026381 + * + * + * + * @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; + + // Root node : + // Child node : 43.03442215 141.47026381 + // + NodeList nodes = node.getChildNodes(); + for (int i=0; i < nodes.getLength(); i++) { + Node nodePoint = nodes.item(i); + switch (nodePoint.getNodeName()) { + case "gml:Point": + iCounter++; + showGmPoint(con, nodePoint, areacode); + break; + default: + System.out.println(nodePoint.getNodeName()); + break; + } + } + return iCounter; + } + + /** + * node : + * + * 43.03442215 141.47026381 + * + * + * @param con + * @param node + * @throws IOException + * @throws SQLException + */ + public static void showGmPoint(Connection con, Node node, int areacode) throws IOException, SQLException { + String latStr = ""; + String lonStr = ""; + String idStr = ""; + + NamedNodeMap attrMap = node.getAttributes(); + if (null != attrMap ) { + for (int j=0; j < attrMap.getLength(); j++) { + if (attrMap.item(j).getNodeName().equals("gml:id")) { + idStr = attrMap.item(j).getNodeValue(); + } + } + } + + NodeList nodes = node.getChildNodes(); + for (int i=0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + if (node2.getNodeName().equals("gml:pos")) { + String positionStr = node2.getTextContent(); + String[] str4Ary = positionStr.split(" "); + latStr = str4Ary[0]; + lonStr = str4Ary[1]; + break; + } + } + + try (PreparedStatement ps = con.prepareStatement("INSERT INTO "+ TABLE_NAME +"(lat,lon,fixed,idref,area) VALUES(?,?,?,?,?)")) { + 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); + System.out.println("INSERT "+ TABLE_NAME +"("+ idStr +", lat="+ lat +", lon="+ lon +", fixed=0, area="+ areacode +")"); + ps.executeUpdate(); + } + } + + /** + * 数値地図情報のデータファイルかどうかを見極める + * @param f + * @return + */ + public static boolean checkGMLfile(File f) { + if (f.isDirectory()) { + return false; + } + String name = f.getName(); + if (!name.startsWith("P07")) { + return false; + } + return name.toUpperCase().endsWith(".XML"); + } + + /** + * 'table.FUEL'を新規に作る + * 既にテーブルが存在する時には何もしない + * @param con + * @throws SQLException + */ + public static void create(Connection con) throws SQLException { + String createSt; + + // 'table.FUEL'を新規に作る + //Db.drop(con, TABLE_NAME); + createSt = "CREATE TABLE "+ TABLE_NAME +" (idref VARCHAR(12) NOT NULL, lat DOUBLE, lon DOUBLE, fixed INT, area INT)"; + Db.updateSQL(con, 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); + */ + } + + /** + * 'table.FUEL'の内容をCSV形式にして標準出力に出力する + * @param con + * @throws java.sql.SQLException + */ + public static void export(Connection con) throws SQLException { + String header = "idref,lat,lon,fixed"; + System.out.println("TABLE: "+ TABLE_NAME); + System.out.println(header); + PreparedStatement ps8 = con.prepareStatement("SELECT idref,lat,lon,fixed,area FROM "+ TABLE_NAME); + try (ResultSet rset8 = ps8.executeQuery()) { + while (rset8.next()) { + String name = rset8.getString(1); + Double lat = rset8.getDouble(2); + Double lon = rset8.getDouble(3); + int fixed = rset8.getInt(4); + int area = rset8.getInt(5); + System.out.println(name +","+ lat +","+ lon +","+ fixed+","+ area); + } + } + } +} \ No newline at end of file