package osm.jp.coverage.fuel;
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 test01_hsqldbの生成確認() {
Connection con = null;
try {
// DB.tableを作成
init();
con = DatabaseTool.openDb("database");
create(con);
} catch (Exception ex) {
fail();
} finally {
if (con != null) {
DatabaseTool.closeDb(con);
}
}
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()) {
long cnt = rset8.getLong(1);
assertThat(Long.toString(cnt), 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(Long.toString(cnt), is("0"));
}
else {
fail();
}
}
} catch (ClassNotFoundException ex) {
Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
} finally {
DatabaseTool.closeDb(hsqldb);
}
}
@Test
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());
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()) {
long cnt = rset8.getLong(1);
assertThat(Long.toString(cnt), 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();
}
}
// ノード: エネオス (2015835273) 場所: 35.4367770, 139.4035710 brand=yes,name=yes
ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='2015835273'");
try (ResultSet rset1 = ps1.executeQuery()) {
if (rset1.next()) {
assertThat(rset1.getInt("score"), is(50));
assertThat(checkRenge(rset1, "35.4367770", "139.4035710"), is(true));
}
else {
fail();
}
}
// ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no,name=yes
ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='1769261234'");
try (ResultSet rset1 = ps1.executeQuery()) {
if (rset1.next()) {
assertThat(rset1.getInt("score"), is(1));
assertThat(checkRenge(rset1, "35.4330583", "139.4006876"), is(true));
}
else {
fail();
}
}
// ノード: 出光 (3877535257) 場所: 45.3985390, 141.6882450 (稚内) brand=no
ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='3877535257'");
try (ResultSet rset1 = ps1.executeQuery()) {
if (rset1.next()) {
assertThat(rset1.getInt("score"), is(1));
assertThat(checkRenge(rset1, "45.3985390", "141.6882450"), is(true));
}
else {
fail();
}
}
// ノード: 605462353 場所: 24.3465566, 124.1519297 (石垣島) name=no, brand=no
ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='605462353'");
try (ResultSet rset1 = ps1.executeQuery()) {
if (rset1.next()) {
assertThat(rset1.getInt("score"), is(1));
assertThat(checkRenge(rset1, "24.3465566", "124.1519297"), is(true));
}
else {
fail();
}
}
// ウェイ: 出光 (161877397) 139.402982078119 35.4372453832977
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.4371428029181", "139.403185626731"), is(true));
}
else {
fail();
}
}
// node: "DISUSED:amenity=fuel" (302164578) https://www.openstreetmap.org/node/302164578
// ノード: エネオス (302164578) 場所: 35.5688084, 139.7300300
ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='302164578'");
try (ResultSet rset1 = ps1.executeQuery()) {
if (rset1.next()) {
assertThat(rset1.getInt("score"), is(50));
assertThat(checkRenge(rset1, "35.5688084", "139.7300300"), is(true));
}
else {
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) {
Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
} 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.0001D;
double down = d1 - 0.0001D;
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;
}
}
