diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index d914c6d..f7127cd 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -25,7 +25,7 @@ /** * メイン - * OSM_EXISTのscoreを集計して,BUS_STOPのfixedをUPDATE + * OSM_EXISTのscoreを集計して,BUSSTOPのfixedをUPDATE * * @param args * */ @@ -74,7 +74,7 @@ } /** - * OSM_EXISTのscoreを集計して,BUS_STOPのfixedをUPDATE + * OSM_EXISTのscoreを集計して,BUSSTOPのfixedをUPDATE * * @param con * @throws SQLException diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index d0c3631..f32f7a3 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -234,7 +234,8 @@ public static void showBusStop(Connection con, Node node, String iFileName, int areacode) throws IOException, SQLException { String gmlidStr = ""; String nameStr = ""; - try (PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE (gmlid=? and area=?)")) { + String sql = String.format("UPDATE %s SET name=?,ifile=? WHERE (gmlid=? and area=?)", DbBusstop.TABLE_NAME); + try (PreparedStatement ps2 = con.prepareStatement(sql)) { ArrayList bris = new ArrayList<>(); NodeList nodes = node.getChildNodes(); for (int i=0; i < nodes.getLength(); i++) { @@ -270,7 +271,7 @@ ps2.setString(2, iFileName); ps2.setString(3, gmlidStr); ps2.setInt(4, areacode); - System.out.println("UPDATE bus_stop SET name='"+ nameStr +"',ifile='"+ iFileName +"' WHERE (gmlid='"+ gmlidStr +"' and area="+ areacode +")"); + System.out.println(String.format("UPDATE %s SET name='%s',ifile='%s' WHERE (gmlid='%s' and area=%s)", DbBusstop.TABLE_NAME, nameStr, iFileName, gmlidStr, areacode)); ps2.executeUpdate(); } } @@ -326,7 +327,7 @@ } /** - * 'table.BUS_STOP'を新規に作る + * 'table.BUSSTOP'を新規に作る * 既にテーブルが存在する時には何もしない * @param conHsql * @throws SQLException @@ -335,7 +336,7 @@ String createSt; HttpPOST httpPOST = new HttpPOST(conHsql, TABLE_NAME); - // 'table.BUS_STOP'を新規に作る + // 'table.BUSSTOP'を新規に作る createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));"; httpPOST.sql(createSt); @@ -344,14 +345,14 @@ } /** - * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する + * 'table.BUSSTOP'の内容をCSV形式にして標準出力に出力する * @param con */ public static void export(Connection con) { try { - System.out.println("TABLE: BUS_STOP"); + System.out.println("TABLE: BUSSTOP"); System.out.println("\"gmlid\",\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\""); - PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,name,lat,lon,fixed,ifile FROM bus_stop"); + PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,name,lat,lon,fixed,ifile FROM "+ DbBusstop.TABLE_NAME); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { String gmlid = rset8.getString("gmlid"); diff --git a/src/osm/jp/coverage/busstop/NagoyaBusstop.java b/src/osm/jp/coverage/busstop/NagoyaBusstop.java index 2b1ae54..09425a5 100644 --- a/src/osm/jp/coverage/busstop/NagoyaBusstop.java +++ b/src/osm/jp/coverage/busstop/NagoyaBusstop.java @@ -103,42 +103,42 @@ * @throws Exception */ public NagoyaBusstop(Connection con, File csvFile) throws Exception { - inputFile(con, csvFile); + 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"); + /** + * 既存のOSMバス停を読み込む + * --> 'existing.xml' + */ + File existingFile = new File("existing.xml"); - if (lat > maxLat) { - maxLat = lat; - } - if (lon > maxLon) { - maxLon = lon; - } - if (lat < minLat) { - minLat = lat; - } - if (lon < minLon) { - minLon = lon; - } - } - rset8.close(); + /** + * インポートしたデータの緯度経度範囲を読み取る + */ + 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 "+ DbBusstop.TABLE_NAME); + try (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; + } + } + } /** * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する @@ -151,9 +151,9 @@ readExistingFile(con, existingFile); PreparedStatement ps1; - ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop"); + ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM " + DbBusstop.TABLE_NAME); 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 ps3 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" 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()) { @@ -345,7 +345,7 @@ * @throws Exception */ public static void inputFile (Connection con, File iFile) throws Exception { - PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (idref,name,kana,fixed,area,lat,lon) VALUES (?,?,?,?,?,?,?)"); + PreparedStatement ps2 = con.prepareStatement("INSERT INTO "+ DbBusstop.TABLE_NAME +" (idref,name,kana,fixed,area,lat,lon) VALUES (?,?,?,?,?,?,?)"); CsvFile csv = new CsvFile(iFile); csv.load(); @@ -378,15 +378,15 @@ public static void clearDb(Connection con) throws SQLException { Statement stmt = con.createStatement(); - long count = stmt.executeUpdate("delete from bus_stop"); - System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。"); + long count = stmt.executeUpdate("delete from "+ DbBusstop.TABLE_NAME); + System.out.println("'Database.busstop'から "+ count +" 件のデータを削除しました。"); count = stmt.executeUpdate("delete from existing_data"); System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。"); } public static void initDb(Connection con) throws SQLException { - // 'table.BUS_STOP'を新規に作る + // 'table.BUSSTOP'を新規に作る DbBusstop.create(con); } diff --git a/src/osm/jp/coverage/busstop/ToPostgis.java b/src/osm/jp/coverage/busstop/ToPostgis.java index 3c6e0ac..76ebca3 100644 --- a/src/osm/jp/coverage/busstop/ToPostgis.java +++ b/src/osm/jp/coverage/busstop/ToPostgis.java @@ -4,7 +4,7 @@ import osm.jp.postgis.PostgisItems; /** - * HSQLDB.BUS_STOPの結果をPOSTGIS.t_busstopへ反映させる + * HSQLDB.BUSSTOPの結果をPOSTGIS.t_busstopへ反映させる * テーブル名： t_busstop * インデックス： ix_busstop_geom * gid PostGISの識別ID @@ -26,7 +26,6 @@ this.items = new PostgisItems(); items.add(new PostgisItem("gmlid", "gmlid")); - items.add(new PostgisItem("idref", "idref")); items.add(new PostgisItem("area", "area")); items.add(new PostgisItem("fixed", "fixed1")); } diff --git a/src/osm/jp/coverage/police/DbPolice.java b/src/osm/jp/coverage/police/DbPolice.java index eeacf7d..259fd51 100644 --- a/src/osm/jp/coverage/police/DbPolice.java +++ b/src/osm/jp/coverage/police/DbPolice.java @@ -255,7 +255,7 @@ } /** - * 'table.BUS_STOP'を新規に作る + * 'table.BUSSTOP'を新規に作る * 既にテーブルが存在する時には何もしない * @param conHsql * @throws SQLException diff --git a/src/osm/jp/postgis/ToPostgis.java b/src/osm/jp/postgis/ToPostgis.java index b91068b..255dd69 100644 --- a/src/osm/jp/postgis/ToPostgis.java +++ b/src/osm/jp/postgis/ToPostgis.java @@ -116,9 +116,14 @@ while (rset1.next()) { items.setResuit(rset1); + String latlon = String.format( + "ST_GeomFromText('POINT(%.7f %.7f)',4612)", + items.lon, + items.lat + ); String sqlStr = "INSERT INTO t_"+ tableName +" (gmlid,idref,fixed,area,code,geom) " - + "VALUES (?,?,?,?,?, ST_GeomFromText('POINT(? ?)',4612))"; + + "VALUES (?,?,?,?,?, "+ latlon +")"; try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) { printMark(); ps.setString(1, items.gmlid); @@ -126,8 +131,6 @@ ps.setInt(3, items.fixed); ps.setInt(4, items.area); ps.setInt(5, items.code); - ps.setDouble(6, items.lon); - ps.setDouble(7, items.lat); ps.executeUpdate(); } } diff --git a/test/osm/jp/coverage/DbTest.java b/test/osm/jp/coverage/DbTest.java index 1593905..3cdd650 100644 --- a/test/osm/jp/coverage/DbTest.java +++ b/test/osm/jp/coverage/DbTest.java @@ -129,7 +129,7 @@ /** - * 'table.BUS_STOP'を新規に作る + * 'table.BUSSTOP'を新規に作る * 既にテーブルが存在する時には何もしない * @param conHsql * @throws SQLException @@ -147,7 +147,7 @@ } /** - * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する + * 'table.BUSSTOP'の内容をCSV形式にして標準出力に出力する * @param con */ public static void export(Connection con) { diff --git a/test/osm/jp/coverage/ToPostgis.java b/test/osm/jp/coverage/ToPostgis.java index c1ac6f2..d0a08d7 100644 --- a/test/osm/jp/coverage/ToPostgis.java +++ b/test/osm/jp/coverage/ToPostgis.java @@ -45,7 +45,7 @@ } /** - * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM BUS_STOP" + * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM BUSSTOP" * POSTGIS "insert into t_busstop(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));" * * @param conHsql diff --git a/test/osm/jp/coverage/busstop/BusstopTest.java b/test/osm/jp/coverage/busstop/BusstopTest.java index 1bdbe7d..7320944 100644 --- a/test/osm/jp/coverage/busstop/BusstopTest.java +++ b/test/osm/jp/coverage/busstop/BusstopTest.java @@ -97,7 +97,7 @@ } // ノード: 八幡前 (3152604023) 場所: 33.9808001, 133.3123441 fixme有り - ps1 = hsqldb.prepareStatement("SELECT * FROM BUS_STOP where name='八幡前' and area=38"); + ps1 = hsqldb.prepareStatement("SELECT * FROM BUSSTOP where name='八幡前' and area=38"); try (ResultSet rset1 = ps1.executeQuery()) { boolean ari = false; while (rset1.next()) {