diff --git a/src/osm/jp/coverage/busstop/Coverage.java b/src/osm/jp/coverage/busstop/Coverage.java index 336212a..2d3d999 100644 --- a/src/osm/jp/coverage/busstop/Coverage.java +++ b/src/osm/jp/coverage/busstop/Coverage.java @@ -18,208 +18,208 @@ public class Coverage { - public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException - { - if (args.length < 1) { - System.out.println("osm.jp.coverage.busstop.Coverage "); - return; - } - File outputFile = new File(args[0]); + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException + { + if (args.length < 1) { + System.out.println("osm.jp.coverage.busstop.Coverage "); + return; + } + File outputFile = new File(args[0]); - // HSQLディレクトリがなければ作る - File dbdir = new File("database"); - if (!dbdir.isDirectory()) { - dbdir.mkdir(); - } - - Connection conPost = DatabaseTool.openDb("postgis"); + // HSQLディレクトリがなければ作る + File dbdir = new File("database"); + if (!dbdir.isDirectory()) { + dbdir.mkdir(); + } - Connection con = DatabaseTool.openDb("database"); - Busstop.initDb(con); - - try { - // 都道府県名(name)の設定 - String sqlStr = "INSERT INTO coverage (area, name) VALUES(?,?);"; - for (int i=0; i < Japan.areaArgs.length; i++) { - PreparedStatement ps = con.prepareStatement(sqlStr); - ps.setInt(1, i); - ps.setString(2, Japan.areaArgs[i]); - try { - ps.executeUpdate(); - } - finally { - ps.close(); - } - } - - // 分母(denominator)、分子(molecule)、Lv を記入 - // 全国の設定 - long denominator = 0L; - long lv1 = 0L; - long lv2 = 0L; - long lv3 = 0L; - PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop;"); - ResultSet rset2 = ps2.executeQuery(); - if (rset2.next()) { - denominator = rset2.getLong(1); - } - rset2.close(); - - ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE fixed > 0;"); - rset2 = ps2.executeQuery(); - if (rset2.next()) { - lv1 = rset2.getLong(1); - } - rset2.close(); - - ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE fixed > 50;"); - rset2 = ps2.executeQuery(); - if (rset2.next()) { - lv2 = rset2.getLong(1); - } - rset2.close(); - - ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE fixed > 100;"); - rset2 = ps2.executeQuery(); - if (rset2.next()) { - lv3 = rset2.getLong(1); - } - rset2.close(); - - sqlStr = "UPDATE coverage SET denominator=?, lv1=?, lv2=?, lv3=? WHERE area=0;"; - PreparedStatement ps = con.prepareStatement(sqlStr); - ps.setLong(1, denominator); - ps.setLong(2, lv1); - ps.setLong(3, lv2); - ps.setLong(4, lv3); - try { - ps.executeUpdate(); - } - finally { - ps.close(); - } - - // 分母(denominator)、分子(molecule)、カバレッジ(Lv) を記入 - // 都道府県名の設定 - for (int i = 1; i < Japan.areaArgs.length; i++) { - ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE area=?;"); - try { - ps2.setInt(1, i); - rset2 = ps2.executeQuery(); - if (rset2.next()) { - denominator = rset2.getLong(1); - } - rset2.close(); - } - finally { - ps2.close(); - } - - ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE (fixed > 0) and (area=?);"); - try { - ps2.setInt(1, i); - rset2 = ps2.executeQuery(); - if (rset2.next()) { - lv1 = rset2.getLong(1); - } - rset2.close(); - } - finally { - ps2.close(); - } - - ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE (fixed >= 50) and (area=?);"); - try { - ps2.setInt(1, i); - rset2 = ps2.executeQuery(); - if (rset2.next()) { - lv2 = rset2.getLong(1); - } - rset2.close(); - } - finally { - ps2.close(); - } - - ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE (fixed >= 100) and (area=?);"); - try { - ps2.setInt(1, i); - rset2 = ps2.executeQuery(); - if (rset2.next()) { - lv3 = rset2.getLong(1); - } - rset2.close(); - } - finally { - ps2.close(); - } - - sqlStr = "UPDATE coverage SET denominator=?, lv1=?, lv2=?, lv3=? WHERE area=?;"; - ps = con.prepareStatement(sqlStr); - try { - ps.setLong(1, denominator); - ps.setLong(2, lv1); - ps.setLong(3, lv2); - ps.setLong(4, lv3); - ps.setInt(5, i); - ps.executeUpdate(); - } - finally { - ps.close(); - } - } + Connection conPost = DatabaseTool.openDb("postgis"); - CsvFile csv = new CsvFile(outputFile); - CsvRecord line = new CsvRecord(); - line.add("コード"); - line.add("都道府県"); - line.add("観測数"); - line.add("Lv1"); - line.add("Lv1(%)"); - line.add("Lv2"); - line.add("Lv2(%)"); - line.add("Lv3"); - line.add("Lv3(%)"); - csv.add(line); - System.out.println(line.toString()); - - DecimalFormat df3 = new DecimalFormat("##0.00"); - ps = con.prepareStatement("SELECT area,name,denominator,lv1,lv2,lv3 FROM coverage ORDER BY area;"); - try { - ResultSet rset1 = ps.executeQuery(); - while (rset1.next()) { - int area = rset1.getInt("area"); - String name = rset1.getString("name"); - denominator = rset1.getLong("denominator"); - lv1 = rset1.getLong("lv1"); - lv2 = rset1.getLong("lv2"); - lv3 = rset1.getLong("lv3"); - - line = new CsvRecord(); - line.add(String.valueOf(area)); - line.add(name); - line.add(String.valueOf(denominator)); - line.add(String.valueOf(lv1)); - line.add(String.valueOf(df3.format((Double.valueOf(lv1) / Double.valueOf(denominator) * 100.0D)))); - line.add(String.valueOf(lv2)); - line.add(String.valueOf(df3.format((Double.valueOf(lv2) / Double.valueOf(denominator) * 100.0D)))); - line.add(String.valueOf(lv3)); - line.add(String.valueOf(df3.format((Double.valueOf(lv3) / Double.valueOf(denominator) * 100.0D)))); - csv.add(line); - System.out.println(line.toString()); - } - } - finally { - ps.close(); - csv.save(); - } - } - finally { - DatabaseTool.closeDb(con); - DatabaseTool.closeDb(conPost); - } - } + Connection con = DatabaseTool.openDb("database"); + Busstop.initDb(con); - public Coverage() { - } + try { + // 都道府県名(name)の設定 + String sqlStr = "INSERT INTO coverage (area, name) VALUES(?,?);"; + for (int i=0; i < Japan.areaArgs.length; i++) { + PreparedStatement ps = con.prepareStatement(sqlStr); + ps.setInt(1, i); + ps.setString(2, Japan.areaArgs[i]); + try { + ps.executeUpdate(); + } + finally { + ps.close(); + } + } + + // 分母(denominator)、分子(molecule)、Lv を記入 + // 全国の設定 + long denominator = 0L; + long lv1 = 0L; + long lv2 = 0L; + long lv3 = 0L; + PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop;"); + ResultSet rset2 = ps2.executeQuery(); + if (rset2.next()) { + denominator = rset2.getLong(1); + } + rset2.close(); + + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE fixed > 0;"); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + lv1 = rset2.getLong(1); + } + rset2.close(); + + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE fixed > 50;"); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + lv2 = rset2.getLong(1); + } + rset2.close(); + + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE fixed > 100;"); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + lv3 = rset2.getLong(1); + } + rset2.close(); + + sqlStr = "UPDATE coverage SET denominator=?, lv1=?, lv2=?, lv3=? WHERE area=0;"; + PreparedStatement ps = con.prepareStatement(sqlStr); + ps.setLong(1, denominator); + ps.setLong(2, lv1); + ps.setLong(3, lv2); + ps.setLong(4, lv3); + try { + ps.executeUpdate(); + } + finally { + ps.close(); + } + + // 分母(denominator)、分子(molecule)、カバレッジ(Lv) を記入 + // 都道府県名の設定 + for (int i = 1; i < Japan.areaArgs.length; i++) { + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE area=?;"); + try { + ps2.setInt(1, i); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + denominator = rset2.getLong(1); + } + rset2.close(); + } + finally { + ps2.close(); + } + + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE (fixed > 0) and (area=?);"); + try { + ps2.setInt(1, i); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + lv1 = rset2.getLong(1); + } + rset2.close(); + } + finally { + ps2.close(); + } + + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE (fixed >= 50) and (area=?);"); + try { + ps2.setInt(1, i); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + lv2 = rset2.getLong(1); + } + rset2.close(); + } + finally { + ps2.close(); + } + + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE (fixed >= 100) and (area=?);"); + try { + ps2.setInt(1, i); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + lv3 = rset2.getLong(1); + } + rset2.close(); + } + finally { + ps2.close(); + } + + sqlStr = "UPDATE coverage SET denominator=?, lv1=?, lv2=?, lv3=? WHERE area=?;"; + ps = con.prepareStatement(sqlStr); + try { + ps.setLong(1, denominator); + ps.setLong(2, lv1); + ps.setLong(3, lv2); + ps.setLong(4, lv3); + ps.setInt(5, i); + ps.executeUpdate(); + } + finally { + ps.close(); + } + } + + CsvFile csv = new CsvFile(outputFile); + CsvRecord line = new CsvRecord(); + line.add("コード"); + line.add("都道府県"); + line.add("観測数"); + line.add("Lv1"); + line.add("Lv1(%)"); + line.add("Lv2"); + line.add("Lv2(%)"); + line.add("Lv3"); + line.add("Lv3(%)"); + csv.add(line); + System.out.println(line.toString()); + + DecimalFormat df3 = new DecimalFormat("##0.00"); + ps = con.prepareStatement("SELECT area,name,denominator,lv1,lv2,lv3 FROM coverage ORDER BY area;"); + try { + ResultSet rset1 = ps.executeQuery(); + while (rset1.next()) { + int area = rset1.getInt("area"); + String name = rset1.getString("name"); + denominator = rset1.getLong("denominator"); + lv1 = rset1.getLong("lv1"); + lv2 = rset1.getLong("lv2"); + lv3 = rset1.getLong("lv3"); + + line = new CsvRecord(); + line.add(String.valueOf(area)); + line.add(name); + line.add(String.valueOf(denominator)); + line.add(String.valueOf(lv1)); + line.add(String.valueOf(df3.format((Double.valueOf(lv1) / Double.valueOf(denominator) * 100.0D)))); + line.add(String.valueOf(lv2)); + line.add(String.valueOf(df3.format((Double.valueOf(lv2) / Double.valueOf(denominator) * 100.0D)))); + line.add(String.valueOf(lv3)); + line.add(String.valueOf(df3.format((Double.valueOf(lv3) / Double.valueOf(denominator) * 100.0D)))); + csv.add(line); + System.out.println(line.toString()); + } + } + finally { + ps.close(); + csv.save(); + } + } + finally { + DatabaseTool.closeDb(con); + DatabaseTool.closeDb(conPost); + } + } + + public Coverage() { + } }