src/osm/jp/ConvBusroute.java

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