diff --git a/src/osm/jp/coverage/fuel/DbFuel.java b/src/osm/jp/coverage/fuel/DbFuel.java index bc47ffc..b2dcf12 100644 --- a/src/osm/jp/coverage/fuel/DbFuel.java +++ b/src/osm/jp/coverage/fuel/DbFuel.java @@ -139,6 +139,7 @@ * * @param con * @param node + * @param areacode * @throws IOException * @throws SQLException */ diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java new file mode 100644 index 0000000..a54c253 --- /dev/null +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -0,0 +1,149 @@ +package osm.jp.coverage.fuel; +import osm.jp.coverage.busstop.*; + +import javax.xml.parsers.*; +import javax.xml.transform.TransformerException; + +import org.xml.sax.*; + +import java.io.*; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.SimpleDateFormat; +import jp.co.areaweb.tools.database.*; + +public class Fuel { + + String filter = ""; + String urlStr = ""; + + public static final boolean DB_INIT = false; + + // 近くのノードを探す範囲(KJS2を中心としたNEER×2(m)四方の領域 + static final int NEER = 150; // 150m(0.15km) + + public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); + + /** + * メイン + * + * java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop [option] + * OPTION: -check OSMデータ上に既存のバス停が存在するかどうかをチェックする + * + * @param args + * @throws IOException + * @throws SQLException + * @throws ClassNotFoundException + * @throws FileNotFoundException + * @throws TransformerException + * @throws SAXException + * @throws ParserConfigurationException + */ + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException + { + // HSQLディレクトリがなければ作る + File dbdir = new File("database"); + if (!dbdir.isDirectory()) { + dbdir.mkdir(); + } + + Connection con = DatabaseTool.openDb("database"); + + try { + new Fuel(con); + } + finally { + DatabaseTool.closeDb(con); + } + } + + /** + * 個別の都道府県「GMLディレクトリ」を処理 + * + * @param con + * @throws SQLException + * @throws FileNotFoundException + * @throws ClassNotFoundException + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + * @throws TransformerException + */ + public Fuel(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { + try ( PreparedStatement ps1 = con.prepareStatement("SELECT idref,lat,lon,fixed,area FROM "+ DbFuel.TABLE_NAME); + PreparedStatement ps2 = con.prepareStatement("SELECT SUM(score) FROM "+ DbExist.TABLE_NAME +" where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); + PreparedStatement ps3 = con.prepareStatement("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed=? WHERE idref=?");) + { + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + String idref = rset1.getString("idref"); + Double lat = rset1.getDouble("lat"); + Double lon = rset1.getDouble("lon"); + int fixed = rset1.getInt("fixed"); + + // 指定の緯度経度を中心とする半径150x2m四方の矩形領域 + RectArea rect = new RectArea(lat, lon, NEER); // 300m 四方 + ps2.setDouble(1, rect.minlat); + ps2.setDouble(2, rect.maxlat); + ps2.setDouble(3, rect.minlon); + ps2.setDouble(4, rect.maxlon); + try (ResultSet rset2 = ps2.executeQuery()) { + if (rset2.next()) { + int score = rset2.getInt(1); + if (fixed != score) { + ps3.setInt(1, score); + ps3.setString(2, idref); + ps3.executeUpdate(); + } + } + } + } + } + } + } + + /** + * + * @param conHsql + * @param conPost + * @param areacode + * @throws java.io.FileNotFoundException + * @throws java.lang.ClassNotFoundException + * @throws java.sql.SQLException + * @throws java.io.IOException + * @throws javax.xml.parsers.ParserConfigurationException + * @throws org.xml.sax.SAXException + */ + public static void updateFile (Connection conHsql, Connection conPost, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + int iCounter = 0; + PreparedStatement ps2; + try (PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) LON, ST_Y(geom) LAT FROM t_busstop WHERE area=?")) { + ps2 = conHsql.prepareStatement("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES (?,?,?,?,?,?)"); + // idref と nameStr をデータベースに格納する + ps1.setInt(1, areacode); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + iCounter++; + int idref = rset1.getInt("gid"); + String nameStr = rset1.getString("name"); + int fixed = rset1.getInt("fixed"); + double lat = rset1.getDouble(4); + double lon = rset1.getDouble(5); + + ps2.setInt(1, idref); + ps2.setString(2, nameStr); + ps2.setInt(3, fixed); + ps2.setInt(4, areacode); + ps2.setDouble(5, lon); + ps2.setDouble(6, lat); + System.out.println("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES ("+ idref +",'"+ nameStr +"',"+ fixed +","+ areacode +","+ lat +","+ lon +")"); + ps2.executeUpdate(); + } + } + } + ps2.close(); + System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); + } +} \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/ToPostgis.java b/src/osm/jp/coverage/fuel/ToPostgis.java new file mode 100644 index 0000000..555ea4e --- /dev/null +++ b/src/osm/jp/coverage/fuel/ToPostgis.java @@ -0,0 +1,70 @@ +package osm.jp.coverage.fuel; + +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; + +/** + * 国土数値情報から読み取ったデータをPostGISへインサートするためのSQLファイルに出力する。 + * 出力ファイル名: "busstop.sql" + * テーブル名: t_busstop + * インデックス: ix_busstop_geom + * gid PostGISの識別ID + * name バス停名称 + * fixed OSMのバス停が周辺に存在するかどうか、存在しない場合は0,存在する場合はその数。 + * geom PostGIS形式の位置情報(4612:) + * @author yuu + * + */ +public class ToPostgis { + public static final String SQL_FILE_NAME = "busstop.sql"; + BufferedWriter ow = null; + + public ToPostgis(File dir) throws UnsupportedEncodingException, FileNotFoundException { + File sqlFile = new File(dir, SQL_FILE_NAME); + this.ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(sqlFile), "UTF-8")); + } + + public int outputDb(Connection con) throws IOException, SQLException { + int counter = 0; + + PreparedStatement ps8 = con.prepareStatement("SELECT idref,name,kana,lat,lon,fixed FROM bus_stop"); + try (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 = "insert into t_busstop(name,fixed,area,geom) VALUES('"+ escapeStr(name) +"',"+ score +",0, ST_GeomFromText('POINT("+ Double.toString(lon) +" "+ Double.toString(lat) +")', 4612));"; + System.out.println(osm_node); + this.ow.write(osm_node); + this.ow.newLine(); + } + } + this.ow.flush(); + return counter; + } + + /** + * postgresql 用に(')をエスケープする + * + * @param name + * @return + */ + public String escapeStr(String name) { + return ((name == null) ? "" : name.replaceAll("'", "''")); + } +} diff --git a/src/t_fuel.sql b/src/t_fuel.sql new file mode 100644 index 0000000..e7c784f --- /dev/null +++ b/src/t_fuel.sql @@ -0,0 +1,13 @@ +DROP TABLE t_fuel; +CREATE TABLE t_fuel (gid SERIAL PRIMARY KEY, idstr text, kana text, fixed integer, area integer, geom GEOMETRY(POINT, 4612)); +CREATE INDEX ix_t_fuel_geom ON t_fuel USING GiST (geom); + +insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000001','アイカワイッチョウメ',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612)); +insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000002','アイカワイッチョウメ',0,0, ST_GeomFromText('POINT(136.969047121627 35.102834616747685)', 4612)); +insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000003','アイオイヤマジュウタク',0,0, ST_GeomFromText('POINT(136.9743151095072 35.10462957171922)', 4612)); +insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000004','アイオイヤマジュウタク',0,0, ST_GeomFromText('POINT(136.97421198072038 35.10334076583495)', 4612)); +insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000005','アイオイヤマジュウタク',0,0, ST_GeomFromText('POINT(136.9744339059364 35.10354449054206)', 4612)); +insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000006','アイオイヤマジュウタクミナミ',0,0, ST_GeomFromText('POINT(136.97261499203069 35.102706336248744)', 4612)); +insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000007','アイオイヤマジュウタクミナミ',0,0, ST_GeomFromText('POINT(136.97317171870043 35.10254162416889)', 4612)); + +