diff --git a/src/LICENSE.txt b/src/LICENSE.txt new file mode 100644 index 0000000..068b77e --- /dev/null +++ b/src/LICENSE.txt @@ -0,0 +1,21 @@ +Copyright (c) 2018 Yuu Hayashi +http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage + +Permission is hereby granted, free of charge, to any person obtaining a +copy of this software and associated documentation files (the +"Software"), to deal in the Software without restriction, including +without limitation the rights to use, copy, modify, merge, publish, +distribute, sublicense, and/or sell copies of the Software, and to +permit persons to whom the Software is furnished to do so, subject to +the following conditions: + +The above copyright notice and this permission notice shall be +included in all copies or substantial portions of the Software. + +THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, +EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF +MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND +NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE +LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION +OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION +WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. \ No newline at end of file diff --git a/src/README.md b/src/README.md new file mode 100644 index 0000000..bd45e5e --- /dev/null +++ b/src/README.md @@ -0,0 +1,7 @@ +# osmCoverage + +* LICENSE: [MIT v1.0](LICENSE.txt) +* Repository: [GitBucket](http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage) + +----- + diff --git a/src/coverage_BUSSTOP.sh b/src/coverage_BUSSTOP.sh index 7facd50..d03052d 100755 --- a/src/coverage_BUSSTOP.sh +++ b/src/coverage_BUSSTOP.sh @@ -1,23 +1,48 @@ -cd /home/yuu/Desktop/workspace/osmCoverageBin -cp ../osmCoverage/dist/osmCoverage.jar . -rm -r database +# +# HOST ~/docker/osm_get +# +# cd ~/docker/osm_get +# docker run --rm -it -v $(pwd)/share:/root/osmCoverageBin/share haya4/osmget bash +# docker run --rm -t -v $(pwd)/share:/root/osmCoverageBin/share haya4/osmget bash coverage_BUSSTOP_.sh +# +pwd +cp share/pbfDate.json GML_BUSSTOP/pbfDate.json +curl -o GML_BUSSTOP/P11-10.removed.json.txt https://raw.githubusercontent.com/yuuhayashi/coverageWeb/master/GML_BUSSTOP/P11-10.removed.json.txt + +# make to PostGIS mkdir database +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.busstop.DbBusstop -INIT +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.busstop.DbBusstop -IMPORT GML_BUSSTOP +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.busstop.DbBusstop -REMOVED GML_BUSSTOP/P11-10.removed.json.txt -rm */update.sql -#java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.DbBusstop -drop -java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.DbBusstop -#java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.DbExistBusstop -drop -java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.DbExistBusstop -java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.Busstop +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.DbExistBusstop -INIT +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.DbExistBusstop -IMPORT -java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.ToCartoCSV -java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.ToPostgis -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.busstop.Coverage coverage.busstop.csv -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.busstop.Coverage coverage.busstop.json -git commit -a -m "coverage update BUSSTOP $(date +%Y-%m-%d_%H:%M)" +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.Busstop +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.busstop.DbBusstop -OUTPUT GML_BUSSTOP/P11-10.removed.txt +mv GML_BUSSTOP/P11-10.removed.txt GML_BUSSTOP/P11-10.removed.json.txt -cp coverage.busstop.json ../www-root/coverage -# cp busstop.carto.csv ../www-root/coverage -cd ../www-root -git add . -git commit -a -m "coverage busstop update $(date +%Y-%m-%d_%H:%M)" +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.busstop.ToPostgis postgis +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.busstop.ToPostgis gisdb + + +## create 't_busstop.carto.csv' +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.ToCartoCSV +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.ToGeoJSON + +ls -l +rm -rf vtile +mkdir -m +w vtile +cp GML_BUSSTOP/pbfDate.json vtile +mv GML_BUSSTOP/busstop?.json vtile +tar czf share/busstopJSON.tgz vtile/*.json + +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.ToGeoJSON -slim +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.Coverage GML_BUSSTOP/coverage.busstop.csv +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.Coverage GML_BUSSTOP/coverage.busstop.json +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.busstop.ExportCSV +mv t_*.csv GML_BUSSTOP + +tar czf share/coverage.busstopJSON.tgz GML_BUSSTOP/coverage.*.json GML_BUSSTOP/*.csv GML_BUSSTOP/pbfDate.json GML_BUSSTOP/*.txt + +chmod -R 777 share diff --git a/src/coverage_FUEL.sh b/src/coverage_FUEL.sh index c237f30..9abf86f 100755 --- a/src/coverage_FUEL.sh +++ b/src/coverage_FUEL.sh @@ -1,9 +1,15 @@ -cd /home/yuu/Desktop/workspace/osmCoverageBin -git checkout master -cp ../osmCoverage/dist/osmCoverage.jar . -rm -r database -mkdir database +# +# HOST ~/docker/osm_get +# +# cd ~/docker/osm_get +# docker run --rm -it -v $(pwd)/share:/root/osmCoverageBin/share haya4/osmget bash +# docker run --rm -t -v $(pwd)/share:/root/osmCoverageBin/share haya4/osmget bash coverage_FUEL_.sh +# +pwd +cp share/pbfDate.json GML_FUEL/pbfDate.json +curl -o GML_FUEL/P07-15.removed.json.txt https://raw.githubusercontent.com/yuuhayashi/coverageWeb/master/GML_FUEL/P07-15.removed.json.txt +mkdir database java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -INIT java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -IMPORT GML_FUEL java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -REMOVED GML_FUEL/P07-15.removed.json.txt @@ -12,16 +18,33 @@ java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbExistFuel -IMPORT java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.Fuel -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -OUTPUT GML_FUEL/P07-15.removed.json.txt +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.DbFuel -OUTPUT GML_FUEL/P07-15.removed.txt +mv GML_FUEL/P07-15.removed.txt GML_FUEL/P07-15.removed.json.txt java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.fuel.ToPostgis postgis java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar osm.jp.coverage.fuel.ToPostgis gisdb -cp coverage.fuel.json GML_FUEL -cp coverage.fuel.csv GML_FUEL -cp fuel?.json GML_FUEL -rm coverage.fuel.* -git add * -git commit -a -m "coverage fuel update $(date +%Y-%m-%d_%H:%M)" -git push gitbucket master +## create 't_fuel.carto.csv' +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.ToCartoCSV +## export from PostGIS to 'fuel.json fuel0.json fuel1.json fuel2.json' +## create 'fuelJSON.tgz' from 'fuel.json fuel0.json fuel1.json fuel2.json' +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.ToGeoJSON +rm -rf vtile +mkdir -m +w vtile +cp GML_FUEL/pbfDate.json vtile +mv fuel?.json vtile +tar czf share/fuelJSON.tgz vtile/*.json + +## create 'coverage.fuelJSON.tgz' +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.ToGeoJSON -slim +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.Coverage coverage.fuel.csv +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.Coverage coverage.fuel.json +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb.jar:postgresql.jar:javax.json.jar osm.jp.coverage.fuel.ExportCSV +mv t_*.csv GML_FUEL + +ls -l +mv fuel?.json GML_FUEL +mv fuel.json GML_FUEL +tar czf share/coverage.fuelJSON.tgz GML_FUEL/coverage.*.json GML_FUEL/*.csv GML_FUEL/pbfDate.json GML_FUEL/*.json.txt +chmod -R 777 share diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index 7aac330..f9709d9 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -614,7 +614,6 @@ String sortStr = "ORDER BY area,gmlid"; String sql = String.format("SELECT * %s %s %s", fromStr, whereStr, sortStr); - removedFile.deleteOnExit(); try (FileWriter fw = new FileWriter(removedFile); PreparedStatement ps1 = hsqldb.prepareStatement(sql)) { @@ -628,7 +627,7 @@ JsonObjectBuilder jsonBuilder = Json.createObjectBuilder(); jsonBuilder.add("gmlid", gmlid); - jsonBuilder.add("idref", idref); + jsonBuilder.add("idref", (idref==null ? "" : idref)); jsonBuilder.add("area", area); JsonObject value = jsonBuilder.build(); String line = value.toString(); @@ -638,6 +637,7 @@ fw.flush(); } } + fw.close(); } } diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index 7a3a11f..a648ad8 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -54,20 +54,19 @@ * @throws TransformerException */ public Busstop(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - String sqlStr4 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"; - try (PreparedStatement ps4 = con.prepareStatement(sqlStr4)) { - System.out.println(sqlStr4); - ps4.executeUpdate(); + String sqlStr0 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"; + try (PreparedStatement ps0 = con.prepareStatement(sqlStr0)) { + System.out.println(sqlStr0); + ps0.executeUpdate(); } - String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ DbExistBusstop.TABLE_NAME; + String sqlStr1 = "SELECT idref,lat,lon,score,name,removed 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=?"; - 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 ps5 = con.prepareStatement(sqlStr5)) + String sqlStr3 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=?,removed=? WHERE gmlid=? and area=?"; + try ( + PreparedStatement ps1 = con.prepareStatement(sqlStr1); + PreparedStatement ps2 = con.prepareStatement(sqlStr2); + PreparedStatement ps3 = con.prepareStatement(sqlStr3)) { System.out.println(sqlStr1); try (ResultSet rset1 = ps1.executeQuery()) { @@ -76,6 +75,7 @@ double lon = rset1.getDouble("lon"); int score = rset1.getInt("score"); String name = rset1.getString("name"); + boolean removed = rset1.getBoolean("removed"); String gmlid = null; int area = 0; int fixed1 = 0; @@ -111,18 +111,22 @@ if ( ((dd * 1000.0D) < NEER) || (((dd * 1000.0D) < NEER*2) && (name != null) && (name.equals(ksjName)))) { - System.out.println(sqlStr3 +" ["+ fixed1 +", "+ gmlid +", "+ area +"]"); + System.out.println(sqlStr3 +" ["+ fixed1 +", "+ removed +", "+ gmlid +", "+ area +"]"); ps3.setInt(1, fixed1); - ps3.setString(2, gmlid); - ps3.setInt(3, area); + ps3.setBoolean(2, removed); + ps3.setString(3, gmlid); + ps3.setInt(4, area); ps3.executeUpdate(); } } } } } - - System.out.println(sqlStr5); + } + + String sqlStr5 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; + System.out.println(sqlStr5); + try (PreparedStatement ps5 = con.prepareStatement(sqlStr5)) { ps5.executeUpdate(); } } diff --git a/test/osm/jp/coverage/busstop/AllTest.java b/test/osm/jp/coverage/busstop/AllTest.java index 70a3b8e..64e1f07 100644 --- a/test/osm/jp/coverage/busstop/AllTest.java +++ b/test/osm/jp/coverage/busstop/AllTest.java @@ -9,8 +9,8 @@ @RunWith(Suite.class) @Suite.SuiteClasses({ - DbExistBusstopTest.class, DbBusstopTest.class, + DbExistBusstopTest.class, BusstopTest.class, ToPostgisTest.class, CoverageTest.class diff --git a/test/osm/jp/coverage/busstop/BusstopTest.java b/test/osm/jp/coverage/busstop/BusstopTest.java index d4c7ec6..d6d8c15 100644 --- a/test/osm/jp/coverage/busstop/BusstopTest.java +++ b/test/osm/jp/coverage/busstop/BusstopTest.java @@ -4,14 +4,17 @@ 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.Japan; import osm.jp.api.RectArea; +import osm.jp.coverage.DbTest; import osm.jp.coverage.PoiTest; import static osm.jp.coverage.busstop.Busstop.NEER; @@ -30,16 +33,24 @@ public void tearDown() throws Exception { } + /** + * java Busstop + * + */ @Test public void test00Busstop_main() { String[] args = new String[0]; try { Busstop.main(args); } catch (Exception ex) { - fail(); + fail(ex.getLocalizedMessage()); } } + /** + * + * + */ @Test public void test01Busstop_dataread() { try (Connection hsqldb = DatabaseTool.openDb("database")) { @@ -55,6 +66,7 @@ System.out.println(rset1.getString("name")); System.out.println(rset1.getDouble("lat")); System.out.println(rset1.getDouble("lon")); + System.out.println(rset1.getBoolean("removed")); } } } @@ -63,6 +75,9 @@ } } + /** + * DbExistBusstop.TABLE_NAME にレコードが１件以上存在すること + */ @Test public void test02Busstop_busstop() { File dir = new File("database"); @@ -122,26 +137,31 @@ /** * ノード: 4940018338 - * 場所: 35.5909251, 139.1498642 + * 場所: 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("database")) { - PreparedStatement ps1 = hsqldb.prepareStatement( - "SELECT * FROM " - + DbExistBusstop.TABLE_NAME - +" where idref='4940018338'" - ); - try (ResultSet rset1 = ps1.executeQuery()) { - if (rset1.next()) { - // nameなし - assertThat(rset1.getInt("score"), is(0)); - assertThat(checkRenge(rset1, "35.5909251", "139.1498642"), is(true)); - assertThat(rset1.getBoolean("removed"), is(false)); - } - else { - fail(); + 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.getInt("fixed1"), is(2)); + assertThat(rset1.getBoolean("removed"), is(false)); + } + else { + fail(); + } } } } @@ -154,21 +174,26 @@ * ノード: 海老名高校前 (2043102034) * 場所: 35.4435042, 139.3878934 * highway=bus_stop + * 廃止されたバス停、付近にGML(gmlid:n4770, area:14) */ @Test public void test02Busstop_2043102034() { try (Connection hsqldb = DatabaseTool.openDb("database")) { - PreparedStatement ps1 = hsqldb.prepareStatement( - "SELECT * FROM "+ DbExistBusstop.TABLE_NAME +" where 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(); + 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(); + } } } } @@ -178,24 +203,33 @@ } /** - * ノード: 厚木ナイロン (1995040609) - * 場所: 35.4433312, 139.3932098 - * public_transport=stop_position,bus=yes + * gmlid:n3249, area:14, name:本郷コミュニティセンター + * 35.41318 139.39504 + * + * ノード: 本郷コミュニティセンター (2032107504) + * 場所: 35.4134755, 139.3952826 + * disused:highway=bus_stop */ @Test - public void test02Busstop_1995040609() { + public void test02Busstop_n3249() { try (Connection hsqldb = DatabaseTool.openDb("database")) { - PreparedStatement ps1 = hsqldb.prepareStatement( - "SELECT * FROM "+ DbExistBusstop.TABLE_NAME +" where 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)); - } - else { - fail(); + 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(); + } } } } @@ -205,31 +239,10 @@ } /** - * ウェイ: 国分寺台第12 (154659062) - * bus_station + * DbExistBusstopが読み取れること + * */ @Test - public void test02Busstop_154659062() { - try (Connection hsqldb = DatabaseTool.openDb("database")) { - PreparedStatement ps1 = hsqldb.prepareStatement( - "SELECT * FROM "+ DbExistBusstop.TABLE_NAME +" where idref='154659062'" - ); - try (ResultSet rset1 = ps1.executeQuery()) { - if (rset1.next()) { - assertThat(rset1.getInt("score"), is(50)); - assertThat(rset1.getBoolean("removed"), is(false)); - } - else { - fail(); - } - } - } - catch (Exception ex) { - fail(ex.toString()); - } - } - - @Test public void test03Busstop_dataread() { try (Connection hsqldb = DatabaseTool.openDb("database")) { String sqlStr1 = "SELECT * FROM "+ DbExistBusstop.TABLE_NAME; @@ -310,4 +323,74 @@ } } + /** + * （４）DISUSEDをファイルに出力する + * java osm.jp.coverage.busstop.DbBusstop -OUTPUT GML_BUSSTOP/P11-10.removed.txt + * + * @throws Exception + */ + @Test + public void t40_main_output() throws Exception { + int cnt = 0; + String databaseName = "database"; + 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", "GML_BUSSTOP/P11-10.removed.json.txt"}; + DbBusstop.main(args); + } + catch(Exception e) { + fail(e.toString()); + } + + File file = new File("GML_BUSSTOP", "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; + + // 指定の緯度経度を中心とする半径200x2m四方の矩形領域 + 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/DbBusstopTest.java b/test/osm/jp/coverage/busstop/DbBusstopTest.java index 33b74c7..76988af 100644 --- a/test/osm/jp/coverage/busstop/DbBusstopTest.java +++ b/test/osm/jp/coverage/busstop/DbBusstopTest.java @@ -148,7 +148,7 @@ try (Connection hsqldb = DatabaseTool.openDb(databaseName)) { int cnt = DbTest.getRecordCount(hsqldb, tableName, "REMOVED=TRUE"); System.out.println("'FUEL REMOVED=TRUE' table count = " + cnt); - assertThat(cnt, is(0)); + assertThat(cnt > 0, is(true)); } catch (Exception e) { fail(e.toString()); @@ -156,7 +156,7 @@ } /** - * （４）DISUSEDをファイルに出力する + * （４a）DISUSEDをファイルに出力する * * @throws Exception */ @@ -172,20 +172,28 @@ } try { - String[] args = new String[]{"-OUTPUT", "GML_BUSSTOP/P11-10.removed.json.txt"}; + String[] args = new String[]{"-OUTPUT", "P11-10.removed.json.txt"}; DbBusstop.main(args); } catch(Exception e) { fail(e.toString()); } - - File file = new File("GML_BUSSTOP", "P11-10.removed.json.txt"); + } + + /** + * （４b）DISUSEDをファイルに出力する + * + * @throws Exception + */ + @Test + public void t41_main_output() throws Exception { + 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)); + assertThat(cnt1 > 0, is(true)); } catch (Exception e) { fail(e.toString()); diff --git a/test/osm/jp/coverage/busstop/SuiteTest.java b/test/osm/jp/coverage/busstop/SuiteTest.java new file mode 100644 index 0000000..f276a48 --- /dev/null +++ b/test/osm/jp/coverage/busstop/SuiteTest.java @@ -0,0 +1,36 @@ +package osm.jp.coverage.busstop; + +import org.junit.After; +import org.junit.AfterClass; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.runner.RunWith; +import org.junit.runners.Suite; + +@RunWith(Suite.class) +@Suite.SuiteClasses({ + DbExistBusstopTest.class, + DbBusstopTest.class, + BusstopTest.class, + ToPostgisTest.class, + CoverageTest.class +}) +public class SuiteTest { + + @BeforeClass + public static void setUpClass() throws Exception { + } + + @AfterClass + public static void tearDownClass() throws Exception { + } + + @Before + public void setUp() throws Exception { + } + + @After + public void tearDown() throws Exception { + } + +}