diff --git a/src/osm/jp/coverage/busstop/DbExistBusstop.java b/src/osm/jp/coverage/busstop/DbExistBusstop.java index df1cbf3..ca10a48 100644 --- a/src/osm/jp/coverage/busstop/DbExistBusstop.java +++ b/src/osm/jp/coverage/busstop/DbExistBusstop.java @@ -113,6 +113,7 @@ * @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name' * @throws Exception エラー */ + @Override public void readExisting(Connection hsqldb, String sqlWhere, int point) throws Exception { Connection osmdb = null; long counter = 0L; diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java index 2fdb284..5d4a40c 100644 --- a/src/osm/jp/coverage/fuel/DbExist.java +++ b/src/osm/jp/coverage/fuel/DbExist.java @@ -1,9 +1,6 @@ package osm.jp.coverage.fuel; import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; import jp.co.areaweb.tools.database.*; import osm.jp.api.Osmdb; diff --git a/src/osm/jp/coverage/fuel/DbFuel.java b/src/osm/jp/coverage/fuel/DbFuel.java index 82ace0b..821f91a 100644 --- a/src/osm/jp/coverage/fuel/DbFuel.java +++ b/src/osm/jp/coverage/fuel/DbFuel.java @@ -95,7 +95,7 @@ } public static void dropTable (Connection conHsql) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - String sqlStr = "DROP TABLE FUEL"; + String sqlStr = "DROP TABLE IF EXISTS FUEL"; try (PreparedStatement ps = conHsql.prepareStatement(sqlStr)) { System.out.println(sqlStr); ps.executeUpdate(); diff --git a/src/osm/jp/coverage/police/DbExist.java b/src/osm/jp/coverage/police/DbExist.java index 723af9a..cbff489 100644 --- a/src/osm/jp/coverage/police/DbExist.java +++ b/src/osm/jp/coverage/police/DbExist.java @@ -1,10 +1,6 @@ package osm.jp.coverage.police; import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import jp.co.areaweb.tools.csv.CsvRecord; import jp.co.areaweb.tools.database.*; import osm.jp.api.Osmdb; @@ -84,7 +80,7 @@ public void getJapanCapabilities(Connection hsqldb) throws Exception { // 通常 → ５０ポイント // FIXMEあり → １ポイント - readExisting(hsqldb, "where amenity='police'", POINT_NO); + readExisting(hsqldb, "where amenity='police'", POINT_FIXME); readExistingSub(hsqldb, "disused:amenity", "police", POINT_NO); readExistingSub(hsqldb, "abandoned:amenity", "police", POINT_NO); readExistingSub(hsqldb, "demolished:amenity", "police", POINT_NO); @@ -94,126 +90,4 @@ readExistingSub(hsqldb, "no:amenity", "police", POINT_NO); } - /** - * 'table.POLICE'の内容をCSV形式にして標準出力に出力する - * @param hsqldb - * @throws java.sql.SQLException - */ - public static void export(Connection hsqldb) throws SQLException { - String header = "idref,lat,lon,score"; - System.out.println(header); - PreparedStatement ps8 = hsqldb.prepareStatement("SELECT idref,lat,lon,score FROM "+ TABLE_NAME); - try (ResultSet rset8 = ps8.executeQuery()) { - while (rset8.next()) { - String idcode = rset8.getString(1); - Double lat = rset8.getDouble(2); - Double lon = rset8.getDouble(3); - int score = rset8.getInt(4); - System.out.println("OSM: "+ idcode +","+ lat +","+ lon +","+ score); - } - } - } - - /** - * File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。
- * その際に、OSMノードを評価し、scoreを算定する - * - * @param hsqldb 反映先のデータベースコネクタ(HSQLDB) - * @param sqlWhere POI条件 - * @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name' - * @throws Exception エラー - */ - public void readExisting(Connection hsqldb, String sqlWhere, int point) throws Exception { - Connection osmdb = null; - long counter = 0L; - try { - osmdb = DatabaseTool.openDb("osmdb"); - String sqlNode = "select osm_id,brand,disused,name,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + sqlWhere; - PreparedStatement ps1 = osmdb.prepareStatement(sqlNode); - try (ResultSet rset1 = ps1.executeQuery()) { - while (rset1.next()) { - counter += importExistingNode(hsqldb, osmdb, rset1, point); - } - } - String sqlArea = "select osm_id,brand,disused,name,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon from planet_osm_polygon " + sqlWhere; - PreparedStatement ps2 = osmdb.prepareStatement(sqlArea); - try (ResultSet rset2 = ps2.executeQuery()) { - while (rset2.next()) { - counter += importExistingNode(hsqldb, osmdb, rset2, point); - } - } - - System.out.println("Exists Node count = " + counter); - } - finally { - DatabaseTool.closeDb(osmdb); - } - } - - /** - * - * SELECT - planet_osm_point.osm_id, - planet_osm_point.amenity, - planet_osm_point.brand, - planet_osm_nodes.id, - planet_osm_nodes.tags - FROM - public.planet_osm_point, - public.planet_osm_nodes - WHERE - planet_osm_point.osm_id = planet_osm_nodes.id and 'disused:amenity'=ANY(planet_osm_nodes.tags) and 'fuel'=ANY(planet_osm_nodes.tags); - * - * @param hsqldb - * @param kStr - * @param vStr - * @param point - * @throws Exception - */ - public void readExistingSub(Connection hsqldb, String kStr, String vStr, int point) throws Exception { - Connection osmdb = null; - long counter = 0L; - try { - osmdb = DatabaseTool.openDb("osmdb"); - - PreparedStatement ps1 = osmdb.prepareStatement("SELECT osm_id,brand,name,disused,tags,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon FROM planet_osm_point,planet_osm_nodes WHERE osm_id=id AND '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)"); - try (ResultSet rset1 = ps1.executeQuery()) { - while (rset1.next()) { - String tags = rset1.getString("tags"); - if (tags != null) { - tags = tags.substring(1); - tags = tags.substring(0, tags.length()-1); - CsvRecord csv = new CsvRecord(); - csv.analizeRecord(tags); - - boolean key = false; - for (String str : csv) { - if (key) { - if (str.equals(vStr)) { - counter += importExistingNode(hsqldb, osmdb, rset1, point); - break; - } - key = false; - } - else { - if (str.equals(kStr)) { - key = true; - } - } - } - } - } - } - PreparedStatement ps2 = osmdb.prepareStatement("SELECT osm_id,brand,disused,name,tags,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon FROM planet_osm_polygon,planet_osm_nodes WHERE osm_id=id AND '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)"); - try (ResultSet rset2 = ps2.executeQuery()) { - while (rset2.next()) { - counter += importExistingNode(hsqldb, osmdb, rset2, point); - } - } - System.out.println("Exists Node count = " + counter); - } - finally { - DatabaseTool.closeDb(osmdb); - } - } } \ No newline at end of file diff --git a/src/osm/jp/coverage/police/Police.java b/src/osm/jp/coverage/police/Police.java index 905a2d5..d9c384e 100644 --- a/src/osm/jp/coverage/police/Police.java +++ b/src/osm/jp/coverage/police/Police.java @@ -57,7 +57,7 @@ /** * HSQL_DB.OSM_EXITのscoreを集計して、HSQL_DB.POLICEのfixedに反映させる * - * @param con + * @param con HSQLDB * @throws SQLException * @throws FileNotFoundException * @throws ClassNotFoundException @@ -72,6 +72,11 @@ String sql2 = "SELECT idref,lat,lon,fixed, area FROM "+ DbPolice.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)"; String sql3 = "UPDATE "+ DbPolice.TABLE_NAME +" SET fixed1=? WHERE idref=? and area=?"; String sql5 = "UPDATE "+ DbPolice.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; + System.out.println(sql1); + System.out.println(sql2); + System.out.println(sql3); + System.out.println(sql4); + System.out.println(sql5); try ( PreparedStatement ps2 = con.prepareStatement(sql2); PreparedStatement ps1 = con.prepareStatement(sql1); PreparedStatement ps3 = con.prepareStatement(sql3); diff --git a/src/police.activity.violet.html b/src/police.activity.violet.html index 6cb4144..b834f43 100644 --- a/src/police.activity.violet.html +++ b/src/police.activity.violet.html @@ -48,7 +48,8 @@ DbPolice.main() 国土数値情報XMLを読み取って -HSQLDB.BUS_STOPテーブルに格納する +"HSQLDB.POLICE"と +"HSQLDB.POLICE2"テーブルに格納する @@ -898,1023 +899,1041 @@ ]]>

