diff --git a/src/osm/jp/coverage/postoffice/ToPostgis.java b/src/osm/jp/coverage/postoffice/ToPostgis.java new file mode 100644 index 0000000..df1b403 --- /dev/null +++ b/src/osm/jp/coverage/postoffice/ToPostgis.java @@ -0,0 +1,34 @@ +package osm.jp.coverage.postoffice; + +/** + * 国土数値情報から読み取ったデータを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:) + * @author yuu + * + */ +public class ToPostgis extends osm.jp.postgis.ToPostgis +{ + public static void main(String[] argv) throws Exception { + boolean update = false; + for (String arg : argv) { + if (arg.toUpperCase().equals("-UPDATE")) { + update = true; + } + } + + ToPostgis model = new ToPostgis(update); + model.transport(); + } + + public ToPostgis(boolean update) { + super(DbPostoffice.TABLE_NAME, update); + } + +} diff --git a/src/osm/jp/postgis/ToPostgis.java b/src/osm/jp/postgis/ToPostgis.java index e281ecb..c06b10b 100644 --- a/src/osm/jp/postgis/ToPostgis.java +++ b/src/osm/jp/postgis/ToPostgis.java @@ -1,6 +1,5 @@ package osm.jp.postgis; -import osm.jp.coverage.busstop.*; import java.io.FileNotFoundException; import java.io.IOException; import java.math.BigDecimal; @@ -14,32 +13,35 @@ import osm.jp.api.HttpPOST; /** - * １０００ｍメッシュをPOSTGIS.t_testへ反映させる - * ノード: 綾西 (368434484) 35.4342443, 139.4092180 - * から - * ノード: 庚申供養 (1679559681) 35.4342940, 139.4207050 - * ノード: 内藤橋 (1742708189) : 35.4432806, 139.4104114 - * までが 1,000m となるはず - * - * テーブル名： t_test - * インデックス： ix_test_geom - * gid PostGISの識別ID - * fixed + * 国土数値情報から読み取ったデータを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:) * @author yuu * */ public class ToPostgis { - public static void main(String[] argv) throws Exception { + boolean UPDATE = false; + String tableName = null; + + public ToPostgis(String tableName, boolean update) { + this.tableName = tableName; + this.UPDATE = update; + } + + public void transport() throws Exception { Connection conHsql = null; Connection conPost = null; try { // DB.tableを作成 conHsql = DatabaseTool.openDb("database"); conPost = DatabaseTool.openDb("postgis"); - - transportNew(conHsql, conPost); - + transport(conHsql, conPost); } finally { if (conHsql != null) { @@ -50,9 +52,10 @@ } } } - + /** - * POSTGIS "insert into t_test(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));" + * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM POSTOFFICE" + * POSTGIS "insert into t_POSTOFFICE(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));" * * @param conHsql * @param conPost @@ -63,45 +66,57 @@ * @throws javax.xml.parsers.ParserConfigurationException * @throws org.xml.sax.SAXException */ - public static void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - HttpPOST.sql(conPost, "DROP TABLE IF EXISTS t_test CASCADE;"); - HttpPOST.sql(conPost, "CREATE TABLE t_test (gid SERIAL PRIMARY KEY,fixed integer,area integer,geom GEOMETRY(POINT, 4612));"); - HttpPOST.sql(conPost, "CREATE INDEX ix_test_geom ON t_test USING GiST (geom);"); + void transport (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + if (this.UPDATE) { + transportUpdate(conHsql, conPost); + } + else { + transportNew(conHsql, conPost); + } + } + void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + HttpPOST.sql(conPost, "DROP TABLE IF EXISTS t_"+ tableName +" CASCADE;"); + HttpPOST.sql(conPost, "CREATE TABLE t_"+ tableName +" (gid SERIAL PRIMARY KEY, idref varchar(24), fixed integer,area integer,geom GEOMETRY(POINT, 4612));"); + HttpPOST.sql(conPost, "CREATE INDEX ix_"+ tableName +"_geom ON t_"+ tableName +" USING GiST (geom);"); toInsert(conHsql, conPost); } - public static void toInsert (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - String sql = "SELECT gmlid,name,lat,lon,fixed1,area FROM "+ DbBusstop.TABLE_NAME; + void transportUpdate (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + toInsert(conHsql, conPost); + + try ( PreparedStatement ps1 = conHsql.prepareStatement("SELECT gid,fixed1 FROM "+ tableName +" where up=1"); + PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_"+ tableName +" set fixed=? WHERE gid=?")) { + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + long gid = rset1.getLong("gid"); + int fixed1 = rset1.getInt("fixed1"); + ps2.setInt(1, fixed1); + ps2.setLong(2, gid); + System.out.println("UPDATE t_"+ tableName +" set fixed="+ fixed1 +" WHERE gid="+ gid); + ps2.executeUpdate(); + } + } + } + } + + void toInsert (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + String sql = "SELECT idref,area,fixed1,lat,lon FROM "+ tableName; + if (this.UPDATE) { + sql += " where up=2"; + } try (PreparedStatement ps1 = conHsql.prepareStatement(sql)) { try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { - String gmlid = rset1.getString("gmlid"); + String idref = rset1.getString("idref"); int area = rset1.getInt("area"); int fixed1 = rset1.getInt("fixed1"); double lat = rset1.getDouble("lat"); double lon = rset1.getDouble("lon"); - String name = rset1.getString("name"); - int fixed = 0; - if (fixed1 >= 100) { - fixed = 3; - } - else if (fixed1 >= 50) { - fixed = 2; - } - else if (fixed1 > 0) { - fixed = 1; - } - String geom = "ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612)"; - String sqlStr = "INSERT INTO t_busstop (gmlid,name,fixed,area,geom) VALUES ('"+ gmlid +"','"+ name +"',"+ fixed +","+ area +","+ geom +")"; - System.out.println(sqlStr); - sqlStr = "INSERT INTO t_busstop (gmlid,name,fixed,area,geom) VALUES (?,?,?,?,"+ geom +")"; + String sqlStr = "INSERT INTO t_"+ tableName +" (idref,fixed,area,geom) VALUES ('"+ idref +"',"+ fixed1 +","+ area +",ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))"; try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) { - ps.setString(1, gmlid); - ps.setString(2, name); - ps.setInt(3, fixed); - ps.setInt(4, area); + System.out.println(sqlStr); ps.executeUpdate(); } }