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