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
-
]]>
-
+