diff --git a/test/P11-10.removed.json.txt b/test/P11-10.removed.json.txt new file mode 100644 index 0000000..17bec02 --- /dev/null +++ b/test/P11-10.removed.json.txt @@ -0,0 +1,13 @@ +{"gmlid":"n12211","idref":"","area":1} +{"gmlid":"n6515","idref":"","area":12} +{"gmlid":"n6517","idref":"","area":12} +{"gmlid":"n3053","idref":"","area":14} +{"gmlid":"n3247","idref":"","area":14} +{"gmlid":"n3248","idref":"","area":14} +{"gmlid":"n3249","idref":"","area":14} +{"gmlid":"n4281","idref":"","area":14} +{"gmlid":"n4425","idref":"","area":14} +{"gmlid":"n4445","idref":"","area":14} +{"gmlid":"n6924","idref":"","area":14} +{"gmlid":"n4439","idref":"","area":27} +{"gmlid":"n4441","idref":"","area":27} diff --git a/test/osm/jp/coverage/busstop/BusstopCheck.java b/test/osm/jp/coverage/busstop/BusstopCheck.java new file mode 100644 index 0000000..1111d59 --- /dev/null +++ b/test/osm/jp/coverage/busstop/BusstopCheck.java @@ -0,0 +1,423 @@ +package osm.jp.coverage.busstop; + +import java.io.File; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.MessageFormat; +import jp.co.areaweb.tools.database.DatabaseTool; +import static org.hamcrest.CoreMatchers.is; +import static org.hamcrest.CoreMatchers.not; +import org.junit.*; +import static org.junit.Assert.*; +import org.junit.runners.MethodSorters; +import osm.jp.api.Coverage; +import osm.jp.api.Japan; +import osm.jp.api.RectArea; +import osm.jp.coverage.DbTest; +import osm.jp.coverage.PoiTest; +import static osm.jp.coverage.busstop.Busstop.NEER; + +/** + * + * @author yuu + */ +@FixMethodOrder (MethodSorters.NAME_ASCENDING) +public class BusstopCheck extends PoiTest { + + @Before + public void setUp() throws Exception { + } + + @After + public void tearDown() throws Exception { + } + + /** + * java Busstop + * + */ + + /** + * + * + */ + @Test + public void test01Busstop_dataread() { + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + String sql = "SELECT * FROM " + + DbBusstop.TABLE_NAME + +" WHERE (lat > '34.69161765717579') and (lat < '34.697025680375795') and (lon > '135.79486712544522') and (lon < '135.80144696784524')"; + PreparedStatement ps1 = hsqldb.prepareStatement(sql); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + System.out.println(rset1.getString("gmlid")); + System.out.println(rset1.getInt("area")); + System.out.println(rset1.getInt("fixed1")); + System.out.println(rset1.getString("name")); + System.out.println(rset1.getDouble("lat")); + System.out.println(rset1.getDouble("lon")); + System.out.println(rset1.getBoolean("removed")); + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * DbExistBusstop.TABLE_NAME にレコードが１件以上存在すること + */ + @Test + public void test02Busstop_busstop() { + File dir = new File(Coverage.DB_PORP_LOCALDB); + assertTrue(dir.exists()); + assertTrue(dir.isDirectory()); + + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM "+ DbExistBusstop.TABLE_NAME); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + long cnt = rset1.getLong(1); + assertThat((cnt > 0), is(true)); + } + else { + fail(); + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 八幡前 (3152604023) + * 場所: 33.9808001, 133.3123441 + * fixme有り + */ + @Test + public void test02Busstop_3152604023() { + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM "+ DbBusstop.TABLE_NAME +" where name='八幡前' and area=38" + ); + try (ResultSet rset1 = ps1.executeQuery()) { + boolean ari = false; + while (rset1.next()) { + // fixme有り + ari = true; + String gmlid = rset1.getString("gmlid"); + String name = rset1.getString("name"); + int fixed = rset1.getInt("fixed"); + int fixed1 = rset1.getInt("fixed1"); + System.out.println(MessageFormat.format("gmlid: '{0}', name: '{1}', fixed: {2}, fixed1: {3}", gmlid, name, fixed, fixed1)); + assertTrue(fixed < 50); + assertThat(rset1.getBoolean("removed"), is(false)); + } + if (!ari) { + fail(); + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 4940018338 （奥牧野） + * 場所: 35.5909251, 139.1498642, area=14 + * highway=bus_stop, name=null, bus=yes, public_transport=platform, operator=神奈中 + * nameなし + */ + @Test + public void test02Busstop_4940018338() { + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + String gmlid = getGmlid(hsqldb, + Double.parseDouble("35.5909251"), + Double.parseDouble("139.1498642"), 150); + assertNotNull(gmlid); + try ( + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM "+ DbBusstop.TABLE_NAME +" where gmlid=? and area=14" + ) + ) { + ps1.setString(1, gmlid); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getString("gmlid"), is(gmlid)); + assertThat(rset1.getInt("fixed1"), is(3)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 古里団地 (2217968698, 2217968723), + * 場所: 35.4002371, 139.4160855, area=14 + * highway=bus_stop, bus=yes, public_transport=platform + */ + @Test + public void test02Busstop_2217968698() { + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + String gmlid = getGmlid(hsqldb, + Double.parseDouble("35.4002371"), + Double.parseDouble("139.4160855"), 150); + assertNotNull(gmlid); + try ( + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM "+ DbBusstop.TABLE_NAME +" where gmlid=? and area=14" + ) + ) { + ps1.setString(1, gmlid); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("fixed1"), is(100)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 海老名高校前 (2043102034) + * 場所: 35.4435042, 139.3878934 + * highway=bus_stop + * 廃止されたバス停、付近にGML(gmlid:n4770, area:14) + */ + @Test + public void test02Busstop_2043102034() { + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + String gmlid = getGmlid(hsqldb, + Double.parseDouble("35.4435042"), + Double.parseDouble("139.3878934"), 150); + assertNotNull(gmlid); + + String sql = "SELECT * FROM "+ DbBusstop.TABLE_NAME +" where gmlid=? and area=14"; + try (PreparedStatement ps1 = hsqldb.prepareStatement(sql)) { + ps1.setString(1, gmlid); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getString("name"), not("海老名高校前")); + } + else { + fail(); + } + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * gmlid:n3249, area:14, name:本郷コミュニティセンター + * 35.41318 139.39504 + * + * ノード: 本郷コミュニティセンター (2032107504) + * 場所: 35.4134755, 139.3952826 + * disused:highway=bus_stop + */ + @Test + public void test02Busstop_n3249() { + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + String gmlid = getGmlid(hsqldb, + Double.parseDouble("35.41318"), + Double.parseDouble("139.39504"), 150); + assertNotNull(gmlid); + + String sql = "SELECT * FROM "+ DbBusstop.TABLE_NAME +" where gmlid=? and area=14"; + try (PreparedStatement ps1 = hsqldb.prepareStatement(sql)) { + ps1.setString(1, gmlid); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getString("name"), is("本郷コミュニティセンター")); + assertThat(rset1.getInt("fixed1"), is(50)); + assertThat(rset1.getBoolean("removed"), is(true)); + } + else { + fail(); + } + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * DbExistBusstopが読み取れること + * + */ + @Test + public void test03Busstop_dataread() { + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + String sqlStr1 = "SELECT * FROM "+ DbExistBusstop.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=?"; + System.out.println(sqlStr1); + try ( PreparedStatement ps2 = hsqldb.prepareStatement(sqlStr2); + PreparedStatement ps1 = hsqldb.prepareStatement(sqlStr1); + PreparedStatement ps3 = hsqldb.prepareStatement(sqlStr3);) + { + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + double lat = rset1.getDouble("lat"); + double lon = rset1.getDouble("lon"); + int score = rset1.getInt("score"); + String name = rset1.getString("name"); + if ((name != null) && !name.trim().isEmpty()) { + score = 50; + } + String gmlid; + int area; + int fixed1; + + // 指定の緯度経度を中心とする半径200x2m四方の矩形領域 + assertTrue(lat > 20.0D); + assertTrue(lat < 50.0D); + assertTrue(lon < 155.0D); + assertTrue(lon > 110.0D); + + 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); + ps2.setDouble(4, rect.maxlon); + try (ResultSet rset2 = ps2.executeQuery()) { + while (rset2.next()) { + System.out.print("{"); + gmlid = rset2.getString("gmlid"); + System.out.print("gmlid:"+gmlid +", "); + area = rset2.getInt("area"); + System.out.print("area:"+area +", "); + fixed1 = rset2.getInt("fixed1"); + fixed1 += score; + System.out.print("fixed1:"+fixed1 +", "); + String ksjName = rset2.getString("name"); + System.out.print("ksjName:"+ksjName +", "); + double lat2 = rset2.getDouble("lat"); + System.out.print("lat2:"+lat2 +", "); + double lon2 = rset2.getDouble("lon"); + System.out.print("lon2:"+lon2 +", "); + + double dd = Japan.distanceKm(lat,lon,lat2,lon2); + System.out.print("dd:"+dd +" "); + System.out.println("}"); + + + System.out.println("distance() -> "+ dd +"(km)"); + if ( ((dd * 1000.0D) < NEER) + || (((dd * 1000.0D) < NEER*2) && (name != null) && (name.equals(ksjName)))) + { + System.out.println(sqlStr3 +" ["+ fixed1 +", "+ gmlid +", "+ area +"]"); + ps3.setInt(1, fixed1); + ps3.setString(2, gmlid); + ps3.setInt(3, area); + //ps3.executeUpdate(); + } + } + } + } + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * （４）DISUSEDをファイルに出力する + * java osm.jp.coverage.busstop.DbBusstop -OUTPUT GML_BUSSTOP/P11-10.removed.txt + * + * @throws Exception + */ + @Test + public void test40_main_output() throws Exception { + int cnt = 0; + String databaseName = Coverage.DB_PORP_LOCALDB; + String tableName = "busstop"; + + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'BUSSTOP REMOVED=TRUE' table count = " + cnt); + } + catch (Exception e) { + fail(e.toString()); + } + + try { + String[] args = new String[]{"-OUTPUT", "P11-10.removed.json.txt"}; + DbBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + File file = new File("P11-10.removed.json.txt"); + assertThat(file.exists(), is(true)); + + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt1 = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'BUSSTOP REMOVED=TRUE' table count = " + cnt1); + assertThat(cnt1, is(cnt)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + String getGmlid(Connection hsqldb, double lat, double lon, int neer) throws SQLException { + double mind = 99999.9D; + String ret = null; + + // 指定の緯度経度を中心とする半径NEERx2m四方の矩形領域 + RectArea rect = new RectArea(lat, lon, neer * 2); // NEERm 四方 + String sqlStr2 = "SELECT * FROM " + + DbBusstop.TABLE_NAME + +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"; + try (PreparedStatement ps2 = hsqldb.prepareStatement(sqlStr2)) { + ps2.setDouble(1, rect.minlat); + ps2.setDouble(2, rect.maxlat); + ps2.setDouble(3, rect.minlon); + ps2.setDouble(4, rect.maxlon); + try (ResultSet rset2 = ps2.executeQuery()) { + while (rset2.next()) { + String gmlid = rset2.getString("gmlid"); + double lat2 = rset2.getDouble("lat"); + double lon2 = rset2.getDouble("lon"); + double dd = Japan.distanceKm(lat,lon,lat2,lon2); + if (dd < mind) { + mind = dd; + ret = gmlid; + } + } + } + } + return ret; + } +} diff --git a/test/osm/jp/coverage/busstop/BusstopTest.java b/test/osm/jp/coverage/busstop/BusstopTest.java index c9ad97b..c4769be 100644 --- a/test/osm/jp/coverage/busstop/BusstopTest.java +++ b/test/osm/jp/coverage/busstop/BusstopTest.java @@ -137,7 +137,7 @@ } /** - * ノード: 4940018338 + * ノード: 4940018338 （奥牧野） * 場所: 35.5909251, 139.1498642, area=14 * highway=bus_stop, name=null, bus=yes, public_transport=platform, operator=神奈中 * nameなし @@ -157,7 +157,41 @@ ps1.setString(1, gmlid); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { - assertThat(rset1.getInt("fixed1"), is(2)); + assertThat(rset1.getInt("fixed1"), is(3)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + /** + * ノード: 古里団地 (2217968698, 2217968723), + * 場所: 35.4002371, 139.4160855, area=14 + * highway=bus_stop, bus=yes, public_transport=platform + */ + @Test + public void test02Busstop_2217968698() { + try (Connection hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB)) { + String gmlid = getGmlid(hsqldb, + Double.parseDouble("35.4002371"), + Double.parseDouble("139.4160855"), 150); + assertNotNull(gmlid); + try ( + PreparedStatement ps1 = hsqldb.prepareStatement( + "SELECT * FROM "+ DbBusstop.TABLE_NAME +" where gmlid=? and area=14" + ) + ) { + ps1.setString(1, gmlid); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("fixed1"), is(100)); assertThat(rset1.getBoolean("removed"), is(false)); } else { diff --git a/test/osm/jp/coverage/busstop/DbBusstopCheck.java b/test/osm/jp/coverage/busstop/DbBusstopCheck.java new file mode 100644 index 0000000..180bf9f --- /dev/null +++ b/test/osm/jp/coverage/busstop/DbBusstopCheck.java @@ -0,0 +1,167 @@ +package osm.jp.coverage.busstop; + +import hayashi.tools.files.DeleteDir; +import java.io.File; +import java.sql.Connection; +import jp.co.areaweb.tools.database.DatabaseTool; +import static org.hamcrest.CoreMatchers.is; +import org.junit.AfterClass; +import static org.junit.Assert.assertThat; +import static org.junit.Assert.fail; +import org.junit.BeforeClass; +import org.junit.FixMethodOrder; +import org.junit.Test; +import org.junit.runners.MethodSorters; +import osm.jp.api.Coverage; +import osm.jp.coverage.DbTest; +import tools.Copy; + +@FixMethodOrder (MethodSorters.NAME_ASCENDING) +public class DbBusstopCheck extends DbTest { + @SuppressWarnings("FieldNameHidesFieldInSuperclass") + public static String gmlFolderName = "GML_BUSSTOP"; + + String databaseName = Coverage.DB_PORP_LOCALDB; + String tableName = "busstop"; + static final int REMOVED_COUNT = 12; + static final String REMOVED_FILE_NAME = "P11-10.removed.json.txt"; + + /** + * Folder[GML_BUSSTOP]がなければ作る + * tar xvf + * + * @throws Exception + */ + @BeforeClass + public static void setUpClass() throws Exception { + DbTest.gmlFolderName = gmlFolderName; + DbTest.setUpClass(); + + Copy.copyFile( + new File("data", REMOVED_FILE_NAME), + new File(gmlFolderName, REMOVED_FILE_NAME) + ); + } + + @AfterClass + public static void tearDownClass() throws Exception { + DbTest.gmlFolderName = gmlFolderName; + DbTest.tearDownClass(); + File file = new File(REMOVED_FILE_NAME); + if (file.exists()) { + DeleteDir.delete(file); + } + } + + /** + * （１）テーブルを初期化する + * + * @throws Exception + */ + @Test + public void t10_main_init() throws Exception { + DbTest.checkDatabase(databaseName); + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + assertThat(DbTest.isTable(hsqldb, tableName), is(true)); + assertThat(DbTest.getRecordCount(hsqldb, tableName) > 250000, is(true)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * （２）GMLファイルを読み取る(国土数値情報の読み取り) + * + * @throws Exception + */ + @Test + public void t25_main_import() throws Exception { + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt = DbTest.getRecordCount(hsqldb, tableName); + System.out.println("'"+ tableName +"' table count = " + cnt); + assertThat(cnt > 100, is(true)); + + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'"+ tableName +" REMOVED=TRUE' table count = " + cnt); + assertThat(cnt > 10, is(true)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * （３）DISUSEDなPOIを読み込む + * + * @throws Exception + */ + @Test + public void t30_main_removed() throws Exception { + File file = new File(gmlFolderName, REMOVED_FILE_NAME); + if (!file.exists()) { + Copy.copyFile(new File("data",REMOVED_FILE_NAME), file); // Apache common-io + } + + int tableCount; + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + tableCount = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'FUEL REMOVED=TRUE' table count = " + tableCount); + assertThat(tableCount >= REMOVED_COUNT, is(true)); + } + catch (Exception e) { + fail(e.toString()); + } + } + + /** + * （４a）DISUSEDをファイルに出力する + * + * @throws Exception + */ + @Test + public void t40_main_output() throws Exception { + String fileName = "P11-10.removed.json.txt"; + int cnt; + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'BUSSTOP REMOVED=TRUE' table count = " + cnt); + } + catch (Exception e) { + fail(e.toString()); + } + + try { + String[] args = new String[]{"-OUTPUT", fileName}; + DbBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + File file = new File(fileName); + assertThat(getFileLineCount(file) >= REMOVED_COUNT, is(true)); + } + + /** + * （４b）DISUSEDをファイルに出力する + * + * @throws Exception + */ + @Test + public void t41_main_output() throws Exception { + File file = new File("GML_BUSSTOP",REMOVED_FILE_NAME); + assertThat(file.exists(), is(true)); + + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt1 = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'BUSSTOP REMOVED=TRUE' table count = " + cnt1); + assertThat(cnt1 >= REMOVED_COUNT, is(true)); + } + catch (Exception e) { + fail(e.toString()); + } + assertThat(getFileLineCount(file) >= REMOVED_COUNT, is(true)); + } + +} diff --git a/test/osm/jp/coverage/busstop/DbExistBusstopCheck.java b/test/osm/jp/coverage/busstop/DbExistBusstopCheck.java new file mode 100644 index 0000000..b4f2a13 --- /dev/null +++ b/test/osm/jp/coverage/busstop/DbExistBusstopCheck.java @@ -0,0 +1,253 @@ +package osm.jp.coverage.busstop; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import javax.json.Json; +import javax.json.JsonArrayBuilder; +import javax.json.JsonBuilderFactory; +import javax.json.JsonObject; +import javax.json.JsonObjectBuilder; +import jp.co.areaweb.tools.database.DatabaseTool; +import static org.hamcrest.CoreMatchers.is; +import org.junit.*; +import static org.junit.Assert.*; +import org.junit.runners.MethodSorters; +import osm.jp.api.Coverage; +import static osm.jp.api.Osmdb.POINT_BUS_NO; +import static osm.jp.api.Osmdb.POINT_FIXME; +import static osm.jp.api.Osmdb.POINT_NO_NAME; +import osm.jp.coverage.DbTest; + +/** + * + * @author yuu + */ +@FixMethodOrder (MethodSorters.NAME_ASCENDING) +public class DbExistBusstopCheck { + String databaseName = Coverage.DB_PORP_LOCALDB; + String tableName = "BUSSTOP_EXIST"; + + /** + * （１）テーブルを初期化する + * + * @throws Exception + */ + + /** + * （２）GMLファイルを読み取る(国土数値情報の読み取り) + * + * @throws Exception + */ + @Test + public void t21_main_import() { + try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { + int cnt = DbTest.getRecordCount(hsqldb, tableName); + System.out.println("'BUSSTOP' table count = " + cnt); + assertThat(cnt > 100, is(true)); + + cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); + System.out.println("'BUSSTOP REMOVED=TRUE' table count = " + cnt); + assertThat(cnt > 0, is(true)); + + String sql = "SELECT * FROM "+ tableName +" WHERE "; + + // REMOVED + PreparedStatement ps1 = hsqldb.prepareStatement(sql +"removed=true"); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + String idref = rset1.getString("idref"); + int area = rset1.getInt("area"); + int score = rset1.getInt("score"); + System.out.println( + String.format("'BUSSTOP REMOVED'{idref='%s', area=%d, score=%d}", idref, area, score) + ); + //assertThat(score >= 50, is(true)); + } + } + + // ノード: 八幡前 (3152604023) + // 場所: 33.9808001, 133.3123441 + // fixme有り + ps1 = hsqldb.prepareStatement(sql +"idref='3152604023'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + // fixme有り + assertThat(rset1.getInt("score"), is(1)); + assertThat(checkRenge(rset1, "33.9808001", "133.3123441"), is(true)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + + // ノード: 4940018338 + // 場所: 35.5909251, 139.1498642 + // highway=bus_stop, name=null, bus=yes,public_transport=platform + ps1 = hsqldb.prepareStatement(sql + "idref='4940018338'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + // nameなし + assertThat(rset1.getInt("score"), is(1)); + assertThat(checkRenge(rset1, "35.5909251", "139.1498642"), is(true)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + + // ノード: 海老名高校前 (2043102034) + // 場所: 35.4435042, 139.3878934 + // disused:highway=bus_stop + // operator=海老名市コミュニティバス + // bench=yes + // ref=11 + ps1 = hsqldb.prepareStatement(sql + "idref='2043102034'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(checkRenge(rset1, "35.4435042", "139.3878934"), is(true)); + assertThat(rset1.getBoolean("removed"), is(true)); + } + else { + fail(); + } + } + + // ノード: 厚木ナイロン (1995040609) + // 場所: 35.4433312, 139.3932098 + // public_transport=stop_position,bus=yes + ps1 = hsqldb.prepareStatement(sql + "idref='1995040609'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(checkRenge(rset1, "35.4433312", "139.3932098"), is(true)); + assertThat(rset1.getBoolean("removed"), is(false)); + fail(); + } + else { + // public_transport=stop_positionは、対象外が正解 + } + } + + // ウェイ: 国分寺台第12 (154659062) + // bus_station + ps1 = hsqldb.prepareStatement(sql + "idref='154659062'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + + // ノード: 古里団地 (2217968723) + // 場所: 35.4005142, 139.4160047 + // public_transport=platform,bus=yes,highway=bus_stop + ps1 = hsqldb.prepareStatement(sql + "idref='2217968723'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + + // ノード: 古里団地 (2217968698) + // 場所: 35.4000350, 139.4162522 + // public_transport=platform,bus=yes,highway=bus_stop + ps1 = hsqldb.prepareStatement(sql + "idref='2217968698'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + } + catch (Exception e) { + fail(e.toString()); + } + } + + @Test + public void test03DbExistBusstopTest_busstop() { + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB); + DbExistBusstop osmExist = new DbExistBusstop(hsqldb); + JsonBuilderFactory factory = Json.createBuilderFactory(null); + JsonArrayBuilder abuilder = factory.createArrayBuilder(); + + JsonObjectBuilder builder = factory.createObjectBuilder(); + builder.add("k","public_transport"); + builder.add("v", "platform"); + JsonObject tag = builder.build(); + abuilder.add(tag); + + builder = factory.createObjectBuilder(); + builder.add("k","bus"); + builder.add("v", "yes"); + tag = builder.build(); + abuilder.add(tag); + + osmExist.readExistingSub(abuilder.build(), POINT_BUS_NO | POINT_NO_NAME | POINT_FIXME, false); + } catch (Exception ex) { + fail(ex.toString()); + } finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + + } + + /** + * （3）'table.OSM_EXIST'の内容をCSV形式にして標準出力に出力する + * + * @throws Exception + */ + @Test + public void t30_main_expport() throws Exception { + try { + String[] args = new String[]{"-EXPORT"}; + DbExistBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + } + + boolean checkRenge(ResultSet rset, String latStr, String lonStr) throws SQLException { + if (checkRenge(rset.getDouble("lat"), latStr)) { + if (checkRenge(rset.getDouble("lon"), lonStr)) { + return true; + } + } + return false; + } + + boolean checkRenge(double d1, String str) throws SQLException { + double base = Double.parseDouble(str); + double up = d1 + 0.00000009D; + double down = d1 - 0.00000009D; + boolean ret = true; + if (Double.compare(base, up) > 0) { + ret = false; + } + if (Double.compare(base, down) < 0) { + ret = false; + } + System.out.println("d1: "+ d1 +" : "+ str +" --> "+ (ret ? "IN" : "out")); + return ret; + } +} diff --git a/test/osm/jp/coverage/busstop/DbExistBusstopTest.java b/test/osm/jp/coverage/busstop/DbExistBusstopTest.java index 36aac6b..4f1731f 100644 --- a/test/osm/jp/coverage/busstop/DbExistBusstopTest.java +++ b/test/osm/jp/coverage/busstop/DbExistBusstopTest.java @@ -188,9 +188,10 @@ assertThat(rset1.getInt("score"), is(50)); assertThat(checkRenge(rset1, "35.4433312", "139.3932098"), is(true)); assertThat(rset1.getBoolean("removed"), is(false)); + fail(); } else { - fail(); + // public_transport=stop_positionは、対象外が正解 } } @@ -206,6 +207,34 @@ fail(); } } + + // ノード: 古里団地 (2217968723) + // 場所: 35.4005142, 139.4160047 + // public_transport=platform,bus=yes,highway=bus_stop + ps1 = hsqldb.prepareStatement(sql + "idref='2217968723'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } + + // ノード: 古里団地 (2217968698) + // 場所: 35.4000350, 139.4162522 + // public_transport=platform,bus=yes,highway=bus_stop + ps1 = hsqldb.prepareStatement(sql + "idref='2217968698'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } + } } catch (Exception e) { fail(e.toString()); diff --git a/test/osm/jp/coverage/busstop/ToPostgisCheck.java b/test/osm/jp/coverage/busstop/ToPostgisCheck.java new file mode 100644 index 0000000..67f6735 --- /dev/null +++ b/test/osm/jp/coverage/busstop/ToPostgisCheck.java @@ -0,0 +1,57 @@ +package osm.jp.coverage.busstop; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import jp.co.areaweb.tools.database.DatabaseTool; +import static org.hamcrest.CoreMatchers.is; +import org.junit.Test; +import static org.junit.Assert.*; + +public class ToPostgisCheck { + + /** + * 実行 + * java -cp $CLASSPATH osm.jp.coverage.busstop.ToPostgis osmdb + * java -cp $CLASSPATH osm.jp.coverage.busstop.ToPostgis gisdb + * + * ToPostgis.main(new String[0]); + * + */ + @Test + public void checkBusstopToPostgis_main() { + } + + /** + * ノード: 古里団地 (2217968698, 2217968723), + * 場所: 35.4002371, 139.4160855, area=14, GMLID=n3009 + * highway=bus_stop, bus=yes, public_transport=platform + */ + @Test + public void checkBusstopToPostgis_2217968698() { + String dbProp = "osmdb"; + String gmlid = "n3009"; + int area = 14; // 神奈川県 + String sql = String.format("SELECT * FROM t_%s where gmlid=? and area=?", DbBusstop.TABLE_NAME); + + try (Connection conHsql = DatabaseTool.openDb(dbProp); + PreparedStatement ps1 = conHsql.prepareStatement(sql)) + { + ps1.setString(1, gmlid); + ps1.setInt(2, area); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("fixed"), is(100)); + assertThat(rset1.getInt("code"), is(0)); + } + else { + fail(); + } + } + } + catch (Exception ex) { + fail(ex.toString()); + } + } + +} diff --git a/test/osmdb.properties b/test/osmdb.properties index 01f08cd..2c9c5d3 100644 --- a/test/osmdb.properties +++ b/test/osmdb.properties @@ -1,4 +1,8 @@ db_driver=org.postgresql.Driver -db_url=jdbc:postgresql://172.17.0.1:5432/osmdb -db_user=alex +#db_url=jdbc:postgresql://172.17.0.1:5432/osmdb +#db_user=alex +#db_passwd=password + +db_url=jdbc:postgresql://192.168.0.26:5432/osmdb +db_user=postgres db_passwd=password