diff --git a/src/osm/jp/api/KatakanaToHiragana.java b/src/osm/jp/api/KatakanaToHiragana.java new file mode 100644 index 0000000..e5d9057 --- /dev/null +++ b/src/osm/jp/api/KatakanaToHiragana.java @@ -0,0 +1,35 @@ +package osm.jp.api; + +public class KatakanaToHiragana { + /* + * ぁあぃいぅうぇえぉおかがきぎくぐけげこごさざしじすずせぜそぞ + * ただちぢっつづてでとどなにぬねのはばぱひびぴふぶぷへべぺほぼぽ + * まみむめもゃやゅゆょよらりるれろゎわゐゑをん + * + * ァアィイゥウェエォオカガキギクグケゲコゴサザシジスズセゼソゾ + * タダチヂッツヅテデトドナニヌネノハバパヒビピフブプヘベペホボポ + * マミムメモャヤュユョヨラリルレロヮワヰヱヲンヴヵヶ + */ + public static String hiraganaToKatakana(String s) { + StringBuffer sb = new StringBuffer(s); + for (int i = 0; i < sb.length(); i++) { + char c = sb.charAt(i); + if (c >= 'ァ' && c <= 'ン') { + sb.setCharAt(i, (char)(c - 'ァ' + 'ぁ')); + } else if (c == 'ヵ') { + sb.setCharAt(i, 'か'); + } else if (c == 'ヶ') { + sb.setCharAt(i, 'け'); + } else if (c == 'ヴ') { + sb.setCharAt(i, 'う'); + sb.insert(i + 1, '゛'); + i++; + } + } + return sb.toString(); + } + + public static void main(String[] args) { + System.out.println(hiraganaToKatakana("ジャヴァ・プログラミング")); + } +} diff --git a/src/osm/jp/coverage/busstop/NagoyaBusstop.java b/src/osm/jp/coverage/busstop/NagoyaBusstop.java index d97ac91..d3a9da2 100644 --- a/src/osm/jp/coverage/busstop/NagoyaBusstop.java +++ b/src/osm/jp/coverage/busstop/NagoyaBusstop.java @@ -1,5 +1,8 @@ package osm.jp.coverage.busstop; import osm.jp.api.HttpPOST; +import osm.jp.api.KatakanaToHiragana; +import osm.jp.postgis.Do_sqlfiles; + import javax.xml.parsers.*; import javax.xml.transform.OutputKeys; import javax.xml.transform.Transformer; @@ -46,6 +49,11 @@ * @throws Exception */ public static void main(String[] args) throws Exception { + File outFile = new File("update.sql"); + if (outFile.isFile()) { + outFile.delete(); + } + // HSQLディレクトリがなければ作る File dbdir = new File("database"); if (!dbdir.isDirectory()) { @@ -56,138 +64,28 @@ NagoyaBusstop.initDb(con); try { - /** - * 都道府県ごとのGMLディレクトリの処理 - */ + NagoyaBusstop.clearDb(con); new NagoyaBusstop(con, new File("TRF0009630..csv")); + + // ローカルデータベース内の情報をPostGIS用の'ToPostgis.SQL_FILE_NAME'「./busstop.sql」に出力する + ToPostgis postgis = new ToPostgis(new File(".")); + postgis.outputDb(con); + postgis.close(); } finally { DatabaseTool.closeDb(con); } - } - - /** - * 個別の都道府県「GMLディレクトリ」を処理 - * - * @param con - * @param gmldir - * @throws Exception - */ - public NagoyaBusstop(Connection con, File csvFile) throws Exception { - ToPostgis postgis = null; + Connection conPost = null; - - File outFile = new File("update.sql"); - if (outFile.isFile()) { - outFile.delete(); - } - try { - NagoyaBusstop.clearDb(con); - inputFile(con, csvFile); - - /** - * 既存のOSMバス停を読み込む - * --> 'existing.xml' - */ - File existingFile = new File("existing.xml"); + conPost = DatabaseTool.openDb("postgis"); + Do_sqlfiles.sqlExecute(conPost, "DROP TABLE nagoya_busstop;"); + Do_sqlfiles.sqlExecute(conPost, "CREATE TABLE nagoya_busstop (gid SERIAL PRIMARY KEY, name text, kana text, fixed integer, area integer, geom GEOMETRY(POINT, 4612));"); + Do_sqlfiles.sqlExecute(conPost, "CREATE INDEX ix_nagoya_busstop_geom ON nagoya_busstop USING GiST (geom);"); + Do_sqlfiles.sqlExecute(conPost, "commit;"); - /** - * インポートしたデータの緯度経度範囲を読み取る - */ - double maxLat = -90.0D; - double minLat = 90.0D; - double maxLon = -180.0D; - double minLon = 180.0D; - PreparedStatement ps8; - ps8 = con.prepareStatement("SELECT lat,lon FROM bus_stop"); - ResultSet rset8 = ps8.executeQuery(); - while (rset8.next()) { - Double lat = rset8.getDouble("lat"); - Double lon = rset8.getDouble("lon"); - - if (lat > maxLat) { - maxLat = lat; - } - if (lon > maxLon) { - maxLon = lon; - } - if (lat < minLat) { - minLat = lat; - } - if (lon < minLon) { - minLon = lon; - } - } - rset8.close(); - - /** - * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する - */ - HttpPOST.getCapabilities(existingFile, "highway", "bus_stop", minLat, maxLat, minLon, maxLon); - readExistingFile(con, existingFile); - HttpPOST.getCapabilities(existingFile, "disused:highway", "bus_stop", minLat, maxLat, minLon, maxLon); - readExistingFile(con, existingFile); - HttpPOST.getCapabilities(existingFile, "public_transport", "platform", minLat, maxLat, minLon, maxLon); - readExistingFile(con, existingFile); - - PreparedStatement ps1; - ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop"); - PreparedStatement ps2 = con.prepareStatement("SELECT count(*) 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 count(*) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); - 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"); - - // 指定の緯度経度を中心とする半径5x2m四方の矩形領域 - System.out.print(idref + "("+ name + ") ...."); - RectArea rect = new RectArea(lat, lon, NEER); // 25m 四方 - ps2.setDouble(1, rect.minlat); - ps2.setDouble(2, rect.maxlat); - ps2.setDouble(3, rect.minlon); - ps2.setDouble(4, rect.maxlon); - ResultSet rset2 = ps2.executeQuery(); - if (rset2.next()) { - int score = rset2.getInt(1); - if (score > 0) { - score = 100; - } - else { - rect = new RectArea(lat, lon, NEER*2); // 50m 四方 - ps4.setDouble(1, rect.minlat); - ps4.setDouble(2, rect.maxlat); - ps4.setDouble(3, rect.minlon); - ps4.setDouble(4, rect.maxlon); - ResultSet rset4 = ps4.executeQuery(); - if (rset4.next()) { - score = rset4.getInt(1); - if (score > 0) { - score = 50; - } - } - rset4.close(); - } - System.out.println("."+ score); - ps3.setInt(1, score); - ps3.setString(2, idref); - ps3.executeUpdate(); - } - rset2.close(); - } - rset1.close(); - ps4.close(); - ps3.close(); - ps2.close(); - ps1.close(); - - // ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する - postgis = new ToPostgis(new File(".")); - postgis.outputDb(con); - postgis.close(); + Do_sqlfiles.sqlExecute(conPost, new File(ToPostgis.SQL_FILE_NAME)); + outputDb2Osm(conPost, new File("Nagoya_busstop.osm")); } finally { if (conPost != null) { @@ -195,6 +93,114 @@ } } } + + /** + * + * @param con + * @param gmldir + * @throws Exception + */ + public NagoyaBusstop(Connection con, File csvFile) throws Exception { + inputFile(con, csvFile); + + /** + * 既存のOSMバス停を読み込む + * --> 'existing.xml' + */ + File existingFile = new File("existing.xml"); + + /** + * インポートしたデータの緯度経度範囲を読み取る + */ + double maxLon = -180.0D; + double minLon = 180.0D; + double maxLat = -90.0D; + double minLat = 90.0D; + PreparedStatement ps8; + ps8 = con.prepareStatement("SELECT lat,lon FROM bus_stop"); + ResultSet rset8 = ps8.executeQuery(); + while (rset8.next()) { + Double lat = rset8.getDouble("lat"); + Double lon = rset8.getDouble("lon"); + + if (lat > maxLat) { + maxLat = lat; + } + if (lon > maxLon) { + maxLon = lon; + } + if (lat < minLat) { + minLat = lat; + } + if (lon < minLon) { + minLon = lon; + } + } + rset8.close(); + + /** + * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する + */ + HttpPOST.getCapabilities(existingFile, "highway", "bus_stop", minLon, maxLon, minLat, maxLat); + readExistingFile(con, existingFile); + HttpPOST.getCapabilities(existingFile, "disused:highway", "bus_stop", minLon, maxLon, minLat, maxLat); + readExistingFile(con, existingFile); + HttpPOST.getCapabilities(existingFile, "public_transport", "platform", minLon, maxLon, minLat, maxLat); + readExistingFile(con, existingFile); + + PreparedStatement ps1; + ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop"); + PreparedStatement ps2 = con.prepareStatement("SELECT count(*) 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 count(*) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); + 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"); + + // 指定の緯度経度を中心とする半径5x2m四方の矩形領域 + System.out.print(idref + "("+ name + ") ...."); + RectArea rect = new RectArea(lat, lon, NEER); // 25m 四方 + ps2.setDouble(1, rect.minlat); + ps2.setDouble(2, rect.maxlat); + ps2.setDouble(3, rect.minlon); + ps2.setDouble(4, rect.maxlon); + ResultSet rset2 = ps2.executeQuery(); + if (rset2.next()) { + int score = rset2.getInt(1); + if (score > 0) { + score = 100; + } + else { + rect = new RectArea(lat, lon, NEER*2); // 50m 四方 + ps4.setDouble(1, rect.minlat); + ps4.setDouble(2, rect.maxlat); + ps4.setDouble(3, rect.minlon); + ps4.setDouble(4, rect.maxlon); + ResultSet rset4 = ps4.executeQuery(); + if (rset4.next()) { + score = rset4.getInt(1); + if (score > 0) { + score = 50; + } + } + rset4.close(); + } + System.out.println("."+ score); + ps3.setInt(1, score); + ps3.setString(2, idref); + ps3.executeUpdate(); + } + rset2.close(); + } + rset1.close(); + ps4.close(); + ps3.close(); + ps2.close(); + ps1.close(); + } static String[] shiftArgs(String[] args) { String[] values = new String[args.length - 1]; @@ -374,13 +380,6 @@ count = stmt.executeUpdate("delete from existing_data"); System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。"); - - count = stmt.executeUpdate("delete from bus_course"); - System.out.println("'Database.bus_course'から "+ count +" 件のデータを削除しました。"); - - count = stmt.executeUpdate("delete from bus_ref"); - System.out.println("'Database.bus_ref'から "+ count +" 件のデータを削除しました。"); - stmt.close(); } public static void initDb(Connection con) throws SQLException { @@ -421,26 +420,18 @@ * @throws ParserConfigurationException * @throws SAXException */ - public static void outputDb2html(Connection con, File dir) throws IOException, SQLException, SAXException, ParserConfigurationException, TransformerException { + public static void outputDb2Osm(Connection con, File osmFile) throws IOException, SQLException, SAXException, ParserConfigurationException, TransformerException { String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis())); - String iCode = dir.getName(); BufferedWriter ow = null; - BufferedWriter gw = null; - PreparedStatement ps8 = con.prepareStatement("SELECT idref,name,kana,lat,lon,fixed FROM bus_stop WHERE fixed=0"); + PreparedStatement ps8 = con.prepareStatement("SELECT gid,name,kana,ST_Y(geom) AS lat,ST_X(geom) AS lon,fixed FROM nagoya_busstop WHERE fixed=0"); { - File osmFile = new File(dir, "Nagoya_busstop.osm"); - - int stopCount = 0; - int fixedCount = 0; - int unfixedCount = 0; - // OSM file header ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8")); - ow.write(""); + ow.write(""); ow.newLine(); - ow.write(""); + ow.write(""); ow.newLine(); double maxLat = -180.0; @@ -451,13 +442,11 @@ { ResultSet rset8 = ps8.executeQuery(); while (rset8.next()) { - stopCount++; - String idref = rset8.getString("idref"); + long idref = rset8.getLong("gid"); String name = rset8.getString("name"); String kana = rset8.getString("kana"); Double lat = rset8.getDouble("lat"); Double lon = rset8.getDouble("lon"); - int fixed = rset8.getInt("fixed"); if (lat > maxLat) { maxLat = lat; @@ -472,10 +461,8 @@ minLon = lon; } - unfixedCount++; - // OSM node - String osm_node = nodeBusstop(con, idref, name, lat, lon, timeStampStr); + String osm_node = nodeBusstop(con, idref, name, KatakanaToHiragana.hiraganaToKatakana(kana), lat, lon, timeStampStr); ow.write(osm_node); ow.newLine(); } @@ -491,9 +478,7 @@ public static Element osmTemplateNode = null; - public static String nodeBusstop(Connection con, String idref, String name, Double lat, Double lon, String timeStampStr) throws SAXException, IOException, ParserConfigurationException, TransformerException, SQLException { - int nodeid = Integer.parseInt(idref.substring(1)) * -1; - + public static String nodeBusstop(Connection con, long gid, String name, String hirakana, Double lat, Double lon, String timeStampStr) throws SAXException, IOException, ParserConfigurationException, TransformerException, SQLException { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); factory.setIgnoringElementContentWhitespace(true); factory.setIgnoringComments(true); @@ -508,10 +493,11 @@ Element node = document.createElement("node"); document.appendChild(node); - node.setAttribute("id", String.valueOf(nodeid)); + node.setAttribute("id", String.valueOf(gid * -1)); node.setAttribute("timestamp", timeStampStr); node.setAttribute("lat", String.valueOf(lat)); node.setAttribute("lon", String.valueOf(lon)); + node.setAttribute("version", "1"); NodeList nodes = osmTemplateNode.getChildNodes(); @@ -551,6 +537,12 @@ tagName.setAttribute("v", name); node.appendChild(tagName); } + else if (nodeName.equals("busStopNameKana")) { + Element tagName = document.createElement("tag"); + tagName.setAttribute("k", nodeValue); + tagName.setAttribute("v", hirakana); + node.appendChild(tagName); + } } } } diff --git a/src/osm/jp/postgis/Do_sqlfiles.java b/src/osm/jp/postgis/Do_sqlfiles.java index 1e487aa..fdeef36 100644 --- a/src/osm/jp/postgis/Do_sqlfiles.java +++ b/src/osm/jp/postgis/Do_sqlfiles.java @@ -46,21 +46,7 @@ for (File iFile : files) { if (checkFile(iFile)) { System.out.println(iFile.getAbsoluteFile()); - - // ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する - InputStreamReader fr = new InputStreamReader(new FileInputStream(iFile), "UTF-8"); - BufferedReader br = new BufferedReader(fr); - - // 既存のreadLine()では\rまたは\n、および\r\nにて改行とみなしている。 - String strLine = null; - while ((strLine = br.readLine()) != null) { // null=ファイルの最後 - System.out.println(strLine); - sqlExecute(con, strLine); - } - System.out.println("commit;"); - sqlExecute(con, "commit;"); - br.close(); - fr.close(); + Do_sqlfiles.sqlExecute(con, iFile); } } } @@ -72,15 +58,38 @@ } /** + * sqlFileを実行する + * @param conn データベースコネクション + * @param sqlFile 実行するＳＱＬファイル + * @throws SQLException ＳＱＬ実行エラー + * @throws IOException + */ + public static void sqlExecute(Connection conn, File sqlFile) throws SQLException, IOException { + // ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する + BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(sqlFile), "UTF-8")); + + // 既存のreadLine()では\rまたは\n、および\r\nにて改行とみなしている。 + String strLine = null; + while ((strLine = br.readLine()) != null) { // null=ファイルの最後 + sqlExecute(conn, strLine); + } + sqlExecute(conn, "commit;"); + br.close(); + } + + /** * sqlStrを実行する * @param conn データベースコネクション * @param sqlStr 実行するＳＱＬ文 * @throws SQLException ＳＱＬ実行エラー */ - static void sqlExecute(Connection conn, String sqlStr) throws SQLException { - Statement stat = conn.createStatement(); - stat.execute(sqlStr); - stat.close(); + public static void sqlExecute(Connection conn, String sqlStr) throws SQLException { + if ((sqlStr != null) && (!sqlStr.trim().equals(""))) { + System.out.println(sqlStr); + Statement stat = conn.createStatement(); + stat.execute(sqlStr); + stat.close(); + } } /** diff --git a/src/osmTag.xml b/src/osmTag.xml index 9577e74..173aab0 100644 --- a/src/osmTag.xml +++ b/src/osmTag.xml @@ -1,6 +1,7 @@ + 　 　 　 diff --git a/src/postgis.properties b/src/postgis.properties index 4b98375..04ba2fe 100644 --- a/src/postgis.properties +++ b/src/postgis.properties @@ -1,4 +1,4 @@ db_driver=org.postgresql.Driver -db_url=jdbc:postgresql://192.168.0.200:5432/gisdb +db_url=jdbc:postgresql://surveyor.mydns.jp:5432/gisdb db_user=gisuser db_passwd=gisuser