diff --git a/src/osm/jp/coverage/postoffice/CoverageAll.java b/src/osm/jp/coverage/postoffice/CoverageAll.java new file mode 100644 index 0000000..d8bc432 --- /dev/null +++ b/src/osm/jp/coverage/postoffice/CoverageAll.java @@ -0,0 +1,36 @@ +package osm.jp.coverage.postoffice; + +import osm.jp.postgis.*; +import java.io.File; +import java.io.FileNotFoundException; +import java.io.IOException; +import java.sql.Connection; +import java.sql.SQLException; +import java.util.ArrayList; +import javax.xml.parsers.ParserConfigurationException; +import javax.xml.transform.TransformerException; +import org.xml.sax.SAXException; +import jp.co.areaweb.tools.database.DatabaseTool; + +public class CoverageAll extends ArrayList { + + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException + { + File outputFileCsv = new File(DbPostoffice.TABLE_NAME +".csv"); + File outputFileJson = new File(DbPostoffice.TABLE_NAME +".json"); + + osm.jp.postgis.CoverageAll obj = new osm.jp.postgis.CoverageAll(DbPostoffice.TABLE_NAME); + + Connection conPost = DatabaseTool.openDb("postgis"); + Connection conHsql = DatabaseTool.openDb("database"); + try { + obj.load(conPost, conHsql); + obj.outputCSV(outputFileCsv); + obj.outputJson(outputFileJson); + } + finally { + DatabaseTool.closeDb(conHsql); + DatabaseTool.closeDb(conPost); + } + } +} diff --git a/src/osm/jp/postgis/Coverage.java b/src/osm/jp/postgis/Coverage.java new file mode 100644 index 0000000..ad080c7 --- /dev/null +++ b/src/osm/jp/postgis/Coverage.java @@ -0,0 +1,30 @@ +package osm.jp.postgis; + +public class Coverage +{ + /** + * 以下はクラスのメンバ変数 + * setter/getterはめんどくさいのでつかわない + */ + public int areacode; // 1..47: 都道府県コード, 0: 全国合計 + public String name; // 都道府県名称 + public long denominator; // 分母(denominator) + public long molecule; // 分子(molecule) + public double par; // パーセンテージ + public String tableName; + + /** + * コンストラクタ + * @param areacode + * @param name + * @param tableName + */ + public Coverage(int areacode, String name, String tableName) { + this.areacode = areacode; + this.name = name; + this.denominator = 1L; + this.molecule = 0L; + this.par = 0.0D; + } + +} diff --git a/src/osm/jp/postgis/CoverageAll.java b/src/osm/jp/postgis/CoverageAll.java new file mode 100644 index 0000000..739914f --- /dev/null +++ b/src/osm/jp/postgis/CoverageAll.java @@ -0,0 +1,195 @@ +package osm.jp.postgis; + +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 java.util.ArrayList; +import javax.xml.parsers.ParserConfigurationException; +import javax.xml.transform.TransformerException; +import org.xml.sax.SAXException; +import jp.co.areaweb.tools.csv.CsvFile; +import jp.co.areaweb.tools.csv.CsvRecord; +import jp.co.areaweb.tools.database.DatabaseTool; +import osm.jp.api.Japan; +import osm.jp.coverage.postoffice.DbPostoffice; + +public class CoverageAll extends ArrayList { + + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException + { + if (args.length < 3) { + System.out.println("osm.jp.postgis.CoverageAll [outputfile.csv] [outputfile.json] [TABLE_NAME]"); + return; + } + File outputFileCsv = new File(args[0]); + File outputFileJson = new File(args[1]); + String tableName = args[2]; + + CoverageAll obj = new CoverageAll(tableName); + + Connection conPost = DatabaseTool.openDb("postgis"); + Connection conHsql = DatabaseTool.openDb("database"); + try { + obj.load(conPost, conHsql); + obj.outputCSV(outputFileCsv); + obj.outputJson(outputFileJson); + } + finally { + DatabaseTool.closeDb(conHsql); + DatabaseTool.closeDb(conPost); + } + } + + public static DecimalFormat df3 = new DecimalFormat("##0.00"); + public String tableName; + public File outputFile; + + /** + * コンストラクタ + * @param tableName + * @throws java.lang.ClassNotFoundException + * @throws java.sql.SQLException + * @throws java.io.IOException + */ + public CoverageAll(String tableName) + throws ClassNotFoundException, SQLException, IOException + { + super(); + this.tableName = tableName; + } + + public void load(Connection conPost, Connection conHsql) + throws ClassNotFoundException, SQLException, IOException + { + // 都道府県名(name)の設定 + for (int i=0; i < Japan.areaArgs.length; i++) { + Coverage cover = new Coverage(i, Japan.areaArgs[i], DbPostoffice.TABLE_NAME); + + // 分母(denominator)、分子(molecule)、Lv を記入 + // 全国の設定 + if (i == 0) { + PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_"+ tableName); + ResultSet rset2 = ps2.executeQuery(); + if (rset2.next()) { + cover.denominator = rset2.getLong(1); + } + rset2.close(); + + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_"+ tableName +" WHERE fixed > 0;"); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + cover.molecule = rset2.getLong(1); + } + rset2.close(); + } + else { + PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_"+ tableName +" 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_"+ tableName +" WHERE (fixed > 0) and (area=?);"); + ps2.setInt(1, i); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + cover.molecule = rset2.getLong(1); + } + rset2.close(); + } + + cover.par = (double)cover.molecule / cover.denominator; + cover.par *= 100.0D; + this.add(cover); + } + } + + /** + * CSVファイルに出力する + * @param outputFile + * @throws IOException + */ + public void outputCSV(File outputFile) 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 : this) { + 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(); + } + } + + public void outputJson(File outputFile) 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\": \"国土数値情報 燃料給油所データ 平成28年(2016)\","); + hw.newLine(); + hw.write(space(level++) + "\"coverage\": ["); + hw.newLine(); + boolean head = true; + for (Coverage cover : this) { + 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))); + } +}