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 <outputfile.csv>");
+ 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)));
}
}