diff --git a/src/ImportGML_FUEL.sh b/src/ImportGML_FUEL.sh index d3634d2..9aa57bb 100755 --- a/src/ImportGML_FUEL.sh +++ b/src/ImportGML_FUEL.sh @@ -1,3 +1,4 @@ +cp ../osmCoverage/dist/osmCoverage.jar . rm -r database mkdir database java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbFuel @@ -7,3 +8,7 @@ java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToCartoCSV java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.csv java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.json +cp coverage.fuel.json ../www-root/coverage +cd ../www-root +git add . +git commit -a -m "coverage fuel update $(date +%Y-%m-%d_%H:%M)" diff --git a/src/ImportGML_FUEL_update.sh b/src/ImportGML_FUEL_update.sh index 4b4961d..2fc12f6 100644 --- a/src/ImportGML_FUEL_update.sh +++ b/src/ImportGML_FUEL_update.sh @@ -1,12 +1,18 @@ +cp ../osmCoverage/dist/osmCoverage.jar . rm -r database mkdir database -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbFuel -update -drop +java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbFuel -update java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbExist java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Fuel java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToPostgis -update java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToCartoCSV java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.csv java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.json +git commit -a -m "coverage fuel update $(date +%Y-%m-%d_%H:%M)" +cp coverage.fuel.json ../www-root/coverage +cd ../www-root +git add . +git commit -a -m "coverage fuel update $(date +%Y-%m-%d_%H:%M)" # java -cp hsqldb_2.2.9.jar org.hsqldb.util.DatabaseManager # db_driver=org.hsqldb.jdbcDriver diff --git a/src/busstop_update.sh b/src/busstop_update.sh new file mode 100755 index 0000000..cb94df9 --- /dev/null +++ b/src/busstop_update.sh @@ -0,0 +1,7 @@ +cp ../osmCoverage/dist/osmCoverage.jar . + +rm */update.sql +java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.Busstop -update +java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar osm.jp.postgis.Do_sqlfiles -update +java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.Coverage coverage.csv +git commit -a -m "coverage update $(date +%Y-%m-%d_%H:%M)" diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index 5b000ae..bb2ba6d 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -23,382 +23,383 @@ public class Busstop { - String filter = ""; - String urlStr = ""; + String filter = ""; + String urlStr = ""; - public static final boolean DB_INIT = false; + public static final boolean DB_INIT = false; - // 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域 - static final int NEER = 150; // 150m(0.15km) - static boolean update = false; // '-update'オプション postgisデータの更新を行う - static boolean noget = false; // '-noget'オプション OSM既存データのGETを行わない - - public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); + // 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域 + static final int NEER = 150; // 150m(0.15km) + static boolean update = false; // '-update'オプション postgisデータの更新を行う + static boolean noget = false; // '-noget'オプション OSM既存データのGETを行わない - /** - * メイン - * - * java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop [option] - * OPTION: -check OSMデータ上に既存のバス停が存在するかどうかをチェックする - * + public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); + + /** + * メイン + * + * java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop [option] + * OPTION: -check OSMデータ上に既存のバス停が存在するかどうかをチェックする + * * @param args - * @throws IOException - * @throws SQLException - * @throws ClassNotFoundException - * @throws FileNotFoundException - * @throws TransformerException - * @throws SAXException - * @throws ParserConfigurationException */ - public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException - { - for (String arg : args) { - if (arg.equals("-update")) { - Busstop.update = true; - } - if (arg.equals("-noget")) { - Busstop.noget = true; - } + * + * @throws IOException + * @throws SQLException + * @throws ClassNotFoundException + * @throws FileNotFoundException + * @throws TransformerException + * @throws SAXException + * @throws ParserConfigurationException */ + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException + { + for (String arg : args) { + if (arg.equals("-update")) { + Busstop.update = true; + } + if (arg.equals("-noget")) { + Busstop.noget = true; + } + } + + // HSQLディレクトリがなければ作る + File dbdir = new File("database"); + if (!dbdir.isDirectory()) { + dbdir.mkdir(); + } + + Connection con = DatabaseTool.openDb("database"); + + try { + /** + * 既存のOSMバス停を読み込む + * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する + * --> 'existing.xml' + */ + if (Busstop.update && !Busstop.noget) { + Busstop.initDb(con); + File existingFile = new File(HttpPOST.EXIST_FILE); + getJapanCapabilities(con, existingFile); } - // HSQLディレクトリがなければ作る - File dbdir = new File("database"); - if (!dbdir.isDirectory()) { - dbdir.mkdir(); - } - - Connection con = DatabaseTool.openDb("database"); - - try { - /** - * 既存のOSMバス停を読み込む - * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する - * --> 'existing.xml' - */ - if (Busstop.update && !Busstop.noget) { - Busstop.initDb(con); - File existingFile = new File(HttpPOST.EXIST_FILE); - getJapanCapabilities(con, existingFile); - } - - /** - * 都道府県ごとのGMLディレクトリの処理 - */ - int fcounter = 0; - File dir = new File("."); - File[] files = dir.listFiles(); - for (File iDir : files) { - if (checkGMLdir(iDir)) { - // GMLディレクトリを処理する - new Busstop(con, iDir); - fcounter++; - } - } - System.out.println("["+ fcounter +"]つのファイルをインポートしました。"); - } - finally { - DatabaseTool.closeDb(con); - } - } - - public static void getJapanCapabilities(Connection con, File oFile) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { - for (Japan all1 : all) { - HttpPOST.getCapabilities("highway", "bus_stop", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - HttpPOST.getCapabilities("highway", "disused:bus_stop", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - HttpPOST.getCapabilities("amenity", "bus_station", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - HttpPOST.getCapabilities("public_transport", "platform", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); - HttpPOST.getCapabilities("public_transport", "stop_position", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con); + /** + * 都道府県ごとのGMLディレクトリの処理 + */ + int fcounter = 0; + File dir = new File("."); + File[] files = dir.listFiles(); + for (File iDir : files) { + if (checkGMLdir(iDir)) { + // GMLディレクトリを処理する + new Busstop(con, iDir); + fcounter++; + } } - } + System.out.println("["+ fcounter +"]つのファイルをインポートしました。"); + } + finally { + DatabaseTool.closeDb(con); + } + } + + public static void getJapanCapabilities(Connection con, File oFile) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { + for (Japan all1 : all) { + HttpPOST.getCapabilities("highway", "bus_stop", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); + readExistingFile(con); + HttpPOST.getCapabilities("highway", "disused:bus_stop", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); + readExistingFile(con); + HttpPOST.getCapabilities("amenity", "bus_station", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); + readExistingFile(con); + HttpPOST.getCapabilities("public_transport", "platform", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); + readExistingFile(con); + HttpPOST.getCapabilities("public_transport", "stop_position", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); + readExistingFile(con); + } + } - /** - * 個別の都道府県「GMLディレクトリ」を処理 - * - * @param con - * @param gmldir - * @throws SQLException - * @throws FileNotFoundException - * @throws ClassNotFoundException - * @throws IOException - * @throws ParserConfigurationException - * @throws SAXException - * @throws TransformerException - */ - public Busstop(Connection con, File gmldir) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - int areacode = Integer.parseInt(gmldir.getName().substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)); + /** + * 個別の都道府県「GMLディレクトリ」を処理 + * + * @param con + * @param gmldir + * @throws SQLException + * @throws FileNotFoundException + * @throws ClassNotFoundException + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + * @throws TransformerException + */ + public Busstop(Connection con, File gmldir) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { + int areacode = Integer.parseInt(gmldir.getName().substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)); - BufferedWriter ow = null; - ToPostgis postgis = null; - Connection conPost = null; - - File outFile = new File(gmldir, "update.sql"); - ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), "UTF-8")); - - if (Busstop.update) { - conPost = DatabaseTool.openDb("postgis"); - } + BufferedWriter ow = null; + ToPostgis postgis = null; + Connection conPost = null; + + File outFile = new File(gmldir, "update.sql"); + ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), "UTF-8")); + + if (Busstop.update) { + conPost = DatabaseTool.openDb("postgis"); + } - File[] files = gmldir.listFiles(); - try { - for (File iFile : files) { - // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。 - if (!checkFile(iFile, areacode)) { - continue; - } + File[] files = gmldir.listFiles(); + try { + for (File iFile : files) { + // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。 + if (!checkFile(iFile, areacode)) { + continue; + } - //Busstop.clearDb(con); - if (!Busstop.update) { - inputFile(con, iFile, areacode); - } + //Busstop.clearDb(con); + if (!Busstop.update) { + inputFile(con, iFile, areacode); + } - if (true) { - - PreparedStatement ps1; - if (Busstop.update) { - ps1 = conPost.prepareStatement("SELECT gid,name,ST_Y(geom) lat,ST_X(geom) lon,fixed FROM t_busstop WHERE area=?"); - } - else { - ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop WHERE area=?"); - } - try (PreparedStatement ps2 = con.prepareStatement("SELECT SUM(score) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)")) { - PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?"); - PreparedStatement ps4 = con.prepareStatement("SELECT SUM(score) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (name = ?)"); - ps1.setInt(1, areacode); - ResultSet rset1 = ps1.executeQuery(); - while (rset1.next()) { - String idref = rset1.getString(1); - String name = rset1.getString("name"); - Double lat = rset1.getDouble("lat"); - Double lon = rset1.getDouble("lon"); - int fixed = rset1.getInt("fixed"); - - // 指定の緯度経度を中心とする半径150x2m四方の矩形領域 - System.out.print(idref + "("+ name + ") ...."); - RectArea rect = new RectArea(lat, lon, NEER); // 300m 四方 - ps2.setDouble(1, rect.minlat); - ps2.setDouble(2, rect.maxlat); - ps2.setDouble(3, rect.minlon); - ps2.setDouble(4, rect.maxlon); - try (ResultSet rset2 = ps2.executeQuery()) { - if (rset2.next()) { - int score = rset2.getInt(1); - if (score > 0) { - System.out.println("."+ score); - if (Busstop.update) { - if (fixed != score) { - String osm_node = "UPDATE t_busstop SET fixed="+ score +" WHERE gid="+ idref +";"; - System.out.println(osm_node); - ow.write(osm_node); - ow.newLine(); - } - } - else { - ps3.setInt(1, score); - ps3.setString(2, idref); - ps3.executeUpdate(); - } + if (true) { + + PreparedStatement ps1; + if (Busstop.update) { + ps1 = conPost.prepareStatement("SELECT gid,name,ST_Y(geom) lat,ST_X(geom) lon,fixed FROM t_busstop WHERE area=?"); + } + else { + ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop WHERE area=?"); + } + try (PreparedStatement ps2 = con.prepareStatement("SELECT SUM(score) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)")) { + PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?"); + PreparedStatement ps4 = con.prepareStatement("SELECT SUM(score) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (name = ?)"); + ps1.setInt(1, areacode); + ResultSet rset1 = ps1.executeQuery(); + while (rset1.next()) { + String idref = rset1.getString(1); + String name = rset1.getString("name"); + Double lat = rset1.getDouble("lat"); + Double lon = rset1.getDouble("lon"); + int fixed = rset1.getInt("fixed"); + + // 指定の緯度経度を中心とする半径150x2m四方の矩形領域 + System.out.print(idref + "("+ name + ") ...."); + RectArea rect = new RectArea(lat, lon, NEER); // 300m 四方 + ps2.setDouble(1, rect.minlat); + ps2.setDouble(2, rect.maxlat); + ps2.setDouble(3, rect.minlon); + ps2.setDouble(4, rect.maxlon); + try (ResultSet rset2 = ps2.executeQuery()) { + if (rset2.next()) { + int score = rset2.getInt(1); + if (score > 0) { + System.out.println("."+ score); + if (Busstop.update) { + if (fixed != score) { + String osm_node = "UPDATE t_busstop SET fixed="+ score +" WHERE gid="+ idref +";"; + System.out.println(osm_node); + ow.write(osm_node); + ow.newLine(); + } + } + else { + ps3.setInt(1, score); + ps3.setString(2, idref); + ps3.executeUpdate(); + } + } + else { + // 指定の緯度経度を中心とする半径150x4m四方の矩形領域 + System.out.print("***"); + rect = new RectArea(lat, lon, NEER*2); // 600m 四方 + ps4.setDouble(1, rect.minlat); + ps4.setDouble(2, rect.maxlat); + ps4.setDouble(3, rect.minlon); + ps4.setDouble(4, rect.maxlon); + ps4.setString(5, name); + try (ResultSet rset4 = ps4.executeQuery()) { + if (rset4.next()) { + score = rset4.getInt(1); + System.out.println(".."+ score); + if (score != fixed) { + if (Busstop.update) { + String osm_node = "UPDATE t_busstop SET fixed="+ score +" WHERE gid="+ idref +";"; + System.out.println(osm_node); + ow.write(osm_node); + ow.newLine(); } else { - // 指定の緯度経度を中心とする半径150x4m四方の矩形領域 - System.out.print("***"); - rect = new RectArea(lat, lon, NEER*2); // 600m 四方 - ps4.setDouble(1, rect.minlat); - ps4.setDouble(2, rect.maxlat); - ps4.setDouble(3, rect.minlon); - ps4.setDouble(4, rect.maxlon); - ps4.setString(5, name); - try (ResultSet rset4 = ps4.executeQuery()) { - if (rset4.next()) { - score = rset4.getInt(1); - System.out.println(".."+ score); - if (score != fixed) { - if (Busstop.update) { - String osm_node = "UPDATE t_busstop SET fixed="+ score +" WHERE gid="+ idref +";"; - System.out.println(osm_node); - ow.write(osm_node); - ow.newLine(); - } - else { - ps3.setInt(1, score); - ps3.setString(2, idref); - ps3.executeUpdate(); - } - } - } - } + ps3.setInt(1, score); + ps3.setString(2, idref); + ps3.executeUpdate(); } } } } - rset1.close(); - ps3.close(); } - ps1.close(); - } - - // ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する - if (!Busstop.update) { - postgis = new ToPostgis(gmldir); - postgis.outputDb(con); - postgis.close(); - } - } - } - finally { - if (conPost != null) { - DatabaseTool.closeDb(conPost); - } - ow.flush(); - ow.close(); - } - } - - static String[] shiftArgs(String[] args) { - String[] values = new String[args.length - 1]; - for (int i=1; i < args.length; i++) { - values[i - 1] = args[i]; - } - return values; - } - - public static void readExistingFile (Connection con) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; - - DocumentBuilderFactory factory; - DocumentBuilder builder; - Node root; - - iCounter = 0; - factory = DocumentBuilderFactory.newInstance(); - builder = factory.newDocumentBuilder(); - factory.setIgnoringElementContentWhitespace(true); - factory.setIgnoringComments(true); - factory.setValidating(true); - File existingFile = new File(HttpPOST.EXIST_FILE); - root = builder.parse(existingFile); - - iCounter += readExistingNodes(con, root); - System.out.println("既存バス停数["+ iCounter +"]"); - } - - static int readExistingNodes(Connection con, Node node) throws IOException, SQLException { - int iCounter = 0; - - NodeList nodes = node.getChildNodes(); - for (int i = 0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - if (node2.getNodeName().equals("node")) { - iCounter++; - importExistingNode(con, node2); - } - else { - iCounter += readExistingNodes(con, node2); - } - } - return iCounter; - } - - static void importExistingNode(Connection con, Node node) throws IOException, SQLException { - String idrefStr = ""; - String latStr = ""; - String lonStr = ""; - String nameStr = ""; - String fixmeStr = ""; - int score = 0; - - NamedNodeMap nodeMap = node.getAttributes(); - if (null != nodeMap) { - for (int j=0; j < nodeMap.getLength(); j++) { - switch (nodeMap.item(j).getNodeName()) { - case "id": - idrefStr = nodeMap.item(j).getNodeValue(); - break; - case "lat": - latStr = nodeMap.item(j).getNodeValue(); - break; - case "lon": - lonStr = nodeMap.item(j).getNodeValue(); - break; - default: - break; - } - } - - NodeList nodes = node.getChildNodes(); - for (int i = 0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - if (node2.getNodeName().equals("tag")) { - NamedNodeMap nodeMap2 = node2.getAttributes(); - if (null != nodeMap2) { - String key = null; - String value = null; - for (int j=0; j < nodeMap2.getLength(); j++) { - if (nodeMap2.item(j).getNodeName().equals("k")) { - key = nodeMap2.item(j).getNodeValue(); - } - else if (nodeMap2.item(j).getNodeName().equals("v")) { - value = nodeMap2.item(j).getNodeValue(); - } - } - - if ((key != null) && key.toLowerCase().equals("name") && (value != null)) { - nameStr = value; - } - if ((key != null) && key.toLowerCase().equals("fixme") && (value != null)) { - fixmeStr = value; - } - } - } - } - - score = 50; - if (nameStr.equals("")) { - score = 1; - } - if (!fixmeStr.equals("")) { - score = 1; - } - - try ( // idref と nameStr をデータベースに格納する - PreparedStatement ps1 = con.prepareStatement("SELECT name,score FROM existing_data WHERE idref=?")) { - ps1.setString(1, idrefStr); - ResultSet rset1 = ps1.executeQuery(); - if (rset1.next()) { - int fixed = rset1.getInt("score"); - if (fixed < score) { - System.out.println("update existing_data : [id:"+ idrefStr +"] score="+ Integer.toString(score) +" "+ nameStr); - try (PreparedStatement ps5 = con.prepareStatement("UPDATE existing_data SET score=? WHERE idref=?")) { - ps5.setInt(1, score); - ps5.setString(2, idrefStr); - ps5.executeUpdate(); } } } - else { - System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")["+ Integer.toString(score) +"]"+ nameStr); - try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name, score) VALUES (?,?,?,?,?)")) { - ps5.setString(1, idrefStr); - ps5.setDouble(2, Double.parseDouble(latStr)); - ps5.setDouble(3, Double.parseDouble(lonStr)); - ps5.setString(4, nameStr); - ps5.setInt(5, score); - ps5.executeUpdate(); + rset1.close(); + ps3.close(); + } + ps1.close(); + } + + // ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する + if (!Busstop.update) { + postgis = new ToPostgis(gmldir); + postgis.outputDb(con); + postgis.close(); + } + } + } + finally { + if (conPost != null) { + DatabaseTool.closeDb(conPost); + } + ow.flush(); + ow.close(); + } + } + + static String[] shiftArgs(String[] args) { + String[] values = new String[args.length - 1]; + for (int i=1; i < args.length; i++) { + values[i - 1] = args[i]; + } + return values; + } + + public static void readExistingFile (Connection con) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + int iCounter = 0; + + DocumentBuilderFactory factory; + DocumentBuilder builder; + Node root; + + iCounter = 0; + factory = DocumentBuilderFactory.newInstance(); + builder = factory.newDocumentBuilder(); + factory.setIgnoringElementContentWhitespace(true); + factory.setIgnoringComments(true); + factory.setValidating(true); + File existingFile = new File(HttpPOST.EXIST_FILE); + root = builder.parse(existingFile); + + iCounter += readExistingNodes(con, root); + System.out.println("既存バス停数["+ iCounter +"]"); + } + + static int readExistingNodes(Connection con, Node node) throws IOException, SQLException { + int iCounter = 0; + + NodeList nodes = node.getChildNodes(); + for (int i = 0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + if (node2.getNodeName().equals("node")) { + iCounter++; + importExistingNode(con, node2); + } + else { + iCounter += readExistingNodes(con, node2); + } + } + return iCounter; + } + + static void importExistingNode(Connection con, Node node) throws IOException, SQLException { + String idrefStr = ""; + String latStr = ""; + String lonStr = ""; + String nameStr = ""; + String fixmeStr = ""; + int score = 0; + + NamedNodeMap nodeMap = node.getAttributes(); + if (null != nodeMap) { + for (int j=0; j < nodeMap.getLength(); j++) { + switch (nodeMap.item(j).getNodeName()) { + case "id": + idrefStr = nodeMap.item(j).getNodeValue(); + break; + case "lat": + latStr = nodeMap.item(j).getNodeValue(); + break; + case "lon": + lonStr = nodeMap.item(j).getNodeValue(); + break; + default: + break; + } + } + + NodeList nodes = node.getChildNodes(); + for (int i = 0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + if (node2.getNodeName().equals("tag")) { + NamedNodeMap nodeMap2 = node2.getAttributes(); + if (null != nodeMap2) { + String key = null; + String value = null; + for (int j=0; j < nodeMap2.getLength(); j++) { + if (nodeMap2.item(j).getNodeName().equals("k")) { + key = nodeMap2.item(j).getNodeValue(); + } + else if (nodeMap2.item(j).getNodeName().equals("v")) { + value = nodeMap2.item(j).getNodeValue(); } } - rset1.close(); + + if ((key != null) && key.toLowerCase().equals("name") && (value != null)) { + nameStr = value; + } + if ((key != null) && key.toLowerCase().equals("fixme") && (value != null)) { + fixmeStr = value; + } } - } - } + } + } + + score = 50; + if (nameStr.equals("")) { + score = 1; + } + if (!fixmeStr.equals("")) { + score = 1; + } + + // idref と nameStr をデータベースに格納する + try (PreparedStatement ps1 = con.prepareStatement("SELECT name,score FROM existing_data WHERE idref=?")) { + ps1.setString(1, idrefStr); + ResultSet rset1 = ps1.executeQuery(); + if (rset1.next()) { + int fixed = rset1.getInt("score"); + if (fixed < score) { + System.out.println("update existing_data : [id:"+ idrefStr +"] score="+ Integer.toString(score) +" "+ nameStr); + try (PreparedStatement ps5 = con.prepareStatement("UPDATE existing_data SET score=? WHERE idref=?")) { + ps5.setInt(1, score); + ps5.setString(2, idrefStr); + ps5.executeUpdate(); + } + } + } + else { + System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")["+ Integer.toString(score) +"]"+ nameStr); + try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name, score) VALUES (?,?,?,?,?)")) { + ps5.setString(1, idrefStr); + ps5.setDouble(2, Double.parseDouble(latStr)); + ps5.setDouble(3, Double.parseDouble(lonStr)); + ps5.setString(4, nameStr); + ps5.setInt(5, score); + ps5.executeUpdate(); + } + } + rset1.close(); + } + } + } - /** - * + /** + * * @param conHsql * @param conPost * @param areacode @@ -408,621 +409,621 @@ * @throws java.io.IOException * @throws javax.xml.parsers.ParserConfigurationException * @throws org.xml.sax.SAXException - */ - public static void updateFile (Connection conHsql, Connection conPost, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; - PreparedStatement ps2; - try (PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) LON, ST_Y(geom) LAT FROM t_busstop WHERE area=?")) { - ps2 = conHsql.prepareStatement("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES (?,?,?,?,?,?)"); - // idref と nameStr をデータベースに格納する - ps1.setInt(1, areacode); - ResultSet rset1 = ps1.executeQuery(); - while (rset1.next()) { - iCounter++; - int idref = rset1.getInt("gid"); - String nameStr = rset1.getString("name"); - int fixed = rset1.getInt("fixed"); - double lat = rset1.getDouble(4); - double lon = rset1.getDouble(5); - - ps2.setInt(1, idref); - ps2.setString(2, nameStr); - ps2.setInt(3, fixed); - ps2.setInt(4, areacode); - ps2.setDouble(5, lon); - ps2.setDouble(6, lat); - System.out.println("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES ("+ idref +",'"+ nameStr +"',"+ fixed +","+ areacode +","+ lat +","+ lon +")"); - ps2.executeUpdate(); + */ + public static void updateFile (Connection conHsql, Connection conPost, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + int iCounter = 0; + PreparedStatement ps2; + try (PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) LON, ST_Y(geom) LAT FROM t_busstop WHERE area=?")) { + ps2 = conHsql.prepareStatement("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES (?,?,?,?,?,?)"); + // idref と nameStr をデータベースに格納する + ps1.setInt(1, areacode); + ResultSet rset1 = ps1.executeQuery(); + while (rset1.next()) { + iCounter++; + int idref = rset1.getInt("gid"); + String nameStr = rset1.getString("name"); + int fixed = rset1.getInt("fixed"); + double lat = rset1.getDouble(4); + double lon = rset1.getDouble(5); + + ps2.setInt(1, idref); + ps2.setString(2, nameStr); + ps2.setInt(3, fixed); + ps2.setInt(4, areacode); + ps2.setDouble(5, lon); + ps2.setDouble(6, lat); + System.out.println("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES ("+ idref +",'"+ nameStr +"',"+ fixed +","+ areacode +","+ lat +","+ lon +")"); + ps2.executeUpdate(); + } + rset1.close(); + } + ps2.close(); + + System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); + } + + /** + * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する + * @param con + * @param iFile + * @param areacode + * @throws FileNotFoundException + * @throws ClassNotFoundException + * @throws SQLException + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + */ + public static void inputFile (Connection con, File iFile, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + int iCounter = 0; + String timeStampStr = null; + + String iStr = iFile.getName(); + + DocumentBuilderFactory factory; + DocumentBuilder builder; + Node root; + + factory = DocumentBuilderFactory.newInstance(); + builder = factory.newDocumentBuilder(); + factory.setIgnoringElementContentWhitespace(true); + factory.setIgnoringComments(true); + factory.setValidating(true); + root = builder.parse(iFile); + + iCounter += showNodes(con, root, iStr.substring(0, iStr.length() - 4), timeStampStr, areacode); + System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); + } + + public static void clearDb(Connection con) throws SQLException { + try (Statement stmt = con.createStatement()) { + long count = stmt.executeUpdate("delete from bus_stop"); + System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。"); + + count = stmt.executeUpdate("delete from existing_data"); + System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。"); + } + } + + public static void initDb(Connection con) throws SQLException { + // 'table.BUS_STOP'を新規に作る + DbBusstop.create(con); + } + + + /** + * + * @param con + * @param node + * @param iFileName // ソースファイル名（拡張子を含まない） + * @param timeStampStr + * @param areacode + * @return + * @throws IOException + * @throws SQLException + */ + public static int showNodes(Connection con, Node node, String iFileName, String timeStampStr, int areacode) throws IOException, SQLException { + int iCounter = 0; + NamedNodeMap nodeMap = node.getAttributes(); + if ( null != nodeMap ) { + for ( int j=0; j < nodeMap.getLength(); j++ ) { + if (nodeMap.item(j).getNodeName().equals("timeStamp")) { + timeStampStr = nodeMap.item(j).getNodeValue(); } - rset1.close(); } - ps2.close(); + } - System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); - } - - /** - * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する - * @param con - * @param iFile - * @param areacode - * @throws FileNotFoundException - * @throws ClassNotFoundException - * @throws SQLException - * @throws IOException - * @throws ParserConfigurationException - * @throws SAXException - */ - public static void inputFile (Connection con, File iFile, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; - String timeStampStr = null; - - String iStr = iFile.getName(); - - DocumentBuilderFactory factory; - DocumentBuilder builder; - Node root; - - factory = DocumentBuilderFactory.newInstance(); - builder = factory.newDocumentBuilder(); - factory.setIgnoringElementContentWhitespace(true); - factory.setIgnoringComments(true); - factory.setValidating(true); - root = builder.parse(iFile); - - iCounter += showNodes(con, root, iStr.substring(0, iStr.length() - 4), timeStampStr, areacode); - System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); - } - - public static void clearDb(Connection con) throws SQLException { - try (Statement stmt = con.createStatement()) { - long count = stmt.executeUpdate("delete from bus_stop"); - System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。"); - - count = stmt.executeUpdate("delete from existing_data"); - System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。"); + NodeList nodes = node.getChildNodes(); + for (int i=0; i bris = new ArrayList(); + NodeList nodes = node.getChildNodes(); + for (int i=0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + if (node2.getNodeName().equals("ksj:POS")) { + NamedNodeMap nodeMap = node2.getAttributes(); + if (null != nodeMap) { + for ( int j=0; j < nodeMap.getLength(); j++ ) { + if (nodeMap.item(j).getNodeName().equals("idref")) { + idrefStr = nodeMap.item(j).getNodeValue(); + System.out.println("found idref='"+ idrefStr +"'"); + break; + } + } } - } - return iCounter; - } + } + else if (node2.getNodeName().equals("ksj:BSN")) { + nameStr = node2.getTextContent(); + } + else if (node2.getNodeName().equals("ksj:BRI")) { + String[] rtn = anaComm(node2); + if (rtn != null) { + bris.add(rtn); + } + } + } - /** - * - * @param con - * @param node - * @param iFileName // ソースファイル名（拡張子を含まない） - * @throws IOException - * @throws SQLException - */ - public static void showED01(Connection con, Node node, String iFileName) throws IOException, SQLException { - String idrefStr = ""; - String nameStr = ""; - PreparedStatement ps1 = con.prepareStatement("SELECT idref FROM bus_stop WHERE idref=?"); - PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (idref,name,ifile) VALUES (?,?,?)"); - try { - ArrayList bris = new ArrayList(); - NodeList nodes = node.getChildNodes(); - for (int i=0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - if (node2.getNodeName().equals("ksj:POS")) { - NamedNodeMap nodeMap = node2.getAttributes(); - if (null != nodeMap) { - for ( int j=0; j < nodeMap.getLength(); j++ ) { - if (nodeMap.item(j).getNodeName().equals("idref")) { - idrefStr = nodeMap.item(j).getNodeValue(); - System.out.println("found idref='"+ idrefStr +"'"); - break; - } - } - } - } - else if (node2.getNodeName().equals("ksj:BSN")) { - nameStr = node2.getTextContent(); - } - else if (node2.getNodeName().equals("ksj:BRI")) { - String[] rtn = anaComm(node2); - if (rtn != null) { - bris.add(rtn); - } - } - } + // idref と nameStr をデータベースに格納する + boolean insert = true; + ps1.setString(1, idrefStr); + ResultSet rset = ps1.executeQuery(); + if (rset.next()) { + insert = false; + } + rset.close(); - // idref と nameStr をデータベースに格納する - boolean insert = true; - ps1.setString(1, idrefStr); - ResultSet rset = ps1.executeQuery(); - if (rset.next()) { - insert = false; - } - rset.close(); + if (insert) { + ps2.setString(1, idrefStr); + ps2.setString(2, nameStr); + ps2.setString(3, iFileName); + System.out.println("INSERT INTO bus_stop (idref,name,ifile) VALUES ('"+ idrefStr +"','"+ nameStr +"','"+ iFileName +"')"); + ps2.executeUpdate(); + } + } + finally { + ps1.close(); + ps2.close(); + } + } - if (insert) { - ps2.setString(1, idrefStr); - ps2.setString(2, nameStr); - ps2.setString(3, iFileName); - System.out.println("INSERT INTO bus_stop (idref,name,ifile) VALUES ('"+ idrefStr +"','"+ nameStr +"','"+ iFileName +"')"); - ps2.executeUpdate(); - } - } - finally { - ps1.close(); - ps2.close(); - } - } + /** + * + * + * 城堀 + * + * + * 1 + * 箱根登山バス + * 小01 + * + * + * + * + * 1 + * 箱根登山バス + * 湯07 + * + * + * + * + * 1 + * 箱根登山バス + * 湯11 + * + * + * + * + * @param con + * @param node + * @param iFileName // ソースファイル名（拡張子を含まない） + * @throws IOException + * @throws SQLException + */ + public static void showBusStop(Connection con, Node node, String iFileName) throws IOException, SQLException { + String idrefStr = ""; + String nameStr = ""; + PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE idref=?"); - /** - * - * - * 城堀 - * - * - * 1 - * 箱根登山バス - * 小01 - * - * - * - * - * 1 - * 箱根登山バス - * 湯07 - * - * - * - * - * 1 - * 箱根登山バス - * 湯11 - * - * - * - * - * @param con - * @param node - * @param iFileName // ソースファイル名（拡張子を含まない） - * @throws IOException - * @throws SQLException - */ - public static void showBusStop(Connection con, Node node, String iFileName) throws IOException, SQLException { - String idrefStr = ""; - String nameStr = ""; - PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE idref=?"); + ArrayList bris = new ArrayList(); + NodeList nodes = node.getChildNodes(); + for (int i=0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + if (node2.getNodeName().equals("ksj:position")) { + NamedNodeMap nodeMap = node2.getAttributes(); + if (null != nodeMap) { + for ( int j=0; j < nodeMap.getLength(); j++ ) { + if (nodeMap.item(j).getNodeName().equals("xlink:href")) { + idrefStr = nodeMap.item(j).getNodeValue(); + idrefStr = idrefStr.substring(1); + System.out.println("found idref='"+ idrefStr +"'"); + break; + } + } + } + } + else if (node2.getNodeName().equals("ksj:busStopName")) { + nameStr = node2.getTextContent(); + } + else if (node2.getNodeName().equals("ksj:busRouteInformation")) { + String[] rtn = anaCommJGD(node2); + if (rtn != null) { + bris.add(rtn); + } + } + } - ArrayList bris = new ArrayList(); - NodeList nodes = node.getChildNodes(); - for (int i=0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - if (node2.getNodeName().equals("ksj:position")) { - NamedNodeMap nodeMap = node2.getAttributes(); - if (null != nodeMap) { - for ( int j=0; j < nodeMap.getLength(); j++ ) { - if (nodeMap.item(j).getNodeName().equals("xlink:href")) { - idrefStr = nodeMap.item(j).getNodeValue(); - idrefStr = idrefStr.substring(1); - System.out.println("found idref='"+ idrefStr +"'"); - break; - } - } - } - } - else if (node2.getNodeName().equals("ksj:busStopName")) { - nameStr = node2.getTextContent(); - } - else if (node2.getNodeName().equals("ksj:busRouteInformation")) { - String[] rtn = anaCommJGD(node2); - if (rtn != null) { - bris.add(rtn); - } - } - } + // idref と nameStr をデータベースに格納する + ps2.setString(1, nameStr); + ps2.setString(2, iFileName); + ps2.setString(3, idrefStr); + ps2.executeUpdate(); + ps2.close(); + } - // idref と nameStr をデータベースに格納する - ps2.setString(1, nameStr); - ps2.setString(2, iFileName); - ps2.setString(3, idrefStr); - ps2.executeUpdate(); - ps2.close(); - } + public static String[] anaComm(Node briNode) { + String[] rtn = new String[3]; + rtn[0] = ""; // corp type + rtn[1] = ""; // course name + rtn[2] = ""; // corp name - public static String[] anaComm(Node briNode) { - String[] rtn = new String[3]; - rtn[0] = ""; // corp type - rtn[1] = ""; // course name - rtn[2] = ""; // corp name + NodeList nodes = briNode.getChildNodes(); + for (int i=0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + if (node2.getNodeName().equals("ksj:BSC")) { + rtn[0] = node2.getTextContent(); + } + else if (node2.getNodeName().equals("ksj:BLN")) { + rtn[1] = node2.getTextContent(); + } + else if (node2.getNodeName().equals("ksj:BOC")) { + rtn[2] = node2.getTextContent(); + } + } + return rtn; + } - NodeList nodes = briNode.getChildNodes(); - for (int i=0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - if (node2.getNodeName().equals("ksj:BSC")) { - rtn[0] = node2.getTextContent(); - } - else if (node2.getNodeName().equals("ksj:BLN")) { - rtn[1] = node2.getTextContent(); - } - else if (node2.getNodeName().equals("ksj:BOC")) { - rtn[2] = node2.getTextContent(); - } - } - return rtn; - } + /** + * + * + * + * 1 + * 箱根登山バス + * 小01 + * + * + * + * @param briNode + * @return + */ + public static String[] anaCommJGD(Node briNode) { + String[] rtn = new String[3]; + int vcnt = 0; - /** - * - * - * - * 1 - * 箱根登山バス - * 小01 - * - * - * - * @param briNode - * @return - */ - public static String[] anaCommJGD(Node briNode) { - String[] rtn = new String[3]; - int vcnt = 0; + NodeList nodes2 = briNode.getChildNodes(); + for (int i=0; i < nodes2.getLength(); i++) { + Node node2 = nodes2.item(i); + if (node2.getNodeName().equals("ksj:BusRouteInformation")) { + NodeList nodes3 = node2.getChildNodes(); + for (int j=0; j < nodes3.getLength(); j++) { + Node node3 = nodes3.item(j); + if (node3.getNodeName().equals("ksj:busType")) { + rtn[0] = new String(node3.getTextContent()); + vcnt++; + } + else if (node3.getNodeName().equals("ksj:busLineName")) { + rtn[1] = new String(node3.getTextContent()); + vcnt++; + } + else if (node3.getNodeName().equals("ksj:busOperationCompany")) { + rtn[2] = new String(node3.getTextContent()); + vcnt++; + } + } + } + } - NodeList nodes2 = briNode.getChildNodes(); - for (int i=0; i < nodes2.getLength(); i++) { - Node node2 = nodes2.item(i); - if (node2.getNodeName().equals("ksj:BusRouteInformation")) { - NodeList nodes3 = node2.getChildNodes(); - for (int j=0; j < nodes3.getLength(); j++) { - Node node3 = nodes3.item(j); - if (node3.getNodeName().equals("ksj:busType")) { - rtn[0] = new String(node3.getTextContent()); - vcnt++; - } - else if (node3.getNodeName().equals("ksj:busLineName")) { - rtn[1] = new String(node3.getTextContent()); - vcnt++; - } - else if (node3.getNodeName().equals("ksj:busOperationCompany")) { - rtn[2] = new String(node3.getTextContent()); - vcnt++; - } - } - } - } + if (vcnt > 0) { + return rtn; + } + return null; + } - if (vcnt > 0) { - return rtn; - } - return null; - } + public static void showGmPoint(Connection con, Node node) throws IOException, SQLException { + String positionStr = ""; + String latStr = ""; + String lonStr = ""; + String idStr = ""; - public static void showGmPoint(Connection con, Node node) throws IOException, SQLException { - String positionStr = ""; - String latStr = ""; - String lonStr = ""; - String idStr = ""; + NamedNodeMap nodeMap = node.getAttributes(); + if ( null != nodeMap ) { + for ( int j=0; j + * 35.14591397 139.10569573 + * + * + * @param con + * @param node + * @param areacode + * @throws IOException + * @throws SQLException + */ + public static void showGmlPoint(Connection con, Node node, int areacode) throws IOException, SQLException { + String positionStr = ""; + String latStr = ""; + String lonStr = ""; + String idStr = ""; - /** - * - * 35.14591397 139.10569573 - * - * - * @param con - * @param node - * @param areacode - * @throws IOException - * @throws SQLException - */ - public static void showGmlPoint(Connection con, Node node, int areacode) throws IOException, SQLException { - String positionStr = ""; - String latStr = ""; - String lonStr = ""; - String idStr = ""; + NamedNodeMap nodeMap = node.getAttributes(); + if ( null != nodeMap ) { + for ( int j=0; j