diff --git a/src/osm/jp/coverage/busstop/ToCartoCSV.java b/src/osm/jp/coverage/busstop/ToCartoCSV.java new file mode 100644 index 0000000..af3df23 --- /dev/null +++ b/src/osm/jp/coverage/busstop/ToCartoCSV.java @@ -0,0 +1,103 @@ +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 jp.co.areaweb.tools.database.DatabaseTool; + +/** + * PostGISデータをCarto用のCSVファイルに出力する。 + * 出力ファイル名: "carto.csv" + * テーブル名: t_busstop + * インデックス: ix_busstop_geom + * gid PostGISの識別ID + * name バス停名称 + * fixed OSMのバス停が周辺に存在するかどうか、存在しない場合は0,存在する場合はその数。 + * geom PostGIS形式の位置情報(4612:) + * @author yuu + * + */ +public class ToCartoCSV { + public static final String CSV_FILE_NAME = "carto.csv"; + public static boolean NAGOYA_MODE = false; + BufferedWriter ow = null; + + public static void main () { + try { + ToCartoCSV obj = new ToCartoCSV(new File(".")); + obj.outputDb(DatabaseTool.openDb("database")); + } catch (Exception e) { + e.printStackTrace(); + } + } + + public ToCartoCSV(File dir) throws UnsupportedEncodingException, FileNotFoundException { + File csvFile = new File(dir, CSV_FILE_NAME); + this.ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8")); + } + + public void setNagoyaMode(boolean mode) { + ToCartoCSV.NAGOYA_MODE = mode; + } + + public boolean isNagoyaMode() { + return ToCartoCSV.NAGOYA_MODE; + } + + public int outputDb(Connection con) throws IOException, SQLException { + int counter = 0; + + // CSV header + System.out.println("name,fixed,area,geom"); + this.ow.write("name,fixed,area,geom"); + this.ow.newLine(); + + PreparedStatement ps8 = con.prepareStatement("SELECT idref,name,kana,lat,lon,fixed FROM bus_stop"); + ResultSet rset8 = ps8.executeQuery(); + while (rset8.next()) { + //String idref = rset8.getString("idref"); + String name = rset8.getString("name"); + //String kana = rset8.getString("kana"); + Double lat = rset8.getDouble("lat"); + Double lon = rset8.getDouble("lon"); + int score = rset8.getInt("fixed"); + + counter++; + String osm_node; + osm_node = "\""+ escapeStr(name) +"\",\""+ score +"\",\"0\",\""+ Double.toString(lon) +"\",\""+ Double.toString(lat) +"\""; + System.out.println(osm_node); + this.ow.write(osm_node); + this.ow.newLine(); + } + rset8.close(); + this.ow.flush(); + return counter; + } + + /** + * postgresql 用に(')をエスケープする + * + * @param name + * @return + */ + public String escapeStr(String name) { + return ((name == null) ? "" : name.replaceAll("'", "''")); + } + + public void close() { + try { + this.ow.close(); + } catch (IOException e) { + e.printStackTrace(); + } + } +}