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.sql.SQLSyntaxErrorException;
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;
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) {
dropTable(conHsql);
}
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);
break;
default:
iCounter += showNodes(con, node2, iFileName, areacode);
break;
}
}
return iCounter;
}
/*
public static void showGmPoint(Connection con, Node node) 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("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("jps:GM_Point.position")) {
NodeList nodes3 = node2.getChildNodes();
for (int j=0; j < nodes3.getLength(); j++) {
Node node3 = nodes3.item(j);
if (node3.getNodeName().equals("jps:DirectPosition")) {
NodeList nodes4 = node3.getChildNodes();
for (int k=0; k < nodes4.getLength(); k++) {
Node node4 = nodes4.item(k);
if (node4.getNodeName().equals("DirectPosition.coordinate")) {
positionStr = node4.getTextContent();
String[] str4Ary = positionStr.split(" ");
latStr = str4Ary[0];
lonStr = str4Ary[1];
break;
}
}
break;
}
}
try (PreparedStatement ps6 = con.prepareStatement("UPDATE bus_stop SET lat=?,lon=?,fixed=? WHERE gmlid=?")) {
double lat = Double.parseDouble(latStr);
double lon = Double.parseDouble(lonStr);
ps6.setDouble(1, lat);
ps6.setDouble(2, lon);
// ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
ps6.setInt(3, 0);
ps6.setString(4, idStr);
System.out.println("UPDATE bus_stop("+ idStr +") lat="+ lat +", lon="+ lon +", fixed=0");
ps6.executeUpdate();
}
}
}
}
*/
/**
* <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 bus_stop (lat,lon,fixed,area,gmlid) VALUES (?,?,?,?,?)")) {
double lat = Double.parseDouble(latStr);
double lon = Double.parseDouble(lonStr);
System.out.println("INSERT INTO bus_stop (lat,lon,fixed,area, gmlid) VALUES ('"+ latStr +"','"+ lonStr +"','0',"+ areacode +",'"+ idStr +"')");
ps6.setDouble(1, lat);
ps6.setDouble(2, lon);
ps6.setInt(3, 0);
ps6.setInt(4, areacode);
ps6.setString(5, idStr);
ps6.executeUpdate();
}
}
}
}
/**
*
* @param con
* @param node
* @param iFileName // ソースファイル名（拡張子を含まない）
* @throws IOException
* @throws SQLException
*/
/*
public static void showED01(Connection con, Node node, String iFileName) throws IOException, SQLException {
String gmlidStr = "";
String nameStr = "";
PreparedStatement ps1 = con.prepareStatement("SELECT gmlid FROM bus_stop WHERE gmlid=?");
PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (gmlid,name,ifile) VALUES (?,?,?)");
try {
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:POS":
NamedNodeMap nodeMap = node2.getAttributes();
if (null != nodeMap) {
for ( int j=0; j < nodeMap.getLength(); j++ ) {
if (nodeMap.item(j).getNodeName().equals("gmlid")) {
gmlidStr = nodeMap.item(j).getNodeValue();
System.out.println("found gmlid='"+ gmlidStr +"'");
break;
}
}
} break;
case "ksj:BSN":
nameStr = node2.getTextContent();
break;
case "ksj:BRI":
String[] rtn = anaComm(node2);
if (rtn != null) {
bris.add(rtn);
} break;
default:
break;
}
}
// gmlid と nameStr をデータベースに格納する
boolean insert = true;
ps1.setString(1, gmlidStr);
try (ResultSet rset = ps1.executeQuery()) {
if (rset.next()) {
insert = false;
}
}
if (insert) {
ps2.setString(1, gmlidStr);
ps2.setString(2, nameStr);
ps2.setString(3, iFileName);
System.out.println("INSERT INTO bus_stop (gmlid,name,ifile) VALUES ('"+ gmlidStr +"','"+ nameStr +"','"+ iFileName +"')");
ps2.executeUpdate();
}
}
finally {
ps1.close();
ps2.close();
}
}
*/
/**
* <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 // ソースファイル名（拡張子を含まない）
* @throws IOException
* @throws SQLException
*/
public static void showBusStop(Connection con, Node node, String iFileName) throws IOException, SQLException {
String gmlidStr = "";
String nameStr = "";
try (PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE gmlid=?")) {
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.executeUpdate();
}
}
public static String[] anaComm(Node briNode) {
String[] rtn = new String[3];
rtn[0] = ""; // corp type
rtn[1] = ""; // course name
rtn[2] = ""; // corp name
NodeList nodes = briNode.getChildNodes();
for (int i=0; i < nodes.getLength(); i++) {
Node node2 = nodes.item(i);
switch (node2.getNodeName()) {
case "ksj:BSC":
rtn[0] = node2.getTextContent();
break;
case "ksj:BLN":
rtn[1] = node2.getTextContent();
break;
case "ksj:BOC":
rtn[2] = node2.getTextContent();
break;
default:
break;
}
}
return rtn;
}
/**
*
* <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 bus_stop (gmlid VARCHAR(12) NOT NULL, gid LONG, name VARCHAR(128), kana VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT bus_stop_pk PRIMARY KEY(gmlid));";
create(conHsql, createSt);
//createSt = "CREATE TABLE existing_data (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(gmlid, lat, lon));";
//create(conHsql, createSt);
//createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);";
//create(conHsql, createSt);
}
static void create(Connection con, String createsql) throws SQLException {
System.out.println(createsql);
try (PreparedStatement ps = con.prepareStatement(createsql)) {
ps.executeUpdate();
}
catch (SQLSyntaxErrorException e) {
System.out.println("83:"+ e.toString());
if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: object name already exists:"))) {
throw e;
}
}
}
/**
* 'table.BUS_STOP'を削除する
* @param conHsql
* @throws java.io.FileNotFoundException
* @throws java.lang.ClassNotFoundException
* @throws SQLException
* @throws javax.xml.parsers.ParserConfigurationException
* @throws org.xml.sax.SAXException
*/
public static void dropTable (Connection conHsql) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
drop(conHsql, "bus_stop");
//drop(conHsql, "existing_data");
//drop(conHsql, "coverage");
}
public static void drop(Connection con, String tableName) throws SQLException {
String createSt = "DROP TABLE "+ tableName +";";
System.out.println(createSt);
try (PreparedStatement ps = con.prepareStatement(createSt)) {
ps.executeUpdate();
}
catch (SQLSyntaxErrorException e) {
System.out.println("107:"+ e.toString());
if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) {
throw e;
}
}
}
/**
* 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する
* @param con
*/
public static void export(Connection con) {
try {
System.out.println("TABLE: BUS_STOP");
System.out.println("\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\"");
PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed,ifile FROM bus_stop");
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);
String ifile = rset8.getString(5);
System.out.println("\""+ 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";
}
