diff --git a/src/osm/jp/coverage/busstop/Coverage.java b/src/osm/jp/coverage/busstop/Coverage.java index 2d3d999..930c7fa 100644 --- a/src/osm/jp/coverage/busstop/Coverage.java +++ b/src/osm/jp/coverage/busstop/Coverage.java @@ -1,13 +1,19 @@ package osm.jp.coverage.busstop; +import java.io.BufferedWriter; import java.io.File; import java.io.FileNotFoundException; +import java.io.FileOutputStream; import java.io.IOException; +import java.io.OutputStreamWriter; +import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.DecimalFormat; +import java.time.LocalDate; +import java.time.format.DateTimeFormatter; import javax.xml.parsers.ParserConfigurationException; import javax.xml.transform.TransformerException; import org.xml.sax.SAXException; @@ -17,209 +23,175 @@ import osm.jp.api.Japan; public class Coverage { + public ststic final String TABLE_NAME = "t_busstop"; 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 "); + System.out.println("osm.jp.coverage.busstop.Coverage [outputfile.csv | outputfile.json]"); return; } File outputFile = new File(args[0]); - // HSQLディレクトリがなければ作る - File dbdir = new File("database"); - if (!dbdir.isDirectory()) { - dbdir.mkdir(); - } - Connection conPost = DatabaseTool.openDb("postgis"); - - Connection con = DatabaseTool.openDb("database"); - Busstop.initDb(con); - + Connection conHsql = DatabaseTool.openDb("database"); try { // 都道府県名(name)の設定 - String sqlStr = "INSERT INTO coverage (area, name) VALUES(?,?);"; + Coverage[] all = new Coverage[Japan.areaArgs.length]; 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(); - } - } + Coverage cover = new Coverage(i, Japan.areaArgs[i]); - // 分母(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(); + // 分母(denominator)、分子(molecule)、Lv を記入 + // 全国の設定 + if (i == 0) { + PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM "+ TABLE_NAME +";"); + ResultSet rset2 = ps2.executeQuery(); + if (rset2.next()) { + cover.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); + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_fuel WHERE fixed > 0;"); rset2 = ps2.executeQuery(); if (rset2.next()) { - denominator = rset2.getLong(1); + cover.molecule = rset2.getLong(1); } rset2.close(); } - finally { - ps2.close(); - } + else { + PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_fuel WHERE (area=?);"); + ps2.setInt(1, i); + ResultSet rset2 = ps2.executeQuery(); + if (rset2.next()) { + cover.denominator = rset2.getLong(1); + } + rset2.close(); - ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE (fixed > 0) and (area=?);"); - try { + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_fuel WHERE (fixed > 0) and (area=?);"); ps2.setInt(1, i); rset2 = ps2.executeQuery(); if (rset2.next()) { - lv1 = rset2.getLong(1); + cover.molecule = 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(); - } + cover.par = (double)cover.molecule / cover.denominator; + cover.par *= 100.0D; + all[i] = cover; } - - 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()); - } + if (outputFile.getName().toUpperCase().endsWith(".CSV")) { + outputCSV(outputFile, all); } - finally { - ps.close(); - csv.save(); + else if (outputFile.getName().toUpperCase().endsWith(".JSON")) { + outputJson(outputFile, all); } } finally { - DatabaseTool.closeDb(con); + DatabaseTool.closeDb(conHsql); DatabaseTool.closeDb(conPost); } } - public Coverage() { + public static DecimalFormat df3 = new DecimalFormat("##0.00"); + + /** + * 以下はクラスのメンバ変数 + * setter/getterはめんどくさいのでつかわない + */ + public int areacode; // 1..47: 都道府県コード, 0: 全国合計 + public String name; // 都道府県名称 + public long denominator; // 分母(denominator) + public long molecule; // 分子(molecule) + public double par; // パーセンテージ + + /** + * コンストラクタ + * @param areacode + * @param name + */ + public Coverage(int areacode, String name) { + this.areacode = areacode; + this.name = name; + this.denominator = 1L; + this.molecule = 0L; + this.par = 0.0D; + } + + /** + * CSVファイルに出力する + * @param outputFile + * @param all 出力するCoverage[] + * @throws IOException + */ + static void outputCSV(File outputFile, Coverage[] all) throws IOException { + CsvFile csv = new CsvFile(outputFile); + CsvRecord line = new CsvRecord(); + line.add("コード"); + line.add("都道府県"); + line.add("観測数"); + line.add("率(%)"); + csv.add(line); + System.out.println(line.toString()); + + try { + for (Coverage cover : all) { + line = new CsvRecord(); + line.add(String.valueOf(cover.areacode)); + line.add(cover.name); + line.add(String.valueOf(cover.denominator)); + line.add(String.valueOf(df3.format(cover.par))); + csv.add(line); + System.out.println(line.toString()); + } + } + finally { + csv.save(); + } + } + + static void outputJson(File outputFile, Coverage[] all) throws FileNotFoundException, UnsupportedEncodingException, IOException { + try (BufferedWriter hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile), "UTF-8"))) { + int level = 0; + hw.write(space(level++) + "{"); + hw.newLine(); + hw.write(space(level) + "\"timestamp\": \""+ LocalDate.now().format(DateTimeFormatter.ISO_DATE) +"\","); + hw.newLine(); + hw.write(space(level) + "\"sourcedata\": \"国土数値情報　燃料給油所データ 平成２８年(2016)\","); + hw.newLine(); + hw.write(space(level++) + "\"coverage\": ["); + hw.newLine(); + boolean head = true; + for (Coverage cover : all) { + if (head == false) { + hw.write(space(level) + ","); + } + else { + head = false; + } + hw.write(space(level++) + "{"); + hw.newLine(); + + hw.write(space(level) + "\"code\": "+ String.valueOf(cover.areacode) +","); + hw.newLine(); + hw.write(space(level) + "\"name\": \""+ cover.name +"\","); + hw.newLine(); + hw.write(space(level) + "\"denominator\": "+ String.valueOf(cover.denominator) +","); + hw.newLine(); + hw.write(space(level) + "\"par\": "+ String.valueOf(df3.format(cover.par))); + hw.newLine(); + + hw.write(space(--level) + "}"); + hw.newLine(); + } + hw.write(space(--level) + "]"); + hw.newLine(); + hw.write(space(--level) + "}"); + hw.newLine(); + hw.flush(); + } + } + static String space(int level) { + return (" ".substring(0,(level > 12 ? 12 : level))); } }