diff --git a/osmCoverage.jar b/osmCoverage.jar deleted file mode 100644 index 7dddcae..0000000 --- a/osmCoverage.jar +++ /dev/null Binary files differ diff --git a/src/osm/jp/coverage/busstop/Coverage.java b/src/osm/jp/coverage/busstop/Coverage.java new file mode 100644 index 0000000..fe6e939 --- /dev/null +++ b/src/osm/jp/coverage/busstop/Coverage.java @@ -0,0 +1,225 @@ +package osm.jp.coverage.busstop; + +import java.io.File; +import java.io.FileNotFoundException; +import java.io.IOException; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.DecimalFormat; +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; + +public class Coverage { + static String[] areaArgs = {"全国","北海道","青森県","岩手県","宮城県","秋田県","山形県","福島県","茨城県","栃木県","群馬県","埼玉県","千葉県","東京都","神奈川県","新潟県","富山県","石川県","福井県","山梨県","長野県","岐阜県","静岡県","愛知県","三重県","滋賀県","京都府","大阪府","兵庫県","奈良県","和歌山県","鳥取県","島根県","岡山県","広島県","山口県","徳島県","香川県","愛媛県","高知県","福岡県","佐賀県","長崎県","熊本県","大分県","宮崎県","鹿児島県","沖縄県"}; + + 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 "); + 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); + + try { + // 都道府県名(name)の設定 + String sqlStr = "INSERT INTO coverage (area, name) VALUES(?,?);"; + for (int i=0; i < areaArgs.length; i++) { + PreparedStatement ps = con.prepareStatement(sqlStr); + ps.setInt(1, i); + ps.setString(2, areaArgs[i]); + try { + ps.executeUpdate(); + } + finally { + ps.close(); + } + } + + // 分母(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(); + + 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 < areaArgs.length; i++) { + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE area=?;"); + try { + ps2.setInt(1, i); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + denominator = rset2.getLong(1); + } + rset2.close(); + } + finally { + ps2.close(); + } + + ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_busstop WHERE (fixed > 0) and (area=?);"); + try { + ps2.setInt(1, i); + rset2 = ps2.executeQuery(); + if (rset2.next()) { + lv1 = 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(); + } + } + + 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()); + } + } + finally { + ps.close(); + csv.save(); + } + } + finally { + DatabaseTool.closeDb(con); + DatabaseTool.closeDb(conPost); + } + } + + public Coverage() { + } +} diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index 42a3488..7ead54c 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -54,6 +54,10 @@ drop(con, "existing_data"); createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));"; create(con, createSt); + + drop(con, "coverage"); + createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);"; + create(con, createSt); } public static void create(Connection con, String createsql) throws SQLException { diff --git a/src/update.sh b/src/update.sh index 7427d6d..00fe588 100755 --- a/src/update.sh +++ b/src/update.sh @@ -1,2 +1,3 @@ java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.Busstop -update java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar osm.jp.postgis.Do_sqlfiles -update +java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.Coverage coverage.csv