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<Coverage> {
+
+ 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<Coverage> {
+
+ 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\": \"国土数値情報 燃料給油所データ 平成２８年(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)));
+ }
+}