diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index 93501ee..ef3e7f0 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -97,6 +97,10 @@ String idref = null; int area = 0; + if (rset1.getString("idref").equals("5338111023")) { + int dummy = 0; + } + // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 RectArea rect = new RectArea(lat, lon, NEER); // 400m 四方 ps2.setDouble(1, rect.minlat); @@ -117,6 +121,7 @@ } } if (idref != null) { + System.out.println(sql3); System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1="+ score +" WHERE idref="+ idref +" and area=" + area); ps3.setInt(1, score); ps3.setString(2, idref); diff --git a/src/osmCoverage_PoliceJson.sh b/src/osmCoverage_PoliceJson.sh index b9bd0dd..6e70cb0 100644 --- a/src/osmCoverage_PoliceJson.sh +++ b/src/osmCoverage_PoliceJson.sh @@ -8,6 +8,7 @@ curl --user yuu:yuu8844 -O http://localhost:8080/job/hayashiLib/lastSuccessfulBuild/artifact/dist/hayashi.jar curl --user yuu:yuu8844 -O http://localhost:8080/job/osmCoverage/lastSuccessfulBuild/artifact/osmCoverage.jar + ### export from PostGIS java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.ToGeoJSON diff --git a/test/osm/jp/coverage/fuel/AllTest.java b/test/osm/jp/coverage/fuel/AllTest.java new file mode 100644 index 0000000..4c759b0 --- /dev/null +++ b/test/osm/jp/coverage/fuel/AllTest.java @@ -0,0 +1,38 @@ +package osm.jp.coverage.fuel; + +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, + DbFuelTest.class, + FuelTest.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/fuel/DbExistTest.java b/test/osm/jp/coverage/fuel/DbExistTest.java index 4426746..849e49d 100644 --- a/test/osm/jp/coverage/fuel/DbExistTest.java +++ b/test/osm/jp/coverage/fuel/DbExistTest.java @@ -12,24 +12,17 @@ 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; -/** - * - * @author yuu - */ +@FixMethodOrder (MethodSorters.NAME_ASCENDING) public class DbExistTest { + public void init() throws Exception { + } + + @Before public void setUp() throws Exception { - File dir = new File("database"); - if (dir.exists()) { - if (dir.isDirectory()) { - dir.deleteOnExit(); - } - else { - throw new Exception("'database' is not directory."); - } - } } @After @@ -41,14 +34,10 @@ Connection con = null; try { // DB.tableを作成 + init(); con = DatabaseTool.openDb("database"); create(con); - } - catch (ClassNotFoundException ex) { - fail(); - } catch (SQLException ex) { - fail(); - } catch (IOException ex) { + } catch (Exception ex) { fail(); } finally { if (con != null) { @@ -98,15 +87,19 @@ } @Test - public void test02_fuel() { + public void test020_fuel() { try { + init(); String[] args = new String[0]; DbExist.main(args); } catch (Exception ex) { fail(ex.toString()); } - + } + + @Test + public void test021_fuel() { File dir = new File("database"); assertTrue(dir.exists()); assertTrue(dir.isDirectory()); @@ -209,6 +202,19 @@ fail(); } } + + // node: "removed:amenity=fuel" (5338111023) https://www.openstreetmap.org/node/5338111023 + // ノード: removed (5338111023) 場所: 35.4305614, 139.3662339 + ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='5338111023'"); + try (ResultSet rset1 = ps1.executeQuery()) { + if (rset1.next()) { + assertThat(rset1.getInt("score"), is(50)); + assertThat(checkRenge(rset1, "35.4305614", "139.3662339"), is(true)); + } + else { + fail(); + } + } } catch (ClassNotFoundException ex) { Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { diff --git a/test/osm/jp/coverage/fuel/DbFuelTest.java b/test/osm/jp/coverage/fuel/DbFuelTest.java new file mode 100644 index 0000000..54cbef7 --- /dev/null +++ b/test/osm/jp/coverage/fuel/DbFuelTest.java @@ -0,0 +1,43 @@ +package osm.jp.coverage.fuel; + +import java.io.IOException; +import java.sql.SQLException; +import javax.xml.parsers.ParserConfigurationException; +import org.junit.After; +import org.junit.AfterClass; +import static org.junit.Assert.fail; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; +import org.xml.sax.SAXException; + +public class DbFuelTest { + + @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 { + } + + @Test + public void test00_main() throws Exception { + try { + String[] args = new String[0]; + DbFuel.main(args); + } + catch(IOException | ClassNotFoundException | SQLException | ParserConfigurationException | SAXException e) { + fail(e.toString()); + } + } + +} diff --git a/test/osm/jp/coverage/fuel/FuelTest.java b/test/osm/jp/coverage/fuel/FuelTest.java new file mode 100644 index 0000000..c2b05ad --- /dev/null +++ b/test/osm/jp/coverage/fuel/FuelTest.java @@ -0,0 +1,188 @@ +package osm.jp.coverage.fuel; + +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 FuelTest { + @Before + public void setUp() throws Exception { + } + + @After + public void tearDown() throws Exception { + } + + @Test + public void test00_Fuel_main() { + try { + String[] args = new String[0]; + Fuel.main(args); + } + catch (IOException | ClassNotFoundException | SQLException | ParserConfigurationException | TransformerException | SAXException ex) { + 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", DbFuel.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(FuelTest.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){ + 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", DbFuel.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(FuelTest.class.getName()).log(Level.SEVERE, null, ex); + fail(ex.toString()); + } finally { + DatabaseTool.closeDb(hsqldb); + } + return ret; + } +}