src/osm/jp/ConvBusstop.java

   1 package osm.jp;\r
   2 import osm.jp.api.HttpPOST;\r
   3 \r
   4 import javax.xml.parsers.*;\r
   5 import javax.xml.transform.OutputKeys;\r
   6 import javax.xml.transform.Transformer;\r
   7 import javax.xml.transform.TransformerException;\r
   8 import javax.xml.transform.TransformerFactory;\r
   9 import javax.xml.transform.dom.DOMSource;\r
  10 import javax.xml.transform.stream.StreamResult;\r
  11 \r
  12 import org.w3c.dom.*;\r
  13 import org.xml.sax.*;\r
  14 \r
  15 import java.io.*;\r
  16 import java.sql.Connection;\r
  17 import java.sql.PreparedStatement;\r
  18 import java.sql.ResultSet;\r
  19 import java.sql.SQLException;\r
  20 import java.sql.Statement;\r
  21 import java.text.SimpleDateFormat;\r
  22 import java.util.ArrayList;\r
  23 import java.util.Calendar;\r
  24 import java.util.Date;\r
  25 \r
  26 import jp.co.areaweb.tools.database.*;\r
  27 \r
  28 public class ConvBusstop {\r
  29 \r
  30         String filter = "";\r
  31         String urlStr = "";\r
  32 \r
  33         public static final boolean DB_INIT = false;\r
  34 \r
  35         // 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域\r
  36         static final int NEER = 150;\r
  37         static boolean nocheck = false;\r
  38 \r
  39         /**\r
  40          * メイン\r
  41          *\r
  42          *      java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop <option>\r
  43          *              OPTION: -nocheck        OSMデータ上に既存のバス停が存在するかどうかをチェックしない\r
  44          *              OPTION: -check  OSMデータ上に既存のバス停が存在するかどうかをチェックする\r
  45          *\r
  46          * @throws IOException\r
  47          * @throws SQLException\r
  48          * @throws ClassNotFoundException\r
  49          * @throws FileNotFoundException\r
  50          * @throws TransformerException\r
  51          * @throws SAXException\r
  52          * @throws ParserConfigurationException */\r
  53         public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException\r
  54         {\r
  55                 int index = 0;\r
  56                 if (args.length > index) {\r
  57                         if (args[index].equals("-check")) {\r
  58                                 ConvBusstop.nocheck = false;\r
  59                                 index++;\r
  60                         }\r
  61                         else if (args[index].equals("-nocheck")) {\r
  62                                 ConvBusstop.nocheck = true;\r
  63                                 index++;\r
  64                         }\r
  65                 }\r
  66 \r
  67                 /**\r
  68                  * アプリケーション [ConvBusstop]\r
  69                  * > java -jar ConvBusstop.jar <オプション>\r
  70                  *      オプション： -exp 実行する直前にデータベースを初期化する（省略可能）\r
  71                  */\r
  72                 File dbdir = new File("database");\r
  73                 if (!dbdir.isDirectory()) {\r
  74                         dbdir.mkdir();\r
  75                 }\r
  76 \r
  77                 Connection con = DatabaseTool.openDb("database");\r
  78                 ConvBusstop.initDb(con);\r
  79 \r
  80                 try {\r
  81                         /**\r
  82                          * バス停データ変換のメイン処理\r
  83                          */\r
  84                         int fcounter = 0;\r
  85                         File dir = new File(".");\r
  86                         File[] files = dir.listFiles();\r
  87                         for (File iFile : files) {\r
  88                                 if (checkFile(iFile)) {\r
  89                                         fcounter++;\r
  90                                         ConvBusstop.clearDb(con);\r
  91                                         inputFile(con, iFile);\r
  92 \r
  93                                         String iStr = iFile.getName();\r
  94                                         iStr = iStr.substring(0, iStr.length() - 4);\r
  95                                         \r
  96                                         /**\r
  97                                          * 既存のOSMバス停を読み込む\r
  98                                          *              --> 'existing.xml'\r
  99                                          */\r
 100                                         if (!ConvBusstop.nocheck && (fcounter > 0)) {\r
 101                                                 /*\r
 102                                                  * 既存のOSMデータファイルがなければ、新たにダウンロードする。\r
 103                                                  * OSMデータファイルがあるときは、ダウンロードしないでそれを使う。\r
 104                                                  */\r
 105                                                 File existingFile = new File(iStr, "existing.xml");\r
 106                                                 if (!existingFile.isFile()) {\r
 107                                                         /**\r
 108                                                          * インポートしたデータの緯度経度範囲を読み取る\r
 109                                                          */\r
 110                                                         double maxLat = -90.0D;\r
 111                                                         double minLat = 90.0D;\r
 112                                                         double maxLon = -180.0D;\r
 113                                                         double minLon = 180.0D;\r
 114                                                         PreparedStatement ps8 = con.prepareStatement("SELECT lat,lon FROM bus_stop");\r
 115                                                         ResultSet rset8 = ps8.executeQuery();\r
 116                                                         while (rset8.next()) {\r
 117                                                                 Double lat = rset8.getDouble("lat");\r
 118                                                                 Double lon = rset8.getDouble("lon");\r
 119 \r
 120                                                                 if (lat > maxLat) {\r
 121                                                                         maxLat = lat;\r
 122                                                                 }\r
 123                                                                 if (lon > maxLon) {\r
 124                                                                         maxLon = lon;\r
 125                                                                 }\r
 126                                                                 if (lat < minLat) {\r
 127                                                                         minLat = lat;\r
 128                                                                 }\r
 129                                                                 if (lon < minLon) {\r
 130                                                                         minLon = lon;\r
 131                                                                 }\r
 132                                                         }\r
 133                                                         rset8.close();\r
 134                                                         \r
 135                                                         /**\r
 136                                                          * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する\r
 137                                                          */\r
 138                                                         HttpPOST.getCapabilities(new File(iStr), minLat, maxLat, minLon, maxLon);\r
 139                                                 }\r
 140                                                 readExistingFile(con, existingFile);\r
 141                                                 \r
 142                                                 PreparedStatement ps1 = con.prepareStatement("SELECT idref,name,lat,lon FROM bus_stop");\r
 143                                                 PreparedStatement ps2 = con.prepareStatement("SELECT count(idref) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");\r
 144                                                 PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?");\r
 145                                                 PreparedStatement ps4 = con.prepareStatement("SELECT count(idref) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (name = ?)");\r
 146                                                 ResultSet rset1 = ps1.executeQuery();\r
 147                                                 while (rset1.next()) {\r
 148                                                         String idref = rset1.getString("idref");\r
 149                                                         String name = rset1.getString("name");\r
 150                                                         Double lat = rset1.getDouble("lat");\r
 151                                                         Double lon = rset1.getDouble("lon");\r
 152                                                         \r
 153                                                         // 指定の緯度経度を中心とする半径150x2m四方の矩形領域\r
 154                                                         System.out.print(idref + "("+ name + ") ....");\r
 155                                                         RectArea rect = new RectArea(lat, lon, NEER);           // 300m 四方\r
 156                                                         ps2.setDouble(1, rect.minlat);\r
 157                                                         ps2.setDouble(2, rect.maxlat);\r
 158                                                         ps2.setDouble(3, rect.minlon);\r
 159                                                         ps2.setDouble(4, rect.maxlon);\r
 160                                                         ResultSet rset2 = ps2.executeQuery();\r
 161                                                         if (rset2.next()) {\r
 162                                                                 int count = rset2.getInt(1);\r
 163                                                                 if (count > 0) {\r
 164                                                                         System.out.println("."+ count);\r
 165                                                                         ps3.setInt(1, count);\r
 166                                                                         ps3.setString(2, idref);\r
 167                                                                         ps3.executeUpdate();\r
 168                                                                 }\r
 169                                                                 else {\r
 170                                                                         // 指定の緯度経度を中心とする半径150x4m四方の矩形領域\r
 171                                                                         System.out.print("***");\r
 172                                                                         rect = new RectArea(lat, lon, NEER*2);          // 600m 四方\r
 173                                                                         ps4.setDouble(1, rect.minlat);\r
 174                                                                         ps4.setDouble(2, rect.maxlat);\r
 175                                                                         ps4.setDouble(3, rect.minlon);\r
 176                                                                         ps4.setDouble(4, rect.maxlon);\r
 177                                                                         ps4.setString(5, name);\r
 178                                                                         ResultSet rset4 = ps4.executeQuery();\r
 179                                                                         if (rset4.next()) {\r
 180                                                                                 count = rset4.getInt(1);\r
 181                                                                                 System.out.println(".."+ count);\r
 182                                                                                 ps3.setInt(1, count);\r
 183                                                                                 ps3.setString(2, idref);\r
 184                                                                                 ps3.executeUpdate();\r
 185                                                                         }\r
 186                                                                         rset4.close();\r
 187                                                                 }\r
 188                                                         }\r
 189                                                         rset2.close();\r
 190                                                 }\r
 191                                                 rset1.close();\r
 192                                                 ps3.close();\r
 193                                                 ps2.close();\r
 194                                                 ps1.close();\r
 195                                         }\r
 196                                         \r
 197 \r
 198                                         // ローカルデータベース内の情報を出力する\r
 199                                         outputDb(con, iStr);\r
 200                                         \r
 201                                         // ローカルデータベース内の情報を「busstop.xml」に出力する\r
 202                                         int counter = outputDb(con, new File(iStr));\r
 203                                         System.out.println("["+ counter +"] 変換完了しました。");\r
 204                                 }\r
 205                         }\r
 206                         System.out.println("["+ fcounter +"]つのファイルをインポートしました。");\r
 207                 }\r
 208                 finally {\r
 209                         DatabaseTool.closeDb(con);\r
 210                 }\r
 211         }\r
 212 \r
 213         static String[] shiftArgs(String[] args) {\r
 214                 String[] values = new String[args.length - 1];\r
 215                 for (int i=1; i < args.length; i++) {\r
 216                         values[i - 1] = new String(args[i]);\r
 217                 }\r
 218                 return values;\r
 219         }\r
 220 \r
 221         public static void readExistingFile (Connection con, File existingFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {\r
 222                 int iCounter = 0;\r
 223 \r
 224                 DocumentBuilderFactory factory;\r
 225                 DocumentBuilder        builder;\r
 226                 Node root;\r
 227 \r
 228                 iCounter = 0;\r
 229                 factory = DocumentBuilderFactory.newInstance();\r
 230                 builder = factory.newDocumentBuilder();\r
 231                 factory.setIgnoringElementContentWhitespace(true);\r
 232                 factory.setIgnoringComments(true);\r
 233                 factory.setValidating(true);\r
 234                 root    = builder.parse(existingFile);\r
 235 \r
 236                 iCounter += readExistingNodes(con, root);\r
 237                 System.out.println("既存バス停数["+ iCounter +"]");\r
 238         }\r
 239         \r
 240         static int readExistingNodes(Connection con, Node node) throws IOException, SQLException {\r
 241                 int iCounter = 0;\r
 242                 \r
 243                 NodeList nodes = node.getChildNodes();\r
 244                 for (int i = 0; i < nodes.getLength(); i++) {\r
 245                         Node node2 = nodes.item(i);\r
 246                         if (node2.getNodeName().equals("node")) {\r
 247                                 iCounter++;\r
 248                                 importExistingNode(con, node2);\r
 249                         }\r
 250                         else {\r
 251                                 iCounter += readExistingNodes(con, node2);\r
 252                         }\r
 253                 }\r
 254                 return iCounter;\r
 255         }\r
 256 \r
 257         static void importExistingNode(Connection con, Node node) throws IOException, SQLException {\r
 258                 String idrefStr = "";\r
 259                 String latStr = "";\r
 260                 String lonStr = "";\r
 261                 String nameStr = "";\r
 262                 PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name) VALUES (?,?,?,?)");\r
 263 \r
 264                 NamedNodeMap nodeMap = node.getAttributes();\r
 265                 if (null != nodeMap) {\r
 266                         for (int j=0; j < nodeMap.getLength(); j++) {\r
 267                                 if (nodeMap.item(j).getNodeName().equals("id")) {\r
 268                                         idrefStr = nodeMap.item(j).getNodeValue();\r
 269                                 }\r
 270                                 else if (nodeMap.item(j).getNodeName().equals("lat")) {\r
 271                                         latStr = nodeMap.item(j).getNodeValue();\r
 272                                 }\r
 273                                 else if (nodeMap.item(j).getNodeName().equals("lon")) {\r
 274                                         lonStr = nodeMap.item(j).getNodeValue();\r
 275                                 }\r
 276                         }\r
 277                         \r
 278                         NodeList nodes = node.getChildNodes();\r
 279                         for (int i = 0; i < nodes.getLength(); i++) {\r
 280                                 Node node2 = nodes.item(i);\r
 281                                 if (node2.getNodeName().equals("tag")) {\r
 282                                         NamedNodeMap nodeMap2 = node2.getAttributes();\r
 283                                         if (null != nodeMap2) {\r
 284                                                 String key = null;\r
 285                                                 String value = null;\r
 286                                                 for (int j=0; j < nodeMap2.getLength(); j++) {\r
 287                                                         if (nodeMap2.item(j).getNodeName().equals("k")) {\r
 288                                                                 key = nodeMap2.item(j).getNodeValue();\r
 289                                                         }\r
 290                                                         else if (nodeMap2.item(j).getNodeName().equals("v")) {\r
 291                                                                 value = nodeMap2.item(j).getNodeValue();\r
 292                                                         }\r
 293                                                 }\r
 294                                                 \r
 295                                                 if ((key != null) && key.toLowerCase().equals("name") && (value != null)) {\r
 296                                                         nameStr = value;\r
 297                                                         break;\r
 298                                                 }\r
 299                                         }\r
 300                                 }\r
 301                         }\r
 302 \r
 303                         // idref と nameStr をデータベースに格納する\r
 304                         System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")"+ nameStr);\r
 305                         ps5.setString(1, idrefStr);\r
 306                         ps5.setDouble(2, Double.parseDouble(latStr));\r
 307                         ps5.setDouble(3, Double.parseDouble(lonStr));\r
 308                         ps5.setString(4, nameStr);\r
 309                         ps5.executeUpdate();\r
 310                         ps5.close();\r
 311                 }\r
 312         }\r
 313         \r
 314         /**\r
 315          * ソースファイルを読み取ってローカルベータベースへ記録する\r
 316          * @param con\r
 317          * @param iFile\r
 318          * @throws FileNotFoundException\r
 319          * @throws ClassNotFoundException\r
 320          * @throws SQLException\r
 321          * @throws IOException\r
 322          * @throws ParserConfigurationException \r
 323          * @throws SAXException \r
 324          */\r
 325         public static void inputFile (Connection con, File iFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {\r
 326                 int iCounter = 0;\r
 327                 String timeStampStr = null;\r
 328 \r
 329                 String iStr = iFile.getName();\r
 330                 File dir = new File(iStr.substring(0, iStr.length() - 4));\r
 331                 dir.mkdir();\r
 332 \r
 333                 DocumentBuilderFactory factory;\r
 334                 DocumentBuilder        builder;\r
 335                 Node root;\r
 336 \r
 337                 iCounter = 0;\r
 338                 factory = DocumentBuilderFactory.newInstance();\r
 339                 builder = factory.newDocumentBuilder();\r
 340                 factory.setIgnoringElementContentWhitespace(true);\r
 341                 factory.setIgnoringComments(true);\r
 342                 factory.setValidating(true);\r
 343                 root    = builder.parse(iStr);\r
 344 \r
 345                 iCounter += showNodes(con, root, iStr.substring(0, iStr.length() - 4), timeStampStr);\r
 346                 System.out.println("バス停数["+ iCounter +"]");\r
 347         }\r
 348 \r
 349         public static void clearDb(Connection con) throws SQLException {\r
 350                 Statement stmt = con.createStatement();\r
 351                 long count = stmt.executeUpdate("delete from bus_stop");\r
 352                 System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。");\r
 353             \r
 354             count = stmt.executeUpdate("delete from existing_data");\r
 355             System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。");\r
 356 \r
 357             count = stmt.executeUpdate("delete from bus_course");\r
 358             System.out.println("'Database.bus_course'から "+ count +" 件のデータを削除しました。");\r
 359 \r
 360             count = stmt.executeUpdate("delete from bus_ref");\r
 361             System.out.println("'Database.bus_ref'から "+ count +" 件のデータを削除しました。");\r
 362             stmt.close();\r
 363         }\r
 364 \r
 365         public static void initDb(Connection con) throws SQLException {\r
 366                 // 'table.BUS_STOP'を新規に作る\r
 367                 DbBusstop.create(con);\r
 368         }\r
 369 \r
 370 \r
 371         /**\r
 372          * ローカルデータベース内の情報を出力する\r
 373          * @param con\r
 374          * @param iCode\r
 375          * @throws IOException\r
 376          * @throws SQLException\r
 377          * @throws TransformerException \r
 378          * @throws ParserConfigurationException \r
 379          * @throws SAXException \r
 380          */\r
 381         public static void outputDb(Connection con, String iCode) throws IOException, SQLException, SAXException, ParserConfigurationException, TransformerException {\r
 382                 SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");\r
 383                 String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));\r
 384                 File dir = new File(iCode);\r
 385                 dir.mkdir();\r
 386 \r
 387                 BufferedWriter ow = null;\r
 388                 BufferedWriter gw = null;\r
 389                 BufferedWriter hw = null;\r
 390                 BufferedWriter ww = null;\r
 391 \r
 392                 // HTML header\r
 393                 File htmlFile = new File(iCode  +".html");\r
 394                 hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(htmlFile), "UTF-8"));\r
 395                 hw.write("<!DOCTYPE html>");\r
 396                 hw.newLine();\r
 397                 hw.write("<html><head><meta charset=\"utf-8\" /></head>");\r
 398                 hw.newLine();\r
 399                 hw.write("<body><table border='1'>");\r
 400                 hw.newLine();\r
 401                 hw.write("<tr>");\r
 402                 hw.write("<td>type</td>");\r
 403                 hw.write("<td>corp</td>");\r
 404                 hw.write("<td>course</td>");\r
 405                 hw.write("<td>GPX</td>");\r
 406                 hw.write("<td>SAMPLE</td>");\r
 407                 hw.write("<td>バス停数</td>");\r
 408                 hw.write("<td>未入力</td>");\r
 409                 hw.write("<td>既存</td>");\r
 410                 hw.write("</tr>");\r
 411                 hw.newLine();\r
 412 \r
 413                 // Wiki header\r
 414                 File wikiFile = new File(iCode  +".wiki.txt");\r
 415                 ww = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(wikiFile), "UTF-8"));\r
 416                 ww.write("= バス路線毎のマッピング状況 =");\r
 417                 ww.newLine();\r
 418                 ww.newLine();\r
 419                 \r
 420                 String maeCorp = "";\r
 421                 boolean firstCorp = true;\r
 422                 \r
 423                 PreparedStatement ps7 = con.prepareStatement("SELECT code,type,corp,course,ifile FROM bus_course WHERE ifile=? ORDER BY type,corp,course");\r
 424                 PreparedStatement ps9 = con.prepareStatement("SELECT idref FROM bus_ref WHERE code=?");\r
 425                 PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed FROM bus_stop WHERE idref=?");\r
 426                 ps7.setString(1, iCode);\r
 427                 ResultSet rset7 = ps7.executeQuery();\r
 428                 while (rset7.next()) {\r
 429                         int code = rset7.getInt(1);\r
 430                         int type = rset7.getInt(2);\r
 431                         String corp = rset7.getString(3);\r
 432                         String course = rset7.getString(4);\r
 433                         \r
 434 \r
 435                         File osmFile = new File(dir, iCode + String.format("_%1$08d", code) +".osm");\r
 436                         File gpxFile = new File(dir, iCode + String.format("_%1$08d", code) +".gpx");\r
 437                         File osmSample = new File(dir, iCode + String.format("_s%1$08d", code) +".osm");\r
 438 \r
 439                         System.out.println("course = "+ course);\r
 440                         int stopCount = 0;\r
 441                         int fixedCount = 0;\r
 442                         int unfixedCount = 0;\r
 443 \r
 444                         // index file header\r
 445                         hw.write("<tr>");\r
 446                         hw.write("<td>"+ type +"</td>");\r
 447                         hw.write("<td>"+ corp +"</td>");\r
 448                         hw.write("<td><a href='"+ dir.getName() +"/"+ osmFile.getName() +"'>"+ course +"</a></td>");\r
 449                         hw.write("<td><a href='"+ dir.getName() +"/"+ gpxFile.getName() +"'>"+ gpxFile.getName() +"</a></td>");\r
 450                         hw.write("<td><a href='"+ dir.getName() +"/"+ osmSample.getName() +"'>"+ osmSample.getName() +"</a></td>");\r
 451                         hw.newLine();\r
 452 \r
 453                         //--------------------------------------------\r
 454                         //      Wiki見出し２: 運行会社\r
 455                         //------------\r
 456                         if (!maeCorp.equals(corp)) {\r
 457                                 if (firstCorp == false) {\r
 458                                         ww.write("|}");\r
 459                                         ww.newLine();\r
 460                                         ww.newLine();\r
 461                                 }\r
 462                                 firstCorp = false;\r
 463                                 \r
 464                                 ww.write("=== "+ corp +" ===");\r
 465                                 ww.newLine();\r
 466                                 ww.newLine();\r
 467 \r
 468                                 ww.write(":{{JA:Tag|network||"+ corp +"}}");\r
 469                                 ww.newLine();\r
 470                                 ww.write(":{{JA:Tag|operator||"+ corp +"}}");\r
 471                                 ww.newLine();\r
 472                                 ww.newLine();\r
 473 \r
 474                                 ww.write("{| class=\"wikitable sortable\" style=\"table-layout: fixed; width: 100%\"");\r
 475                                 ww.newLine();\r
 476                                 ww.write("!style=\"width: 100px\"| ref");\r
 477                                 ww.newLine();\r
 478                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(マスタ)");\r
 479                                 ww.newLine();\r
 480                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(往路)");\r
 481                                 ww.newLine();\r
 482                                 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(復路)");\r
 483                                 ww.newLine();\r
 484                                 ww.write("!class=\"unsortable\"| 備考");\r
 485                                 ww.newLine();\r
 486 \r
 487                                 maeCorp = new String(corp);\r
 488                         }\r
 489                         \r
 490                         // Wiki\r
 491                         ww.write("|-");\r
 492                         ww.newLine();\r
 493                         ww.write("| "+ course +" ");            // ref\r
 494                         ww.write("|| {{State Route|r=0}} {{relation|0|tools=no}} ");                    // 編集状況 (マスタ)\r
 495                         ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} ");                // 編集状況 (往路)\r
 496                         ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} ");                // 編集状況 (復路)\r
 497                         ww.write("|| ");                // 備考\r
 498                         ww.newLine();\r
 499 \r
 500 \r
 501                         // OSM file header\r
 502                         ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));\r
 503                         ow.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");\r
 504                         ow.newLine();\r
 505                         ow.write("<osm version=\"0.6\" generator=\"ConvBusstop\">");\r
 506                         ow.newLine();\r
 507 \r
 508                         // GPX file header\r
 509                         gw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(gpxFile), "UTF-8"));\r
 510                         gw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");\r
 511                         gw.newLine();\r
 512                         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 \">");\r
 513                         gw.newLine();\r
 514 \r
 515                         double maxLat = -180.0;\r
 516                         double minLat = 180.0;\r
 517                         double maxLon = -180.0;\r
 518                         double minLon = 180.0;\r
 519 \r
 520                         ps9.setInt(1, code);\r
 521                         ResultSet rset9 = ps9.executeQuery();\r
 522                         while (rset9.next()) {\r
 523                                 String idref = rset9.getString(1);\r
 524 \r
 525                                 ps8.setString(1, idref);\r
 526                                 ResultSet rset8 = ps8.executeQuery();\r
 527                                 if (rset8.next()) {\r
 528                                         stopCount++;\r
 529                                         String name = rset8.getString(1);\r
 530                                         Double lat = rset8.getDouble(2);\r
 531                                         Double lon = rset8.getDouble(3);\r
 532                                         int fixed = rset8.getInt(4);\r
 533 \r
 534                                         if (lat > maxLat) {\r
 535                                                 maxLat = lat;\r
 536                                         }\r
 537                                         if (lon > maxLon) {\r
 538                                                 maxLon = lon;\r
 539                                         }\r
 540                                         if (lat < minLat) {\r
 541                                                 minLat = lat;\r
 542                                         }\r
 543                                         if (lon < minLon) {\r
 544                                                 minLon = lon;\r
 545                                         }\r
 546                                         \r
 547                                         System.out.println("\tway point = "+ idref +", lat="+ lat +", lon="+ lon +", name="+ name);\r
 548                                         fixedCount += fixed;\r
 549                                         if (fixed == 0) {\r
 550                                                 unfixedCount++;\r
 551 \r
 552                                                 // OSM node\r
 553                                                 int nodeid = Integer.parseInt(idref.substring(1)) * -1;\r
 554                                                 String osm_node = nodeBusstop(nodeid, name, lat, lon, timeStampStr);\r
 555                                                 ow.write(osm_node);\r
 556                                                 ow.newLine();\r
 557 \r
 558                                                 // TEXT node\r
 559                                                 //File txtFile = new File(dir, iCode + idref +".txt");\r
 560                                                 //BufferedWriter gw2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(txtFile), "UTF-8"));\r
 561                                                 //gw2.write(osm_node);\r
 562                                                 //gw2.newLine();\r
 563                                                 //gw2.close();\r
 564 \r
 565                                                 // GPX waypoint\r
 566                                                 gw.write("<wpt lat=\""+ lat +"\" lon=\""+ lon +"\">\n");\r
 567                                                 gw.write(" <time>"+ timeStampStr +"Z</time>\n");\r
 568                                                 gw.write(" <name><![CDATA["+ name +"]]></name>\n");\r
 569                                                 //gw.write(" <link href=\""+ txtFile.getName() +"\"><text>"+ idref +"</text></link>\n");\r
 570                                                 gw.write("</wpt>\n");\r
 571                                                 gw.newLine();\r
 572                                         }\r
 573                                 }\r
 574                                 rset8.close();\r
 575                         }\r
 576                         rset9.close();\r
 577 \r
 578                         // INDEX file\r
 579                         hw.write("<td>"+ stopCount +"</td>");\r
 580                         hw.write("<td>"+ unfixedCount +"</td>");\r
 581                         hw.write("<td>"+ fixedCount +"</td>");\r
 582                         hw.write("</tr>");\r
 583                         hw.newLine();\r
 584 \r
 585                         // OSM file header\r
 586                         BufferedWriter ow2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmSample), "UTF-8"));\r
 587                         ow2.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");\r
 588                         ow2.newLine();\r
 589                         ow2.write("<osm version=\"0.5\" generator=\"ConvBusstop\">");\r
 590                         ow2.newLine();\r
 591 \r
 592                         ow2.write(nodeBusstop(code, "SAMPLE", ((maxLat+minLat)/2), ((maxLon+minLon)/2), timeStampStr));\r
 593                         ow2.newLine();\r
 594                         ow2.write("</osm>");\r
 595                         ow2.newLine();\r
 596                         ow2.close();\r
 597 \r
 598                         // OSM file footer\r
 599                         ow.write("</osm>");\r
 600                         ow.newLine();\r
 601                         ow.close();\r
 602 \r
 603                         // GPX file footer\r
 604                         gw.write("</gpx>");\r
 605                         gw.newLine();\r
 606                         gw.close();\r
 607                 }\r
 608                 rset7.close();\r
 609 \r
 610                 // Wiki footer\r
 611                 ww.close();\r
 612 \r
 613                 // index file footer\r
 614                 hw.write("</table></body></html>");\r
 615                 hw.newLine();\r
 616                 hw.close();\r
 617         }\r
 618 \r
 619         public static int outputDb(Connection con, File dir) throws IOException, SQLException, SAXException, ParserConfigurationException, TransformerException {\r
 620                 File osmFile = new File(dir, "busstop.osm");\r
 621                 int counter = 0;\r
 622 \r
 623                 SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");\r
 624                 String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));\r
 625                 BufferedWriter ow = null;\r
 626                 \r
 627                 // OSM file header\r
 628                 ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));\r
 629                 ow.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");\r
 630                 ow.newLine();\r
 631                 ow.write("<osm version=\"0.6\" generator=\"ConvBusstop\">");\r
 632                 ow.newLine();\r
 633                 \r
 634                 PreparedStatement ps8 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop");\r
 635                 ResultSet rset8 = ps8.executeQuery();\r
 636                 while (rset8.next()) {\r
 637                         String idref = rset8.getString("idref");\r
 638                         String name = rset8.getString("name");\r
 639                         Double lat = rset8.getDouble("lat");\r
 640                         Double lon = rset8.getDouble("lon");\r
 641                         int fixed = rset8.getInt("fixed");\r
 642                         \r
 643                         if (ConvBusstop.nocheck || (fixed < 1)) {\r
 644                                 // OSM node\r
 645                                 counter++;\r
 646                                 int nodeid = Integer.parseInt(idref.substring(1)) * -1;\r
 647                                 String osm_node = nodeBusstop(nodeid, name, lat, lon, timeStampStr);\r
 648                                 System.out.println(osm_node);\r
 649                                 ow.write(osm_node);\r
 650                                 ow.newLine();\r
 651                         }\r
 652                 }\r
 653                 rset8.close();\r
 654 \r
 655                 // OSM file footer\r
 656                 ow.write("</osm>");\r
 657                 ow.newLine();\r
 658                 ow.close();\r
 659                 return counter;\r
 660         }\r
 661         \r
 662         public static Element osmTemplateNode = null;\r
 663         \r
 664         public static String nodeBusstop(int code, String name, Double lat, Double lon, String timeStampStr) throws SAXException, IOException, ParserConfigurationException, TransformerException {\r
 665                 DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();\r
 666                 factory.setIgnoringElementContentWhitespace(true);\r
 667                 factory.setIgnoringComments(true);\r
 668                 factory.setValidating(true);\r
 669 \r
 670                 if (osmTemplateNode == null) {\r
 671                         Document root = factory.newDocumentBuilder().parse(new File("osmTag.xml"));\r
 672                         osmTemplateNode = root.getDocumentElement();\r
 673                 }\r
 674                 \r
 675         Document document = factory.newDocumentBuilder().newDocument();          // Documentの生成\r
 676         Element node = document.createElement("node");\r
 677         document.appendChild(node);\r
 678         \r
 679         node.setAttribute("id", String.valueOf(code));\r
 680         node.setAttribute("timestamp", timeStampStr + "Z");\r
 681         node.setAttribute("lat", String.valueOf(lat));\r
 682         node.setAttribute("lon", String.valueOf(lon));\r
 683         \r
 684         \r
 685                 NodeList nodes = osmTemplateNode.getChildNodes();\r
 686                 for (int i = 0; i < nodes.getLength(); i++) {\r
 687                         Node node2 = nodes.item(i);\r
 688                         \r
 689                         if (node2.getNodeName().equals("tag")) {\r
 690                                 NamedNodeMap nodeMap2 = node2.getAttributes();\r
 691                                 if (null != nodeMap2) {\r
 692                                         String key = null;\r
 693                                         String value = null;\r
 694                                         for (int j=0; j < nodeMap2.getLength(); j++) {\r
 695                                                 if (nodeMap2.item(j).getNodeName().equals("k")) {\r
 696                                                         key = nodeMap2.item(j).getNodeValue();\r
 697                                                 }\r
 698                                                 else if (nodeMap2.item(j).getNodeName().equals("v")) {\r
 699                                                         value = nodeMap2.item(j).getNodeValue();\r
 700                                                 }\r
 701                                         }\r
 702                                         if ((key != null) && (value != null)) {\r
 703                                         Element tag = document.createElement("tag");\r
 704                                         tag.setAttribute("k", key);\r
 705                                         tag.setAttribute("v", value);\r
 706                                         node.appendChild(tag);\r
 707                                         }\r
 708                                 }\r
 709                         }\r
 710                         else if (node2.getNodeName().equals("ksj")) {\r
 711                                 NamedNodeMap nodeMap2 = node2.getAttributes();\r
 712                                 if (null != nodeMap2) {\r
 713                                         for (int j=0; j < nodeMap2.getLength(); j++) {\r
 714                                                 if (nodeMap2.item(j).getNodeName().equals("busStopName")) {\r
 715                                                         Element tagName = document.createElement("tag");\r
 716                                                 tagName.setAttribute("k", nodeMap2.item(j).getNodeValue());\r
 717                                                 tagName.setAttribute("v", name);\r
 718                                                 node.appendChild(tagName);\r
 719                                                 }\r
 720                                                 else if (nodeMap2.item(j).getNodeName().equals("busOperationCompany")) {\r
 721                                                 }\r
 722                                                 else if (nodeMap2.item(j).getNodeName().equals("busLineName")) {\r
 723                                                 }\r
 724                                         }\r
 725                                 }\r
 726                         }\r
 727                 }\r
 728 \r
 729                 StringWriter sw = new StringWriter();\r
 730                 TransformerFactory tfactory = TransformerFactory.newInstance(); \r
 731                 Transformer transformer = tfactory.newTransformer(); \r
 732                 transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");\r
 733                 transformer.transform(new DOMSource(node), new StreamResult(sw)); \r
 734                 return sw.toString();\r
 735         }\r
 736 \r
 737         /**\r
 738          *\r
 739          * @param con\r
 740          * @param node\r
 741          * @param iFileName             // ソースファイル名（拡張子を含まない）\r
 742          * @param timeStampStr\r
 743          * @return\r
 744          * @throws IOException\r
 745          * @throws SQLException\r
 746          */\r
 747         public static int showNodes(Connection con, Node node, String iFileName, String timeStampStr) throws IOException, SQLException {\r
 748                 int iCounter = 0;\r
 749                 NamedNodeMap nodeMap = node.getAttributes();\r
 750                 if ( null != nodeMap ) {\r
 751                         for ( int j=0; j < nodeMap.getLength(); j++ ) {\r
 752                                 if (nodeMap.item(j).getNodeName().equals("timeStamp")) {\r
 753                                         timeStampStr = nodeMap.item(j).getNodeValue();\r
 754                                 }\r
 755                         }\r
 756                 }\r
 757 \r
 758                 NodeList nodes = node.getChildNodes();\r
 759                 for (int i=0; i<nodes.getLength(); i++) {\r
 760                         Node node2 = nodes.item(i);\r
 761                         if (node2.getNodeName().equals("jps:GM_Point")) {\r
 762                                 showGmPoint(con, node2);\r
 763                         }\r
 764                         else if (node2.getNodeName().equals("gml:Point")) {\r
 765                                 showGmlPoint(con, node2);\r
 766                         }\r
 767 \r
 768                         else if (node2.getNodeName().equals("ksj:ED01")) {\r
 769                                 iCounter++;\r
 770                                 showED01(con, node2, iFileName);\r
 771                         }\r
 772                         else if (node2.getNodeName().equals("ksj:BusStop")) {\r
 773                                 iCounter++;\r
 774                                 showBusStop(con, node2, iFileName);\r
 775                         }\r
 776 \r
 777                         else {\r
 778                                 iCounter += showNodes(con, node2, iFileName, timeStampStr);\r
 779                         }\r
 780                 }\r
 781                 return iCounter;\r
 782         }\r
 783 \r
 784         /**\r
 785          *\r
 786          * @param con\r
 787          * @param node\r
 788          * @param iFileName             // ソースファイル名（拡張子を含まない）\r
 789          * @throws IOException\r
 790          * @throws SQLException\r
 791          */\r
 792         public static void showED01(Connection con, Node node, String iFileName) throws IOException, SQLException {\r
 793                 String idrefStr = "";\r
 794                 String nameStr = "";\r
 795                 PreparedStatement ps1 = con.prepareStatement("SELECT idref FROM bus_stop WHERE idref=?");\r
 796                 PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (idref,name,ifile) VALUES (?,?,?)");\r
 797                 PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");\r
 798                 PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");\r
 799                 PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");\r
 800                 PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");\r
 801 \r
 802                 ArrayList<String[]> bris = new ArrayList<String[]>();\r
 803                 NodeList nodes = node.getChildNodes();\r
 804                 for (int i=0; i < nodes.getLength(); i++) {\r
 805                         Node node2 = nodes.item(i);\r
 806                         if (node2.getNodeName().equals("ksj:POS")) {\r
 807                                 NamedNodeMap nodeMap = node2.getAttributes();\r
 808                                 if (null != nodeMap) {\r
 809                                         for ( int j=0; j < nodeMap.getLength(); j++ ) {\r
 810                                                 if (nodeMap.item(j).getNodeName().equals("idref")) {\r
 811                                                         idrefStr = nodeMap.item(j).getNodeValue();\r
 812                                                         System.out.println("found idref='"+ idrefStr +"'");\r
 813                                                         break;\r
 814                                                 }\r
 815                                         }\r
 816                                 }\r
 817                         }\r
 818                         else if (node2.getNodeName().equals("ksj:BSN")) {\r
 819                                 nameStr = node2.getTextContent();\r
 820                         }\r
 821                         else if (node2.getNodeName().equals("ksj:BRI")) {\r
 822                                 String[] rtn = anaComm(node2);\r
 823                                 if (rtn != null) {\r
 824                                         bris.add(rtn);\r
 825                                 }\r
 826                         }\r
 827                 }\r
 828 \r
 829                 // idref と nameStr をデータベースに格納する\r
 830                 boolean insert = true;\r
 831                 ps1.setString(1, idrefStr);\r
 832                 ResultSet rset = ps1.executeQuery();\r
 833                 if (rset.next()) {\r
 834                         insert = false;\r
 835                 }\r
 836                 rset.close();\r
 837 \r
 838                 if (insert) {\r
 839                         ps2.setString(1, idrefStr);\r
 840                         ps2.setString(2, nameStr);\r
 841                         ps2.setString(3, iFileName);\r
 842                         System.out.println("INSERT INTO bus_stop (idref,name,ifile) VALUES ('"+ idrefStr +"','"+ nameStr +"','"+ iFileName +"')");\r
 843                         ps2.executeUpdate();\r
 844                 }\r
 845 \r
 846                 for (String[] rtn : bris) {\r
 847                         int code = 0;\r
 848                         ps3.setString(1, rtn[1]);\r
 849                         ps3.setString(2, rtn[2]);\r
 850                         ps3.setString(3, iFileName);\r
 851                         rset = ps3.executeQuery();\r
 852                         if (rset.next()) {\r
 853                                 code = rset.getInt(1);\r
 854                         }\r
 855                         rset.close();\r
 856 \r
 857                         if (code == 0) {\r
 858                                 ps6.setString(1, iFileName);\r
 859                                 ResultSet rset6 = ps6.executeQuery();\r
 860                                 if (rset6.next()) {\r
 861                                         code = rset6.getInt(1);\r
 862                                 }\r
 863                                 rset6.close();\r
 864                                 code++;\r
 865 \r
 866                                 System.out.println("code="+code);\r
 867                                 ps4.setInt(1, code);\r
 868                                 ps4.setInt(2, Integer.parseInt(rtn[0]));\r
 869                                 ps4.setString(3, rtn[2]);\r
 870                                 ps4.setString(4, rtn[1]);\r
 871                                 ps4.setString(5, iFileName);\r
 872                                 ps4.executeUpdate();\r
 873                         }\r
 874 \r
 875                         ps5.setString(1, idrefStr);\r
 876                         ps5.setInt(2, code);\r
 877                         ps5.executeUpdate();\r
 878                 }\r
 879 \r
 880                 ps1.close();\r
 881                 ps2.close();\r
 882                 ps3.close();\r
 883                 ps4.close();\r
 884                 ps5.close();\r
 885         }\r
 886 \r
 887         /**\r
 888          * <ksj:BusStop gml:id="ED01_1">\r
 889          *      <ksj:position xlink:href="#n1"/>\r
 890          *      <ksj:busStopName>城堀</ksj:busStopName>\r
 891          *      <ksj:busRouteInformation>\r
 892          *              <ksj:BusRouteInformation>\r
 893          *                      <ksj:busType>1</ksj:busType>\r
 894          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>\r
 895          *                      <ksj:busLineName>小01</ksj:busLineName>\r
 896          *              </ksj:BusRouteInformation>\r
 897          *      </ksj:busRouteInformation>\r
 898          *      <ksj:busRouteInformation>\r
 899          *              <ksj:BusRouteInformation>\r
 900          *                      <ksj:busType>1</ksj:busType>\r
 901          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>\r
 902          *                      <ksj:busLineName>湯07</ksj:busLineName>\r
 903          *              </ksj:BusRouteInformation>\r
 904          *      </ksj:busRouteInformation>\r
 905          *      <ksj:busRouteInformation>\r
 906          *              <ksj:BusRouteInformation>\r
 907          *                      <ksj:busType>1</ksj:busType>\r
 908          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>\r
 909          *                      <ksj:busLineName>湯11</ksj:busLineName>\r
 910          *              </ksj:BusRouteInformation>\r
 911          *      </ksj:busRouteInformation>\r
 912          * </ksj:BusStop>\r
 913          *\r
 914          * @param con\r
 915          * @param node\r
 916          * @param iFileName             // ソースファイル名（拡張子を含まない）\r
 917          * @throws IOException\r
 918          * @throws SQLException\r
 919          */\r
 920         public static void showBusStop(Connection con, Node node, String iFileName) throws IOException, SQLException {\r
 921                 String idrefStr = "";\r
 922                 String nameStr = "";\r
 923                 PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE idref=?");\r
 924                 PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");\r
 925                 PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");\r
 926                 PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");\r
 927                 PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");\r
 928 \r
 929                 ArrayList<String[]> bris = new ArrayList<String[]>();\r
 930                 NodeList nodes = node.getChildNodes();\r
 931                 for (int i=0; i < nodes.getLength(); i++) {\r
 932                         Node node2 = nodes.item(i);\r
 933                         if (node2.getNodeName().equals("ksj:position")) {\r
 934                                 NamedNodeMap nodeMap = node2.getAttributes();\r
 935                                 if (null != nodeMap) {\r
 936                                         for ( int j=0; j < nodeMap.getLength(); j++ ) {\r
 937                                                 if (nodeMap.item(j).getNodeName().equals("xlink:href")) {\r
 938                                                         idrefStr = nodeMap.item(j).getNodeValue();\r
 939                                                         idrefStr = idrefStr.substring(1);\r
 940                                                         System.out.println("found idref='"+ idrefStr +"'");\r
 941                                                         break;\r
 942                                                 }\r
 943                                         }\r
 944                                 }\r
 945                         }\r
 946                         else if (node2.getNodeName().equals("ksj:busStopName")) {\r
 947                                 nameStr = node2.getTextContent();\r
 948                         }\r
 949                         else if (node2.getNodeName().equals("ksj:busRouteInformation")) {\r
 950                                 String[] rtn = anaCommJGD(node2);\r
 951                                 if (rtn != null) {\r
 952                                         bris.add(rtn);\r
 953                                 }\r
 954                         }\r
 955                 }\r
 956 \r
 957                 // idref と nameStr をデータベースに格納する\r
 958                 ps2.setString(1, nameStr);\r
 959                 ps2.setString(2, iFileName);\r
 960                 ps2.setString(3, idrefStr);\r
 961                 ps2.executeUpdate();\r
 962 \r
 963                 for (String[] rtn : bris) {\r
 964                         int code = 0;\r
 965                         ps3.setString(1, rtn[1]);\r
 966                         ps3.setString(2, rtn[2]);\r
 967                         ps3.setString(3, iFileName);\r
 968                         ResultSet rset = ps3.executeQuery();\r
 969                         if (rset.next()) {\r
 970                                 code = rset.getInt(1);\r
 971                         }\r
 972                         rset.close();\r
 973 \r
 974                         if (code == 0) {\r
 975                                 ps6.setString(1, iFileName);\r
 976                                 ResultSet rset6 = ps6.executeQuery();\r
 977                                 if (rset6.next()) {\r
 978                                         code = rset6.getInt(1);\r
 979                                 }\r
 980                                 rset6.close();\r
 981                                 code++;\r
 982 \r
 983                                 System.out.println("course="+ code +" : "+ rtn[0] +" : "+ rtn[1] +" : "+ rtn[2] );\r
 984                                 ps4.setInt(1, code);\r
 985                                 ps4.setInt(2, Integer.parseInt(rtn[0]));\r
 986                                 ps4.setString(3, rtn[2]);\r
 987                                 ps4.setString(4, rtn[1]);\r
 988                                 ps4.setString(5, iFileName);\r
 989                                 ps4.executeUpdate();\r
 990                         }\r
 991 \r
 992                         ps5.setString(1, idrefStr);\r
 993                         ps5.setInt(2, code);\r
 994                         ps5.executeUpdate();\r
 995                 }\r
 996 \r
 997                 ps2.close();\r
 998                 ps3.close();\r
 999                 ps4.close();\r
1000                 ps5.close();\r
1001         }\r
1002 \r
1003         public static String[] anaComm(Node briNode) {\r
1004                 String[] rtn = new String[3];\r
1005                 rtn[0] = "";    // corp type\r
1006                 rtn[1] = "";    // course name\r
1007                 rtn[2] = "";    // corp name\r
1008 \r
1009                 NodeList nodes = briNode.getChildNodes();\r
1010                 for (int i=0; i < nodes.getLength(); i++) {\r
1011                         Node node2 = nodes.item(i);\r
1012                         if (node2.getNodeName().equals("ksj:BSC")) {\r
1013                                 rtn[0] = node2.getTextContent();\r
1014                         }\r
1015                         else if (node2.getNodeName().equals("ksj:BLN")) {\r
1016                                 rtn[1] = node2.getTextContent();\r
1017                         }\r
1018                         else if (node2.getNodeName().equals("ksj:BOC")) {\r
1019                                 rtn[2] = node2.getTextContent();\r
1020                         }\r
1021                 }\r
1022                 return rtn;\r
1023         }\r
1024 \r
1025         /**\r
1026          *\r
1027          *      <ksj:busRouteInformation>\r
1028          *              <ksj:BusRouteInformation>\r
1029          *                      <ksj:busType>1</ksj:busType>\r
1030          *                      <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>\r
1031          *                      <ksj:busLineName>小01</ksj:busLineName>\r
1032          *              </ksj:BusRouteInformation>\r
1033          *      </ksj:busRouteInformation>\r
1034          *\r
1035          * @param briNode\r
1036          * @return\r
1037          */\r
1038         public static String[] anaCommJGD(Node briNode) {\r
1039                 String[] rtn = new String[3];\r
1040                 int vcnt = 0;\r
1041 \r
1042                 NodeList nodes2 = briNode.getChildNodes();\r
1043                 for (int i=0; i < nodes2.getLength(); i++) {\r
1044                         Node node2 = nodes2.item(i);\r
1045                         if (node2.getNodeName().equals("ksj:BusRouteInformation")) {\r
1046                                 NodeList nodes3 = node2.getChildNodes();\r
1047                                 for (int j=0; j < nodes3.getLength(); j++) {\r
1048                                         Node node3 = nodes3.item(j);\r
1049                                         if (node3.getNodeName().equals("ksj:busType")) {\r
1050                                                 rtn[0] = new String(node3.getTextContent());\r
1051                                                 vcnt++;\r
1052                                         }\r
1053                                         else if (node3.getNodeName().equals("ksj:busLineName")) {\r
1054                                                 rtn[1] = new String(node3.getTextContent());\r
1055                                                 vcnt++;\r
1056                                         }\r
1057                                         else if (node3.getNodeName().equals("ksj:busOperationCompany")) {\r
1058                                                 rtn[2] = new String(node3.getTextContent());\r
1059                                                 vcnt++;\r
1060                                         }\r
1061                                 }\r
1062                         }\r
1063                 }\r
1064 \r
1065                 if (vcnt > 0) {\r
1066                         return rtn;\r
1067                 }\r
1068                 return null;\r
1069         }\r
1070 \r
1071         public static void showGmPoint(Connection con, Node node) throws IOException, SQLException {\r
1072                 String positionStr = "";\r
1073                 String latStr = "";\r
1074                 String lonStr = "";\r
1075                 String idStr = "";\r
1076 \r
1077                 NamedNodeMap nodeMap = node.getAttributes();\r
1078                 if ( null != nodeMap ) {\r
1079                         for ( int j=0; j<nodeMap.getLength(); j++ ) {\r
1080                                 if (nodeMap.item(j).getNodeName().equals("id")) {\r
1081                                         idStr = nodeMap.item(j).getNodeValue();\r
1082                                 }\r
1083                         }\r
1084                 }\r
1085 \r
1086                 NodeList nodes = node.getChildNodes();\r
1087                 for (int i=0; i < nodes.getLength(); i++) {\r
1088                         Node node2 = nodes.item(i);\r
1089                         if (node2.getNodeName().equals("jps:GM_Point.position")) {\r
1090                                 NodeList nodes3 = node2.getChildNodes();\r
1091                                 for (int j=0; j < nodes3.getLength(); j++) {\r
1092                                         Node node3 = nodes3.item(j);\r
1093                                         if (node3.getNodeName().equals("jps:DirectPosition")) {\r
1094                                                 NodeList nodes4 = node3.getChildNodes();\r
1095                                                 for (int k=0; k < nodes4.getLength(); k++) {\r
1096                                                         Node node4 = nodes4.item(k);\r
1097                                                         if (node4.getNodeName().equals("DirectPosition.coordinate")) {\r
1098                                                                 positionStr = node4.getTextContent();\r
1099                                                                 String[] str4Ary = positionStr.split(" ");\r
1100                                                                 latStr = str4Ary[0];\r
1101                                                                 lonStr = str4Ary[1];\r
1102                                                                 break;\r
1103                                                         }\r
1104                                                 }\r
1105                                                 break;\r
1106                                         }\r
1107                                 }\r
1108 \r
1109                                 PreparedStatement ps6 = con.prepareStatement("UPDATE bus_stop SET lat=?,lon=?,fixed=? WHERE idref=?");\r
1110                                 double lat = Double.parseDouble(latStr);\r
1111                                 double lon = Double.parseDouble(lonStr);\r
1112                                 ps6.setDouble(1, lat);\r
1113                                 ps6.setDouble(2, lon);\r
1114                                 // ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));\r
1115                                 ps6.setInt(3, 0);\r
1116                                 ps6.setString(4, idStr);\r
1117                                 System.out.println("UPDATE bus_stop("+ idStr +") lat="+ lat +", lon="+ lon +", fixed=0");\r
1118                                 ps6.executeUpdate();\r
1119                                 ps6.close();\r
1120                         }\r
1121                 }\r
1122         }\r
1123 \r
1124         /**\r
1125          * <gml:Point gml:id="n1">\r
1126          *      <gml:pos>35.14591397 139.10569573</gml:pos>\r
1127          * </gml:Point>\r
1128          *\r
1129          * @param con\r
1130          * @param node\r
1131          * @throws IOException\r
1132          * @throws SQLException\r
1133          */\r
1134         public static void showGmlPoint(Connection con, Node node) throws IOException, SQLException {\r
1135                 String positionStr = "";\r
1136                 String latStr = "";\r
1137                 String lonStr = "";\r
1138                 String idStr = "";\r
1139 \r
1140                 NamedNodeMap nodeMap = node.getAttributes();\r
1141                 if ( null != nodeMap ) {\r
1142                         for ( int j=0; j<nodeMap.getLength(); j++ ) {\r
1143                                 if (nodeMap.item(j).getNodeName().equals("gml:id")) {\r
1144                                         idStr = nodeMap.item(j).getNodeValue();\r
1145                                 }\r
1146                         }\r
1147                 }\r
1148 \r
1149                 NodeList nodes = node.getChildNodes();\r
1150                 for (int i=0; i < nodes.getLength(); i++) {\r
1151                         Node node2 = nodes.item(i);\r
1152                         if (node2.getNodeName().equals("gml:pos")) {\r
1153                                 positionStr = node2.getTextContent().trim();\r
1154                                 String[] str4Ary = positionStr.split(" ");\r
1155                                 latStr = str4Ary[0];\r
1156                                 lonStr = str4Ary[1];\r
1157                                 \r
1158                                 PreparedStatement ps6 = con.prepareStatement("INSERT INTO bus_stop (lat,lon,fixed,idref) VALUES (?,?,?,?)");\r
1159                                 double lat = Double.parseDouble(latStr);\r
1160                                 double lon = Double.parseDouble(lonStr);\r
1161                                 System.out.println("INSERT INTO bus_stop (lat,lon,fixed,idref) VALUES ('"+ latStr +"','"+ lonStr +"','0','"+ idStr +"')");\r
1162 \r
1163                                 ps6.setDouble(1, lat);\r
1164                                 ps6.setDouble(2, lon);\r
1165                                 //ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));\r
1166                                 ps6.setInt(3, 0);\r
1167                                 ps6.setString(4, idStr);\r
1168                                 ps6.executeUpdate();\r
1169                                 ps6.close();\r
1170                         }\r
1171                 }\r
1172         }\r
1173 \r
1174         static boolean checkFile(File f) {\r
1175                 String name = f.getName();\r
1176                 if (!name.startsWith("P11-")) {\r
1177                         return false;\r
1178                 }\r
1179                 if (!name.toUpperCase().endsWith(".XML")) {\r
1180                         return false;\r
1181                 }\r
1182                 return true;\r
1183         }\r
1184 }