package osm.jp.coverage.busstop;
import java.io.*;
import java.sql.Connection;
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;
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.HttpPOST;
public class DbBusstop {
public static final String TABLE_NAME = "bus_stop";
public static boolean DROP = false;
File inputFile;
String filter = "";
int iCounter = 0;
String urlStr = "";
Connection con;
String timeStampStr = null;
File dir = null;
/** メイン
* 動作条件； HSQLDBのフォルダを削除した状態で実行すること。
* フォルダを削除しないで実行する場合は「-DROP」オプションを使うこと
* @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
{
for (String arg : args) {
if (arg.toUpperCase().equals("-DROP")) {
DROP = true;
}
}
// 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");
if (DROP) {
HttpPOST.sql(conHsql, "DROP TABLE "+ TABLE_NAME);
HttpPOST.sql(conHsql, "DROP INDEX "+ TABLE_NAME +"_index;");
}
create(conHsql);
try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) {
ps.executeUpdate();
}
/**
* 都道府県ごとの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++;
}
}
}
}
System.out.println("["+ fcounter +"]つのGMLファイルをインポートしました。");
DbBusstop.export(conHsql);
}
finally {
if (conHsql != null) {
DatabaseTool.closeDb(conHsql);
}
}
}
/**
* 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する
* @param conHsql
* @param iFile
* @param areacode
* @throws FileNotFoundException
* @throws ClassNotFoundException
* @throws SQLException
* @throws IOException
* @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;
String iStr = iFile.getName();
DocumentBuilderFactory factory;
DocumentBuilder builder;
Node root;
factory = DocumentBuilderFactory.newInstance();
builder = factory.newDocumentBuilder();
factory.setIgnoringElementContentWhitespace(true);
factory.setIgnoringComments(true);
factory.setValidating(true);
root = builder.parse(iFile);
iCounter += showNodes(conHsql, root, iStr.substring(0, iStr.length() - 4), areacode);
System.out.println("("+ areacode +") バス停数["+ iCounter +"]");
}
/**
*
* @param con
* @param node
* @param iFileName // ソースファイル名（拡張子を含まない）
* @param areacode
* @return
* @throws IOException
* @throws SQLException
*/
public static int showNodes(Connection con, Node node, String iFileName, int areacode) throws IOException, SQLException {
int iCounter = 0;
NodeList nodes = node.getChildNodes();
for (int i=0; i<nodes.getLength(); i++) {
Node node2 = nodes.item(i);
switch (node2.getNodeName()) {
case "gml:Point":
showGmlPoint(con, node2, areacode);
break;
case "ksj:BusStop":
iCounter++;
showBusStop(con, node2, iFileName, areacode);
break;
default:
iCounter += showNodes(con, node2, iFileName, areacode);
break;
}
}
return iCounter;
}
/**
* <gml:Point gml:id="n1">
* <gml:pos>35.14591397 139.10569573</gml:pos>
* </gml:Point>
*
* @param con
* @param node
* @param areacode
* @throws IOException
* @throws SQLException
*/
public static void showGmlPoint(Connection con, Node node, int areacode) throws IOException, SQLException {
String positionStr = "";
String latStr = "";
String lonStr = "";
String idStr = "";
NamedNodeMap nodeMap = node.getAttributes();
if ( null != nodeMap ) {
for ( int j=0; j<nodeMap.getLength(); j++ ) {
if (nodeMap.item(j).getNodeName().equals("gml:id")) {
idStr = nodeMap.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")) {
positionStr = node2.getTextContent().trim();
String[] str4Ary = positionStr.split(" ");
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)")) {
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)");
ps6.setDouble(1, lat);
ps6.setDouble(2, lon);
ps6.setInt(3, areacode);
ps6.setString(4, idStr);
ps6.executeUpdate();
}
}
}
}
/**
* <ksj:BusStop gml:id="ED01_1">
* <ksj:position xlink:href="#n1"/>
* <ksj:busStopName>城堀</ksj:busStopName>
* <ksj:busRouteInformation>
* <ksj:BusRouteInformation>
* <ksj:busType>1</ksj:busType>
* <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
* <ksj:busLineName>小01</ksj:busLineName>
* </ksj:BusRouteInformation>
* </ksj:busRouteInformation>
* <ksj:busRouteInformation>
* <ksj:BusRouteInformation>
* <ksj:busType>1</ksj:busType>
* <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
* <ksj:busLineName>湯07</ksj:busLineName>
* </ksj:BusRouteInformation>
* </ksj:busRouteInformation>
* <ksj:busRouteInformation>
* <ksj:BusRouteInformation>
* <ksj:busType>1</ksj:busType>
* <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
* <ksj:busLineName>湯11</ksj:busLineName>
* </ksj:BusRouteInformation>
* </ksj:busRouteInformation>
* </ksj:BusStop>
*
* @param con
* @param node
* @param iFileName // ソースファイル名（拡張子を含まない）
* @param areacode
* @throws IOException
* @throws SQLException
*/
public static void showBusStop(Connection con, Node node, String iFileName, int areacode) throws IOException, SQLException {
String gmlidStr = "";
String nameStr = "";
try (PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE (gmlid=? and area=?)")) {
ArrayList<String[]> bris = new ArrayList<>();
NodeList nodes = node.getChildNodes();
for (int i=0; i < nodes.getLength(); i++) {
Node node2 = nodes.item(i);
switch (node2.getNodeName()) {
case "ksj:position":
NamedNodeMap nodeMap = node2.getAttributes();
if (null != nodeMap) {
for ( int j=0; j < nodeMap.getLength(); j++ ) {
if (nodeMap.item(j).getNodeName().equals("xlink:href")) {
gmlidStr = nodeMap.item(j).getNodeValue();
gmlidStr = gmlidStr.substring(1);
System.out.println("found gmlid='"+ gmlidStr +"'");
break;
}
}
} break;
case "ksj:busStopName":
nameStr = node2.getTextContent();
break;
case "ksj:busRouteInformation":
String[] rtn = anaCommJGD(node2);
if (rtn != null) {
bris.add(rtn);
} break;
default:
break;
}
}
// gmlid と nameStr をデータベースに格納する
ps2.setString(1, nameStr);
ps2.setString(2, iFileName);
ps2.setString(3, gmlidStr);
ps2.setInt(4, areacode);
System.out.println("UPDATE bus_stop SET name='"+ nameStr +"',ifile='"+ iFileName +"' WHERE (gmlid='"+ gmlidStr +"' and area="+ areacode +")");
ps2.executeUpdate();
}
}
/**
*
* <ksj:busRouteInformation>
* <ksj:BusRouteInformation>
* <ksj:busType>1</ksj:busType>
* <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
* <ksj:busLineName>小01</ksj:busLineName>
* </ksj:BusRouteInformation>
* </ksj:busRouteInformation>
*
* @param briNode
* @return
*/
public static String[] anaCommJGD(Node briNode) {
String[] rtn = new String[3];
int vcnt = 0;
NodeList nodes2 = briNode.getChildNodes();
for (int i=0; i < nodes2.getLength(); i++) {
Node node2 = nodes2.item(i);
if (node2.getNodeName().equals("ksj:BusRouteInformation")) {
NodeList nodes3 = node2.getChildNodes();
for (int j=0; j < nodes3.getLength(); j++) {
Node node3 = nodes3.item(j);
switch (node3.getNodeName()) {
case "ksj:busType":
rtn[0] = node3.getTextContent();
vcnt++;
break;
case "ksj:busLineName":
rtn[1] = node3.getTextContent();
vcnt++;
break;
case "ksj:busOperationCompany":
rtn[2] = node3.getTextContent();
vcnt++;
break;
default:
break;
}
}
}
}
if (vcnt > 0) {
return rtn;
}
return null;
}
/**
* 'table.BUS_STOP'を新規に作る
* 既にテーブルが存在する時には何もしない
* @param conHsql
* @throws SQLException
*/
public static void create(Connection conHsql) throws SQLException {
String createSt;
// 'table.BUS_STOP'を新規に作る
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(conHsql, createSt);
createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);";
HttpPOST.sql(conHsql, createSt);
}
/**
* 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する
* @param con
*/
public static void export(Connection con) {
try {
System.out.println("TABLE: BUS_STOP");
System.out.println("\"gmlid\",\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\"");
PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,name,lat,lon,fixed,ifile FROM bus_stop");
try (ResultSet rset8 = ps8.executeQuery()) {
while (rset8.next()) {
String gmlid = rset8.getString("gmlid");
String name = rset8.getString("name");
Double lat = rset8.getDouble("lat");
Double lon = rset8.getDouble("lon");
int fixed = rset8.getInt("fixed");
String ifile = rset8.getString("ifile");
System.out.println("\""+ gmlid +"\",\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\"");
}
}
}
catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 数値地図情報のGMLデータファイルかどうかを見極める
* @param f
* @return
*/
static boolean checkFile(File f, int areacode) {
String name = f.getName();
if (!name.startsWith(GML_DIR_PREFIX)) {
return false;
}
if (!name.toUpperCase().endsWith(".XML")) {
return false;
}
return Integer.parseInt(name.substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)) == areacode;
}
/**
* 数値地図情報のデータディレクトリかどうかを見極める
* @param f
* @return
*/
public static boolean checkGMLdir(File f) {
if (!f.isDirectory()) {
return false;
}
String name = f.getName();
if (!name.startsWith(GML_DIR_PREFIX)) {
return false;
}
return name.toUpperCase().endsWith(GML_DIR_PRIFIX);
}
public static final String GML_DIR_PREFIX = "P11-10_";
public static final String GML_DIR_PRIFIX = "_GML";
}
