2 import osm.jp.api.HttpPOST;
\r
4 import javax.xml.parsers.*;
\r
5 import javax.xml.transform.TransformerException;
\r
7 import org.w3c.dom.*;
\r
8 import org.xml.sax.*;
\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.text.SimpleDateFormat;
\r
17 import java.util.ArrayList;
\r
18 import java.util.Calendar;
\r
19 import java.util.Date;
\r
21 import jp.co.areaweb.tools.database.*;
\r
23 public class ConvBusstop {
\r
28 public static final boolean DB_INIT = false;
\r
30 // 近くのバス停を探す範囲(バス停を中心としたNEER×2m四方の領域
\r
31 static final int NEER = 150;
\r
32 static boolean nocheck = false;
\r
37 * java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop <option>
\r
38 * OPTION: -nocheck OSMデータ上に既存のバス停が存在するかどうかをチェックしない
\r
39 * OPTION: -check OSMデータ上に既存のバス停が存在するかどうかをチェックする
\r
41 * @throws IOException
\r
42 * @throws SQLException
\r
43 * @throws ClassNotFoundException
\r
44 * @throws FileNotFoundException
\r
45 * @throws TransformerException
\r
46 * @throws SAXException
\r
47 * @throws ParserConfigurationException */
\r
48 public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException
\r
51 if (args.length > index) {
\r
52 if (args[index].equals("-check")) {
\r
53 ConvBusstop.nocheck = false;
\r
56 else if (args[index].equals("-nocheck")) {
\r
57 ConvBusstop.nocheck = true;
\r
63 * アプリケーション [ConvBusstop]
\r
64 * > java -jar ConvBusstop.jar <オプション>
\r
65 * オプション: -exp 実行する直前にデータベースを初期化する(省略可能)
\r
67 File dbdir = new File("database");
\r
68 if (!dbdir.isDirectory()) {
\r
72 Connection con = DatabaseTool.openDb("database");
\r
73 ConvBusstop.initDb(con);
\r
80 if (args.length > index) {
\r
81 File iFile = new File(args[index]);
\r
83 inputFile(con, iFile);
\r
86 File dir = new File(".");
\r
87 File[] files = dir.listFiles();
\r
88 for (File iFile : files) {
\r
89 if (checkFile(iFile)) {
\r
91 ConvBusstop.clearDb(con);
\r
92 inputFile(con, iFile);
\r
96 System.out.println("["+ fcounter +"]つのファイルをインポートしました。");
\r
100 * --> 'existing.xml'
\r
102 if (!ConvBusstop.nocheck && (fcounter > 0)) {
\r
104 * 既存のOSMデータファイルがなければ、新たにダウンロードする。
\r
105 * OSMデータファイルがあるときは、ダウンロードしないでそれを使う。
\r
107 File existingFile = new File("existing.xml");
\r
108 if (!existingFile.isFile()) {
\r
110 * インポートしたデータの緯度経度範囲を読み取る
\r
112 double maxLat = -90.0D;
\r
113 double minLat = 90.0D;
\r
114 double maxLon = -180.0D;
\r
115 double minLon = 180.0D;
\r
116 PreparedStatement ps8 = con.prepareStatement("SELECT lat,lon FROM bus_stop");
\r
117 ResultSet rset8 = ps8.executeQuery();
\r
118 while (rset8.next()) {
\r
119 Double lat = rset8.getDouble("lat");
\r
120 Double lon = rset8.getDouble("lon");
\r
122 if (lat > maxLat) {
\r
125 if (lon > maxLon) {
\r
128 if (lat < minLat) {
\r
131 if (lon < minLon) {
\r
138 * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する
\r
140 HttpPOST.getCapabilities(minLat, maxLat, minLon, maxLon);
\r
142 readExistingFile(con, existingFile);
\r
144 PreparedStatement ps1 = con.prepareStatement("SELECT idref,name,lat,lon FROM bus_stop");
\r
145 PreparedStatement ps2 = con.prepareStatement("SELECT count(idref) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
\r
146 PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?");
\r
147 ResultSet rset1 = ps1.executeQuery();
\r
148 while (rset1.next()) {
\r
149 String idref = rset1.getString("idref");
\r
150 String name = rset1.getString("name");
\r
151 Double lat = rset1.getDouble("lat");
\r
152 Double lon = rset1.getDouble("lon");
\r
154 // 指定の緯度経度を中心とする半径75m四方の矩形領域
\r
155 System.out.print(idref + "("+ name + ") ....");
\r
156 RectArea rect = new RectArea(lat, lon, NEER); // 100m 四方
\r
157 ps2.setDouble(1, rect.minlat);
\r
158 ps2.setDouble(2, rect.maxlat);
\r
159 ps2.setDouble(3, rect.minlon);
\r
160 ps2.setDouble(4, rect.maxlon);
\r
161 ResultSet rset2 = ps2.executeQuery();
\r
162 if (rset2.next()) {
\r
163 int count = rset2.getInt(1);
\r
164 System.out.println("...."+ count);
\r
165 ps3.setInt(1, count);
\r
166 ps3.setString(2, idref);
\r
167 ps3.executeUpdate();
\r
177 // ローカルデータベース内の情報を出力する
\r
178 if (args.length > index) {
\r
179 File iFile = new File(args[index]);
\r
180 String iStr = iFile.getName();
\r
181 outputDb(con, iStr.substring(0, iStr.length() - 4));
\r
184 File dir = new File(".");
\r
185 File[] files = dir.listFiles();
\r
186 for (File iFile : files) {
\r
187 if (checkFile(iFile)) {
\r
188 String iStr = iFile.getName();
\r
189 outputDb(con, iStr.substring(0, iStr.length() - 4));
\r
194 // ローカルデータベース内の情報を「busstop.xml」に出力する
\r
195 int counter = outputDb(con);
\r
197 System.out.println("["+ counter +"] 変換完了しました。");
\r
200 DatabaseTool.closeDb(con);
\r
204 static String[] shiftArgs(String[] args) {
\r
205 String[] values = new String[args.length - 1];
\r
206 for (int i=1; i < args.length; i++) {
\r
207 values[i - 1] = new String(args[i]);
\r
212 public static void readExistingFile (Connection con, File existingFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
\r
215 DocumentBuilderFactory factory;
\r
216 DocumentBuilder builder;
\r
220 factory = DocumentBuilderFactory.newInstance();
\r
221 builder = factory.newDocumentBuilder();
\r
222 factory.setIgnoringElementContentWhitespace(true);
\r
223 factory.setIgnoringComments(true);
\r
224 factory.setValidating(true);
\r
225 root = builder.parse(existingFile);
\r
227 iCounter += readExistingNodes(con, root);
\r
228 System.out.println("既存バス停数["+ iCounter +"]");
\r
231 static int readExistingNodes(Connection con, Node node) throws IOException, SQLException {
\r
234 NodeList nodes = node.getChildNodes();
\r
235 for (int i = 0; i < nodes.getLength(); i++) {
\r
236 Node node2 = nodes.item(i);
\r
237 if (node2.getNodeName().equals("node")) {
\r
239 importExistingNode(con, node2);
\r
242 iCounter += readExistingNodes(con, node2);
\r
248 static void importExistingNode(Connection con, Node node) throws IOException, SQLException {
\r
249 String idrefStr = "";
\r
250 String latStr = "";
\r
251 String lonStr = "";
\r
252 PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon) VALUES (?,?,?)");
\r
254 NamedNodeMap nodeMap = node.getAttributes();
\r
255 if (null != nodeMap) {
\r
256 for (int j=0; j < nodeMap.getLength(); j++) {
\r
257 if (nodeMap.item(j).getNodeName().equals("id")) {
\r
258 idrefStr = nodeMap.item(j).getNodeValue();
\r
260 else if (nodeMap.item(j).getNodeName().equals("lat")) {
\r
261 latStr = nodeMap.item(j).getNodeValue();
\r
263 else if (nodeMap.item(j).getNodeName().equals("lon")) {
\r
264 lonStr = nodeMap.item(j).getNodeValue();
\r
268 // idref と nameStr をデータベースに格納する
\r
269 System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")");
\r
270 ps5.setString(1, idrefStr);
\r
271 ps5.setDouble(2, Double.parseDouble(latStr));
\r
272 ps5.setDouble(3, Double.parseDouble(lonStr));
\r
273 ps5.executeUpdate();
\r
279 * ソースファイルを読み取ってローカルベータベースへ記録する
\r
282 * @throws FileNotFoundException
\r
283 * @throws ClassNotFoundException
\r
284 * @throws SQLException
\r
285 * @throws IOException
\r
286 * @throws ParserConfigurationException
\r
287 * @throws SAXException
\r
289 public static void inputFile (Connection con, File iFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
\r
291 String timeStampStr = null;
\r
293 String iStr = iFile.getName();
\r
294 File dir = new File(iStr.substring(0, iStr.length() - 4));
\r
297 DocumentBuilderFactory factory;
\r
298 DocumentBuilder builder;
\r
302 factory = DocumentBuilderFactory.newInstance();
\r
303 builder = factory.newDocumentBuilder();
\r
304 factory.setIgnoringElementContentWhitespace(true);
\r
305 factory.setIgnoringComments(true);
\r
306 factory.setValidating(true);
\r
307 root = builder.parse(iStr);
\r
309 iCounter += showNodes(con, root, iStr.substring(0, iStr.length() - 4), timeStampStr);
\r
310 System.out.println("バス停数["+ iCounter +"]");
\r
313 public static void clearDb(Connection con) throws SQLException {
\r
314 Statement stmt = con.createStatement();
\r
315 long count = stmt.executeUpdate("delete from bus_stop");
\r
316 System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。");
\r
318 count = stmt.executeUpdate("delete from existing_data");
\r
319 System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。");
\r
321 count = stmt.executeUpdate("delete from bus_course");
\r
322 System.out.println("'Database.bus_course'から "+ count +" 件のデータを削除しました。");
\r
324 count = stmt.executeUpdate("delete from bus_ref");
\r
325 System.out.println("'Database.bus_ref'から "+ count +" 件のデータを削除しました。");
\r
329 public static void initDb(Connection con) throws SQLException {
\r
332 // 'table.BUS_STOP'を新規に作る
\r
333 DbBusstop.create(con);
\r
335 createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));";
\r
336 DbBusstop.create(con, createSt);
\r
338 createSt = "CREATE TABLE bus_course (code int, type int, corp VARCHAR(128) NOT NULL, course VARCHAR(512), ifile VARCHAR(128), CONSTRAINT bus_course_pk PRIMARY KEY(code));";
\r
339 DbBusstop.create(con, createSt);
\r
341 createSt = "CREATE TABLE bus_ref (idref VARCHAR(12), code INT);";
\r
342 DbBusstop.create(con, createSt);
\r
347 * ローカルデータベース内の情報を出力する
\r
350 * @throws IOException
\r
351 * @throws SQLException
\r
353 public static void outputDb(Connection con, String iCode) throws IOException, SQLException {
\r
354 SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
\r
355 String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));
\r
356 File dir = new File(iCode);
\r
359 BufferedWriter ow = null;
\r
360 BufferedWriter gw = null;
\r
361 BufferedWriter hw = null;
\r
362 BufferedWriter ww = null;
\r
365 File htmlFile = new File(iCode +".html");
\r
366 hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(htmlFile), "UTF-8"));
\r
367 hw.write("<!DOCTYPE html>");
\r
369 hw.write("<html><head><meta charset=\"utf-8\" /></head>");
\r
371 hw.write("<body><table border='1'>");
\r
374 hw.write("<td>type</td>");
\r
375 hw.write("<td>corp</td>");
\r
376 hw.write("<td>course</td>");
\r
377 hw.write("<td>GPX</td>");
\r
378 hw.write("<td>SAMPLE</td>");
\r
379 hw.write("<td>バス停数</td>");
\r
380 hw.write("<td>未入力</td>");
\r
381 hw.write("<td>既存</td>");
\r
386 File wikiFile = new File(iCode +".wiki.txt");
\r
387 ww = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(wikiFile), "UTF-8"));
\r
388 ww.write("= バス路線毎のマッピング状況 =");
\r
392 String maeCorp = "";
\r
393 boolean firstCorp = true;
\r
395 PreparedStatement ps7 = con.prepareStatement("SELECT code,type,corp,course,ifile FROM bus_course WHERE ifile=? ORDER BY type,corp,course");
\r
396 PreparedStatement ps9 = con.prepareStatement("SELECT idref FROM bus_ref WHERE code=?");
\r
397 PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed FROM bus_stop WHERE idref=?");
\r
398 ps7.setString(1, iCode);
\r
399 ResultSet rset7 = ps7.executeQuery();
\r
400 while (rset7.next()) {
\r
401 int code = rset7.getInt(1);
\r
402 int type = rset7.getInt(2);
\r
403 String corp = rset7.getString(3);
\r
404 String course = rset7.getString(4);
\r
407 File osmFile = new File(dir, iCode + String.format("_%1$08d", code) +".osm");
\r
408 File gpxFile = new File(dir, iCode + String.format("_%1$08d", code) +".gpx");
\r
409 File osmSample = new File(dir, iCode + String.format("_s%1$08d", code) +".osm");
\r
411 System.out.println("course = "+ course);
\r
413 int fixedCount = 0;
\r
414 int unfixedCount = 0;
\r
416 // index file header
\r
418 hw.write("<td>"+ type +"</td>");
\r
419 hw.write("<td>"+ corp +"</td>");
\r
420 hw.write("<td><a href='"+ dir.getName() +"/"+ osmFile.getName() +"'>"+ course +"</a></td>");
\r
421 hw.write("<td><a href='"+ dir.getName() +"/"+ gpxFile.getName() +"'>"+ gpxFile.getName() +"</a></td>");
\r
422 hw.write("<td><a href='"+ dir.getName() +"/"+ osmSample.getName() +"'>"+ osmSample.getName() +"</a></td>");
\r
425 //--------------------------------------------
\r
428 if (!maeCorp.equals(corp)) {
\r
429 if (firstCorp == false) {
\r
436 ww.write("=== "+ corp +" ===");
\r
440 ww.write(":{{JA:Tag|network||"+ corp +"}}");
\r
442 ww.write(":{{JA:Tag|operator||"+ corp +"}}");
\r
446 ww.write("{| class=\"wikitable sortable\" style=\"table-layout: fixed; width: 100%\"");
\r
448 ww.write("!style=\"width: 100px\"| ref");
\r
450 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(マスタ)");
\r
452 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(往路)");
\r
454 ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(復路)");
\r
456 ww.write("!class=\"unsortable\"| 備考");
\r
459 maeCorp = new String(corp);
\r
465 ww.write("| "+ course +" "); // ref
\r
466 ww.write("|| {{State Route|r=0}} {{relation|0|tools=no}} "); // 編集状況 (マスタ)
\r
467 ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} "); // 編集状況 (往路)
\r
468 ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} "); // 編集状況 (復路)
\r
469 ww.write("|| "); // 備考
\r
474 ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));
\r
475 ow.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
\r
477 ow.write("<osm version=\"0.6\" generator=\"ConvBusstop\">");
\r
481 gw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(gpxFile), "UTF-8"));
\r
482 gw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
\r
484 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
487 double maxLat = -180.0;
\r
488 double minLat = 180.0;
\r
489 double maxLon = -180.0;
\r
490 double minLon = 180.0;
\r
492 ps9.setInt(1, code);
\r
493 ResultSet rset9 = ps9.executeQuery();
\r
494 while (rset9.next()) {
\r
495 String idref = rset9.getString(1);
\r
497 ps8.setString(1, idref);
\r
498 ResultSet rset8 = ps8.executeQuery();
\r
499 if (rset8.next()) {
\r
501 String name = rset8.getString(1);
\r
502 Double lat = rset8.getDouble(2);
\r
503 Double lon = rset8.getDouble(3);
\r
504 int fixed = rset8.getInt(4);
\r
506 if (lat > maxLat) {
\r
509 if (lon > maxLon) {
\r
512 if (lat < minLat) {
\r
515 if (lon < minLon) {
\r
519 System.out.println("\tway point = "+ idref +", lat="+ lat +", lon="+ lon +", name="+ name);
\r
520 fixedCount += fixed;
\r
525 int nodeid = Integer.parseInt(idref.substring(1)) * -1;
\r
526 String osm_node = nodeBusstop(nodeid, name, lat, lon, timeStampStr);
\r
527 ow.write(osm_node);
\r
531 //File txtFile = new File(dir, iCode + idref +".txt");
\r
532 //BufferedWriter gw2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(txtFile), "UTF-8"));
\r
533 //gw2.write(osm_node);
\r
538 gw.write("<wpt lat=\""+ lat +"\" lon=\""+ lon +"\">\n");
\r
539 gw.write(" <time>"+ timeStampStr +"Z</time>\n");
\r
540 gw.write(" <name><![CDATA["+ name +"]]></name>\n");
\r
541 //gw.write(" <link href=\""+ txtFile.getName() +"\"><text>"+ idref +"</text></link>\n");
\r
542 gw.write("</wpt>\n");
\r
551 hw.write("<td>"+ stopCount +"</td>");
\r
552 hw.write("<td>"+ unfixedCount +"</td>");
\r
553 hw.write("<td>"+ fixedCount +"</td>");
\r
558 BufferedWriter ow2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmSample), "UTF-8"));
\r
559 ow2.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
\r
561 ow2.write("<osm version=\"0.5\" generator=\"ConvBusstop\">");
\r
564 ow2.write(nodeBusstop(code, "SAMPLE", ((maxLat+minLat)/2), ((maxLon+minLon)/2), timeStampStr));
\r
566 ow2.write("</osm>");
\r
571 ow.write("</osm>");
\r
576 gw.write("</gpx>");
\r
585 // index file footer
\r
586 hw.write("</table></body></html>");
\r
591 public static int outputDb(Connection con) throws IOException, SQLException {
\r
592 File osmFile = new File("busstop.osm");
\r
595 SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
\r
596 String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));
\r
597 BufferedWriter ow = null;
\r
600 ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));
\r
601 ow.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
\r
603 ow.write("<osm version=\"0.6\" generator=\"ConvBusstop\">");
\r
606 PreparedStatement ps8 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop");
\r
607 ResultSet rset8 = ps8.executeQuery();
\r
608 while (rset8.next()) {
\r
609 String idref = rset8.getString("idref");
\r
610 String name = rset8.getString("name");
\r
611 Double lat = rset8.getDouble("lat");
\r
612 Double lon = rset8.getDouble("lon");
\r
613 int fixed = rset8.getInt("fixed");
\r
615 if (ConvBusstop.nocheck || (fixed < 1)) {
\r
618 int nodeid = Integer.parseInt(idref.substring(1)) * -1;
\r
619 String osm_node = nodeBusstop(nodeid, name, lat, lon, timeStampStr);
\r
620 System.out.println(osm_node);
\r
621 ow.write(osm_node);
\r
628 ow.write("</osm>");
\r
634 public static String nodeBusstop(int code, String name, Double lat, Double lon, String timeStampStr) {
\r
635 String osm_node = ("<node id=\""+ code +"\" timestamp=\""+ timeStampStr +"Z\" lat=\""+ lat +"\" lon=\""+ lon +"\">\n");
\r
636 osm_node += "<tag k=\"name\" v=\""+ name +"\"/>\n";
\r
637 osm_node += "<tag k=\"fixme\" v=\"platform/stop_positionを選択して、正しい位置に移動させてください\"/>\n";
\r
638 osm_node += "<tag k=\"source\" v=\"KSJ2\"/>\n";
\r
639 osm_node += "<tag k=\"source_ref\" v=\"http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-P11.html\"/>\n";
\r
640 osm_node += "<tag k=\"created_by\" v=\"National-Land-Numerical-Information_MLIT_Japan\"/>\n";
\r
641 osm_node += "<tag k=\"note\" v=\"National-Land Numerical Information (Bus stop) 2012, MLIT Japan\"/>\n";
\r
642 osm_node += "<tag k=\"note:ja\" v=\"国土数値情報(バス停留所)平成24年 国土交通省\"/>\n";
\r
643 osm_node += "<tag k=\"public_transport\" v=\"platform\"/>\n";
\r
644 osm_node += "<tag k=\"public_transport\" v=\"stop_position\"/>\n";
\r
645 osm_node += "<tag k=\"highway\" v=\"bus_stop\"/>\n";
\r
646 osm_node += "<tag k=\"bus\" v=\"yes\"/>\n";
\r
647 osm_node += "</node>\n";
\r
655 * @param iFileName // ソースファイル名(拡張子を含まない)
\r
656 * @param timeStampStr
\r
658 * @throws IOException
\r
659 * @throws SQLException
\r
661 public static int showNodes(Connection con, Node node, String iFileName, String timeStampStr) throws IOException, SQLException {
\r
663 NamedNodeMap nodeMap = node.getAttributes();
\r
664 if ( null != nodeMap ) {
\r
665 for ( int j=0; j < nodeMap.getLength(); j++ ) {
\r
666 if (nodeMap.item(j).getNodeName().equals("timeStamp")) {
\r
667 timeStampStr = nodeMap.item(j).getNodeValue();
\r
672 NodeList nodes = node.getChildNodes();
\r
673 for (int i=0; i<nodes.getLength(); i++) {
\r
674 Node node2 = nodes.item(i);
\r
675 if (node2.getNodeName().equals("jps:GM_Point")) {
\r
676 showGmPoint(con, node2);
\r
678 else if (node2.getNodeName().equals("gml:Point")) {
\r
679 showGmlPoint(con, node2);
\r
682 else if (node2.getNodeName().equals("ksj:ED01")) {
\r
684 showED01(con, node2, iFileName);
\r
686 else if (node2.getNodeName().equals("ksj:BusStop")) {
\r
688 showBusStop(con, node2, iFileName);
\r
692 iCounter += showNodes(con, node2, iFileName, timeStampStr);
\r
702 * @param iFileName // ソースファイル名(拡張子を含まない)
\r
703 * @throws IOException
\r
704 * @throws SQLException
\r
706 public static void showED01(Connection con, Node node, String iFileName) throws IOException, SQLException {
\r
707 String idrefStr = "";
\r
708 String nameStr = "";
\r
709 PreparedStatement ps1 = con.prepareStatement("SELECT idref FROM bus_stop WHERE idref=?");
\r
710 PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (idref,name,ifile) VALUES (?,?,?)");
\r
711 PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");
\r
712 PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");
\r
713 PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");
\r
714 PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");
\r
716 ArrayList<String[]> bris = new ArrayList<String[]>();
\r
717 NodeList nodes = node.getChildNodes();
\r
718 for (int i=0; i < nodes.getLength(); i++) {
\r
719 Node node2 = nodes.item(i);
\r
720 if (node2.getNodeName().equals("ksj:POS")) {
\r
721 NamedNodeMap nodeMap = node2.getAttributes();
\r
722 if (null != nodeMap) {
\r
723 for ( int j=0; j < nodeMap.getLength(); j++ ) {
\r
724 if (nodeMap.item(j).getNodeName().equals("idref")) {
\r
725 idrefStr = nodeMap.item(j).getNodeValue();
\r
726 System.out.println("found idref='"+ idrefStr +"'");
\r
732 else if (node2.getNodeName().equals("ksj:BSN")) {
\r
733 nameStr = node2.getTextContent();
\r
735 else if (node2.getNodeName().equals("ksj:BRI")) {
\r
736 String[] rtn = anaComm(node2);
\r
743 // idref と nameStr をデータベースに格納する
\r
744 boolean insert = true;
\r
745 ps1.setString(1, idrefStr);
\r
746 ResultSet rset = ps1.executeQuery();
\r
753 ps2.setString(1, idrefStr);
\r
754 ps2.setString(2, nameStr);
\r
755 ps2.setString(3, iFileName);
\r
756 System.out.println("INSERT INTO bus_stop (idref,name,ifile) VALUES ('"+ idrefStr +"','"+ nameStr +"','"+ iFileName +"')");
\r
757 ps2.executeUpdate();
\r
760 for (String[] rtn : bris) {
\r
762 ps3.setString(1, rtn[1]);
\r
763 ps3.setString(2, rtn[2]);
\r
764 ps3.setString(3, iFileName);
\r
765 rset = ps3.executeQuery();
\r
767 code = rset.getInt(1);
\r
772 ps6.setString(1, iFileName);
\r
773 ResultSet rset6 = ps6.executeQuery();
\r
774 if (rset6.next()) {
\r
775 code = rset6.getInt(1);
\r
780 System.out.println("code="+code);
\r
781 ps4.setInt(1, code);
\r
782 ps4.setInt(2, Integer.parseInt(rtn[0]));
\r
783 ps4.setString(3, rtn[2]);
\r
784 ps4.setString(4, rtn[1]);
\r
785 ps4.setString(5, iFileName);
\r
786 ps4.executeUpdate();
\r
789 ps5.setString(1, idrefStr);
\r
790 ps5.setInt(2, code);
\r
791 ps5.executeUpdate();
\r
802 * <ksj:BusStop gml:id="ED01_1">
\r
803 * <ksj:position xlink:href="#n1"/>
\r
804 * <ksj:busStopName>城堀</ksj:busStopName>
\r
805 * <ksj:busRouteInformation>
\r
806 * <ksj:BusRouteInformation>
\r
807 * <ksj:busType>1</ksj:busType>
\r
808 * <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
\r
809 * <ksj:busLineName>小01</ksj:busLineName>
\r
810 * </ksj:BusRouteInformation>
\r
811 * </ksj:busRouteInformation>
\r
812 * <ksj:busRouteInformation>
\r
813 * <ksj:BusRouteInformation>
\r
814 * <ksj:busType>1</ksj:busType>
\r
815 * <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
\r
816 * <ksj:busLineName>湯07</ksj:busLineName>
\r
817 * </ksj:BusRouteInformation>
\r
818 * </ksj:busRouteInformation>
\r
819 * <ksj:busRouteInformation>
\r
820 * <ksj:BusRouteInformation>
\r
821 * <ksj:busType>1</ksj:busType>
\r
822 * <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
\r
823 * <ksj:busLineName>湯11</ksj:busLineName>
\r
824 * </ksj:BusRouteInformation>
\r
825 * </ksj:busRouteInformation>
\r
830 * @param iFileName // ソースファイル名(拡張子を含まない)
\r
831 * @throws IOException
\r
832 * @throws SQLException
\r
834 public static void showBusStop(Connection con, Node node, String iFileName) throws IOException, SQLException {
\r
835 String idrefStr = "";
\r
836 String nameStr = "";
\r
837 PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE idref=?");
\r
838 PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");
\r
839 PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");
\r
840 PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");
\r
841 PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");
\r
843 ArrayList<String[]> bris = new ArrayList<String[]>();
\r
844 NodeList nodes = node.getChildNodes();
\r
845 for (int i=0; i < nodes.getLength(); i++) {
\r
846 Node node2 = nodes.item(i);
\r
847 if (node2.getNodeName().equals("ksj:position")) {
\r
848 NamedNodeMap nodeMap = node2.getAttributes();
\r
849 if (null != nodeMap) {
\r
850 for ( int j=0; j < nodeMap.getLength(); j++ ) {
\r
851 if (nodeMap.item(j).getNodeName().equals("xlink:href")) {
\r
852 idrefStr = nodeMap.item(j).getNodeValue();
\r
853 idrefStr = idrefStr.substring(1);
\r
854 System.out.println("found idref='"+ idrefStr +"'");
\r
860 else if (node2.getNodeName().equals("ksj:busStopName")) {
\r
861 nameStr = node2.getTextContent();
\r
863 else if (node2.getNodeName().equals("ksj:busRouteInformation")) {
\r
864 String[] rtn = anaCommJGD(node2);
\r
871 // idref と nameStr をデータベースに格納する
\r
872 ps2.setString(1, nameStr);
\r
873 ps2.setString(2, iFileName);
\r
874 ps2.setString(3, idrefStr);
\r
875 ps2.executeUpdate();
\r
877 for (String[] rtn : bris) {
\r
879 ps3.setString(1, rtn[1]);
\r
880 ps3.setString(2, rtn[2]);
\r
881 ps3.setString(3, iFileName);
\r
882 ResultSet rset = ps3.executeQuery();
\r
884 code = rset.getInt(1);
\r
889 ps6.setString(1, iFileName);
\r
890 ResultSet rset6 = ps6.executeQuery();
\r
891 if (rset6.next()) {
\r
892 code = rset6.getInt(1);
\r
897 System.out.println("course="+ code +" : "+ rtn[0] +" : "+ rtn[1] +" : "+ rtn[2] );
\r
898 ps4.setInt(1, code);
\r
899 ps4.setInt(2, Integer.parseInt(rtn[0]));
\r
900 ps4.setString(3, rtn[2]);
\r
901 ps4.setString(4, rtn[1]);
\r
902 ps4.setString(5, iFileName);
\r
903 ps4.executeUpdate();
\r
906 ps5.setString(1, idrefStr);
\r
907 ps5.setInt(2, code);
\r
908 ps5.executeUpdate();
\r
917 public static String[] anaComm(Node briNode) {
\r
918 String[] rtn = new String[3];
\r
919 rtn[0] = ""; // corp type
\r
920 rtn[1] = ""; // course name
\r
921 rtn[2] = ""; // corp name
\r
923 NodeList nodes = briNode.getChildNodes();
\r
924 for (int i=0; i < nodes.getLength(); i++) {
\r
925 Node node2 = nodes.item(i);
\r
926 if (node2.getNodeName().equals("ksj:BSC")) {
\r
927 rtn[0] = node2.getTextContent();
\r
929 else if (node2.getNodeName().equals("ksj:BLN")) {
\r
930 rtn[1] = node2.getTextContent();
\r
932 else if (node2.getNodeName().equals("ksj:BOC")) {
\r
933 rtn[2] = node2.getTextContent();
\r
941 * <ksj:busRouteInformation>
\r
942 * <ksj:BusRouteInformation>
\r
943 * <ksj:busType>1</ksj:busType>
\r
944 * <ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
\r
945 * <ksj:busLineName>小01</ksj:busLineName>
\r
946 * </ksj:BusRouteInformation>
\r
947 * </ksj:busRouteInformation>
\r
952 public static String[] anaCommJGD(Node briNode) {
\r
953 String[] rtn = new String[3];
\r
956 NodeList nodes2 = briNode.getChildNodes();
\r
957 for (int i=0; i < nodes2.getLength(); i++) {
\r
958 Node node2 = nodes2.item(i);
\r
959 if (node2.getNodeName().equals("ksj:BusRouteInformation")) {
\r
960 NodeList nodes3 = node2.getChildNodes();
\r
961 for (int j=0; j < nodes3.getLength(); j++) {
\r
962 Node node3 = nodes3.item(j);
\r
963 if (node3.getNodeName().equals("ksj:busType")) {
\r
964 rtn[0] = new String(node3.getTextContent());
\r
967 else if (node3.getNodeName().equals("ksj:busLineName")) {
\r
968 rtn[1] = new String(node3.getTextContent());
\r
971 else if (node3.getNodeName().equals("ksj:busOperationCompany")) {
\r
972 rtn[2] = new String(node3.getTextContent());
\r
985 public static void showGmPoint(Connection con, Node node) throws IOException, SQLException {
\r
986 String positionStr = "";
\r
987 String latStr = "";
\r
988 String lonStr = "";
\r
991 NamedNodeMap nodeMap = node.getAttributes();
\r
992 if ( null != nodeMap ) {
\r
993 for ( int j=0; j<nodeMap.getLength(); j++ ) {
\r
994 if (nodeMap.item(j).getNodeName().equals("id")) {
\r
995 idStr = nodeMap.item(j).getNodeValue();
\r
1000 NodeList nodes = node.getChildNodes();
\r
1001 for (int i=0; i < nodes.getLength(); i++) {
\r
1002 Node node2 = nodes.item(i);
\r
1003 if (node2.getNodeName().equals("jps:GM_Point.position")) {
\r
1004 NodeList nodes3 = node2.getChildNodes();
\r
1005 for (int j=0; j < nodes3.getLength(); j++) {
\r
1006 Node node3 = nodes3.item(j);
\r
1007 if (node3.getNodeName().equals("jps:DirectPosition")) {
\r
1008 NodeList nodes4 = node3.getChildNodes();
\r
1009 for (int k=0; k < nodes4.getLength(); k++) {
\r
1010 Node node4 = nodes4.item(k);
\r
1011 if (node4.getNodeName().equals("DirectPosition.coordinate")) {
\r
1012 positionStr = node4.getTextContent();
\r
1013 String[] str4Ary = positionStr.split(" ");
\r
1014 latStr = str4Ary[0];
\r
1015 lonStr = str4Ary[1];
\r
1023 PreparedStatement ps6 = con.prepareStatement("UPDATE bus_stop SET lat=?,lon=?,fixed=? WHERE idref=?");
\r
1024 double lat = Double.parseDouble(latStr);
\r
1025 double lon = Double.parseDouble(lonStr);
\r
1026 ps6.setDouble(1, lat);
\r
1027 ps6.setDouble(2, lon);
\r
1028 // ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
\r
1030 ps6.setString(4, idStr);
\r
1031 System.out.println("UPDATE bus_stop("+ idStr +") lat="+ lat +", lon="+ lon +", fixed=0");
\r
1032 ps6.executeUpdate();
\r
1039 * <gml:Point gml:id="n1">
\r
1040 * <gml:pos>35.14591397 139.10569573</gml:pos>
\r
1045 * @throws IOException
\r
1046 * @throws SQLException
\r
1048 public static void showGmlPoint(Connection con, Node node) throws IOException, SQLException {
\r
1049 String positionStr = "";
\r
1050 String latStr = "";
\r
1051 String lonStr = "";
\r
1052 String idStr = "";
\r
1054 NamedNodeMap nodeMap = node.getAttributes();
\r
1055 if ( null != nodeMap ) {
\r
1056 for ( int j=0; j<nodeMap.getLength(); j++ ) {
\r
1057 if (nodeMap.item(j).getNodeName().equals("gml:id")) {
\r
1058 idStr = nodeMap.item(j).getNodeValue();
\r
1063 NodeList nodes = node.getChildNodes();
\r
1064 for (int i=0; i < nodes.getLength(); i++) {
\r
1065 Node node2 = nodes.item(i);
\r
1066 if (node2.getNodeName().equals("gml:pos")) {
\r
1067 positionStr = node2.getTextContent().trim();
\r
1068 String[] str4Ary = positionStr.split(" ");
\r
1069 latStr = str4Ary[0];
\r
1070 lonStr = str4Ary[1];
\r
1072 PreparedStatement ps6 = con.prepareStatement("INSERT INTO bus_stop (lat,lon,fixed,idref) VALUES (?,?,?,?)");
\r
1073 double lat = Double.parseDouble(latStr);
\r
1074 double lon = Double.parseDouble(lonStr);
\r
1075 System.out.println("INSERT INTO bus_stop (lat,lon,fixed,idref) VALUES ('"+ latStr +"','"+ lonStr +"','0','"+ idStr +"')");
\r
1077 ps6.setDouble(1, lat);
\r
1078 ps6.setDouble(2, lon);
\r
1079 //ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
\r
1081 ps6.setString(4, idStr);
\r
1082 ps6.executeUpdate();
\r
1088 static boolean checkFile(File f) {
\r
1089 String name = f.getName();
\r
1090 if (!name.startsWith("P11-")) {
\r
1093 if (!name.toUpperCase().endsWith(".XML")) {
\r