- embedded diagram image + embedded diagram image \ No newline at end of file diff --git a/test/osm/jp/coverage/fuel/FuelTest.java b/test/osm/jp/coverage/fuel/FuelTest.java index 2a08265..1dba643 100644 --- a/test/osm/jp/coverage/fuel/FuelTest.java +++ b/test/osm/jp/coverage/fuel/FuelTest.java @@ -12,7 +12,6 @@ import static org.junit.Assert.*; import org.junit.runners.MethodSorters; import osm.jp.api.HttpPOST; -import osm.jp.coverage.busstop.Busstop; @FixMethodOrder (MethodSorters.NAME_ASCENDING) public class FuelTest { @@ -25,9 +24,16 @@ } @Test + @SuppressWarnings("UseSpecificCatch") public void test00_Fuel_main() { + String[] args = new String[0]; - Busstop.main(args); + try { + Fuel.main(args); + } catch (Exception ex) { + Logger.getLogger(FuelTest.class.getName()).log(Level.SEVERE, null, ex); + fail(ex.toString()); + } } /** @@ -96,6 +102,7 @@ * GML: 14, n00375 */ @Test + @SuppressWarnings("UseSpecificCatch") public void test91_removed() { Connection hsqldb = null; String sql1 = String.format("SELECT * FROM %s where idref='%s'", HttpPOST.TABLE_NAME, "5338111023"); diff --git a/test/osm/jp/coverage/police/AllTest.java b/test/osm/jp/coverage/police/AllTest.java new file mode 100644 index 0000000..cd9563e --- /dev/null +++ b/test/osm/jp/coverage/police/AllTest.java @@ -0,0 +1,38 @@ +package osm.jp.coverage.police; + +import java.io.File; +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({ + DbExistTest.class, + DbPoliceTest.class, + PoliceTest.class +}) +public class AllTest { + + @BeforeClass + public static void setUpClass() throws Exception { + File dir = new File("database"); + dir.deleteOnExit(); + dir.mkdir(); + } + + @AfterClass + public static void tearDownClass() throws Exception { + } + + @Before + public void setUp() throws Exception { + } + + @After + public void tearDown() throws Exception { + } + +} diff --git a/test/osm/jp/coverage/police/DbExistTest.java b/test/osm/jp/coverage/police/DbExistTest.java new file mode 100644 index 0000000..59c2b2c --- /dev/null +++ b/test/osm/jp/coverage/police/DbExistTest.java @@ -0,0 +1,135 @@ +package osm.jp.coverage.police; + +import java.io.File; +import java.io.IOException; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.logging.Level; +import java.util.logging.Logger; +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 static osm.jp.api.Osmdb.create; + +@FixMethodOrder (MethodSorters.NAME_ASCENDING) +public class DbExistTest { + public void init() throws Exception { + } + + @Before + public void setUp() throws Exception { + } + + @After + public void tearDown() throws Exception { + } + + @Test + public void test020_main() { + try { + init(); + String[] args = new String[0]; + DbExist.main(args); + } + catch (Exception ex) { + fail(ex.toString()); + } + } + + @Test + public void test021_police() { + File dir = new File("database"); + assertTrue(dir.exists()); + assertTrue(dir.isDirectory()); + + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb("database"); + + PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM AREA_NODE"); + try (ResultSet rset8 = ps8.executeQuery()) { + if (rset8.next()) { + assertThat(rset8.getInt(1), is(0)); + } + else { + fail(); + } + } + + PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + long cnt = rset1.getLong(1); + assertThat((cnt > 0), is(true)); + } + else { + fail(); + } + } + + // ノード: 海老名警察署 (1420826312) 場所: 35.4454680, 139.3915960 + ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='1420826312'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(checkRenge(rset1, "35.4454680", "139.3915960"), is(true)); + } + else { + fail(); + } + } + + // ウェイ: 192565027 海老名警察署 場所: 35.4454680, 139.3915960 + ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='161877397'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(checkRenge(rset1, "35.4454680", "139.3915960"), is(true)); + } + else { + fail(); + } + } + + } catch (ClassNotFoundException ex) { + Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); + fail(); + } catch (SQLException ex) { + Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); + fail(); + } catch (IOException ex) { + Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); + fail(); + } finally { + DatabaseTool.closeDb(hsqldb); + } + } + + 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.0000005D; + double down = d1 - 0.0000005D; + 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/police/DbPoliceTest.java b/test/osm/jp/coverage/police/DbPoliceTest.java index 1cdcb4f..517cd58 100644 --- a/test/osm/jp/coverage/police/DbPoliceTest.java +++ b/test/osm/jp/coverage/police/DbPoliceTest.java @@ -13,12 +13,14 @@ import static org.hamcrest.CoreMatchers.is; import org.junit.*; import static org.junit.Assert.*; +import org.junit.runners.MethodSorters; import org.xml.sax.SAXException; /** * * @author yuu */ +@FixMethodOrder (MethodSorters.NAME_ASCENDING) public class DbPoliceTest { @Before public void setUp() throws Exception { @@ -29,6 +31,18 @@ } @Test + public void test01_police() { + try { + String[] args = new String[0]; + DbPolice.main(args); + } + catch (IOException | ClassNotFoundException | SQLException | ParserConfigurationException | SAXException ex) { + ex.printStackTrace(); + fail(ex.toString()); + } + } + + @Test public void test02_police() { try { String[] args = new String[0]; diff --git a/test/osm/jp/coverage/police/PoliceTest.java b/test/osm/jp/coverage/police/PoliceTest.java new file mode 100644 index 0000000..e6957d5 --- /dev/null +++ b/test/osm/jp/coverage/police/PoliceTest.java @@ -0,0 +1,188 @@ +package osm.jp.coverage.police; + +import java.io.IOException; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.logging.Level; +import java.util.logging.Logger; +import javax.xml.parsers.ParserConfigurationException; +import javax.xml.transform.TransformerException; +import jp.co.areaweb.tools.database.DatabaseTool; +import org.junit.*; +import static org.junit.Assert.*; +import org.junit.runners.MethodSorters; +import org.xml.sax.SAXException; +import osm.jp.api.HttpPOST; + +@FixMethodOrder (MethodSorters.NAME_ASCENDING) +public class PoliceTest { + @Before + public void setUp() throws Exception { + } + + @After + public void tearDown() throws Exception { + } + + @Test + public void test00_Fuel_main() { + try { + String[] args = new String[0]; + Police.main(args); + } + catch (IOException | ClassNotFoundException | SQLException | ParserConfigurationException | TransformerException | SAXException ex) { + ex.printStackTrace(); + fail(ex.toString()); + } + } + + /** + * OSM: removed:amenity=fuel 35.4305614, 139.3662339 + * GML: 14, n00375 + */ + @Test + public void test01_dataread() { + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb("database"); + + double lat = 35.43035D; // 35.43035 35.4305614, 139.3662339 + double lon = 139.36622D; //139.36622 + String where = String.format("(lat > '%s') and (lat < '%s') and (lon > '%s') and (lon < '%s')", + String.valueOf(lat-0.001D), String.valueOf(lat+0.001D), + String.valueOf(lon-0.001D), String.valueOf(lon+0.001D)); + String sql = String.format("SELECT * FROM %s WHERE %s", DbPolice.TABLE_NAME, where); + PreparedStatement ps1 = hsqldb.prepareStatement(sql); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + System.out.println(rset1.getString("idref")); + System.out.println(rset1.getString("gid")); + System.out.println(rset1.getInt("area")); + System.out.println(rset1.getInt("fixed")); + System.out.println(rset1.getInt("fixed1")); + System.out.println(rset1.getDouble("lat")); + System.out.println(rset1.getDouble("lon")); + } + } + + } catch (IOException | ClassNotFoundException | SQLException ex) { + Logger.getLogger(PoliceTest.class.getName()).log(Level.SEVERE, null, ex); + fail(ex.toString()); + } finally { + DatabaseTool.closeDb(hsqldb); + } + } + + 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; + } + + /** + * OSM: 5338111023 removed:amenity=fuel 35.4305614, 139.3662339 + * GML: 14, n00375 + */ + @Test + public void test91_removed() { + Connection hsqldb = null; + String sql1 = String.format("SELECT * FROM %s where idref='%s'", HttpPOST.TABLE_NAME, "5338111023"); + try { + hsqldb = DatabaseTool.openDb("database"); + try (PreparedStatement ps1 = hsqldb.prepareStatement(sql1)){ + try (ResultSet rset1 = ps1.executeQuery()) { + assertTrue(rset1.next()); + } + } + } + catch(Exception e){ + e.printStackTrace(); + fail(e.toString()); + } finally { + DatabaseTool.closeDb(hsqldb); + } + + double lat = 35.43035D; // 35.43035 35.4305614, 139.3662339 + double lon = 139.36622D; //139.36622 + String idrefs = getRefid(lat, lon); + System.out.println(idrefs); + } + + /** + * OSM: ノード: 出光 (1600343871) 35.4214195, 139.3600994 + * GML: 14, n00375 + */ + @Test + public void test92_normal() { + double lat = 35.4214195D; + double lon = 139.3600994D; + String idrefs = getRefid(lat, lon); + System.out.println(idrefs); + } + + /** + * + * + * @param lat 35.43035D; // 35.43035 35.4305614, 139.3662339 + * @param lon 139.36622D + * @return + */ + public String getRefid(double lat, double lon) { + String ret = ""; + + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb("database"); + + String where = String.format("(lat > '%s') and (lat < '%s') and (lon > '%s') and (lon < '%s')", + String.valueOf(lat-0.001D), String.valueOf(lat+0.001D), + String.valueOf(lon-0.001D), String.valueOf(lon+0.001D)); + String sql = String.format("SELECT * FROM %s WHERE %s", DbPolice.TABLE_NAME, where); + System.out.println(sql); + PreparedStatement ps1 = hsqldb.prepareStatement(sql); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + System.out.println("{"); + System.out.println("idref:" + rset1.getString("idref")); + System.out.println("gid:" + rset1.getString("gid")); + System.out.println("area:" + rset1.getInt("area")); + System.out.println("fixed:" + rset1.getInt("fixed")); + System.out.println("fixed1:" + rset1.getInt("fixed1")); + System.out.println("lat:" + rset1.getDouble("lat")); + System.out.println("lon:" + rset1.getDouble("lon")); + System.out.println("}"); + if (ret.length() > 0) { + ret += ","; + } + ret += rset1.getString("idref"); + } + } + } catch (IOException | ClassNotFoundException | SQLException ex) { + Logger.getLogger(PoliceTest.class.getName()).log(Level.SEVERE, null, ex); + fail(ex.toString()); + } finally { + DatabaseTool.closeDb(hsqldb); + } + return ret; + } +}