src/osm/jp/ConvBusroute.java

   1 package osm.jp;\r
   2 import hayashi.yuu.tools.logger.LoggerFactory;\r
   3 \r
   4 import javax.xml.parsers.*;\r
   5 import javax.xml.transform.TransformerException;\r
   6 \r
   7 import org.w3c.dom.*;\r
   8 import org.xml.sax.*;\r
   9 \r
  10 import java.io.*;\r
  11 import java.sql.Connection;\r
  12 import java.sql.PreparedStatement;\r
  13 import java.sql.ResultSet;\r
  14 import java.sql.SQLException;\r
  15 import java.sql.Statement;\r
  16 import java.util.logging.Logger;\r
  17 \r
  18 import jp.co.areaweb.tools.database.*;\r
  19 \r
  20 public class ConvBusroute {\r
  21 \r
  22         String filter = "";\r
  23         String urlStr = "";\r
  24 \r
  25         public static final boolean DB_INIT = false;\r
  26 \r
  27         // 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域\r
  28         static final int NEER = 75;\r
  29         static boolean nocheck = true;\r
  30         static Logger logger = LoggerFactory.getInstance();\r
  31 \r
  32         /**\r
  33          * メイン\r
  34          *\r
  35          *      java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusroute\r
  36          *\r
  37          * @throws IOException\r
  38          * @throws SQLException\r
  39          * @throws ClassNotFoundException\r
  40          * @throws FileNotFoundException\r
  41          * @throws TransformerException\r
  42          * @throws SAXException\r
  43          * @throws ParserConfigurationException */\r
  44         public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException\r
  45         {\r
  46                 /**\r
  47                  * アプリケーション [ConvBusroute]\r
  48                  * > java -jar ConvBusstop.jar <オプション>\r
  49                  *      オプション： -exp 実行する直前にデータベースを初期化する（省略可能）\r
  50                  */\r
  51                 File dbdir = new File("database");\r
  52                 if (!dbdir.isDirectory()) {\r
  53                         dbdir.mkdir();\r
  54                 }\r
  55 \r
  56                 Connection con = DatabaseTool.openDb("database");\r
  57                 ConvBusroute.initDb(con);\r
  58 \r
  59                 try {\r
  60                         /**\r
  61                          * バスルートデータ変換のメイン処理\r
  62                          */\r
  63                         int fcounter = 0;\r
  64                         File dir = new File(".");\r
  65                         File[] files = dir.listFiles();\r
  66                         for (File iFile : files) {\r
  67                                 if (checkFile(iFile)) {\r
  68                                         fcounter++;\r
  69                                         ConvBusroute.clearDb(con);\r
  70                                         inputFile(con, iFile);\r
  71 \r
  72                                         // ローカルデータベース内の情報を出力する\r
  73                                         String iStr = iFile.getName();\r
  74                                         outputDb(con, iStr.substring(0, iStr.length() - 4));\r
  75                                 }\r
  76                         }\r
  77                         logger.info("["+ fcounter +"]つのファイルをインポートしました。");\r
  78                 }\r
  79                 finally {\r
  80                         DatabaseTool.closeDb(con);\r
  81                 }\r
  82         }\r
  83 \r
  84         /**\r
  85          * ソースファイルを読み取ってローカルベータベースへ記録する\r
  86          * @param con\r
  87          * @param iFile\r
  88          * @throws FileNotFoundException\r
  89          * @throws ClassNotFoundException\r
  90          * @throws SQLException\r
  91          * @throws IOException\r
  92          * @throws ParserConfigurationException \r
  93          * @throws SAXException \r
  94          */\r
  95         public static void inputFile (Connection con, File iFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {\r
  96                 int iCounter = 0;\r
  97 \r
  98                 String iStr = iFile.getName();\r
  99                 File dir = new File(iStr.substring(0, iStr.length() - 4));\r
 100                 dir.mkdir();\r
 101 \r
 102                 DocumentBuilderFactory factory;\r
 103                 DocumentBuilder        builder;\r
 104                 Node root;\r
 105 \r
 106                 iCounter = 0;\r
 107                 factory = DocumentBuilderFactory.newInstance();\r
 108                 builder = factory.newDocumentBuilder();\r
 109                 factory.setIgnoringElementContentWhitespace(true);\r
 110                 factory.setIgnoringComments(true);\r
 111                 factory.setValidating(true);\r
 112                 root    = builder.parse(iStr);\r
 113 \r
 114                 iCounter += importNodes(con, root, iStr.substring(0, iStr.length() - 4));\r
 115                 logger.info("バスルート数["+ iCounter +"]");\r
 116         }\r
 117 \r
 118         public static void clearDb(Connection con) throws SQLException {\r
 119                 Statement stmt = con.createStatement();\r
 120                 long count = stmt.executeUpdate("delete from bus_route");\r
 121             logger.info("'bus_route'から "+ count +" 件のデータを削除しました。");\r
 122             \r
 123             count = stmt.executeUpdate("delete from bus_Curve");\r
 124             logger.info("'bus_Curve'から "+ count +" 件のデータを削除しました。");\r
 125             \r
 126             stmt.close();\r
 127         }\r
 128 \r
 129         public static void initDb(Connection con) throws SQLException {\r
 130                 String createSt;\r
 131                 PreparedStatement ps;\r
 132 \r
 133                 // 'table.BUS_STOP'を新規に作る\r
 134                 DbBusstop.create(con);\r
 135 \r
 136                 // 'table.bus_route'を新規に作る\r
 137                 createSt = "CREATE TABLE bus_route (cvid VARCHAR(12), bsc int, boc VARCHAR(128) NOT NULL, bln VARCHAR(512));";\r
 138                 logger.info(createSt);\r
 139                 ps = con.prepareStatement(createSt);\r
 140                 try {\r
 141                         ps.executeUpdate();\r
 142                 }\r
 143                 catch (SQLException e) {\r
 144                         if (!(e.toString().startsWith("Table already exists:"))) {\r
 145                                 throw e;\r
 146                         }\r
 147                 }\r
 148                 finally {\r
 149                         ps.close();\r
 150                 }\r
 151 \r
 152                 // 'table.bus_Curve'を新規に作る\r
 153                 createSt = "CREATE TABLE bus_Curve (idref VARCHAR(12), seq INT, lat DOUBLE, lon DOUBLE, CONSTRAINT bus_curve_pk PRIMARY KEY(idref,seq));";\r
 154                 logger.info(createSt);\r
 155                 ps = con.prepareStatement(createSt);\r
 156                 try {\r
 157                         ps.executeUpdate();\r
 158                 }\r
 159                 catch (SQLException e) {\r
 160                         if (!(e.toString().startsWith("Table already exists:"))) {\r
 161                                 throw e;\r
 162                         }\r
 163                 }\r
 164                 finally {\r
 165                         ps.close();\r
 166                 }\r
 167         }\r
 168 \r
 169 \r
 170         /**\r
 171          * ローカルデータベース内の情報を出力する\r
 172          * @param con\r
 173          * @param iCode\r
 174          * @throws IOException\r
 175          * @throws SQLException\r
 176          */\r
 177         public static void outputDb(Connection con, String iCode) throws IOException, SQLException {\r
 178                 File dir = new File(iCode);\r
 179                 dir.mkdir();\r
 180 \r
 181                 BufferedWriter gw = null;\r
 182                 BufferedWriter hw = null;\r
 183 \r
 184                 // HTML header\r
 185                 File htmlFile = new File(iCode  +".html");\r
 186                 hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(htmlFile), "UTF-8"));\r
 187                 hw.write("<!DOCTYPE html>");\r
 188                 hw.newLine();\r
 189                 hw.write("<html><head><meta charset=\"utf-8\" /></head>");\r
 190                 hw.newLine();\r
 191                 hw.write("<body><table border='1'>");\r
 192                 hw.newLine();\r
 193                 hw.write("<tr>");\r
 194                 hw.write("<td>boc</td>");\r
 195                 hw.write("<td>bln</td>");\r
 196                 hw.write("<td>GPX</td>");\r
 197                 hw.write("</tr>");\r
 198                 hw.newLine();\r
 199                 \r
 200                 String maeid = "";\r
 201                 String maeroute = "";\r
 202                 File gpxFile = null;\r
 203 \r
 204                 System.out.println("Database request....");\r
 205                 PreparedStatement ps7 = con.prepareStatement("SELECT bus_route.bsc, bus_route.boc, bus_route.bln, bus_Curve.idref, bus_Curve.seq, bus_Curve.lat, bus_Curve.lon FROM bus_route INNER JOIN bus_Curve ON bus_route.cvid = bus_Curve.idref ORDER BY bus_route.bsc,bus_route.boc,bus_route.bln, bus_route.cvid,bus_Curve.seq");\r
 206                 ResultSet rset7 = ps7.executeQuery();\r
 207                 while (rset7.next()) {\r
 208                         int bsc = rset7.getInt(1);\r
 209                         String boc = rset7.getString(2);\r
 210                         String bln = rset7.getString(3);\r
 211                         String cvid = rset7.getString(4);\r
 212                         //int seq = rset7.getInt(5);\r
 213                         Double lat = rset7.getDouble(6);\r
 214                         Double lon = rset7.getDouble(7);\r
 215 \r
 216                         if (!maeroute.equals(boc +" "+ bln)) {\r
 217                                 // GPX file\r
 218                                 if (!maeroute.equals("")) {\r
 219                                         if (!maeid.equals(cvid)) {\r
 220                                                 // GPX <trkseg/>\r
 221                                                 if (!maeid.equals("")) {\r
 222                                                         gw.write("</trkseg>");\r
 223                                                         gw.newLine();\r
 224                                                         maeid = "";\r
 225                                                 }\r
 226                                         }\r
 227                                         \r
 228                                         // GPX file footer\r
 229                                         gw.write("</trk>");\r
 230                                         gw.write("</gpx>");\r
 231                                         gw.newLine();\r
 232                                         gw.close();\r
 233                                         System.out.println();\r
 234                                 }\r
 235                                 maeroute = new String(boc +" "+ bln);\r
 236 \r
 237                                 System.out.print("export course("+ maeroute +") ");\r
 238 \r
 239                                 gpxFile = new File(dir, iCode + cvid +".gpx");\r
 240                                 gw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(gpxFile), "UTF-8"));\r
 241                                 gw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");\r
 242                                 gw.newLine();\r
 243                                 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
 244                                 gw.newLine();\r
 245 \r
 246                                 // GPX <trk/>\r
 247                                 gw.write("<trk>");\r
 248                                 gw.write(" <name>"+ bsc +" "+ boc +" "+ bln +" "+ cvid +"</name>");\r
 249                                 gw.newLine();\r
 250 \r
 251                                 // INDEX file\r
 252                                 hw.write("<tr>");\r
 253                                 hw.write("<td>"+ boc +"</td>");\r
 254                                 hw.write("<td>"+ bln +"</td>");\r
 255                                 hw.write("<td><a href='"+ dir.getName() +"/"+ gpxFile.getName() +"'>"+ gpxFile.getName() +"</a></td>");\r
 256                                 hw.write("</tr>");\r
 257                                 hw.newLine();\r
 258                         }\r
 259                         \r
 260                         if (!maeid.equals(cvid)) {\r
 261                                 if (!maeid.equals("")) {\r
 262                                         // GPX file footer\r
 263                                         gw.write("</trkseg>");\r
 264                                         gw.newLine();\r
 265                                 }\r
 266                                 System.out.println();\r
 267 \r
 268                                 // GPX <trkseg/>\r
 269                                 gw.write("<trkseg>");\r
 270                                 gw.newLine();\r
 271                         }\r
 272                         maeid = new String(cvid);\r
 273                         \r
 274                         // GPX <trkpt/>\r
 275                         System.out.print(".");\r
 276                         gw.write("<trkpt lat=\""+ lat +"\" lon=\""+ lon +"\"></trkpt>");\r
 277                         gw.newLine();\r
 278                 }\r
 279                 rset7.close();\r
 280 \r
 281                 // GPX file footer\r
 282                 gw.write("</trkseg>");\r
 283                 gw.write("</trk>");\r
 284                 gw.write("</gpx>");\r
 285                 gw.newLine();\r
 286                 gw.close();\r
 287 \r
 288                 // index file footer\r
 289                 hw.write("</table></body></html>");\r
 290                 hw.newLine();\r
 291                 hw.close();\r
 292         }\r
 293         \r
 294         public static BufferedWriter createGPX(File gpxFile, String cvid) throws IOException {\r
 295                 BufferedWriter gw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(gpxFile), "UTF-8"));\r
 296                 gw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");\r
 297                 gw.newLine();\r
 298                 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
 299                 gw.newLine();\r
 300                 return gw;              \r
 301         }\r
 302 \r
 303 \r
 304         /**\r
 305          *\r
 306          * @param con\r
 307          * @param node\r
 308          * @param iFileName             // ソースファイル名（拡張子を含まない）\r
 309          * @return\r
 310          * @throws IOException\r
 311          * @throws SQLException\r
 312          */\r
 313         public static int importNodes(Connection con, Node node, String iFileName) throws IOException, SQLException {\r
 314                 int iCounter = 0;\r
 315 \r
 316                 NodeList nodes = node.getChildNodes();\r
 317                 for (int i=0; i<nodes.getLength(); i++) {\r
 318                         Node node2 = nodes.item(i);\r
 319                         if (node2.getNodeName().equals("gml:Curve")) {\r
 320                                 importGmlCurve(con, node2);\r
 321                         }\r
 322                         else if (node2.getNodeName().equals("ksj:BusRoute")) {\r
 323                                 iCounter++;\r
 324                                 importBusRoute(con, node2, iFileName);\r
 325                         }\r
 326                         else {\r
 327                                 iCounter += importNodes(con, node2, iFileName);\r
 328                         }\r
 329                 }\r
 330                 return iCounter;\r
 331         }\r
 332         \r
 333         public static void importGmlCurve(Connection con, Node node) throws IOException, SQLException {\r
 334                 String positionStr = "";\r
 335                 String latStr = "";\r
 336                 String lonStr = "";\r
 337                 String idStr = "";\r
 338 \r
 339                 NamedNodeMap nodeMap = node.getAttributes();\r
 340                 if ( null != nodeMap ) {\r
 341                         for ( int j=0; j<nodeMap.getLength(); j++ ) {\r
 342                                 if (nodeMap.item(j).getNodeName().equals("gml:id")) {\r
 343                                         idStr = nodeMap.item(j).getNodeValue();\r
 344                                 }\r
 345                         }\r
 346                 }\r
 347 \r
 348                 NodeList nodes = node.getChildNodes();\r
 349                 for (int i=0; i < nodes.getLength(); i++) {\r
 350                         Node node2 = nodes.item(i);\r
 351                         if (node2.getNodeName().equals("gml:segments")) {\r
 352                                 NodeList nodes3 = node2.getChildNodes();\r
 353                                 for (int j=0; j < nodes3.getLength(); j++) {\r
 354                                         Node node3 = nodes3.item(j);\r
 355                                         if (node3.getNodeName().equals("gml:LineStringSegment")) {\r
 356                                                 NodeList nodes4 = node3.getChildNodes();\r
 357                                                 for (int k=0; k < nodes4.getLength(); k++) {\r
 358                                                         Node node4 = nodes4.item(k);\r
 359                                                         if (node4.getNodeName().equals("gml:posList")) {\r
 360                                                                 BufferedReader bf = new BufferedReader(new StringReader(node4.getTextContent()));\r
 361                                                                 String line;\r
 362                                                                 int seq = 0;\r
 363                                                                 while ((line = bf.readLine()) != null) {\r
 364                                                                         seq++;\r
 365                                                                         positionStr = line.trim();\r
 366                                                                         if (!positionStr.equals("")) {\r
 367                                                                                 String[] str4Ary = positionStr.split(" ");\r
 368                                                                                 latStr = str4Ary[0];\r
 369                                                                                 lonStr = str4Ary[1];\r
 370                                                                                 double lat = Double.parseDouble(latStr);\r
 371                                                                                 double lon = Double.parseDouble(lonStr);\r
 372                                                                                 \r
 373                                                                                 System.out.println("import course("+ idStr+","+ seq +","+ lat +","+ lon +")");\r
 374 \r
 375                                                                                 PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_Curve (idref,seq,lat,lon) VALUES (?,?,?,?)");\r
 376                                                                                 ps2.setString(1, idStr);\r
 377                                                                                 ps2.setInt(2, seq);\r
 378                                                                                 ps2.setDouble(3, lat);\r
 379                                                                                 ps2.setDouble(4, lon);\r
 380                                                                                 ps2.executeUpdate();\r
 381                                                                                 ps2.close();\r
 382                                                                         }\r
 383                                                                 }\r
 384                                                         }\r
 385                                                 }\r
 386                                         }\r
 387                                 }\r
 388                         }\r
 389                 }\r
 390         }\r
 391 \r
 392         public static void importBusRoute(Connection con, Node node, String iFileName) throws IOException, SQLException {\r
 393                 String cvId = "";\r
 394                 int bsc = -1;\r
 395                 String boc = "";\r
 396                 String bln = "";\r
 397                 \r
 398                 NodeList nodes = node.getChildNodes();\r
 399                 for (int i=0; i < nodes.getLength(); i++) {\r
 400                         Node node2 = nodes.item(i);\r
 401                         if (node2.getNodeName().equals("ksj:brt")) {\r
 402                                 NamedNodeMap nodeMap = node2.getAttributes();\r
 403                                 if (null != nodeMap) {\r
 404                                         for ( int j=0; j < nodeMap.getLength(); j++ ) {\r
 405                                                 if (nodeMap.item(j).getNodeName().equals("xlink:href")) {\r
 406                                                         cvId = nodeMap.item(j).getNodeValue();\r
 407                                                         cvId = cvId.substring(1);\r
 408                                                         logger.info("found idref='"+ cvId +"'");\r
 409                                                         break;\r
 410                                                 }\r
 411                                         }\r
 412                                 }\r
 413                         }\r
 414                         else if (node2.getNodeName().equals("ksj:bsc")) {\r
 415                                 String str = node2.getTextContent();\r
 416                                 bsc = Integer.parseInt(str);\r
 417                         }\r
 418                         else if (node2.getNodeName().equals("ksj:boc")) {\r
 419                                 boc = node2.getTextContent();\r
 420                         }\r
 421                         else if (node2.getNodeName().equals("ksj:bln")) {\r
 422                                 bln = node2.getTextContent();\r
 423                         }\r
 424                 }\r
 425 \r
 426                 PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_route (cvid,bsc,boc,bln) VALUES (?,?,?,?)");\r
 427                 ps2.setString(1, cvId);\r
 428                 ps2.setInt(2, bsc);\r
 429                 ps2.setString(3, boc);\r
 430                 ps2.setString(4, bln);\r
 431                 ps2.executeUpdate();\r
 432                 ps2.close();\r
 433         }\r
 434 \r
 435 \r
 436         /**\r
 437          * exp: KANAGAWA-ken [N07-11_14.xml]\r
 438          * \r
 439          * @param f\r
 440          * @return\r
 441          */\r
 442         static boolean checkFile(File f) {\r
 443                 String name = f.getName();\r
 444                 if (!name.toUpperCase().startsWith("N07-")) {\r
 445                         return false;\r
 446                 }\r
 447                 if (!name.toLowerCase().endsWith(".xml")) {\r
 448                         return false;\r
 449                 }\r
 450                 return true;\r
 451         }\r
 452 }