diff --git a/gisdb.properties b/gisdb.properties new file mode 100644 index 0000000..6437dc0 --- /dev/null +++ b/gisdb.properties @@ -0,0 +1,4 @@ +db_driver=org.postgresql.Driver +db_url=jdbc:postgresql://192.168.0.29:5432/gisdb +db_user=postgres +db_passwd=yuu8844 diff --git a/lib/postgresql-42.2.4.jar b/lib/postgresql-42.2.4.jar new file mode 100644 index 0000000..4f747be --- /dev/null +++ b/lib/postgresql-42.2.4.jar Binary files differ diff --git a/lib/postgresql-9.4.1212.jar b/lib/postgresql-9.4.1212.jar deleted file mode 100644 index b0de752..0000000 --- a/lib/postgresql-9.4.1212.jar +++ /dev/null Binary files differ diff --git a/postgis.properties b/postgis.properties deleted file mode 100644 index 04ba2fe..0000000 --- a/postgis.properties +++ /dev/null @@ -1,4 +0,0 @@ -db_driver=org.postgresql.Driver -db_url=jdbc:postgresql://surveyor.mydns.jp:5432/gisdb -db_user=gisuser -db_passwd=gisuser diff --git a/src/osm/jp/postgis/Postgis.java b/src/osm/jp/postgis/Postgis.java index f815dd0..0cdeda3 100644 --- a/src/osm/jp/postgis/Postgis.java +++ b/src/osm/jp/postgis/Postgis.java @@ -1,22 +1,22 @@ package osm.jp.postgis; +import java.io.File; +import java.io.FileInputStream; import java.io.IOException; +import java.io.InputStreamReader; +import java.io.LineNumberReader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.SQLSyntaxErrorException; +import java.util.ArrayList; +import jp.co.areaweb.tools.csv.CsvRecord; import jp.co.areaweb.tools.database.DatabaseTool; +import static osm.jp.postgis.ToPostgis.printMark; /** - * 国土数値情報から読み取ったデータをPostGISへインサートするためのSQLファイルに出力する。 - * 出力ファイル名： "t_POSTOFFICE.sql" - * テーブル名： t_POSTOFFICE - * インデックス： ix_t_POSTOFFICE_geom - * gid PostGISの識別ID - * idstr 国土数値情報のノードID - * fixed OSMのNodeが周辺に存在するかどうか、存在しない場合は0,存在する場合はそのScoreの合計。 - * area 都道府県コード - * geom PostGIS形式の位置情報(4612:) + * CSVファイルの内容をPostGIS.db.tableに書き戻す + * * @author yuu * */ @@ -25,15 +25,32 @@ public static void main(String[] args) throws Exception { try (Postgis db = new Postgis()) { db.initTableAll(); + db.importCsvAll(); } } - static final String TABLE_FUEL = "t_fuel"; - static final String[] TABLES = {TABLE_FUEL}; + TableInfo[] tables; Connection con = null; + class TableInfo { + String tableName; + String csvPath; + + TableInfo(String tableName, String csvPath) { + this.tableName = tableName; + this.csvPath = csvPath; + } + } + public Postgis() throws ClassNotFoundException, SQLException, IOException { - con = DatabaseTool.openDb("postgis"); + ArrayList list = new ArrayList<>(); + list.add(new TableInfo("t_busstop", "GML_BUSSTOP/t_busstop.csv")); + list.add(new TableInfo("t_fuel", "GML_FUEL/t_fuel.csv")); + list.add(new TableInfo("t_police", "GML_POLICE/t_police.csv")); + list.add(new TableInfo("t_postoffice", "GML_POSTOFFICE/t_postoffice.csv")); + this.tables = list.toArray(new TableInfo[list.size()]); + + con = DatabaseTool.openDb("gisdb"); } @Override @@ -44,14 +61,14 @@ } public void initTableAll() throws Exception { - for (String tableName : TABLES) { - initTable(tableName); + for (TableInfo tableInfo : tables) { + initTable(tableInfo.tableName); } } public void initTable(String tableName) throws Exception { sql("DROP TABLE IF EXISTS "+ tableName +" CASCADE;"); - sql("CREATE TABLE public."+ tableName + sql("CREATE TABLE "+ tableName +" (" + "gid SERIAL PRIMARY KEY, " + "gmlid varchar(24), " @@ -63,6 +80,92 @@ + ");"); sql("CREATE INDEX ix_"+ tableName +"_geom ON "+ tableName +" USING GiST (geom);"); } + + public void importCsvAll() throws Exception { + for (TableInfo info : this.tables) { + importCsv(info); + } + } + + public void importCsv(TableInfo info) throws Exception { + String charsetName = "UTF-8"; + File csvFile = new File(info.csvPath); + try (LineNumberReader reader = new LineNumberReader(new InputStreamReader(new FileInputStream(csvFile), charsetName))) { + String line; + ArrayList header = new ArrayList<>(); + while ((line = reader.readLine()) != null) { + if (line.trim().length() == 0) { + continue; + } + + CsvRecord record = new CsvRecord(); + record.analizeRecord(line); + if (header.isEmpty()) { + for (String str : record) { + header.add(str); + } + } + else { + toInsert(info.tableName, header, record); + } + } + } + } + + public void toInsert(String tableName, ArrayList header, CsvRecord record) + throws Exception + { + String gmlidStr = getStr(header, record, "gmlid"); + String idrefStr = getStr(header, record, "idref"); + int area = getInt(header, record, "area"); + int fixed = getInt(header, record, "fixed"); + int code = getInt(header, record, "code"); + String latStr = record.get(header.indexOf("lat")); + String lonStr = record.get(header.indexOf("lon")); + String latlon = String.format( + "ST_GeomFromText('POINT(%.7f %.7f)',4612)", + Double.parseDouble(lonStr), + Double.parseDouble(latStr) + ); + String sqlStr = "INSERT INTO "+ tableName + +" (gmlid,idref,area,fixed,code,geom) " + + "VALUES (?,?,?,?,?, "+ latlon +")"; + try (PreparedStatement ps = this.con.prepareStatement(sqlStr)) { + printMark(); + ps.setString(1, gmlidStr); // gmlid + ps.setString(2, idrefStr); // idref + ps.setInt(3, area); // area + ps.setInt(4, fixed); // fixed + ps.setInt(5, code); // code + ps.executeUpdate(); + } + catch (SQLSyntaxErrorException e) { + System.out.println("107:"+ e.toString()); + if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) { + throw e; + } + } + } + + String getStr(ArrayList header, CsvRecord record, String key) { + int i = header.indexOf(key); + if (i < 0) { + return ""; + } + else { + return record.get(i); + } + } + + int getInt(ArrayList header, CsvRecord record, String key) { + int i = header.indexOf(key); + if (i < 0) { + return 0; + } + else { + return Integer.parseInt(record.get(i)); + } + } public void sql(String sql) throws SQLException { System.out.println(sql);