diff --git a/src/osm/jp/api/HttpPOST.java b/src/osm/jp/api/HttpPOST.java
index 66c8233..a8d0092 100644
--- a/src/osm/jp/api/HttpPOST.java
+++ b/src/osm/jp/api/HttpPOST.java
@@ -35,33 +35,41 @@
// 送信
PrintWriter pw = new PrintWriter(new BufferedWriter(new OutputStreamWriter(urlconn.getOutputStream(), "utf-8")));
- pw.print("");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print(" ");
- pw.print("");
+ outputWriter(pw, "");
+ outputWriter(pw, " ");
+ /*
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ */
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ /*
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ */
+ /*
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ */
+ /*
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ */
+ outputWriter(pw, " ");
+ outputWriter(pw, " ");
+ outputWriter(pw, "");
pw.close(); // closeで送信完了
System.out.println("レスポンスコード[" + urlconn.getResponseCode() + "] " +
@@ -83,4 +91,9 @@
reader.close();
urlconn.disconnect();
}
+
+ public static void outputWriter(PrintWriter pw, String text) {
+ System.out.println("\t" + text);
+ pw.print(text);
+ }
}
diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java
index 9baaa29..ee4e8fc 100644
--- a/src/osm/jp/coverage/busstop/Busstop.java
+++ b/src/osm/jp/coverage/busstop/Busstop.java
@@ -28,6 +28,7 @@
// 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域
static final int NEER = 150; // 150m(0.15km)
static boolean nocheck = false;
+ static boolean update = false;
public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
@@ -47,15 +48,15 @@
* @throws ParserConfigurationException */
public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException
{
- int index = 0;
- if (args.length > index) {
- if (args[index].equals("-check")) {
+ for (int i=0; i < args.length; i++) {
+ if (args[i].equals("-check")) {
Busstop.nocheck = false;
- index++;
}
- else if (args[index].equals("-nocheck")) {
+ else if (args[i].equals("-nocheck")) {
Busstop.nocheck = true;
- index++;
+ }
+ if (args[i].equals("-update")) {
+ Busstop.update = true;
}
}
@@ -105,124 +106,188 @@
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");
+ if (outFile.isFile()) {
+ outFile.delete();
+ }
+ ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), "UTF-8"));
+
+ if (Busstop.update) {
+ conPost = DatabaseTool.openDb("postgis");
+ }
File[] files = gmldir.listFiles();
- for (File iFile : files) {
- // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。
- if (!checkFile(iFile, areacode)) {
- continue;
- }
-
- Busstop.clearDb(con);
- inputFile(con, iFile, areacode);
-
- /**
- * 既存のOSMバス停を読み込む
- * --> 'existing.xml'
- */
- if (!Busstop.nocheck) {
- /*
- * 既存のOSMデータファイルがなければ、新たにダウンロードする。
- * OSMデータファイルがあるときは、ダウンロードしないでそれを使う。
- */
- File existingFile = new File(gmldir, String.format("existing_%02d.xml", areacode));
- if (!existingFile.isFile()) {
- /**
- * インポートしたデータの緯度経度範囲を読み取る
- */
- double maxLat = -90.0D;
- double minLat = 90.0D;
- double maxLon = -180.0D;
- double minLon = 180.0D;
- PreparedStatement 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, minLat, maxLat, minLon, maxLon);
+ try {
+ for (File iFile : files) {
+ // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。
+ if (!checkFile(iFile, areacode)) {
+ continue;
}
- readExistingFile(con, existingFile);
-
- PreparedStatement ps1 = con.prepareStatement("SELECT idref,name,lat,lon FROM bus_stop WHERE area=?");
- 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("idref");
- String name = rset1.getString("name");
- Double lat = rset1.getDouble("lat");
- Double lon = rset1.getDouble("lon");
-
- // 指定の緯度経度を中心とする半径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);
- ResultSet rset2 = ps2.executeQuery();
- if (rset2.next()) {
- int score = rset2.getInt(1);
- if (score > 0) {
- System.out.println("."+ score);
- ps3.setInt(1, score);
- ps3.setString(2, idref);
- ps3.executeUpdate();
+
+ Busstop.clearDb(con);
+ if (!Busstop.update) {
+ inputFile(con, iFile, areacode);
+ }
+
+ /**
+ * 既存のOSMバス停を読み込む
+ * --> 'existing.xml'
+ */
+ if (!Busstop.nocheck) {
+ /*
+ * 既存のOSMデータファイルがなければ、新たにダウンロードする。
+ * OSMデータファイルがあるときは、ダウンロードしないでそれを使う。
+ */
+ File existingFile = new File(gmldir, String.format("existing_%02d.xml", areacode));
+ if (!existingFile.isFile() || Busstop.update) {
+ if (existingFile.isFile()) {
+ existingFile.delete();
+ }
+
+ /**
+ * インポートしたデータの緯度経度範囲を読み取る
+ */
+ double maxLat = -90.0D;
+ double minLat = 90.0D;
+ double maxLon = -180.0D;
+ double minLon = 180.0D;
+ PreparedStatement ps8;
+ if (Busstop.update) {
+ ps8 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) lon, ST_Y(geom) lat FROM t_busstop WHERE area=?");
+ ps8.setInt(1, areacode);
}
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);
- ResultSet rset4 = ps4.executeQuery();
- if (rset4.next()) {
- score = rset4.getInt(1);
- System.out.println(".."+ score);
- ps3.setInt(1, score);
- ps3.setString(2, idref);
- ps3.executeUpdate();
- }
- rset4.close();
+ 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」に出力する
+ */
+ System.out.println("AREA: "+ areacode);
+ HttpPOST.getCapabilities(existingFile, minLat, maxLat, minLon, maxLon);
}
- rset2.close();
+ readExistingFile(con, existingFile);
+
+ 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=?");
+ }
+ 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);
+ 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);
+ 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();
+ }
+ }
+ }
+ rset4.close();
+ }
+ }
+ rset2.close();
+ }
+ rset1.close();
+ ps3.close();
+ ps2.close();
+ ps1.close();
}
- rset1.close();
- ps3.close();
- ps2.close();
- ps1.close();
+
+ // ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する
+ if (!Busstop.update) {
+ postgis = new ToPostgis(gmldir);
+ postgis.outputDb(con);
+ postgis.close();
+ }
}
-
- // ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する
- postgis = new ToPostgis(gmldir);
- postgis.outputDb(con);
- postgis.close();
+ }
+ finally {
+ if (conPost != null) {
+ DatabaseTool.closeDb(conPost);
+ }
+ ow.flush();
+ ow.close();
}
}
@@ -277,7 +342,7 @@
String nameStr = "";
String fixmeStr = "";
int score = 0;
- PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name, score) VALUES (?,?,?,?,?)");
+
NamedNodeMap nodeMap = node.getAttributes();
if (null != nodeMap) {
@@ -330,6 +395,7 @@
// idref と nameStr をデータベースに格納する
System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")["+ Integer.toString(score) +"]"+ nameStr);
+ 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));
@@ -341,6 +407,41 @@
}
/**
+ *
+ */
+ public static void updateFile (Connection conHsql, Connection conPost, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
+ int iCounter = 0;
+ PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) LON, ST_Y(geom) LAT FROM t_busstop WHERE area=?");
+ PreparedStatement 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();
+ ps1.close();
+ ps2.close();
+
+ System.out.println("("+ areacode +") バス停数["+ iCounter +"]");
+ }
+
+ /**
* 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する
* @param con
* @param iFile
@@ -370,7 +471,7 @@
root = builder.parse(iFile);
iCounter += showNodes(con, root, iStr.substring(0, iStr.length() - 4), timeStampStr, areacode);
- System.out.println("バス停数["+ iCounter +"]");
+ System.out.println("("+ areacode +") バス停数["+ iCounter +"]");
}
public static void clearDb(Connection con) throws SQLException {
diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java
index 3ca5331..684ba76 100644
--- a/src/osm/jp/coverage/busstop/DbBusstop.java
+++ b/src/osm/jp/coverage/busstop/DbBusstop.java
@@ -54,24 +54,6 @@
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, "bus_course");
- createSt = "CREATE TABLE bus_course (code int, type int, corp VARCHAR(128) NOT NULL, course VARCHAR(512), ifile VARCHAR(128), CONSTRAINT bus_course_pk PRIMARY KEY(code));";
- create(con, createSt);
-
- drop(con, "bus_ref");
- createSt = "CREATE TABLE bus_ref (idref VARCHAR(12), code INT);";
- create(con, createSt);
-
- // 'table.bus_route'を新規に作る
- drop(con, "bus_route");
- createSt = "CREATE TABLE bus_route (cvid VARCHAR(12), bsc int, boc VARCHAR(128) NOT NULL, bln VARCHAR(512));";
- create(con, createSt);
-
- // 'table.bus_Curve'を新規に作る
- drop(con, "bus_Curve");
- createSt = "CREATE TABLE bus_Curve (idref VARCHAR(12), seq INT, lat DOUBLE, lon DOUBLE, CONSTRAINT bus_curve_pk PRIMARY KEY(idref,seq));";
- create(con, createSt);
}
public static void create(Connection con, String createsql) throws SQLException {
@@ -115,6 +97,29 @@
}
/**
+ * '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
*/