src/osm/jp/ConvBusstop.java

   1 package osm.jp;
   2 import osm.jp.api.HttpGET;
   3
   4 import javax.xml.parsers.*;
   5 import javax.xml.transform.TransformerException;
   6
   7 import org.w3c.dom.*;
   8 import org.xml.sax.*;
   9
  10 import java.io.*;
  11 import java.sql.Connection;
  12 import java.sql.PreparedStatement;
  13 import java.sql.ResultSet;
  14 import java.sql.SQLException;
  15 import java.sql.Statement;
  16 import java.text.SimpleDateFormat;
  17 import java.util.ArrayList;
  18 import java.util.Calendar;
  19 import java.util.Date;
  20
  21 import jp.co.areaweb.tools.database.*;
  22
  23 public class ConvBusstop {
  24
  25         String filter = "";
  26         String urlStr = "";
  27
  28         public static final boolean DB_INIT = false;
  29
  30         // 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域
  31         static final int NEER = 75;
  32         static boolean nocheck = true;
  33
  34         /**
  35          * メイン
  36          *
  37          *      java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop <option>
  38          *              OPTION: -nocheck        OSMデータ上に既存のバス停が存在するかどうかをチェックしない
  39          *              OPTION: -check  OSMデータ上に既存のバス停が存在するかどうかをチェックする
  40          *
  41          * @throws IOException
  42          * @throws SQLException
  43          * @throws ClassNotFoundException
  44          * @throws FileNotFoundException
  45          * @throws TransformerException
  46          * @throws SAXException
  47          * @throws ParserConfigurationException */
  48         public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException
  49         {
  50                 int index = 0;
  51                 if (args.length > index) {
  52                         if (args[index].equals("-check")) {
  53                                 ConvBusstop.nocheck = false;
  54                                 index++;
  55                         }
  56                         if (args[index].equals("-nocheck")) {
  57                                 ConvBusstop.nocheck = true;
  58                                 index++;
  59                         }
  60                 }
  61
  62                 /**
  63                  * アプリケーション [ConvBusstop]
  64                  * > java -jar ConvBusstop.jar <オプション>
  65                  *      オプション： -exp 実行する直前にデータベースを初期化する（省略可能）
  66                  */
  67                 File dbdir = new File("database");
  68                 if (!dbdir.isDirectory()) {
  69                         dbdir.mkdir();
  70                 }
  71
  72                 Connection con = DatabaseTool.openDb("database");
  73                 ConvBusstop.initDb(con);
  74
  75                 try {
  76                         /**
  77                          * バス停データ変換のメイン処理
  78                          */
  79                         int fcounter = 0;
  80                         if (args.length > index) {
  81                                 File iFile = new File(args[index]);
  82                                 fcounter++;
  83
  84                                 inputFile(con, iFile);
  85                                 String iStr = iFile.getName();
  86                                 outputDb(con, iStr.substring(0, iStr.length() - 4));
  87                         }
  88                         else {
  89                                 File dir = new File(".");
  90                                 File[] files = dir.listFiles();
  91                                 for (File iFile : files) {
  92                                         if (checkFile(iFile)) {
  93                                                 fcounter++;
  94                                                 ConvBusstop.clearDb(con);
  95                                                 inputFile(con, iFile);
  96
  97                                                 // ローカルデータベース内の情報を出力する
  98                                                 String iStr = iFile.getName();
  99                                                 outputDb(con, iStr.substring(0, iStr.length() - 4));
 100                                         }
 101                                 }
 102                         }
 103                         System.out.println("["+ fcounter +"]つのファイルをインポートしました。");
 104                 }
 105                 finally {
 106                         DatabaseTool.closeDb(con);
 107                 }
 108         }
 109
 110         static String[] shiftArgs(String[] args) {
 111                 String[] values = new String[args.length - 1];
 112                 for (int i=1; i < args.length; i++) {
 113                         values[i - 1] = new String(args[i]);
 114                 }
 115                 return values;
 116         }
 117
 118         /**
 119          * ソースファイルを読み取ってローカルベータベースへ記録する
 120          * @param con
 121          * @param iFile
 122          * @throws FileNotFoundException
 123          * @throws ClassNotFoundException
 124          * @throws SQLException
 125          * @throws IOException
 126          * @throws ParserConfigurationException
 127          * @throws SAXException
 128          */
 129         public static void inputFile (Connection con, File iFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
 130                 int iCounter = 0;
 131                 String timeStampStr = null;
 132
 133                 String iStr = iFile.getName();
 134                 File dir = new File(iStr.substring(0, iStr.length() - 4));
 135                 dir.mkdir();
 136
 137                 DocumentBuilderFactory factory;
 138                 DocumentBuilder        builder;
 139                 Node root;
 140
 141                 iCounter = 0;
 142                 factory = DocumentBuilderFactory.newInstance();
 143                 builder = factory.newDocumentBuilder();
 144                 factory.setIgnoringElementContentWhitespace(true);
 145                 factory.setIgnoringComments(true);
 146                 factory.setValidating(true);
 147                 root    = builder.parse(iStr);
 148
 149                 iCounter += showNodes(con, root, iStr.substring(0, iStr.length() - 4), timeStampStr);
 150                 System.out.println("バス停数["+ iCounter +"]");
 151         }
 152
 153         public static void clearDb(Connection con) throws SQLException {
 154                 Statement stmt = con.createStatement();
 155                 long count = stmt.executeUpdate("delete from bus_stop");
 156                 System.out.println("'bus_stop'から "+ count +" 件のデータを削除しました。");
 157
 158             count = stmt.executeUpdate("delete from bus_course");
 159             System.out.println("'bus_course'から "+ count +" 件のデータを削除しました。");
 160
 161             count = stmt.executeUpdate("delete from bus_ref");
 162             System.out.println("'bus_ref'から "+ count +" 件のデータを削除しました。");
 163             stmt.close();
 164         }
 165
 166         public static void initDb(Connection con) throws SQLException {
 167                 String createSt;
 168                 PreparedStatement ps;
 169
 170                 // 'table.BUS_STOP'を新規に作る
 171                 DbBusstop.create(con);
 172
 173                 createSt = "CREATE TABLE bus_course (code int, type int, corp VARCHAR(128) NOT NULL, course VARCHAR(512), ifile VARCHAR(128), CONSTRAINT bus_course_pk PRIMARY KEY(code));";
 174                 System.out.println(createSt);
 175                 ps = con.prepareStatement(createSt);
 176                 ps.executeUpdate();
 177                 ps.close();
 178
 179                 createSt = "CREATE TABLE bus_ref (idref VARCHAR(12), code INT);";
 180                 System.out.println(createSt);
 181                 ps = con.prepareStatement(createSt);
 182                 ps.executeUpdate();
 183                 ps.close();
 184         }
 185
 186
 187         /**
 188          * ローカルデータベース内の情報を出力する
 189          * @param con
 190          * @param iCode
 191          * @throws IOException
 192          * @throws SQLException
 193          */
 194         public static void outputDb(Connection con, String iCode) throws IOException, SQLException {
 195                 SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
 196                 String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));
 197                 File dir = new File(iCode);
 198                 dir.mkdir();
 199
 200                 BufferedWriter ow = null;
 201                 BufferedWriter gw = null;
 202                 BufferedWriter hw = null;
 203                 BufferedWriter ww = null;
 204
 205                 // HTML header
 206                 File htmlFile = new File(iCode  +".html");
 207                 hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(htmlFile), "UTF-8"));
 208                 hw.write("<!DOCTYPE html>");
 209                 hw.newLine();
 210                 hw.write("<html><head><meta charset=\"utf-8\" /></head>");
 211                 hw.newLine();
 212                 hw.write("<body><table border='1'>");
 213                 hw.newLine();
 214                 hw.write("<tr>");
 215                 hw.write("<td>type</td>");
 216                 hw.write("<td>corp</td>");
 217                 hw.write("<td>course</td>");
 218                 hw.write("<td>GPX</td>");
 219                 hw.write("<td>SAMPLE</td>");
 220                 hw.write("<td>バス停数</td>");
 221                 hw.write("<td>未入力</td>");
 222                 hw.write("<td>既存</td>");
 223                 hw.write("</tr>");
 224                 hw.newLine();
 225
 226                 // Wiki header
 227                 File wikiFile = new File(iCode  +".wiki.txt");
 228                 ww = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(wikiFile), "UTF-8"));
 229                 ww.write("= バス路線毎のマッピング状況 =");
 230                 ww.newLine();
 231                 ww.newLine();
 232
 233                 String maeCorp = "";
 234                 boolean firstCorp = true;
 235
 236                 PreparedStatement ps7 = con.prepareStatement("SELECT code,type,corp,course,ifile FROM bus_course WHERE ifile=? ORDER BY corp,course");
 237                 PreparedStatement ps9 = con.prepareStatement("SELECT idref FROM bus_ref WHERE code=?");
 238                 PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed FROM bus_stop WHERE idref=?");
 239                 ps7.setString(1, iCode);
 240                 ResultSet rset7 = ps7.executeQuery();
 241                 while (rset7.next()) {
 242                         int code = rset7.getInt(1);
 243                         int type = rset7.getInt(2);
 244                         String corp = rset7.getString(3);
 245                         String course = rset7.getString(4);
 246
 247
 248                         File osmFile = new File(dir, iCode + String.format("_%1$08d", code) +".osm");
 249                         File gpxFile = new File(dir, iCode + String.format("_%1$08d", code) +".gpx");
 250                         File osmSample = new File(dir, iCode + String.format("_s%1$08d", code) +".osm");
 251
 252                         System.out.println("course = "+ course);
 253                         int stopCount = 0;
 254                         int fixedCount = 0;
 255                         int unfixedCount = 0;
 256
 257                         // index file header
 258                         hw.write("<tr>");
 259                         hw.write("<td>"+ type +"</td>");
 260                         hw.write("<td>"+ corp +"</td>");
 261                         hw.write("<td><a href='"+ dir.getName() +"/"+ osmFile.getName() +"'>"+ course +"</a></td>");
 262                         hw.write("<td><a href='"+ dir.getName() +"/"+ gpxFile.getName() +"'>"+ gpxFile.getName() +"</a></td>");
 263                         hw.write("<td><a href='"+ dir.getName() +"/"+ osmSample.getName() +"'>"+ osmSample.getName() +"</a></td>");
 264                         hw.newLine();
 265
 266                         //--------------------------------------------
 267                         //      Wiki見出し２: 運行会社
 268                         //------------
 269                         if (!maeCorp.equals(corp)) {
 270                                 if (firstCorp == false) {
 271                                         ww.write("|}");
 272                                         ww.newLine();
 273                                         ww.newLine();
 274                                 }
 275                                 firstCorp = false;
 276
 277                                 ww.write("=== "+ corp +" ===");
 278                                 ww.newLine();
 279                                 ww.newLine();
 280
 281                                 ww.write(":{{JA:Tag|network||"+ corp +"}}");
 282                                 ww.newLine();
 283                                 ww.write(":{{JA:Tag|operator||"+ corp +"}}");
 284                                 ww.newLine();
 285                                 ww.newLine();
 286
 287                                 ww.write("{| class=\"wikitable sortable\" style=\"table-layout: fixed; width: 100%\"");
 288                                 ww.newLine();
 289                                 ww.write("!style=\"width: 100px\"| ref");
 290                                 ww.newLine();
 291                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(マスタ)");
 292                                 ww.newLine();
 293                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(往路)");
 294                                 ww.newLine();
 295                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(復路)");
 296                                 ww.newLine();
 297                                 ww.write("!class=\"unsortable\"| 備考");
 298                                 ww.newLine();
 299
 300                                 maeCorp = new String(corp);
 301                         }
 302
 303                         // Wiki
 304                         ww.write("|-");
 305                         ww.newLine();
 306                         ww.write("| "+ course +" ");            // ref
 307                         ww.write("|| {{State Route|r=0}} {{relation|0|tools=no}} ");                    // 編集状況 (マスタ)
 308                         ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} ");                // 編集状況 (往路)
 309                         ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} ");                // 編集状況 (復路)
 310                         ww.write("|| ");                // 備考
 311                         ww.newLine();
 312
 313
 314                         // OSM file header
 315                         ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));
 316                         ow.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
 317                         ow.newLine();
 318                         ow.write("<osm version=\"0.5\" generator=\"ReadKIBAN\">");
 319                         ow.newLine();
 320
 321                         // GPX file header
 322                         gw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(gpxFile), "UTF-8"));
 323                         gw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
 324                         gw.newLine();
 325                         gw.write("<gpx xmlns=\"http://www.topografix.com/GPX/1/1\" version=\"1.1\" creator=\"osmtracker-android\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd \">");
 326                         gw.newLine();
 327
 328                         double maxLat = 0.0;
 329                         double minLat = 180.0;
 330                         double maxLon = 0.0;
 331                         double minLon = 180.0;
 332
 333                         ps9.setInt(1, code);
 334                         ResultSet rset9 = ps9.executeQuery();
 335                         while (rset9.next()) {
 336                                 String idref = rset9.getString(1);
 337
 338                                 ps8.setString(1, idref);
 339                                 ResultSet rset8 = ps8.executeQuery();
 340                                 if (rset8.next()) {
 341                                         stopCount++;
 342                                         String name = rset8.getString(1);
 343                                         Double lat = rset8.getDouble(2);
 344                                         Double lon = rset8.getDouble(3);
 345                                         int fixed = rset8.getInt(4);
 346
 347                                         if (lat > maxLat) {
 348                                                 maxLat = lat;
 349                                         }
 350                                         if (lon > maxLon) {
 351                                                 maxLon = lon;
 352                                         }
 353                                         if (lat < minLat) {
 354                                                 minLat = lat;
 355                                         }
 356                                         if (lon < minLon) {
 357                                                 minLon = lon;
 358                                         }
 359
 360                                         System.out.println("\tway point = "+ idref +", lat="+ lat +", lon="+ lon);
 361                                         fixedCount += fixed;
 362                                         if (fixed == 0) {
 363                                                 unfixedCount++;
 364
 365                                                 // OSM node
 366                                                 int nodeid = Integer.parseInt(idref.substring(1)) * -1;
 367                                                 String osm_node = nodeBusstop(nodeid, name, lat, lon, timeStampStr);
 368                                                 ow.write(osm_node);
 369                                                 ow.newLine();
 370
 371                                                 // TEXT node
 372                                                 File txtFile = new File(dir, iCode + idref +".txt");
 373                                                 BufferedWriter gw2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(txtFile), "UTF-8"));
 374                                                 gw2.write(osm_node);
 375                                                 gw2.newLine();
 376                                                 gw2.close();
 377
 378                                                 // GPX waypoint
 379                                                 gw.write("<wpt lat=\""+ lat +"\" lon=\""+ lon +"\">\n");
 380                                                 gw.write(" <time>"+ timeStampStr +"Z</time>\n");
 381                                                 gw.write(" <name><![CDATA["+ name +"]]></name>\n");
 382                                                 gw.write(" <link href=\""+ txtFile.getName() +"\"><text>"+ idref +"</text></link>\n");
 383                                                 gw.write("</wpt>\n");
 384                                                 gw.newLine();
 385                                         }
 386                                 }
 387                                 rset8.close();
 388                         }
 389                         rset9.close();
 390
 391                         // INDEX file
 392                         hw.write("<td>"+ stopCount +"</td>");
 393                         hw.write("<td>"+ unfixedCount +"</td>");
 394                         hw.write("<td>"+ fixedCount +"</td>");
 395                         hw.write("</tr>");
 396                         hw.newLine();
 397
 398                         // OSM file header
 399                         BufferedWriter ow2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmSample), "UTF-8"));
 400                         ow2.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
 401                         ow2.newLine();
 402                         ow2.write("<osm version=\"0.5\" generator=\"ReadKIBAN\">");
 403                         ow2.newLine();
 404
 405                         ow2.write(nodeBusstop(code, "SAMPLE", ((maxLat+minLat)/2), ((maxLon+minLon)/2), timeStampStr));
 406                         ow2.newLine();
 407                         ow2.write("</osm>");
 408                         ow2.newLine();
 409                         ow2.close();
 410
 411                         // OSM file footer
 412                         ow.write("</osm>");
 413                         ow.newLine();
 414                         ow.close();
 415
 416                         // GPX file footer
 417                         gw.write("</gpx>");
 418                         gw.newLine();
 419                         gw.close();
 420                 }
 421                 rset7.close();
 422
 423                 // Wiki footer
 424                 ww.close();
 425
 426                 // index file footer
 427                 hw.write("</table></body></html>");
 428                 hw.newLine();
 429                 hw.close();
 430         }
 431
 432         public static String nodeBusstop(int code, String name, Double lat, Double lon, String timeStampStr) {
 433                 String osm_node = ("<node id=\""+ code +"\" timestamp=\""+ timeStampStr +"Z\" lat=\""+ lat +"\" lon=\""+ lon +"\">\n");
 434                 osm_node += "<tag k=\"name\" v=\""+ name +"\"/>\n";
 435                 osm_node += "<tag k=\"fixme\" v=\"このバス停を正しい位置に移動させて\"/>\n";
 436                 osm_node += "<tag k=\"source\" v=\"KSJ2\"/>\n";
 437                 osm_node += "<tag k=\"source_ref\" v=\"http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-P11.html\"/>\n";
 438                 osm_node += "<tag k=\"created_by\" v=\"National-Land-Numerical-Information_MLIT_Japan\"/>\n";
 439                 osm_node += "<tag k=\"note\" v=\"National-Land Numerical Information (Bus stop) 2012, MLIT Japan\"/>\n";
 440                 osm_node += "<tag k=\"note:ja\" v=\"国土数値情報（バス停留所）平成２４年　国土交通省\"/>\n";
 441                 osm_node += "<tag k=\"public_transport\" v=\"platform\"/>\n";
 442                 osm_node += "<tag k=\"highway\" v=\"bus_stop\"/>\n";
 443                 osm_node += "<tag k=\"bus\" v=\"yes\"/>\n";
 444                 osm_node += "</node>\n";
 445                 return osm_node;
 446         }
 447
 448         /**
 449          *
 450          * @param con
 451          * @param node
 452          * @param iFileName             // ソースファイル名（拡張子を含まない）
 453          * @param timeStampStr
 454          * @return
 455          * @throws IOException
 456          * @throws SQLException
 457          */
 458         public static int showNodes(Connection con, Node node, String iFileName, String timeStampStr) throws IOException, SQLException {
 459                 int iCounter = 0;
 460                 NamedNodeMap nodeMap = node.getAttributes();
 461                 if ( null != nodeMap ) {
 462                         for ( int j=0; j < nodeMap.getLength(); j++ ) {
 463                                 if (nodeMap.item(j).getNodeName().equals("timeStamp")) {
 464                                         timeStampStr = nodeMap.item(j).getNodeValue();
 465                                 }
 466                         }
 467                 }
 468
 469                 NodeList nodes = node.getChildNodes();
 470                 for (int i=0; i<nodes.getLength(); i++) {
 471                         Node node2 = nodes.item(i);
 472                         if (node2.getNodeName().equals("jps:GM_Point")) {
 473                                 showGmPoint(con, node2);
 474                         }
 475                         else if (node2.getNodeName().equals("gml:Point")) {
 476                                 showGmlPoint(con, node2);
 477                         }
 478
 479                         else if (node2.getNodeName().equals("ksj:ED01")) {
 480                                 iCounter++;
 481                                 showED01(con, node2, iFileName);
 482                         }
 483                         else if (node2.getNodeName().equals("ksj:BusStop")) {
 484                                 iCounter++;
 485                                 showBusStop(con, node2, iFileName);
 486                         }
 487
 488                         else {
 489                                 iCounter += showNodes(con, node2, iFileName, timeStampStr);
 490                         }
 491                 }
 492                 return iCounter;
 493         }
 494
 495         /**
 496          *
 497          * @param con
 498          * @param node
 499          * @param iFileName             // ソースファイル名（拡張子を含まない）
 500          * @throws IOException
 501          * @throws SQLException
 502          */
 503         public static void showED01(Connection con, Node node, String iFileName) throws IOException, SQLException {
 504                 String idrefStr = "";
 505                 String nameStr = "";
 506                 PreparedStatement ps1 = con.prepareStatement("SELECT idref FROM bus_stop WHERE idref=?");
 507                 PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (idref,name,ifile) VALUES (?,?,?)");
 508                 PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");
 509                 PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");
 510                 PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");
 511                 PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");
 512
 513                 ArrayList<String[]> bris = new ArrayList<String[]>();
 514                 NodeList nodes = node.getChildNodes();
 515                 for (int i=0; i < nodes.getLength(); i++) {
 516                         Node node2 = nodes.item(i);
 517                         if (node2.getNodeName().equals("ksj:POS")) {
 518                                 NamedNodeMap nodeMap = node2.getAttributes();
 519                                 if (null != nodeMap) {
 520                                         for ( int j=0; j < nodeMap.getLength(); j++ ) {
 521                                                 if (nodeMap.item(j).getNodeName().equals("idref")) {
 522                                                         idrefStr = nodeMap.item(j).getNodeValue();
 523                                                         System.out.println("found idref='"+ idrefStr +"'");
 524                                                         break;
 525                                                 }
 526                                         }
 527                                 }
 528                         }
 529                         else if (node2.getNodeName().equals("ksj:BSN")) {
 530                                 nameStr = node2.getTextContent();
 531                         }
 532                         else if (node2.getNodeName().equals("ksj:BRI")) {
 533                                 String[] rtn = anaComm(node2);
 534                                 if (rtn != null) {
 535                                         bris.add(rtn);
 536                                 }
 537                         }
 538                 }
 539
 540                 // idref と nameStr をデータベースに格納する
 541                 boolean insert = true;
 542                 ps1.setString(1, idrefStr);
 543                 ResultSet rset = ps1.executeQuery();
 544                 if (rset.next()) {
 545                         insert = false;
 546                 }
 547                 rset.close();
 548
 549                 if (insert) {
 550                         ps2.setString(1, idrefStr);
 551                         ps2.setString(2, nameStr);
 552                         ps2.setString(3, iFileName);
 553                         System.out.println("INSERT INTO bus_stop (idref,name,ifile) VALUES ('"+ idrefStr +"','"+ nameStr +"','"+ iFileName +"')");
 554                         ps2.executeUpdate();
 555                 }
 556
 557                 for (String[] rtn : bris) {
 558                         int code = 0;
 559                         ps3.setString(1, rtn[1]);
 560                         ps3.setString(2, rtn[2]);
 561                         ps3.setString(3, iFileName);
 562                         rset = ps3.executeQuery();
 563                         if (rset.next()) {
 564                                 code = rset.getInt(1);
 565                         }
 566                         rset.close();
 567
 568                         if (code == 0) {
 569                                 ps6.setString(1, iFileName);
 570                                 ResultSet rset6 = ps6.executeQuery();
 571                                 if (rset6.next()) {
 572                                         code = rset6.getInt(1);
 573                                 }
 574                                 rset6.close();
 575                                 code++;
 576
 577                                 System.out.println("code="+code);
 578                                 ps4.setInt(1, code);
 579                                 ps4.setInt(2, Integer.parseInt(rtn[0]));
 580                                 ps4.setString(3, rtn[2]);
 581                                 ps4.setString(4, rtn[1]);
 582                                 ps4.setString(5, iFileName);
 583                                 ps4.executeUpdate();
 584                         }
 585
 586                         ps5.setString(1, idrefStr);
 587                         ps5.setInt(2, code);
 588                         ps5.executeUpdate();
 589                 }
 590
 591                 ps1.close();
 592                 ps2.close();
 593                 ps3.close();
 594                 ps4.close();
 595                 ps5.close();
 596         }
 597
 598         /**
 599          * <ksj:BusStop gml:id="ED01_1">
 600          *      <ksj:position xlink:href="#n1"/>
 601          *      <ksj:busStopName>城堀</ksj:busStopName>
 602          *      <ksj:busRouteInformation>
 603          *              <ksj:BusRouteInformation>
 604          *                      <ksj:busType>1</ksj:busType>
 605          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
 606          *                      <ksj:busLineName>小01</ksj:busLineName>
 607          *              </ksj:BusRouteInformation>
 608          *      </ksj:busRouteInformation>
 609          *      <ksj:busRouteInformation>
 610          *              <ksj:BusRouteInformation>
 611          *                      <ksj:busType>1</ksj:busType>
 612          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
 613          *                      <ksj:busLineName>湯07</ksj:busLineName>
 614          *              </ksj:BusRouteInformation>
 615          *      </ksj:busRouteInformation>
 616          *      <ksj:busRouteInformation>
 617          *              <ksj:BusRouteInformation>
 618          *                      <ksj:busType>1</ksj:busType>
 619          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
 620          *                      <ksj:busLineName>湯11</ksj:busLineName>
 621          *              </ksj:BusRouteInformation>
 622          *      </ksj:busRouteInformation>
 623          * </ksj:BusStop>
 624          *
 625          * @param con
 626          * @param node
 627          * @param iFileName             // ソースファイル名（拡張子を含まない）
 628          * @throws IOException
 629          * @throws SQLException
 630          */
 631         public static void showBusStop(Connection con, Node node, String iFileName) throws IOException, SQLException {
 632                 String idrefStr = "";
 633                 String nameStr = "";
 634                 PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE idref=?");
 635                 PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");
 636                 PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");
 637                 PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");
 638                 PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");
 639
 640                 ArrayList<String[]> bris = new ArrayList<String[]>();
 641                 NodeList nodes = node.getChildNodes();
 642                 for (int i=0; i < nodes.getLength(); i++) {
 643                         Node node2 = nodes.item(i);
 644                         if (node2.getNodeName().equals("ksj:position")) {
 645                                 NamedNodeMap nodeMap = node2.getAttributes();
 646                                 if (null != nodeMap) {
 647                                         for ( int j=0; j < nodeMap.getLength(); j++ ) {
 648                                                 if (nodeMap.item(j).getNodeName().equals("xlink:href")) {
 649                                                         idrefStr = nodeMap.item(j).getNodeValue();
 650                                                         idrefStr = idrefStr.substring(1);
 651                                                         System.out.println("found idref='"+ idrefStr +"'");
 652                                                         break;
 653                                                 }
 654                                         }
 655                                 }
 656                         }
 657                         else if (node2.getNodeName().equals("ksj:busStopName")) {
 658                                 nameStr = node2.getTextContent();
 659                         }
 660                         else if (node2.getNodeName().equals("ksj:busRouteInformation")) {
 661                                 String[] rtn = anaCommJGD(node2);
 662                                 if (rtn != null) {
 663                                         bris.add(rtn);
 664                                 }
 665                         }
 666                 }
 667
 668                 // idref と nameStr をデータベースに格納する
 669                 ps2.setString(1, nameStr);
 670                 ps2.setString(2, iFileName);
 671                 ps2.setString(3, idrefStr);
 672                 ps2.executeUpdate();
 673
 674                 for (String[] rtn : bris) {
 675                         int code = 0;
 676                         ps3.setString(1, rtn[1]);
 677                         ps3.setString(2, rtn[2]);
 678                         ps3.setString(3, iFileName);
 679                         ResultSet rset = ps3.executeQuery();
 680                         if (rset.next()) {
 681                                 code = rset.getInt(1);
 682                         }
 683                         rset.close();
 684
 685                         if (code == 0) {
 686                                 ps6.setString(1, iFileName);
 687                                 ResultSet rset6 = ps6.executeQuery();
 688                                 if (rset6.next()) {
 689                                         code = rset6.getInt(1);
 690                                 }
 691                                 rset6.close();
 692                                 code++;
 693
 694                                 System.out.println("course="+ code +" : "+ rtn[0] +" : "+ rtn[1] +" : "+ rtn[2] );
 695                                 ps4.setInt(1, code);
 696                                 ps4.setInt(2, Integer.parseInt(rtn[0]));
 697                                 ps4.setString(3, rtn[2]);
 698                                 ps4.setString(4, rtn[1]);
 699                                 ps4.setString(5, iFileName);
 700                                 ps4.executeUpdate();
 701                         }
 702
 703                         ps5.setString(1, idrefStr);
 704                         ps5.setInt(2, code);
 705                         ps5.executeUpdate();
 706                 }
 707
 708                 ps2.close();
 709                 ps3.close();
 710                 ps4.close();
 711                 ps5.close();
 712         }
 713
 714         public static String[] anaComm(Node briNode) {
 715                 String[] rtn = new String[3];
 716                 rtn[0] = "";    // corp type
 717                 rtn[1] = "";    // course name
 718                 rtn[2] = "";    // corp name
 719
 720                 NodeList nodes = briNode.getChildNodes();
 721                 for (int i=0; i < nodes.getLength(); i++) {
 722                         Node node2 = nodes.item(i);
 723                         if (node2.getNodeName().equals("ksj:BSC")) {
 724                                 rtn[0] = node2.getTextContent();
 725                         }
 726                         else if (node2.getNodeName().equals("ksj:BLN")) {
 727                                 rtn[1] = node2.getTextContent();
 728                         }
 729                         else if (node2.getNodeName().equals("ksj:BOC")) {
 730                                 rtn[2] = node2.getTextContent();
 731                         }
 732                 }
 733                 return rtn;
 734         }
 735
 736         /**
 737          *
 738          *      <ksj:busRouteInformation>
 739          *              <ksj:BusRouteInformation>
 740          *                      <ksj:busType>1</ksj:busType>
 741          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
 742          *                      <ksj:busLineName>小01</ksj:busLineName>
 743          *              </ksj:BusRouteInformation>
 744          *      </ksj:busRouteInformation>
 745          *
 746          * @param briNode
 747          * @return
 748          */
 749         public static String[] anaCommJGD(Node briNode) {
 750                 String[] rtn = new String[3];
 751                 int vcnt = 0;
 752
 753                 NodeList nodes2 = briNode.getChildNodes();
 754                 for (int i=0; i < nodes2.getLength(); i++) {
 755                         Node node2 = nodes2.item(i);
 756                         if (node2.getNodeName().equals("ksj:BusRouteInformation")) {
 757                                 NodeList nodes3 = node2.getChildNodes();
 758                                 for (int j=0; j < nodes3.getLength(); j++) {
 759                                         Node node3 = nodes3.item(j);
 760                                         if (node3.getNodeName().equals("ksj:busType")) {
 761                                                 rtn[0] = new String(node3.getTextContent());
 762                                                 vcnt++;
 763                                         }
 764                                         else if (node3.getNodeName().equals("ksj:busLineName")) {
 765                                                 rtn[1] = new String(node3.getTextContent());
 766                                                 vcnt++;
 767                                         }
 768                                         else if (node3.getNodeName().equals("ksj:busOperationCompany")) {
 769                                                 rtn[2] = new String(node3.getTextContent());
 770                                                 vcnt++;
 771                                         }
 772                                 }
 773                         }
 774                 }
 775
 776                 if (vcnt > 0) {
 777                         return rtn;
 778                 }
 779                 return null;
 780         }
 781
 782         public static void showGmPoint(Connection con, Node node) throws IOException, SQLException {
 783                 String positionStr = "";
 784                 String latStr = "";
 785                 String lonStr = "";
 786                 String idStr = "";
 787
 788                 NamedNodeMap nodeMap = node.getAttributes();
 789                 if ( null != nodeMap ) {
 790                         for ( int j=0; j<nodeMap.getLength(); j++ ) {
 791                                 if (nodeMap.item(j).getNodeName().equals("id")) {
 792                                         idStr = nodeMap.item(j).getNodeValue();
 793                                 }
 794                         }
 795                 }
 796
 797                 NodeList nodes = node.getChildNodes();
 798                 for (int i=0; i < nodes.getLength(); i++) {
 799                         Node node2 = nodes.item(i);
 800                         if (node2.getNodeName().equals("jps:GM_Point.position")) {
 801                                 NodeList nodes3 = node2.getChildNodes();
 802                                 for (int j=0; j < nodes3.getLength(); j++) {
 803                                         Node node3 = nodes3.item(j);
 804                                         if (node3.getNodeName().equals("jps:DirectPosition")) {
 805                                                 NodeList nodes4 = node3.getChildNodes();
 806                                                 for (int k=0; k < nodes4.getLength(); k++) {
 807                                                         Node node4 = nodes4.item(k);
 808                                                         if (node4.getNodeName().equals("DirectPosition.coordinate")) {
 809                                                                 positionStr = node4.getTextContent();
 810                                                                 String[] str4Ary = positionStr.split(" ");
 811                                                                 latStr = str4Ary[0];
 812                                                                 lonStr = str4Ary[1];
 813                                                                 break;
 814                                                         }
 815                                                 }
 816                                                 break;
 817                                         }
 818                                 }
 819
 820                                 PreparedStatement ps6 = con.prepareStatement("UPDATE bus_stop SET lat=?,lon=?,fixed=? WHERE idref=?");
 821                                 double lat = Double.parseDouble(latStr);
 822                                 double lon = Double.parseDouble(lonStr);
 823                                 ps6.setDouble(1, lat);
 824                                 ps6.setDouble(2, lon);
 825                                 ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
 826                                 ps6.setString(4, idStr);
 827                                 System.out.println("UPDATE bus_stop("+ idStr +") lat="+ lat +", lon="+ lon +", fixed="+ (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
 828                                 ps6.executeUpdate();
 829                                 ps6.close();
 830                         }
 831                 }
 832         }
 833
 834         /**
 835          * <gml:Point gml:id="n1">
 836          *      <gml:pos>35.14591397 139.10569573</gml:pos>
 837          * </gml:Point>
 838          *
 839          * @param con
 840          * @param node
 841          * @throws IOException
 842          * @throws SQLException
 843          */
 844         public static void showGmlPoint(Connection con, Node node) throws IOException, SQLException {
 845                 String positionStr = "";
 846                 String latStr = "";
 847                 String lonStr = "";
 848                 String idStr = "";
 849
 850                 NamedNodeMap nodeMap = node.getAttributes();
 851                 if ( null != nodeMap ) {
 852                         for ( int j=0; j<nodeMap.getLength(); j++ ) {
 853                                 if (nodeMap.item(j).getNodeName().equals("gml:id")) {
 854                                         idStr = nodeMap.item(j).getNodeValue();
 855                                 }
 856                         }
 857                 }
 858
 859                 NodeList nodes = node.getChildNodes();
 860                 for (int i=0; i < nodes.getLength(); i++) {
 861                         Node node2 = nodes.item(i);
 862                         if (node2.getNodeName().equals("gml:pos")) {
 863                                 positionStr = node2.getTextContent().trim();
 864                                 String[] str4Ary = positionStr.split(" ");
 865                                 latStr = str4Ary[0];
 866                                 lonStr = str4Ary[1];
 867
 868                                 PreparedStatement ps6 = con.prepareStatement("INSERT INTO bus_stop (lat,lon,fixed,idref) VALUES (?,?,?,?)");
 869                                 double lat = Double.parseDouble(latStr);
 870                                 double lon = Double.parseDouble(lonStr);
 871                                 System.out.println("INSERT INTO bus_stop (lat,lon,fixed,idref) VALUES ('"+ latStr +"','"+ lonStr +"','"+ (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)) +"','"+ idStr +"')");
 872
 873                                 ps6.setDouble(1, lat);
 874                                 ps6.setDouble(2, lon);
 875                                 ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
 876                                 ps6.setString(4, idStr);
 877                                 ps6.executeUpdate();
 878                                 ps6.close();
 879                         }
 880                 }
 881         }
 882
 883         static boolean checkFile(File f) {
 884                 String name = f.getName();
 885                 if (!name.startsWith("P11-")) {
 886                         return false;
 887                 }
 888                 if (!name.toUpperCase().endsWith(".XML")) {
 889                         return false;
 890                 }
 891                 return true;
 892         }
 893 }