diff --git a/output.xml b/output.xml new file mode 100644 index 0000000..e69de29 --- /dev/null +++ b/output.xml diff --git a/src/ImportGML_FUEL.sh b/src/ImportGML_FUEL.sh index b51e471..1aca98b 100755 --- a/src/ImportGML_FUEL.sh +++ b/src/ImportGML_FUEL.sh @@ -4,3 +4,5 @@ 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 +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 \ No newline at end of file diff --git a/src/osm/jp/api/HttpPOST.java b/src/osm/jp/api/HttpPOST.java index da4f204..725c0ed 100644 --- a/src/osm/jp/api/HttpPOST.java +++ b/src/osm/jp/api/HttpPOST.java @@ -15,30 +15,47 @@ //public static String host = "http://api06.dev.openstreetmap.org"; //public static String host = "http://api.openstreetmap.org"; public static String host = "http://overpass-api.de"; + public static final String EXIST_FILE = "exist.osm.xml"; public static void main(String[] args) throws MalformedURLException, ProtocolException, IOException { double minlat = 35.13d; double maxlat = 35.66d; double minlon = 138.99d; double maxlon = 139.79d; - getCapabilities(new File("output.xml"), "highway", "bus_stop", minlat, maxlat, minlon, maxlon); - getCapabilities(new File("output.xml"), "highway", "disused:bus_stop", minlat, maxlat, minlon, maxlon); - getCapabilities(new File("output.xml"), "amenity", "bus_station", minlat, maxlat, minlon, maxlon); - getCapabilities(new File("output.xml"), "public_transport", "platform", minlat, maxlat, minlon, maxlon); - getCapabilities(new File("output.xml"), "public_transport", "stop_position", minlat, maxlat, minlon, maxlon); + //getCapabilities(new File("output.xml"), "highway", "bus_stop", minlat, maxlat, minlon, maxlon); + //getCapabilities(new File("output.xml"), "highway", "disused:bus_stop", minlat, maxlat, minlon, maxlon); + //getCapabilities(new File("output.xml"), "amenity", "bus_station", minlat, maxlat, minlon, maxlon); + //getCapabilities(new File("output.xml"), "public_transport", "platform", minlat, maxlat, minlon, maxlon); + getCapabilities("public_transport", "stop_position", minlat, maxlat, minlon, maxlon, "node"); + getCapabilities("amenity", "fuel", minlat, maxlat, minlon, maxlon, "way"); } - public static void getCapabilities(File oFile, String key, String value, double minLat, double maxLat, double minLon, double maxLon) throws MalformedURLException, ProtocolException, IOException { - if (oFile.isFile()) { - oFile.delete(); - } + public static void getCapabilities(String key, String value, double minLat, double maxLat, double minLon, double maxLon) throws MalformedURLException, ProtocolException, IOException { + getCapabilities(key, value, minLat, maxLat, minLon, maxLon, "node"); + } - BufferedWriter hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(oFile), "UTF-8")); - getCapabilities(hw, key, value, minLat, maxLat, minLon, maxLon); - hw.close(); + public static void getCapabilities(String key, String value, double minLat, double maxLat, double minLon, double maxLon, String type) throws MalformedURLException, ProtocolException, IOException { + StringBuilder queryText = new StringBuilder(); + queryText.append(""); + queryText.append(" "); + queryText.append(" "); + queryText.append(" "); + queryText.append(" "); + queryText.append(" "); + queryText.append(" "); + queryText.append(" "); + queryText.append(""); + getQuery(queryText.toString()); } - - public static void getCapabilities(BufferedWriter hw, String key, String value, double minLat, double maxLat, double minLon, double maxLon) throws MalformedURLException, ProtocolException, IOException { + + /** + * + * @param queryText クエリテキスト（Overpass_API/Overpass_QL） + * @throws MalformedURLException + * @throws ProtocolException + * @throws IOException + */ + public static void getQuery(String queryText) throws MalformedURLException, ProtocolException, IOException { System.out.println(host + "/api/interpreter"); URL url = new URL(host + "/api/interpreter"); int responsecode = 0; @@ -52,18 +69,10 @@ urlconn.setRequestProperty("Content-Type","text/xml;charset=utf-8"); urlconn.connect(); - // 送信 - PrintWriter pw = new PrintWriter(new BufferedWriter(new OutputStreamWriter(urlconn.getOutputStream(), "utf-8"))); - outputWriter(pw, ""); - outputWriter(pw, " "); - outputWriter(pw, " "); - outputWriter(pw, " "); - outputWriter(pw, " "); - outputWriter(pw, " "); - outputWriter(pw, " "); - outputWriter(pw, " "); - outputWriter(pw, ""); - pw.close(); // closeで送信完了 + try (PrintWriter pw = new PrintWriter(new BufferedWriter(new OutputStreamWriter(urlconn.getOutputStream(), "utf-8")))) { + outputWriter(pw, queryText); + pw.flush(); + } try { TimeUnit.SECONDS.sleep(1); @@ -91,17 +100,22 @@ } else { System.out.println("\n---- ボディ ----"); - - BufferedReader reader = new BufferedReader(new InputStreamReader(urlconn.getInputStream(), "UTF-8")); - while (true) { - String line = reader.readLine(); - if (line == null) { - break; + + File oFile = new File(HttpPOST.EXIST_FILE); + oFile.deleteOnExit(); + try (BufferedWriter hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(oFile), "UTF-8"))) { + try (BufferedReader reader = new BufferedReader(new InputStreamReader(urlconn.getInputStream(), "UTF-8"))) { + while (true) { + String line = reader.readLine(); + if (line == null) { + break; + } + hw.write(line); + hw.newLine(); + } + hw.flush(); } - hw.write(line); - hw.newLine(); } - reader.close(); } urlconn.disconnect(); try { @@ -110,7 +124,6 @@ } while ((responsecode == 429) || (responsecode == 504)); } - public static void outputWriter(PrintWriter pw, String text) { System.out.println("\t" + text); diff --git a/src/osm/jp/api/Japan.java b/src/osm/jp/api/Japan.java index 6a45297..d226c6e 100644 --- a/src/osm/jp/api/Japan.java +++ b/src/osm/jp/api/Japan.java @@ -5,20 +5,23 @@ public double maxLat; public double minLon; public double maxLon; + public static String[] areaArgs = {"全国","北海道","青森県","岩手県","宮城県","秋田県","山形県","福島県","茨城県","栃木県","群馬県","埼玉県","千葉県","東京都","神奈川県","新潟県","富山県","石川県","福井県","山梨県","長野県","岐阜県","静岡県","愛知県","三重県","滋賀県","京都府","大阪府","兵庫県","奈良県","和歌山県","鳥取県","島根県","岡山県","広島県","山口県","徳島県","香川県","愛媛県","高知県","福岡県","佐賀県","長崎県","熊本県","大分県","宮崎県","鹿児島県","沖縄県"}; - public Japan(double minLat, double maxLat, double minLon, double maxLon) { - this.minLat = minLat; - this.maxLat = maxLat; - this.minLon = minLon; - this.maxLon = maxLon; - } - public Japan(double minLat, double minLon, double delta) { + public Japan(double minLon, double minLat, double delta) { this.minLat = minLat; this.maxLat = minLat + delta; this.minLon = minLon; this.maxLon = minLon + delta; } + + public String getSNWE() { + return (this.minLat +","+ this.maxLat +","+ this.minLon +","+ this.maxLon); + } + + public String getSWNE() { + return (this.minLat +","+ this.minLon +","+ this.maxLat +","+ this.maxLon); + } public static Japan[] all = new Japan[] { new Japan(140D, 45D, 1D), diff --git a/src/osm/jp/api/OsmnodeArea.java b/src/osm/jp/api/OsmnodeArea.java new file mode 100644 index 0000000..631a7d9 --- /dev/null +++ b/src/osm/jp/api/OsmnodeArea.java @@ -0,0 +1,38 @@ +/* + * To change this license header, choose License Headers in Project Properties. + * To change this template file, choose Tools | Templates + * and open the template in the editor. + */ +package osm.jp.api; + +import org.w3c.dom.Node; + +/** + * OSM.xml の「Area(way)」ノード + * 例） + * 
{@code 
+ *  
+ *   
+ *   
+ *   
+ *   
+ *   
+ *   
+ *   
+ *   
+ *   
+ *   
+ *   
+ * 
+ * }
+ * + * @author yuu + */ +public class OsmnodeArea { + Node areanode = null; + + public OsmnodeArea(Node node) { + this.areanode = node; + } + +} diff --git a/src/osm/jp/api/OsmnodeNd.java b/src/osm/jp/api/OsmnodeNd.java new file mode 100644 index 0000000..97e5153 --- /dev/null +++ b/src/osm/jp/api/OsmnodeNd.java @@ -0,0 +1,42 @@ +/* + * To change this license header, choose License Headers in Project Properties. + * To change this template file, choose Tools | Templates + * and open the template in the editor. + */ +package osm.jp.api; + +import java.sql.Connection; +import org.w3c.dom.NamedNodeMap; +import org.w3c.dom.Node; + +/** + * OSM.xml の「Area(way)」ノード + * 例） + * 
{@code 
+ *   
+ *   
+ *   
+ *   
+ * }
+ * + * @author yuu + */ +public class OsmnodeNd { + Node ndnode = null; + String ref = null; + + public OsmnodeNd(Node node) { + this.ndnode = node; + NamedNodeMap nodeMap2 = this.ndnode.getAttributes(); + for (int j=0; j < nodeMap2.getLength(); j++) { + if (nodeMap2.item(j).getNodeName().equals("ref")) { + ref = nodeMap2.item(j).getNodeValue(); + } + } + } + + public String getRef() { + return ref; + } + +} diff --git a/src/osm/jp/api/OsmnodeTag.java b/src/osm/jp/api/OsmnodeTag.java new file mode 100644 index 0000000..526d5cb --- /dev/null +++ b/src/osm/jp/api/OsmnodeTag.java @@ -0,0 +1,58 @@ +/* + * To change this license header, choose License Headers in Project Properties. + * To change this template file, choose Tools | Templates + * and open the template in the editor. + */ +package osm.jp.api; + +import org.w3c.dom.NamedNodeMap; +import org.w3c.dom.Node; + +/** + * OSM.xml の「tag」ノード + * 例） + * 
{@code 
+ *   
+ *   
+ *   
+ *   
+ *   
+ * }
+ * + * @author yuu + */ +public class OsmnodeTag { + Node tagnode = null; + + public OsmnodeTag(Node node) { + this.tagnode = node; + } + + /** + * + * @param k keyは大文字小文字を区別しません。小文字で指定すること + * @return + */ + public String getValue(String k) { + NamedNodeMap nodeMap2 = this.tagnode.getAttributes(); + if (null == nodeMap2) { + return null; + } + + 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(k)) { + return value; + } + return null; + } +} diff --git a/src/osm/jp/api/Position.java b/src/osm/jp/api/Position.java new file mode 100644 index 0000000..ea2d52f --- /dev/null +++ b/src/osm/jp/api/Position.java @@ -0,0 +1,20 @@ +/* + * To change this license header, choose License Headers in Project Properties. + * To change this template file, choose Tools | Templates + * and open the template in the editor. + */ +package osm.jp.api; + +/** + * + * @author yuu + */ +public class Position { + public double lon; + public double lat; + + public Position(double lat, double lon) { + this.lat = lat; + this.lon = lon; + } +} diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index d861539..5b000ae 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -76,7 +76,7 @@ */ if (Busstop.update && !Busstop.noget) { Busstop.initDb(con); - File existingFile = new File("existing.xml"); + File existingFile = new File(HttpPOST.EXIST_FILE); getJapanCapabilities(con, existingFile); } @@ -102,16 +102,16 @@ public static void getJapanCapabilities(Connection con, File oFile) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { for (Japan all1 : all) { - HttpPOST.getCapabilities(oFile, "highway", "bus_stop", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con, oFile); - HttpPOST.getCapabilities(oFile, "highway", "disused:bus_stop", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con, oFile); - HttpPOST.getCapabilities(oFile, "amenity", "bus_station", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con, oFile); - HttpPOST.getCapabilities(oFile, "public_transport", "platform", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con, oFile); - HttpPOST.getCapabilities(oFile, "public_transport", "stop_position", all1.minLat, all1.maxLat, all1.minLon, all1.maxLon); - readExistingFile(con, oFile); + 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); } } @@ -268,7 +268,7 @@ return values; } - public static void readExistingFile (Connection con, File existingFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + public static void readExistingFile (Connection con) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { int iCounter = 0; DocumentBuilderFactory factory; @@ -281,6 +281,7 @@ factory.setIgnoringElementContentWhitespace(true); factory.setIgnoringComments(true); factory.setValidating(true); + File existingFile = new File(HttpPOST.EXIST_FILE); root = builder.parse(existingFile); iCounter += readExistingNodes(con, root); @@ -803,6 +804,7 @@ * * @param con * @param node + * @param areacode * @throws IOException * @throws SQLException */ diff --git a/src/osm/jp/coverage/busstop/Coverage.java b/src/osm/jp/coverage/busstop/Coverage.java index fe6e939..336212a 100644 --- a/src/osm/jp/coverage/busstop/Coverage.java +++ b/src/osm/jp/coverage/busstop/Coverage.java @@ -14,9 +14,9 @@ import jp.co.areaweb.tools.csv.CsvFile; import jp.co.areaweb.tools.csv.CsvRecord; import jp.co.areaweb.tools.database.DatabaseTool; +import osm.jp.api.Japan; public class Coverage { - static String[] areaArgs = {"全国","北海道","青森県","岩手県","宮城県","秋田県","山形県","福島県","茨城県","栃木県","群馬県","埼玉県","千葉県","東京都","神奈川県","新潟県","富山県","石川県","福井県","山梨県","長野県","岐阜県","静岡県","愛知県","三重県","滋賀県","京都府","大阪府","兵庫県","奈良県","和歌山県","鳥取県","島根県","岡山県","広島県","山口県","徳島県","香川県","愛媛県","高知県","福岡県","佐賀県","長崎県","熊本県","大分県","宮崎県","鹿児島県","沖縄県"}; public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException { @@ -40,10 +40,10 @@ try { // 都道府県名(name)の設定 String sqlStr = "INSERT INTO coverage (area, name) VALUES(?,?);"; - for (int i=0; i < areaArgs.length; i++) { + for (int i=0; i < Japan.areaArgs.length; i++) { PreparedStatement ps = con.prepareStatement(sqlStr); ps.setInt(1, i); - ps.setString(2, areaArgs[i]); + ps.setString(2, Japan.areaArgs[i]); try { ps.executeUpdate(); } @@ -101,7 +101,7 @@ // 分母(denominator)、分子(molecule)、カバレッジ(Lv) を記入 // 都道府県名の設定 - for (int i = 1; i < areaArgs.length; i++) { + for (int i = 1; i < Japan.areaArgs.length; i++) { ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE area=?;"); try { ps2.setInt(1, i); diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index 7ead54c..8a761c3 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -10,141 +10,132 @@ import jp.co.areaweb.tools.database.*; public class DbBusstop { - File inputFile; - String filter = ""; - int iCounter = 0; - String urlStr = ""; - Connection con; - String timeStampStr = null; - File dir = null; + File inputFile; + String filter = ""; + int iCounter = 0; + String urlStr = ""; + Connection con; + String timeStampStr = null; + File dir = null; - /** メイン - * @throws IOException - * @throws SQLException - * @throws ClassNotFoundException - * @throws FileNotFoundException */ - public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException - { - Connection con = null; - try { - con = DatabaseTool.openDb("database"); - DbBusstop.export(con); - } - finally { - if (con != null) { - DatabaseTool.closeDb(con); - } - } - } + /** メイン + * @param args + * @throws IOException + * @throws SQLException + * @throws ClassNotFoundException + * @throws FileNotFoundException */ + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException + { + Connection con = null; + try { + con = DatabaseTool.openDb("database"); + DbBusstop.export(con); + } + finally { + if (con != null) { + DatabaseTool.closeDb(con); + } + } + } - /** - * 'table.BUS_STOP'を新規に作る - * 既にテーブルが存在する時には何もしない - * @param con - * @throws SQLException - */ - public static void create(Connection con) throws SQLException { - String createSt; + /** + * 'table.BUS_STOP'を新規に作る + * 既にテーブルが存在する時には何もしない + * @param con + * @throws SQLException + */ + public static void create(Connection con) throws SQLException { + String createSt; - // 'table.BUS_STOP'を新規に作る - drop(con, "bus_stop"); - createSt = "CREATE TABLE bus_stop (idref VARCHAR(12) NOT NULL, name VARCHAR(128), kana VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, area INT, ifile VARCHAR(128), CONSTRAINT bus_stop_pk PRIMARY KEY(idref));"; - create(con, createSt); + // 'table.BUS_STOP'を新規に作る + drop(con, "bus_stop"); + createSt = "CREATE TABLE bus_stop (idref VARCHAR(12) NOT NULL, name VARCHAR(128), kana VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, area INT, ifile VARCHAR(128), CONSTRAINT bus_stop_pk PRIMARY KEY(idref));"; + create(con, createSt); - drop(con, "existing_data"); - createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));"; - create(con, createSt); - - drop(con, "coverage"); - createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);"; - create(con, createSt); - } + drop(con, "existing_data"); + createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));"; + create(con, createSt); - public static void create(Connection con, String createsql) throws SQLException { - System.out.println(createsql); - PreparedStatement ps = con.prepareStatement(createsql); - try { - ps.executeUpdate(); - } - catch (SQLSyntaxErrorException e) { - System.out.println("83:"+ e.toString()); - if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: object name already exists:"))) { - throw e; - } - } - finally { - ps.close(); - } - } + drop(con, "coverage"); + createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);"; + create(con, createSt); + } - /** - * 'table.BUS_STOP'を削除する - * @param con - * @throws SQLException - */ - public static void drop(Connection con, String tableName) throws SQLException { - String createSt = "DROP TABLE "+ tableName +";"; - System.out.println(createSt); - PreparedStatement ps = con.prepareStatement(createSt); - try { - ps.executeUpdate(); - } - catch (SQLSyntaxErrorException e) { - System.out.println("107:"+ e.toString()); - if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) { - throw e; - } - } - finally { - ps.close(); - } - } - - /** - * 'table.BUS_STOP'の内容を空にする - * @param con - * @throws SQLException - */ - public static void clear(Connection con, String tableName) throws SQLException { - String createSt = "DELETE FROM "+ tableName +";"; - System.out.println(createSt); - PreparedStatement ps = con.prepareStatement(createSt); - try { - ps.executeUpdate(); - } - catch (SQLSyntaxErrorException e) { - System.out.println("107:"+ e.toString()); - if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) { - throw e; - } - } - finally { - ps.close(); - } - } - - /** - * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する - * @param con - */ - public static void export(Connection con) { - try { - System.out.println("TABLE: BUS_STOP"); - System.out.println("\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\""); - PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed,ifile FROM bus_stop"); - ResultSet rset8 = ps8.executeQuery(); - while (rset8.next()) { - String name = rset8.getString(1); - Double lat = rset8.getDouble(2); - Double lon = rset8.getDouble(3); - int fixed = rset8.getInt(4); - String ifile = rset8.getString(5); - System.out.println("\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\""); - } - rset8.close(); - } - catch (SQLException e) { - e.printStackTrace(); - } - } + public static void create(Connection con, String createsql) throws SQLException { + System.out.println(createsql); + try (PreparedStatement ps = con.prepareStatement(createsql)) { + ps.executeUpdate(); + } + catch (SQLSyntaxErrorException e) { + System.out.println("83:"+ e.toString()); + if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: object name already exists:"))) { + throw e; + } + } + } + + /** + * 'table.BUS_STOP'を削除する + * @param con + * @param tableName + * @throws SQLException + */ + public static void drop(Connection con, String tableName) throws SQLException { + String createSt = "DROP TABLE "+ tableName +";"; + System.out.println(createSt); + try (PreparedStatement ps = con.prepareStatement(createSt)) { + ps.executeUpdate(); + } + catch (SQLSyntaxErrorException e) { + System.out.println("107:"+ e.toString()); + if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) { + throw e; + } + } + } + + /** + * 'table.BUS_STOP'の内容を空にする + * @param con + * @param tableName + * @throws SQLException + */ + public static void clear(Connection con, String tableName) throws SQLException { + String createSt = "DELETE FROM "+ tableName +";"; + System.out.println(createSt); + try (PreparedStatement ps = con.prepareStatement(createSt)) { + ps.executeUpdate(); + } + catch (SQLSyntaxErrorException e) { + System.out.println("107:"+ e.toString()); + if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) { + throw e; + } + } + } + + /** + * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する + * @param con + */ + public static void export(Connection con) { + try { + System.out.println("TABLE: BUS_STOP"); + System.out.println("\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\""); + PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed,ifile FROM bus_stop"); + try (ResultSet rset8 = ps8.executeQuery()) { + while (rset8.next()) { + String name = rset8.getString(1); + Double lat = rset8.getDouble(2); + Double lon = rset8.getDouble(3); + int fixed = rset8.getInt(4); + String ifile = rset8.getString(5); + System.out.println("\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\""); + } + } + } + catch (SQLException e) { + e.printStackTrace(); + } + } } \ No newline at end of file diff --git a/src/osm/jp/coverage/busstop/NagoyaBusstop.java b/src/osm/jp/coverage/busstop/NagoyaBusstop.java index 097294a..e2079a7 100644 --- a/src/osm/jp/coverage/busstop/NagoyaBusstop.java +++ b/src/osm/jp/coverage/busstop/NagoyaBusstop.java @@ -44,8 +44,9 @@ /** * メイン * - * java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop