diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index a96fc48..e410503 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -12,6 +12,8 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; +import java.util.logging.Level; +import java.util.logging.Logger; import jp.co.areaweb.tools.database.*; import osm.jp.api.Japan; @@ -27,15 +29,8 @@ * OSM_EXISTのscoreを集計して,BUS_STOPのfixedをUPDATE * * @param args - * - * @throws IOException - * @throws SQLException - * @throws ClassNotFoundException - * @throws FileNotFoundException - * @throws TransformerException - * @throws SAXException - * @throws ParserConfigurationException */ - public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException + * */ + public static void main(String[] args) { // HSQLディレクトリがなければ作る File dbdir = new File("database"); @@ -43,12 +38,39 @@ dbdir.mkdir(); } - Connection con = DatabaseTool.openDb("database"); + Connection con; try { - new Busstop(con); - } - finally { - DatabaseTool.closeDb(con); + con = DatabaseTool.openDb("database"); + try { + new Busstop(con); + } + catch (SQLException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + ex.printStackTrace(); + } catch (ClassNotFoundException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + ex.printStackTrace(); + } catch (IOException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + ex.printStackTrace(); + } catch (ParserConfigurationException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + ex.printStackTrace(); + } catch (SAXException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + ex.printStackTrace(); + } catch (TransformerException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + ex.printStackTrace(); + } finally { + DatabaseTool.closeDb(con); + } + } catch (ClassNotFoundException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + } catch (SQLException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); + } catch (IOException ex) { + Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex); } } @@ -102,18 +124,27 @@ 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.equals(ksjName)))) + || (((dd * 1000.0D) < NEER*2) && (name != null) && (name.equals(ksjName)))) { System.out.println(sqlStr3 +" ["+ fixed1 +", "+ gmlid +", "+ area +"]"); ps3.setInt(1, fixed1); diff --git a/test/osm/jp/coverage/busstop/AllTest.java b/test/osm/jp/coverage/busstop/AllTest.java new file mode 100644 index 0000000..820e04a --- /dev/null +++ b/test/osm/jp/coverage/busstop/AllTest.java @@ -0,0 +1,43 @@ +/* + * To change this license header, choose License Headers in Project Properties. + * To change this template file, choose Tools | Templates + * and open the template in the editor. + */ +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; + +/** + * + * @author yuu + */ +@RunWith(Suite.class) +@Suite.SuiteClasses({ + osm.jp.coverage.busstop.DbExistBusstopTest.class, + osm.jp.coverage.busstop.DbBusstopTest.class, + osm.jp.coverage.busstop.BusstopTest.class +}) +public class AllTest { + + @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 { + } + +} diff --git a/test/osm/jp/coverage/busstop/BusstopTest.java b/test/osm/jp/coverage/busstop/BusstopTest.java index f27082d..f0c7ba4 100644 --- a/test/osm/jp/coverage/busstop/BusstopTest.java +++ b/test/osm/jp/coverage/busstop/BusstopTest.java @@ -8,14 +8,14 @@ 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 static org.hamcrest.CoreMatchers.is; import org.junit.*; import static org.junit.Assert.*; -import org.xml.sax.SAXException; -import static osm.jp.api.Osmdb.create; +import osm.jp.api.HttpPOST; +import osm.jp.api.Japan; +import osm.jp.api.RectArea; +import static osm.jp.coverage.busstop.Busstop.NEER; /** * @@ -30,13 +30,122 @@ public void tearDown() throws Exception { } + @Test + public void test01_dataread() { + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb("database"); + + PreparedStatement ps1 = hsqldb.prepareStatement("SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM bus_stop WHERE (lat > '34.69161765717579') and (lat < '34.697025680375795') and (lon > '135.79486712544522') and (lon < '135.80144696784524')"); + 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")); + } + } + + } catch (ClassNotFoundException ex) { + Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); + } catch (SQLException ex) { + Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); + } catch (IOException ex) { + Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); + } finally { + DatabaseTool.closeDb(hsqldb); + } + } + + @Test + public void test03_dataread() { + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb("database"); + String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ HttpPOST.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 = null; + int area = 0; + int fixed1 = 0; + // 指定の緯度経度を中心とする半径200x2m四方の矩形領域 + 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 (ClassNotFoundException ex) { + Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); + } catch (SQLException ex) { + Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); + } catch (IOException ex) { + Logger.getLogger(BusstopTest.class.getName()).log(Level.SEVERE, null, ex); + } finally { + DatabaseTool.closeDb(hsqldb); + } + } + @Test public void test02_busstop() { try { String[] args = new String[0]; - DbExistBusstop.main(args); - DbBusstop.main(args); Busstop.main(args); } catch (Exception ex) { diff --git a/test/osm/jp/coverage/busstop/DbBusstopTest.java b/test/osm/jp/coverage/busstop/DbBusstopTest.java new file mode 100644 index 0000000..4b4b521 --- /dev/null +++ b/test/osm/jp/coverage/busstop/DbBusstopTest.java @@ -0,0 +1,50 @@ +/* + * To change this license header, choose License Headers in Project Properties. + * To change this template file, choose Tools | Templates + * and open the template in the editor. + */ +package osm.jp.coverage.busstop; + +import java.io.FileNotFoundException; +import java.io.IOException; +import java.sql.SQLException; +import javax.xml.parsers.ParserConfigurationException; +import org.junit.After; +import org.junit.AfterClass; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; +import org.junit.experimental.runners.Enclosed; +import org.junit.runner.RunWith; +import org.xml.sax.SAXException; + +/** + * + * @author yuu + */ +@RunWith(Enclosed.class) +public class DbBusstopTest { + + @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 mainTest() throws ClassNotFoundException, IOException, FileNotFoundException, SQLException, ParserConfigurationException, SAXException { + String[] args = new String[0]; + DbBusstop.main(args); + } + +}