diff --git a/src/osm/jp/api/HttpPOST.java b/src/osm/jp/api/HttpPOST.java index e5cc453..b091080 100644 --- a/src/osm/jp/api/HttpPOST.java +++ b/src/osm/jp/api/HttpPOST.java @@ -53,17 +53,11 @@ * @throws SQLException */ public static void create(Connection con) throws SQLException { - create(con, false); - } - - public static void create(Connection con, boolean withdrop) throws SQLException { String createSt; - if (withdrop) { - sql(con, "DROP TABLE "+ HttpPOST.TABLE_NAME +";"); - sql(con, "DROP INDEX "+ HttpPOST.TABLE_NAME +"_index;"); - sql(con, "DROP TABLE AREA_NODE"); - } + sql(con, "DROP TABLE IF EXISTS "+ HttpPOST.TABLE_NAME +" CASCADE"); + //sql(con, "DROP INDEX "+ HttpPOST.TABLE_NAME +"_index;"); + sql(con, "DROP TABLE IF EXISTS AREA_NODE CASCADE"); // 'table.FUEL_EXIST'を新規に作る createSt = "CREATE TABLE "+ HttpPOST.TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));"; @@ -164,21 +158,21 @@ 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(); + try ( + BufferedWriter hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(oFile), "UTF-8")); + BufferedReader reader = new BufferedReader(new InputStreamReader(urlconn.getInputStream(), "UTF-8")) + ) { + while (true) { + String line = reader.readLine(); + if (line == null) { + break; } - hw.flush(); + hw.write(line); + hw.newLine(); } + hw.flush(); } } - } catch (java.net.ConnectException e) { // レスポンスコード[600] レスポンスメッセージ[接続がタイムアウトしました (Connection timed out)] diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index ab046e5..1ee3df2 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -14,11 +14,11 @@ import java.text.SimpleDateFormat; import jp.co.areaweb.tools.database.*; -import osm.jp.coverage.fuel.DbFuel; +import osm.jp.api.Japan; public class Busstop { - // 近くのバス停を探す範囲(バス停を中心としたNEER×2m四方の領域 - static final int NEER = 200; // 200m(0.2km) + // 近くのバス停を探す範囲(バス停を中心としたNEER×2m四方の領域 + static final int NEER = 150; // 150m public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); @@ -59,16 +59,21 @@ * @throws TransformerException */ public Busstop(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - try ( PreparedStatement ps2 = con.prepareStatement("SELECT gmlid,lat,lon,fixed,fixed1,area FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); - PreparedStatement ps1 = con.prepareStatement("SELECT idref,lat,lon,score,name FROM "+ HttpPOST.TABLE_NAME); - PreparedStatement ps3 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?"); - PreparedStatement ps4 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"); - PreparedStatement ps5 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)" )) + String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ HttpPOST.TABLE_NAME; + String sqlStr2 = "SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"; + String sqlStr3 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?"; + String sqlStr4 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"; + String sqlStr5 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; + try ( PreparedStatement ps2 = con.prepareStatement(sqlStr2); + PreparedStatement ps1 = con.prepareStatement(sqlStr1); + PreparedStatement ps3 = con.prepareStatement(sqlStr3); + PreparedStatement ps4 = con.prepareStatement(sqlStr4); + PreparedStatement ps5 = con.prepareStatement(sqlStr5)) { - System.out.println("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"); + System.out.println(sqlStr4); ps4.executeUpdate(); - System.out.println("SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME); + System.out.println(sqlStr1); try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { double lat = rset1.getDouble("lat"); @@ -83,8 +88,8 @@ int fixed1 = 0; // 指定の緯度経度を中心とする半径200x2m四方の矩形領域 - RectArea rect = new RectArea(lat, lon, NEER*2); // 400m 四方 - //System.out.println("SELECT gmlid,lat,lon,fixed1,area FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); + RectArea rect = new RectArea(lat, lon, NEER*2); // 600m 四方 + System.out.println(sqlStr2 +"["+ rect.minlat +", "+ rect.maxlat +", "+ rect.minlon +", "+ rect.maxlon +"]"); ps2.setDouble(1, rect.minlat); ps2.setDouble(2, rect.maxlat); ps2.setDouble(3, rect.minlon); @@ -94,28 +99,29 @@ gmlid = rset2.getString("gmlid"); area = rset2.getInt("area"); fixed1 = rset2.getInt("fixed1"); - score += fixed1; - System.out.println("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1="+ score +" WHERE gmlid="+ gmlid +" and area=" + area); - ps3.setInt(1, score); - ps3.setString(2, gmlid); - ps3.setInt(3, area); - ps3.executeUpdate(); + fixed1 += score; + String ksjName = rset2.getString("name"); + double lat2 = rset2.getDouble("lat"); + double lon2 = rset2.getDouble("lon"); + + double dd = Japan.distanceKm(lat,lon,lat2,lon2); + System.out.println("distance() -> "+ dd +"(km)"); + if ( ((dd * 1000.0D) < NEER) + || (((dd * 1000.0D) < NEER*2) && (name.equals(ksjName)))) + { + System.out.println(sqlStr3 +" ["+ fixed1 +", "+ gmlid +", "+ area +"]"); + ps3.setInt(1, fixed1); + ps3.setString(2, gmlid); + ps3.setInt(3, area); + ps3.executeUpdate(); + } } } } } - System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"); + System.out.println(sqlStr5); ps5.executeUpdate(); - } } - - public static final double ONE_KM_LAT = 0.009013372D; - public static final double ONE_KM_LON = 0.010966404D; - static double distance(double lat1, double lon1, double lat2, double lon2) { - double dlat = Math.abs(lat1 - lat2) / ONE_KM_LAT / 1000D; - double dlon = Math.abs(lon1 - lon2) / ONE_KM_LON / 1000D; - return Math.sqrt(dlat*dlat + dlon*dlon); - } } \ No newline at end of file diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index 4cc5947..528ce27 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -19,7 +19,6 @@ public class DbBusstop { public static final String TABLE_NAME = "bus_stop"; - public static boolean DROP = false; File inputFile; String filter = ""; @@ -41,12 +40,6 @@ * @throws org.xml.sax.SAXException */ public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException { - for (String arg : args) { - if (arg.toUpperCase().equals("-DROP")) { - DROP = true; - } - } - // HSQLディレクトリがなければエラー File dbdir = new File("database"); if (!dbdir.isDirectory()) { @@ -56,14 +49,8 @@ Connection conHsql = null; try { conHsql = DatabaseTool.openDb("database"); - if (DROP) { - HttpPOST.sql(conHsql, "DROP TABLE "+ TABLE_NAME); - HttpPOST.sql(conHsql, "DROP INDEX "+ TABLE_NAME +"_index;"); - } + HttpPOST.sql(conHsql, "DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); create(conHsql); - try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) { - ps.executeUpdate(); - } /** * 都道府県ごとのGMLディレクトリの処理 diff --git a/src/osm/jp/coverage/busstop/DbExistBusstop.java b/src/osm/jp/coverage/busstop/DbExistBusstop.java index 403d4f3..7c46cfe 100644 --- a/src/osm/jp/coverage/busstop/DbExistBusstop.java +++ b/src/osm/jp/coverage/busstop/DbExistBusstop.java @@ -25,7 +25,6 @@ public static boolean DROP = false; // 近くのバス停を探す範囲(バス停を中心としたNEER×2m四方の領域 - static final int NEER = 150; // 150m(0.15km) static boolean update = false; // '-update'オプション postgisデータの更新を行う static boolean noget = false; // '-noget'オプション OSM既存データのGETを行わない @@ -52,16 +51,10 @@ * @throws ParserConfigurationException */ public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException { - for (String arg : args) { - if (arg.toUpperCase().equals("-DROP")) { - DROP = true; - } - } - Connection conHsql = null; try { conHsql = DatabaseTool.openDb("database"); - HttpPOST.create(conHsql, DROP); + HttpPOST.create(conHsql); /** * 既存のOSMバス停を読み込む @@ -84,8 +77,8 @@ * ( * node[highway=bus_stop](35.42,139.39,35.45,139.42); * node[highway="disused:bus_stop"](35.42,139.39,35.45,139.42); - * node[public_transport=platform]node[bus=yes](35.42,139.39,35.45,139.42); - * node[public_transport=stop_position](35.42,139.39,35.45,139.42); + * node[public_transport=platform][bus=yes](35.42,139.39,35.45,139.42); + * node[public_transport=stop_position][bus=yes](35.42,139.39,35.45,139.42); * node[amenity=bus_station](35.42,139.39,35.45,139.42); * (way[amenity=bus_station](35.42,139.39,35.45,139.42);>;); * ); @@ -111,8 +104,8 @@ queryText.append("("); queryText.append(" node[highway=bus_stop]("+ area.getSWNE() +");"); queryText.append(" node[highway=\"disused:bus_stop\"]("+ area.getSWNE() +");"); - queryText.append(" node[public_transport=platform][bus=yes]("+ area.getSWNE() +"););"); - queryText.append(" node[public_transport=stop_position]("+ area.getSWNE() +");"); + queryText.append(" node[public_transport=platform][bus=yes]("+ area.getSWNE() +");"); + queryText.append(" node[public_transport=stop_position][bus=yes]("+ area.getSWNE() +");"); queryText.append(" node[amenity=bus_station]("+ area.getSWNE() +");"); queryText.append(" (way[amenity=bus_station]("+ area.getSWNE() +");>;);"); queryText.append(");"); diff --git a/src/osm/jp/coverage/busstop/NagoyaBusstop.java b/src/osm/jp/coverage/busstop/NagoyaBusstop.java index 58b45a8..5e97cbe 100644 --- a/src/osm/jp/coverage/busstop/NagoyaBusstop.java +++ b/src/osm/jp/coverage/busstop/NagoyaBusstop.java @@ -70,10 +70,10 @@ new NagoyaBusstop(con, new File("TRF0009630..csv")); // ローカルデータベース内の情報をPostGIS用の'ToPostgis.SQL_FILE_NAME'「./busstop.sql」に出力する - ToPostgis postgis = new ToPostgis(new File(".")); - postgis.setNagoyaMode(true); - postgis.outputDb(con); - postgis.close(); + ToPostgis postgis = new ToPostgis(); + //postgis.setNagoyaMode(true); + //postgis.outputDb(con); + //postgis.close(); } finally { DatabaseTool.closeDb(con); @@ -87,7 +87,7 @@ Do_sqlfiles.sqlExecute(conPost, "CREATE INDEX ix_nagoya_busstop_geom ON nagoya_busstop USING GiST (geom);"); Do_sqlfiles.sqlExecute(conPost, "commit;"); - Do_sqlfiles.sqlExecute(conPost, new File(ToPostgis.SQL_FILE_NAME)); + //Do_sqlfiles.sqlExecute(conPost, new File(ToPostgis.SQL_FILE_NAME)); outputDb2Osm(conPost, new File("Nagoya_busstop.osm")); } finally { @@ -416,7 +416,7 @@ /** * ローカルデータベース内の情報をOSMファイルに出力する * @param con - * @param iCode + * @param osmFile * @throws IOException * @throws SQLException * @throws TransformerException diff --git a/src/osm/jp/coverage/busstop/ToPostgis.java b/src/osm/jp/coverage/busstop/ToPostgis.java index ca0c4f9..63f310c 100644 --- a/src/osm/jp/coverage/busstop/ToPostgis.java +++ b/src/osm/jp/coverage/busstop/ToPostgis.java @@ -89,12 +89,12 @@ fixed = 1; } String geom = "ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612)"; - String sqlStr = "INSERT INTO t_busstop (gmlid,name,fixed,area,geom) VALUES (?,?,?,?,"+ geom +")"; - System.out.println(sqlStr +" ["+ gmlid +", '"+ name +"', "+ fixed +", "+ area +"]"); + String sqlStr = "INSERT INTO t_busstop (gmlid,fixed,name,area,geom) VALUES (?,?,?,?,"+ geom +")"; + System.out.println(sqlStr +" ["+ gmlid +", "+ fixed +", '"+ name +"', "+ area +"]"); try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) { ps.setString(1, gmlid); - ps.setString(2, name); - ps.setInt(3, fixed); + ps.setInt(2, fixed); + ps.setString(3, name); ps.setInt(4, area); ps.executeUpdate(); } diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java index 469f5be..b0e43b0 100644 --- a/src/osm/jp/coverage/fuel/DbExist.java +++ b/src/osm/jp/coverage/fuel/DbExist.java @@ -30,7 +30,7 @@ try { // DB.tableを作成 con = DatabaseTool.openDb("database"); - HttpPOST.create(con, false); + HttpPOST.create(con); /** * 既存のOSMガソリンスタンドを読み込む diff --git a/src/osm/jp/coverage/test/DbExistTest.java b/src/osm/jp/coverage/test/DbExistTest.java index b2939ff..6e663ce 100644 --- a/src/osm/jp/coverage/test/DbExistTest.java +++ b/src/osm/jp/coverage/test/DbExistTest.java @@ -51,16 +51,10 @@ * @throws ParserConfigurationException */ public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException { - for (String arg : args) { - if (arg.toUpperCase().equals("-DROP")) { - DROP = true; - } - } - Connection conHsql = null; try { conHsql = DatabaseTool.openDb("database"); - HttpPOST.create(conHsql, DROP); + HttpPOST.create(conHsql); getJapanCapabilities(conHsql); } diff --git a/src/test.activity.violet.html b/src/test.activity.violet.html index 1e23ebc..80ba074 100644 --- a/src/test.activity.violet.html +++ b/src/test.activity.violet.html @@ -200,9 +200,9 @@ - Busstop.main() + Test.main() OSM_EXISTのscoreを集計して -BUS_STOPのfixed1をUPDATE +TESTのfixed1をUPDATE @@ -235,216 +235,72 @@ - POSTGIS.t_busstop + POSTGIS.t_test - - 255 - 228 - 181 - 255 - + - - - - + + + + 1 - - - - + + + + 1 - - - - + + + + 1 - + ToPostgis.main() -HSQLDB.BUS_STOPの結果を -POSTGIS.t_busstopへ反映させる +HSQLDB.TESTの結果を +POSTGIS.t_testへ反映させる - - - - + + + + 1 - + Create & Insert - - - - + + + + 1 - - - - - 1 - - 255 - 255 - 255 - 255 - - - 0 - 0 - 0 - 255 - - - - - - - - 1 - - - - - ToCartoCSV.main() - - - - - - - - 1 - - - - - - - - - 1 - - - - - t_fuel.csv - - - 255 - 228 - 181 - 255 - - - - - - - 1 - - - - - - - - - 1 - - 255 - 255 - 255 - 255 - - - 0 - 0 - 0 - 255 - - - - Coverage (JSON) -Coverage (CSV) - - - - - - - - 1 - - 255 - 255 - 255 - 255 - - - 0 - 0 - 0 - 255 - - - - - - - - 1 - - - - - - - - - 1 - - - - - Coverage.json -Coverage.csv - - - 255 - 228 - 181 - 255 - - - - + + - - - - + + + + 1 @@ -453,13 +309,13 @@ - + - - - - + + + + 1 @@ -468,13 +324,13 @@ - + - - - - + + + + 1 @@ -483,13 +339,13 @@ - + - - - - + + + + 1 @@ -498,13 +354,13 @@ - + - - - - + + + + 1 @@ -513,13 +369,13 @@ - + - - - - + + + + 1 @@ -528,13 +384,13 @@ - + - - - - + + + + 1 @@ -543,13 +399,13 @@ - + - - - - + + + + 1 @@ -558,13 +414,13 @@ - + - - - - + + + + 1 @@ -573,13 +429,13 @@ - + - - - - + + + + 1 @@ -588,22 +444,22 @@ - + - - - - + + + + 1 - + - - - - + + + + 1 @@ -612,22 +468,22 @@ - + - - - - - + + + + + 1 - - - - - - - + + + + + + + 1 @@ -636,13 +492,13 @@ - - - - - - - + + + + + + + 1 @@ -651,13 +507,13 @@ - - - - - - - + + + + + + + 1 @@ -666,13 +522,13 @@ - + - - - - + + + + 1 @@ -681,13 +537,13 @@ - + - - - - + + + + 1 @@ -696,13 +552,13 @@ - - + + - - - - + + + + 1 @@ -711,13 +567,13 @@ - + - - - - + + + + 1 @@ -726,966 +582,643 @@ - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - ]]>

- embedded diagram image + embedded diagram image \ No newline at end of file