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                         /**
 106                          * 既存のOSMバス停を読み込む
 107                          *              --> 'existing.xml'
 108                          */
 109                         if (!ConvBusstop.nocheck) {
 110                                 /**
 111                                  * インポートしたデータの緯度経度範囲を読み取る
 112                                  */
 113                                 double maxLat = -90.0D;
 114                                 double minLat = 90.0D;
 115                                 double maxLon = -180.0D;
 116                                 double minLon = 180.0D;
 117                                 PreparedStatement ps8 = con.prepareStatement("SELECT lat,lon FROM bus_stop");
 118                                 ResultSet rset8 = ps8.executeQuery();
 119                                 while (rset8.next()) {
 120                                         Double lat = rset8.getDouble("lat");
 121                                         Double lon = rset8.getDouble("lon");
 122
 123                                         if (lat > maxLat) {
 124                                                 maxLat = lat;
 125                                         }
 126                                         if (lon > maxLon) {
 127                                                 maxLon = lon;
 128                                         }
 129                                         if (lat < minLat) {
 130                                                 minLat = lat;
 131                                         }
 132                                         if (lon < minLon) {
 133                                                 minLon = lon;
 134                                         }
 135                                 }
 136                                 rset8.close();
 137
 138                                 readExistingFile(con, new File("existing.xml"));
 139
 140                                 PreparedStatement ps1 = con.prepareStatement("SELECT idref,lat,lon FROM bus_stop");
 141                                 PreparedStatement ps2 = con.prepareStatement("SELECT count(idref) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
 142                                 PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?");
 143                                 ResultSet rset1 = ps1.executeQuery();
 144                                 while (rset1.next()) {
 145                                         String idref = rset1.getString("idref");
 146                                         Double lat = rset1.getDouble("lat");
 147                                         Double lon = rset1.getDouble("lon");
 148
 149                                         // 指定の緯度経度を中心とする半径７５m四方の矩形領域
 150                                         RectArea rect = new RectArea(lat, lon, 75);
 151                                         ps2.setDouble(1, rect.minlat);
 152                                         ps2.setDouble(2, rect.maxlat);
 153                                         ps2.setDouble(3, rect.minlon);
 154                                         ps2.setDouble(4, rect.maxlon);
 155                                         ResultSet rset2 = ps2.executeQuery();
 156                                         if (rset2.next()) {
 157                                                 int count = rset2.getInt(1);
 158                                                 ps3.setInt(1, count);
 159                                                 ps3.setString(1, idref);
 160                                                 ps3.executeQuery();
 161                                         }
 162                                         rset2.close();
 163                                 }
 164                                 rset1.close();
 165                         }
 166                         System.out.println("["+ fcounter +"] 変換完了しました。");
 167                 }
 168                 finally {
 169                         DatabaseTool.closeDb(con);
 170                 }
 171         }
 172
 173         static String[] shiftArgs(String[] args) {
 174                 String[] values = new String[args.length - 1];
 175                 for (int i=1; i < args.length; i++) {
 176                         values[i - 1] = new String(args[i]);
 177                 }
 178                 return values;
 179         }
 180
 181         public static void readExistingFile (Connection con, File existingFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
 182                 int iCounter = 0;
 183
 184                 DocumentBuilderFactory factory;
 185                 DocumentBuilder        builder;
 186                 Node root;
 187
 188                 iCounter = 0;
 189                 factory = DocumentBuilderFactory.newInstance();
 190                 builder = factory.newDocumentBuilder();
 191                 factory.setIgnoringElementContentWhitespace(true);
 192                 factory.setIgnoringComments(true);
 193                 factory.setValidating(true);
 194                 root    = builder.parse(existingFile);
 195
 196                 iCounter += readExistingNodes(con, root);
 197                 System.out.println("既存バス停数["+ iCounter +"]");
 198         }
 199
 200         static int readExistingNodes(Connection con, Node node) throws IOException, SQLException {
 201                 int iCounter = 0;
 202
 203                 NodeList nodes = node.getChildNodes();
 204                 for (int i = 0; i < nodes.getLength(); i++) {
 205                         Node node2 = nodes.item(i);
 206                         if (node2.getNodeName().equals("node")) {
 207                                 iCounter++;
 208                                 importExistingNode(con, node2);
 209                         }
 210                         else {
 211                                 iCounter += readExistingNodes(con, node2);
 212                         }
 213                 }
 214                 return iCounter;
 215         }
 216
 217         static void importExistingNode(Connection con, Node node) throws IOException, SQLException {
 218                 String idrefStr = "";
 219                 String latStr = "";
 220                 String lonStr = "";
 221                 PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon) VALUES (?,?,?)");
 222
 223                 NamedNodeMap nodeMap = node.getAttributes();
 224                 if (null != nodeMap) {
 225                         for (int j=0; j < nodeMap.getLength(); j++) {
 226                                 if (nodeMap.item(j).getNodeName().equals("id")) {
 227                                         idrefStr = nodeMap.item(j).getNodeValue();
 228                                 }
 229                                 if (nodeMap.item(j).getNodeName().equals("lat")) {
 230                                         latStr = nodeMap.item(j).getNodeValue();
 231                                 }
 232                                 if (nodeMap.item(j).getNodeName().equals("lon")) {
 233                                         lonStr = nodeMap.item(j).getNodeValue();
 234                                 }
 235                         }
 236
 237                         // idref と nameStr をデータベースに格納する
 238                         ps5.setString(1, idrefStr);
 239                         ps5.setDouble(2, Double.parseDouble(latStr));
 240                         ps5.setDouble(3, Double.parseDouble(lonStr));
 241                         ps5.executeUpdate();
 242                         ps5.close();
 243                 }
 244         }
 245
 246         /**
 247          * ソースファイルを読み取ってローカルベータベースへ記録する
 248          * @param con
 249          * @param iFile
 250          * @throws FileNotFoundException
 251          * @throws ClassNotFoundException
 252          * @throws SQLException
 253          * @throws IOException
 254          * @throws ParserConfigurationException
 255          * @throws SAXException
 256          */
 257         public static void inputFile (Connection con, File iFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
 258                 int iCounter = 0;
 259                 String timeStampStr = null;
 260
 261                 String iStr = iFile.getName();
 262                 File dir = new File(iStr.substring(0, iStr.length() - 4));
 263                 dir.mkdir();
 264
 265                 DocumentBuilderFactory factory;
 266                 DocumentBuilder        builder;
 267                 Node root;
 268
 269                 iCounter = 0;
 270                 factory = DocumentBuilderFactory.newInstance();
 271                 builder = factory.newDocumentBuilder();
 272                 factory.setIgnoringElementContentWhitespace(true);
 273                 factory.setIgnoringComments(true);
 274                 factory.setValidating(true);
 275                 root    = builder.parse(iStr);
 276
 277                 iCounter += showNodes(con, root, iStr.substring(0, iStr.length() - 4), timeStampStr);
 278                 System.out.println("バス停数["+ iCounter +"]");
 279         }
 280
 281         public static void clearDb(Connection con) throws SQLException {
 282                 Statement stmt = con.createStatement();
 283                 long count = stmt.executeUpdate("delete from bus_stop");
 284                 System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。");
 285
 286             count = stmt.executeUpdate("delete from existing_data");
 287             System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。");
 288
 289             count = stmt.executeUpdate("delete from bus_course");
 290             System.out.println("'Database.bus_course'から "+ count +" 件のデータを削除しました。");
 291
 292             count = stmt.executeUpdate("delete from bus_ref");
 293             System.out.println("'Database.bus_ref'から "+ count +" 件のデータを削除しました。");
 294             stmt.close();
 295         }
 296
 297         public static void initDb(Connection con) throws SQLException {
 298                 String createSt;
 299
 300                 // 'table.BUS_STOP'を新規に作る
 301                 DbBusstop.create(con);
 302
 303                 createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));";
 304                 DbBusstop.create(con, createSt);
 305
 306                 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));";
 307                 DbBusstop.create(con, createSt);
 308
 309                 createSt = "CREATE TABLE bus_ref (idref VARCHAR(12), code INT);";
 310                 DbBusstop.create(con, createSt);
 311         }
 312
 313
 314         /**
 315          * ローカルデータベース内の情報を出力する
 316          * @param con
 317          * @param iCode
 318          * @throws IOException
 319          * @throws SQLException
 320          */
 321         public static void outputDb(Connection con, String iCode) throws IOException, SQLException {
 322                 SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
 323                 String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));
 324                 File dir = new File(iCode);
 325                 dir.mkdir();
 326
 327                 BufferedWriter ow = null;
 328                 BufferedWriter gw = null;
 329                 BufferedWriter hw = null;
 330                 BufferedWriter ww = null;
 331
 332                 // HTML header
 333                 File htmlFile = new File(iCode  +".html");
 334                 hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(htmlFile), "UTF-8"));
 335                 hw.write("<!DOCTYPE html>");
 336                 hw.newLine();
 337                 hw.write("<html><head><meta charset=\"utf-8\" /></head>");
 338                 hw.newLine();
 339                 hw.write("<body><table border='1'>");
 340                 hw.newLine();
 341                 hw.write("<tr>");
 342                 hw.write("<td>type</td>");
 343                 hw.write("<td>corp</td>");
 344                 hw.write("<td>course</td>");
 345                 hw.write("<td>GPX</td>");
 346                 hw.write("<td>SAMPLE</td>");
 347                 hw.write("<td>バス停数</td>");
 348                 hw.write("<td>未入力</td>");
 349                 hw.write("<td>既存</td>");
 350                 hw.write("</tr>");
 351                 hw.newLine();
 352
 353                 // Wiki header
 354                 File wikiFile = new File(iCode  +".wiki.txt");
 355                 ww = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(wikiFile), "UTF-8"));
 356                 ww.write("= バス路線毎のマッピング状況 =");
 357                 ww.newLine();
 358                 ww.newLine();
 359
 360                 String maeCorp = "";
 361                 boolean firstCorp = true;
 362
 363                 PreparedStatement ps7 = con.prepareStatement("SELECT code,type,corp,course,ifile FROM bus_course WHERE ifile=? ORDER BY type,corp,course");
 364                 PreparedStatement ps9 = con.prepareStatement("SELECT idref FROM bus_ref WHERE code=?");
 365                 PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed FROM bus_stop WHERE idref=?");
 366                 ps7.setString(1, iCode);
 367                 ResultSet rset7 = ps7.executeQuery();
 368                 while (rset7.next()) {
 369                         int code = rset7.getInt(1);
 370                         int type = rset7.getInt(2);
 371                         String corp = rset7.getString(3);
 372                         String course = rset7.getString(4);
 373
 374
 375                         File osmFile = new File(dir, iCode + String.format("_%1$08d", code) +".osm");
 376                         File gpxFile = new File(dir, iCode + String.format("_%1$08d", code) +".gpx");
 377                         File osmSample = new File(dir, iCode + String.format("_s%1$08d", code) +".osm");
 378
 379                         System.out.println("course = "+ course);
 380                         int stopCount = 0;
 381                         int fixedCount = 0;
 382                         int unfixedCount = 0;
 383
 384                         // index file header
 385                         hw.write("<tr>");
 386                         hw.write("<td>"+ type +"</td>");
 387                         hw.write("<td>"+ corp +"</td>");
 388                         hw.write("<td><a href='"+ dir.getName() +"/"+ osmFile.getName() +"'>"+ course +"</a></td>");
 389                         hw.write("<td><a href='"+ dir.getName() +"/"+ gpxFile.getName() +"'>"+ gpxFile.getName() +"</a></td>");
 390                         hw.write("<td><a href='"+ dir.getName() +"/"+ osmSample.getName() +"'>"+ osmSample.getName() +"</a></td>");
 391                         hw.newLine();
 392
 393                         //--------------------------------------------
 394                         //      Wiki見出し２: 運行会社
 395                         //------------
 396                         if (!maeCorp.equals(corp)) {
 397                                 if (firstCorp == false) {
 398                                         ww.write("|}");
 399                                         ww.newLine();
 400                                         ww.newLine();
 401                                 }
 402                                 firstCorp = false;
 403
 404                                 ww.write("=== "+ corp +" ===");
 405                                 ww.newLine();
 406                                 ww.newLine();
 407
 408                                 ww.write(":{{JA:Tag|network||"+ corp +"}}");
 409                                 ww.newLine();
 410                                 ww.write(":{{JA:Tag|operator||"+ corp +"}}");
 411                                 ww.newLine();
 412                                 ww.newLine();
 413
 414                                 ww.write("{| class=\"wikitable sortable\" style=\"table-layout: fixed; width: 100%\"");
 415                                 ww.newLine();
 416                                 ww.write("!style=\"width: 100px\"| ref");
 417                                 ww.newLine();
 418                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(マスタ)");
 419                                 ww.newLine();
 420                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(往路)");
 421                                 ww.newLine();
 422                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(復路)");
 423                                 ww.newLine();
 424                                 ww.write("!class=\"unsortable\"| 備考");
 425                                 ww.newLine();
 426
 427                                 maeCorp = new String(corp);
 428                         }
 429
 430                         // Wiki
 431                         ww.write("|-");
 432                         ww.newLine();
 433                         ww.write("| "+ course +" ");            // ref
 434                         ww.write("|| {{State Route|r=0}} {{relation|0|tools=no}} ");                    // 編集状況 (マスタ)
 435                         ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} ");                // 編集状況 (往路)
 436                         ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} ");                // 編集状況 (復路)
 437                         ww.write("|| ");                // 備考
 438                         ww.newLine();
 439
 440
 441                         // OSM file header
 442                         ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));
 443                         ow.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
 444                         ow.newLine();
 445                         ow.write("<osm version=\"0.6\" generator=\"ReadKIBAN\">");
 446                         ow.newLine();
 447
 448                         // GPX file header
 449                         gw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(gpxFile), "UTF-8"));
 450                         gw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
 451                         gw.newLine();
 452                         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 \">");
 453                         gw.newLine();
 454
 455                         double maxLat = -180.0;
 456                         double minLat = 180.0;
 457                         double maxLon = -180.0;
 458                         double minLon = 180.0;
 459
 460                         ps9.setInt(1, code);
 461                         ResultSet rset9 = ps9.executeQuery();
 462                         while (rset9.next()) {
 463                                 String idref = rset9.getString(1);
 464
 465                                 ps8.setString(1, idref);
 466                                 ResultSet rset8 = ps8.executeQuery();
 467                                 if (rset8.next()) {
 468                                         stopCount++;
 469                                         String name = rset8.getString(1);
 470                                         Double lat = rset8.getDouble(2);
 471                                         Double lon = rset8.getDouble(3);
 472                                         int fixed = rset8.getInt(4);
 473
 474                                         if (lat > maxLat) {
 475                                                 maxLat = lat;
 476                                         }
 477                                         if (lon > maxLon) {
 478                                                 maxLon = lon;
 479                                         }
 480                                         if (lat < minLat) {
 481                                                 minLat = lat;
 482                                         }
 483                                         if (lon < minLon) {
 484                                                 minLon = lon;
 485                                         }
 486
 487                                         System.out.println("\tway point = "+ idref +", lat="+ lat +", lon="+ lon +", name="+ name);
 488                                         fixedCount += fixed;
 489                                         if (fixed == 0) {
 490                                                 unfixedCount++;
 491
 492                                                 // OSM node
 493                                                 int nodeid = Integer.parseInt(idref.substring(1)) * -1;
 494                                                 String osm_node = nodeBusstop(nodeid, name, lat, lon, timeStampStr);
 495                                                 ow.write(osm_node);
 496                                                 ow.newLine();
 497
 498                                                 // TEXT node
 499                                                 //File txtFile = new File(dir, iCode + idref +".txt");
 500                                                 //BufferedWriter gw2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(txtFile), "UTF-8"));
 501                                                 //gw2.write(osm_node);
 502                                                 //gw2.newLine();
 503                                                 //gw2.close();
 504
 505                                                 // GPX waypoint
 506                                                 gw.write("<wpt lat=\""+ lat +"\" lon=\""+ lon +"\">\n");
 507                                                 gw.write(" <time>"+ timeStampStr +"Z</time>\n");
 508                                                 gw.write(" <name><![CDATA["+ name +"]]></name>\n");
 509                                                 //gw.write(" <link href=\""+ txtFile.getName() +"\"><text>"+ idref +"</text></link>\n");
 510                                                 gw.write("</wpt>\n");
 511                                                 gw.newLine();
 512                                         }
 513                                 }
 514                                 rset8.close();
 515                         }
 516                         rset9.close();
 517
 518                         // INDEX file
 519                         hw.write("<td>"+ stopCount +"</td>");
 520                         hw.write("<td>"+ unfixedCount +"</td>");
 521                         hw.write("<td>"+ fixedCount +"</td>");
 522                         hw.write("</tr>");
 523                         hw.newLine();
 524
 525                         // OSM file header
 526                         BufferedWriter ow2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmSample), "UTF-8"));
 527                         ow2.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
 528                         ow2.newLine();
 529                         ow2.write("<osm version=\"0.5\" generator=\"ReadKIBAN\">");
 530                         ow2.newLine();
 531
 532                         ow2.write(nodeBusstop(code, "SAMPLE", ((maxLat+minLat)/2), ((maxLon+minLon)/2), timeStampStr));
 533                         ow2.newLine();
 534                         ow2.write("</osm>");
 535                         ow2.newLine();
 536                         ow2.close();
 537
 538                         // OSM file footer
 539                         ow.write("</osm>");
 540                         ow.newLine();
 541                         ow.close();
 542
 543                         // GPX file footer
 544                         gw.write("</gpx>");
 545                         gw.newLine();
 546                         gw.close();
 547                 }
 548                 rset7.close();
 549
 550                 // Wiki footer
 551                 ww.close();
 552
 553                 // index file footer
 554                 hw.write("</table></body></html>");
 555                 hw.newLine();
 556                 hw.close();
 557         }
 558
 559         public static String nodeBusstop(int code, String name, Double lat, Double lon, String timeStampStr) {
 560                 String osm_node = ("<node id=\""+ code +"\" timestamp=\""+ timeStampStr +"Z\" lat=\""+ lat +"\" lon=\""+ lon +"\">\n");
 561                 osm_node += "<tag k=\"name\" v=\""+ name +"\"/>\n";
 562                 osm_node += "<tag k=\"fixme\" v=\"platform/stop_positionを選択して、正しい位置に移動させてください\"/>\n";
 563                 osm_node += "<tag k=\"source\" v=\"KSJ2\"/>\n";
 564                 osm_node += "<tag k=\"source_ref\" v=\"http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-P11.html\"/>\n";
 565                 osm_node += "<tag k=\"created_by\" v=\"National-Land-Numerical-Information_MLIT_Japan\"/>\n";
 566                 osm_node += "<tag k=\"note\" v=\"National-Land Numerical Information (Bus stop) 2012, MLIT Japan\"/>\n";
 567                 osm_node += "<tag k=\"note:ja\" v=\"国土数値情報（バス停留所）平成２４年　国土交通省\"/>\n";
 568                 osm_node += "<tag k=\"public_transport\" v=\"platform\"/>\n";
 569                 osm_node += "<tag k=\"public_transport\" v=\"stop_position\"/>\n";
 570                 osm_node += "<tag k=\"highway\" v=\"bus_stop\"/>\n";
 571                 osm_node += "<tag k=\"bus\" v=\"yes\"/>\n";
 572                 osm_node += "</node>\n";
 573                 return osm_node;
 574         }
 575
 576         /**
 577          *
 578          * @param con
 579          * @param node
 580          * @param iFileName             // ソースファイル名（拡張子を含まない）
 581          * @param timeStampStr
 582          * @return
 583          * @throws IOException
 584          * @throws SQLException
 585          */
 586         public static int showNodes(Connection con, Node node, String iFileName, String timeStampStr) throws IOException, SQLException {
 587                 int iCounter = 0;
 588                 NamedNodeMap nodeMap = node.getAttributes();
 589                 if ( null != nodeMap ) {
 590                         for ( int j=0; j < nodeMap.getLength(); j++ ) {
 591                                 if (nodeMap.item(j).getNodeName().equals("timeStamp")) {
 592                                         timeStampStr = nodeMap.item(j).getNodeValue();
 593                                 }
 594                         }
 595                 }
 596
 597                 NodeList nodes = node.getChildNodes();
 598                 for (int i=0; i<nodes.getLength(); i++) {
 599                         Node node2 = nodes.item(i);
 600                         if (node2.getNodeName().equals("jps:GM_Point")) {
 601                                 showGmPoint(con, node2);
 602                         }
 603                         else if (node2.getNodeName().equals("gml:Point")) {
 604                                 showGmlPoint(con, node2);
 605                         }
 606
 607                         else if (node2.getNodeName().equals("ksj:ED01")) {
 608                                 iCounter++;
 609                                 showED01(con, node2, iFileName);
 610                         }
 611                         else if (node2.getNodeName().equals("ksj:BusStop")) {
 612                                 iCounter++;
 613                                 showBusStop(con, node2, iFileName);
 614                         }
 615
 616                         else {
 617                                 iCounter += showNodes(con, node2, iFileName, timeStampStr);
 618                         }
 619                 }
 620                 return iCounter;
 621         }
 622
 623         /**
 624          *
 625          * @param con
 626          * @param node
 627          * @param iFileName             // ソースファイル名（拡張子を含まない）
 628          * @throws IOException
 629          * @throws SQLException
 630          */
 631         public static void showED01(Connection con, Node node, String iFileName) throws IOException, SQLException {
 632                 String idrefStr = "";
 633                 String nameStr = "";
 634                 PreparedStatement ps1 = con.prepareStatement("SELECT idref FROM bus_stop WHERE idref=?");
 635                 PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (idref,name,ifile) VALUES (?,?,?)");
 636                 PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");
 637                 PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");
 638                 PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");
 639                 PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");
 640
 641                 ArrayList<String[]> bris = new ArrayList<String[]>();
 642                 NodeList nodes = node.getChildNodes();
 643                 for (int i=0; i < nodes.getLength(); i++) {
 644                         Node node2 = nodes.item(i);
 645                         if (node2.getNodeName().equals("ksj:POS")) {
 646                                 NamedNodeMap nodeMap = node2.getAttributes();
 647                                 if (null != nodeMap) {
 648                                         for ( int j=0; j < nodeMap.getLength(); j++ ) {
 649                                                 if (nodeMap.item(j).getNodeName().equals("idref")) {
 650                                                         idrefStr = nodeMap.item(j).getNodeValue();
 651                                                         System.out.println("found idref='"+ idrefStr +"'");
 652                                                         break;
 653                                                 }
 654                                         }
 655                                 }
 656                         }
 657                         else if (node2.getNodeName().equals("ksj:BSN")) {
 658                                 nameStr = node2.getTextContent();
 659                         }
 660                         else if (node2.getNodeName().equals("ksj:BRI")) {
 661                                 String[] rtn = anaComm(node2);
 662                                 if (rtn != null) {
 663                                         bris.add(rtn);
 664                                 }
 665                         }
 666                 }
 667
 668                 // idref と nameStr をデータベースに格納する
 669                 boolean insert = true;
 670                 ps1.setString(1, idrefStr);
 671                 ResultSet rset = ps1.executeQuery();
 672                 if (rset.next()) {
 673                         insert = false;
 674                 }
 675                 rset.close();
 676
 677                 if (insert) {
 678                         ps2.setString(1, idrefStr);
 679                         ps2.setString(2, nameStr);
 680                         ps2.setString(3, iFileName);
 681                         System.out.println("INSERT INTO bus_stop (idref,name,ifile) VALUES ('"+ idrefStr +"','"+ nameStr +"','"+ iFileName +"')");
 682                         ps2.executeUpdate();
 683                 }
 684
 685                 for (String[] rtn : bris) {
 686                         int code = 0;
 687                         ps3.setString(1, rtn[1]);
 688                         ps3.setString(2, rtn[2]);
 689                         ps3.setString(3, iFileName);
 690                         rset = ps3.executeQuery();
 691                         if (rset.next()) {
 692                                 code = rset.getInt(1);
 693                         }
 694                         rset.close();
 695
 696                         if (code == 0) {
 697                                 ps6.setString(1, iFileName);
 698                                 ResultSet rset6 = ps6.executeQuery();
 699                                 if (rset6.next()) {
 700                                         code = rset6.getInt(1);
 701                                 }
 702                                 rset6.close();
 703                                 code++;
 704
 705                                 System.out.println("code="+code);
 706                                 ps4.setInt(1, code);
 707                                 ps4.setInt(2, Integer.parseInt(rtn[0]));
 708                                 ps4.setString(3, rtn[2]);
 709                                 ps4.setString(4, rtn[1]);
 710                                 ps4.setString(5, iFileName);
 711                                 ps4.executeUpdate();
 712                         }
 713
 714                         ps5.setString(1, idrefStr);
 715                         ps5.setInt(2, code);
 716                         ps5.executeUpdate();
 717                 }
 718
 719                 ps1.close();
 720                 ps2.close();
 721                 ps3.close();
 722                 ps4.close();
 723                 ps5.close();
 724         }
 725
 726         /**
 727          * <ksj:BusStop gml:id="ED01_1">
 728          *      <ksj:position xlink:href="#n1"/>
 729          *      <ksj:busStopName>城堀</ksj:busStopName>
 730          *      <ksj:busRouteInformation>
 731          *              <ksj:BusRouteInformation>
 732          *                      <ksj:busType>1</ksj:busType>
 733          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
 734          *                      <ksj:busLineName>小01</ksj:busLineName>
 735          *              </ksj:BusRouteInformation>
 736          *      </ksj:busRouteInformation>
 737          *      <ksj:busRouteInformation>
 738          *              <ksj:BusRouteInformation>
 739          *                      <ksj:busType>1</ksj:busType>
 740          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
 741          *                      <ksj:busLineName>湯07</ksj:busLineName>
 742          *              </ksj:BusRouteInformation>
 743          *      </ksj:busRouteInformation>
 744          *      <ksj:busRouteInformation>
 745          *              <ksj:BusRouteInformation>
 746          *                      <ksj:busType>1</ksj:busType>
 747          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
 748          *                      <ksj:busLineName>湯11</ksj:busLineName>
 749          *              </ksj:BusRouteInformation>
 750          *      </ksj:busRouteInformation>
 751          * </ksj:BusStop>
 752          *
 753          * @param con
 754          * @param node
 755          * @param iFileName             // ソースファイル名（拡張子を含まない）
 756          * @throws IOException
 757          * @throws SQLException
 758          */
 759         public static void showBusStop(Connection con, Node node, String iFileName) throws IOException, SQLException {
 760                 String idrefStr = "";
 761                 String nameStr = "";
 762                 PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE idref=?");
 763                 PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");
 764                 PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");
 765                 PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");
 766                 PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");
 767
 768                 ArrayList<String[]> bris = new ArrayList<String[]>();
 769                 NodeList nodes = node.getChildNodes();
 770                 for (int i=0; i < nodes.getLength(); i++) {
 771                         Node node2 = nodes.item(i);
 772                         if (node2.getNodeName().equals("ksj:position")) {
 773                                 NamedNodeMap nodeMap = node2.getAttributes();
 774                                 if (null != nodeMap) {
 775                                         for ( int j=0; j < nodeMap.getLength(); j++ ) {
 776                                                 if (nodeMap.item(j).getNodeName().equals("xlink:href")) {
 777                                                         idrefStr = nodeMap.item(j).getNodeValue();
 778                                                         idrefStr = idrefStr.substring(1);
 779                                                         System.out.println("found idref='"+ idrefStr +"'");
 780                                                         break;
 781                                                 }
 782                                         }
 783                                 }
 784                         }
 785                         else if (node2.getNodeName().equals("ksj:busStopName")) {
 786                                 nameStr = node2.getTextContent();
 787                         }
 788                         else if (node2.getNodeName().equals("ksj:busRouteInformation")) {
 789                                 String[] rtn = anaCommJGD(node2);
 790                                 if (rtn != null) {
 791                                         bris.add(rtn);
 792                                 }
 793                         }
 794                 }
 795
 796                 // idref と nameStr をデータベースに格納する
 797                 ps2.setString(1, nameStr);
 798                 ps2.setString(2, iFileName);
 799                 ps2.setString(3, idrefStr);
 800                 ps2.executeUpdate();
 801
 802                 for (String[] rtn : bris) {
 803                         int code = 0;
 804                         ps3.setString(1, rtn[1]);
 805                         ps3.setString(2, rtn[2]);
 806                         ps3.setString(3, iFileName);
 807                         ResultSet rset = ps3.executeQuery();
 808                         if (rset.next()) {
 809                                 code = rset.getInt(1);
 810                         }
 811                         rset.close();
 812
 813                         if (code == 0) {
 814                                 ps6.setString(1, iFileName);
 815                                 ResultSet rset6 = ps6.executeQuery();
 816                                 if (rset6.next()) {
 817                                         code = rset6.getInt(1);
 818                                 }
 819                                 rset6.close();
 820                                 code++;
 821
 822                                 System.out.println("course="+ code +" : "+ rtn[0] +" : "+ rtn[1] +" : "+ rtn[2] );
 823                                 ps4.setInt(1, code);
 824                                 ps4.setInt(2, Integer.parseInt(rtn[0]));
 825                                 ps4.setString(3, rtn[2]);
 826                                 ps4.setString(4, rtn[1]);
 827                                 ps4.setString(5, iFileName);
 828                                 ps4.executeUpdate();
 829                         }
 830
 831                         ps5.setString(1, idrefStr);
 832                         ps5.setInt(2, code);
 833                         ps5.executeUpdate();
 834                 }
 835
 836                 ps2.close();
 837                 ps3.close();
 838                 ps4.close();
 839                 ps5.close();
 840         }
 841
 842         public static String[] anaComm(Node briNode) {
 843                 String[] rtn = new String[3];
 844                 rtn[0] = "";    // corp type
 845                 rtn[1] = "";    // course name
 846                 rtn[2] = "";    // corp name
 847
 848                 NodeList nodes = briNode.getChildNodes();
 849                 for (int i=0; i < nodes.getLength(); i++) {
 850                         Node node2 = nodes.item(i);
 851                         if (node2.getNodeName().equals("ksj:BSC")) {
 852                                 rtn[0] = node2.getTextContent();
 853                         }
 854                         else if (node2.getNodeName().equals("ksj:BLN")) {
 855                                 rtn[1] = node2.getTextContent();
 856                         }
 857                         else if (node2.getNodeName().equals("ksj:BOC")) {
 858                                 rtn[2] = node2.getTextContent();
 859                         }
 860                 }
 861                 return rtn;
 862         }
 863
 864         /**
 865          *
 866          *      <ksj:busRouteInformation>
 867          *              <ksj:BusRouteInformation>
 868          *                      <ksj:busType>1</ksj:busType>
 869          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
 870          *                      <ksj:busLineName>小01</ksj:busLineName>
 871          *              </ksj:BusRouteInformation>
 872          *      </ksj:busRouteInformation>
 873          *
 874          * @param briNode
 875          * @return
 876          */
 877         public static String[] anaCommJGD(Node briNode) {
 878                 String[] rtn = new String[3];
 879                 int vcnt = 0;
 880
 881                 NodeList nodes2 = briNode.getChildNodes();
 882                 for (int i=0; i < nodes2.getLength(); i++) {
 883                         Node node2 = nodes2.item(i);
 884                         if (node2.getNodeName().equals("ksj:BusRouteInformation")) {
 885                                 NodeList nodes3 = node2.getChildNodes();
 886                                 for (int j=0; j < nodes3.getLength(); j++) {
 887                                         Node node3 = nodes3.item(j);
 888                                         if (node3.getNodeName().equals("ksj:busType")) {
 889                                                 rtn[0] = new String(node3.getTextContent());
 890                                                 vcnt++;
 891                                         }
 892                                         else if (node3.getNodeName().equals("ksj:busLineName")) {
 893                                                 rtn[1] = new String(node3.getTextContent());
 894                                                 vcnt++;
 895                                         }
 896                                         else if (node3.getNodeName().equals("ksj:busOperationCompany")) {
 897                                                 rtn[2] = new String(node3.getTextContent());
 898                                                 vcnt++;
 899                                         }
 900                                 }
 901                         }
 902                 }
 903
 904                 if (vcnt > 0) {
 905                         return rtn;
 906                 }
 907                 return null;
 908         }
 909
 910         public static void showGmPoint(Connection con, Node node) throws IOException, SQLException {
 911                 String positionStr = "";
 912                 String latStr = "";
 913                 String lonStr = "";
 914                 String idStr = "";
 915
 916                 NamedNodeMap nodeMap = node.getAttributes();
 917                 if ( null != nodeMap ) {
 918                         for ( int j=0; j<nodeMap.getLength(); j++ ) {
 919                                 if (nodeMap.item(j).getNodeName().equals("id")) {
 920                                         idStr = nodeMap.item(j).getNodeValue();
 921                                 }
 922                         }
 923                 }
 924
 925                 NodeList nodes = node.getChildNodes();
 926                 for (int i=0; i < nodes.getLength(); i++) {
 927                         Node node2 = nodes.item(i);
 928                         if (node2.getNodeName().equals("jps:GM_Point.position")) {
 929                                 NodeList nodes3 = node2.getChildNodes();
 930                                 for (int j=0; j < nodes3.getLength(); j++) {
 931                                         Node node3 = nodes3.item(j);
 932                                         if (node3.getNodeName().equals("jps:DirectPosition")) {
 933                                                 NodeList nodes4 = node3.getChildNodes();
 934                                                 for (int k=0; k < nodes4.getLength(); k++) {
 935                                                         Node node4 = nodes4.item(k);
 936                                                         if (node4.getNodeName().equals("DirectPosition.coordinate")) {
 937                                                                 positionStr = node4.getTextContent();
 938                                                                 String[] str4Ary = positionStr.split(" ");
 939                                                                 latStr = str4Ary[0];
 940                                                                 lonStr = str4Ary[1];
 941                                                                 break;
 942                                                         }
 943                                                 }
 944                                                 break;
 945                                         }
 946                                 }
 947
 948                                 PreparedStatement ps6 = con.prepareStatement("UPDATE bus_stop SET lat=?,lon=?,fixed=? WHERE idref=?");
 949                                 double lat = Double.parseDouble(latStr);
 950                                 double lon = Double.parseDouble(lonStr);
 951                                 ps6.setDouble(1, lat);
 952                                 ps6.setDouble(2, lon);
 953                                 // ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
 954                                 ps6.setInt(3, 0);
 955                                 ps6.setString(4, idStr);
 956                                 System.out.println("UPDATE bus_stop("+ idStr +") lat="+ lat +", lon="+ lon +", fixed="+ (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
 957                                 ps6.executeUpdate();
 958                                 ps6.close();
 959                         }
 960                 }
 961         }
 962
 963         /**
 964          * <gml:Point gml:id="n1">
 965          *      <gml:pos>35.14591397 139.10569573</gml:pos>
 966          * </gml:Point>
 967          *
 968          * @param con
 969          * @param node
 970          * @throws IOException
 971          * @throws SQLException
 972          */
 973         public static void showGmlPoint(Connection con, Node node) throws IOException, SQLException {
 974                 String positionStr = "";
 975                 String latStr = "";
 976                 String lonStr = "";
 977                 String idStr = "";
 978
 979                 NamedNodeMap nodeMap = node.getAttributes();
 980                 if ( null != nodeMap ) {
 981                         for ( int j=0; j<nodeMap.getLength(); j++ ) {
 982                                 if (nodeMap.item(j).getNodeName().equals("gml:id")) {
 983                                         idStr = nodeMap.item(j).getNodeValue();
 984                                 }
 985                         }
 986                 }
 987
 988                 NodeList nodes = node.getChildNodes();
 989                 for (int i=0; i < nodes.getLength(); i++) {
 990                         Node node2 = nodes.item(i);
 991                         if (node2.getNodeName().equals("gml:pos")) {
 992                                 positionStr = node2.getTextContent().trim();
 993                                 String[] str4Ary = positionStr.split(" ");
 994                                 latStr = str4Ary[0];
 995                                 lonStr = str4Ary[1];
 996
 997                                 PreparedStatement ps6 = con.prepareStatement("INSERT INTO bus_stop (lat,lon,fixed,idref) VALUES (?,?,?,?)");
 998                                 double lat = Double.parseDouble(latStr);
 999                                 double lon = Double.parseDouble(lonStr);
1000                                 System.out.println("INSERT INTO bus_stop (lat,lon,fixed,idref) VALUES ('"+ latStr +"','"+ lonStr +"','"+ (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)) +"','"+ idStr +"')");
1001
1002                                 ps6.setDouble(1, lat);
1003                                 ps6.setDouble(2, lon);
1004                                 //ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
1005                                 ps6.setInt(3, 0);
1006                                 ps6.setString(4, idStr);
1007                                 ps6.executeUpdate();
1008                                 ps6.close();
1009                         }
1010                 }
1011         }
1012
1013         static boolean checkFile(File f) {
1014                 String name = f.getName();
1015                 if (!name.startsWith("P11-")) {
1016                         return false;
1017                 }
1018                 if (!name.toUpperCase().endsWith(".XML")) {
1019                         return false;
1020                 }
1021                 return true;
1022         }
1023 }