diff --git a/.gitignore b/.gitignore index c58e9de..a688bcc 100644 --- a/.gitignore +++ b/.gitignore @@ -1,6 +1,6 @@ *.class *.zip -bin/** +bin/ classes/ sample/ dest/** diff --git a/src/osm/jp/coverage/busstop/Coverage.java b/src/osm/jp/coverage/busstop/Coverage.java index 40adba0..9e20ab4 100644 --- a/src/osm/jp/coverage/busstop/Coverage.java +++ b/src/osm/jp/coverage/busstop/Coverage.java @@ -1,197 +1,40 @@ 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 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.postgis.CoverageAll; public class Coverage { public static 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 | outputfile.json]"); - return; - } - File outputFile = new File(args[0]); - + File dir = new File("GML_BUSSTOP"); + File outputFileCsv = new File(dir, "coverage.busstop.csv"); + File outputFileJson = new File(dir, "coverage.busstop.json"); + + CoverageAll obj = new CoverageAll(); + obj.setTablename(DbBusstop.TABLE_NAME); + obj.setData(new ArrayList<>()); + Connection conPost = DatabaseTool.openDb("postgis"); Connection conHsql = DatabaseTool.openDb("database"); try { - // 都道府県名(name)の設定 - Coverage[] all = new Coverage[Japan.areaArgs.length]; - for (int i=0; i < Japan.areaArgs.length; i++) { - Coverage cover = new Coverage(i, Japan.areaArgs[i]); - - // 分母(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 "+ TABLE_NAME +" WHERE fixed > 1;"); - rset2 = ps2.executeQuery(); - if (rset2.next()) { - cover.molecule = rset2.getLong(1); - } - rset2.close(); - } - else { - PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM "+ TABLE_NAME +" 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 "+ TABLE_NAME +" WHERE (fixed > 1) 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; - all[i] = cover; - } - if (outputFile.getName().toUpperCase().endsWith(".CSV")) { - outputCSV(outputFile, all); - } - else if (outputFile.getName().toUpperCase().endsWith(".JSON")) { - outputJson(outputFile, all); - } + obj.load(conPost, conHsql, DbBusstop.TABLE_NAME, ""); + obj.outputCSV(outputFileCsv); + obj.outputJson(outputFileJson, "国土数値情報　バス停留所データ 平成２２年"); } finally { DatabaseTool.closeDb(conHsql); DatabaseTool.closeDb(conPost); } } - - 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\": \"国土数値情報　バス停留所データ 平成２２年\","); - 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))); - } } diff --git a/src/osm/jp/coverage/busstop/ToGeoJSON.java b/src/osm/jp/coverage/busstop/ToGeoJSON.java index ed02d4b..b239a27 100644 --- a/src/osm/jp/coverage/busstop/ToGeoJSON.java +++ b/src/osm/jp/coverage/busstop/ToGeoJSON.java @@ -1,18 +1,12 @@ package osm.jp.coverage.busstop; -import java.io.BufferedWriter; import java.io.File; -import java.io.FileOutputStream; -import java.io.OutputStreamWriter; import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; - import jp.co.areaweb.tools.database.DatabaseTool; /** * PostGISデータをGeoJSONファイルに出力する。 - * 出力ファイル名： "busstop.json" + * 出力ファイル名： "./GML_BUSSTOP/busstop.json" * テーブル名： t_busstop * fixed OSMの周辺に存在するかどうか、存在しない場合は0,存在する場合は1。ブランド有りは５０。 * geom PostGIS形式の位置情報(4612:) @@ -31,57 +25,11 @@ slim = true; } } + osm.jp.postgis.ToGeoJSON obj = new osm.jp.postgis.ToGeoJSON(DbBusstop.TABLE_NAME); Connection con = DatabaseTool.openDb("postgis"); - outputDb(con, "", new File("busstop.json"), false); - outputDb(con, "WHERE fixed=0", new File("busstop0.json"), slim); - outputDb(con, "WHERE fixed=1", new File("busstop1.json"), slim); - outputDb(con, "WHERE fixed>1", new File("busstop2.json"), slim); - } - - public static void outputDb(Connection con, String whereStr, File outputFile, boolean slim) throws Exception { - try (BufferedWriter ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile), "UTF-8"))) { - String line = "{" + - "\"type\": \"FeatureCollection\"," + - "\"features\": ["; - System.out.println(line); - ow.write(line); - ow.newLine(); - - String sql = "SELECT row_to_json(feature) FROM (" - + "select 'Feature' As type, " - + "ST_AsGeoJSON(ST_Transform(t_busstop.geom,4326))::json As geometry, row_to_json((" - + "SELECT p FROM (SELECT t_busstop.gmlid, t_busstop.fixed) AS p" - + ")) AS properties From t_busstop "+ whereStr +" order by area,gmlid) As feature"; - if (slim) { - sql = "SELECT row_to_json(feature) FROM (" - + "select 'Feature' As type, ST_AsGeoJSON(ST_Transform(t_busstop.geom,4326))::json As geometry From t_busstop "+ whereStr +" order by area,gmlid) As feature"; - } - PreparedStatement ps8 = con.prepareStatement(sql); - try (ResultSet rset8 = ps8.executeQuery()) { - boolean top = true; - while (rset8.next()) { - if (top) { - top = false; - } - else { - line = ","; - System.out.println(line); - ow.write(line); - ow.newLine(); - } - line = rset8.getString(1); - System.out.print(line); - ow.write(line); - } - } - System.out.println(); - ow.newLine(); - - line = "]}"; - System.out.println(line); - ow.write(line); - ow.newLine(); - ow.flush(); - } + obj.outputDb(con, "", new File("GML_BUSSTOP", "busstop.json"), false); + obj.outputDb(con, "WHERE fixed=0", new File("GML_BUSSTOP", "busstop0.json"), false); + obj.outputDb(con, "WHERE fixed=1", new File("GML_BUSSTOP", "busstop1.json"), false); + obj.outputDb(con, "WHERE fixed>1", new File("GML_BUSSTOP", "busstop2.json"), false); } }