vals = bar.getValue(); // 値を取得
- for(String str : vals) {
- System.out.print("["+ str +"],");
- }
- System.out.println();
- }
- if ((responsecode == 429) || (responsecode == 504) || (responsecode == 500)) {
- // レスポンスコード[429] レスポンスメッセージ[Too Many Requests]
- // レスポンスコード[500] レスポンスメッセージ[Internal server error]
- // レスポンスコード[504] レスポンスメッセージ[Gateway Timeout]
- System.out.print("Waite 5 minites.");
- try {
- TimeUnit.MINUTES.sleep(5);
- } catch (InterruptedException e) {}
- }
- else {
- System.out.println("\n---- ボディ ----");
-
- File oFile = new File(Osmdb.EXIST_FILE);
- oFile.deleteOnExit();
- try (
- BufferedWriter hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(oFile), "UTF-8"));
- BufferedReader reader = new BufferedReader(new InputStreamReader(urlconn.getInputStream(), "UTF-8"))
- ) {
- while (true) {
- String line = reader.readLine();
- if (line == null) {
- break;
- }
- hw.write(line);
- hw.newLine();
- }
- hw.flush();
- }
- }
- }
- catch (java.net.ConnectException e) {
- // レスポンスコード[600] レスポンスメッセージ[接続がタイムアウトしました (Connection timed out)]
- responsecode = 600;
- }
- finally {
- urlconn.disconnect();
- }
- System.out.print("Waite 5 seconds.");
- try {
- TimeUnit.SECONDS.sleep(5);
- } catch (InterruptedException e) {}
- }
- while ((responsecode == 429) || (responsecode == 504) || (responsecode == 600));
- }
-
public static void outputWriter(PrintWriter pw, String text) {
System.out.println("\t" + text);
pw.print(text);
@@ -199,34 +83,85 @@
/**
* File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。
* その際に、OSMノードを評価し、scoreを算定する
- * @param con 反映先のデータベースコネクタ(HSQLDB)
+ *
+ * @param hsqldb 反映先のデータベースコネクタ(HSQLDB)
+ * @param sqlWhere POI条件
* @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name'
- * @throws FileNotFoundException File(HttpPOST.EXIST_FILE)が存在しない
- * @throws ClassNotFoundException
- * @throws SQLException データベースエラー
- * @throws IOException
- * @throws ParserConfigurationException
- * @throws SAXException
+ * @throws Exception エラー
*/
- public void readExistingFile (Connection con, int point) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
+ public void readExistingNodes(Connection hsqldb, String sqlWhere, int point) throws Exception {
+ Connection osmdb = null;
+ long counter = 0L;
+ try {
+ osmdb = DatabaseTool.openDb("osmdb");
+
+ PreparedStatement ps1 = osmdb.prepareStatement("select osm_id,amenity,brand,disused,name,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + sqlWhere);
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ while (rset1.next()) {
+ counter += importExistingNode(hsqldb, rset1, point);
+ }
+ }
+ System.out.println("Exists Node count = " + counter);
+ }
+ finally {
+ DatabaseTool.closeDb(osmdb);
+ }
- DocumentBuilderFactory factory;
- DocumentBuilder builder;
+ //readAreaNodes(hsqldb, root);
+
+ }
- factory = DocumentBuilderFactory.newInstance();
- builder = factory.newDocumentBuilder();
- factory.setIgnoringElementContentWhitespace(true);
- factory.setIgnoringComments(true);
- factory.setValidating(true);
+ int importExistingNode(Connection hsqldb, ResultSet rset, int point) throws IOException, SQLException {
+
+ String osmidStr = rset.getString("osm_id");
+ String latStr = rset.getString("lat");
+ String lonStr = rset.getString("lon");
+ String brandStr = rset.getString("brand");
+ String nameStr = rset.getString("name");
+ String fixmeStr = "";
- Node root = builder.parse(new File(Osmdb.EXIST_FILE));
+ int score = 50;
+ if (((point & POINT_BRAND) != 0) && isNull(brandStr)) {
+ score = 1;
+ }
+ if (((point & POINT_NAME) != 0) && isNull(nameStr)) {
+ score = 1;
+ }
+ if (((point & POINT_FIXME) != 0) && !isNull(fixmeStr)) {
+ score = 1;
+ }
- readAreaNodes(con, root);
- int iCounter = readExistingNodes(con, root, point);
- System.out.println("既存ノード数["+ iCounter +"]");
+ // idref と brandStr をデータベースに格納する
+ System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES ("+ osmidStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N");
+ try (PreparedStatement ps5 = hsqldb.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) {
+ ps5.setString(1, osmidStr);
+ ps5.setDouble(2, Double.parseDouble(latStr));
+ ps5.setDouble(3, Double.parseDouble(lonStr));
+ ps5.setInt(4, score);
+ ps5.setString(5, nameStr);
+ ps5.executeUpdate();
+ return 1;
+ }
+ catch (HsqlException | SQLIntegrityConstraintViolationException e) {
+ // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
+ // [HsqlException]は、無視する
+ // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
+ // [SQLIntegrityConstraintViolationException]は、無視する
+ }
+ return 0;
}
- public int readAreaNodes(Connection con, Node node) throws IOException, SQLException {
+ boolean isNull(String vstr) {
+ if (vstr == null) {
+ return true;
+ }
+ if (vstr.trim().length() < 1) {
+ return true;
+ }
+ return false;
+ }
+
+ int readAreaNodes(Connection con, Node node) throws IOException, SQLException {
int iCounter = 0;
NodeList nodes = node.getChildNodes();
@@ -245,7 +180,7 @@
return iCounter;
}
- public void importAreaNode(Connection con, Node node) throws IOException, SQLException {
+ void importAreaNode(Connection con, Node node) throws IOException, SQLException {
String idrefStr = "";
String latStr = "";
String lonStr = "";
@@ -284,113 +219,9 @@
}
}
- public int readExistingNodes(Connection con, Node node, int point) throws IOException, SQLException {
- int iCounter = 0;
- NodeList nodes = node.getChildNodes();
- for (int i = 0; i < nodes.getLength(); i++) {
- Node node2 = nodes.item(i);
- switch (node2.getNodeName()) {
- case "node":
- iCounter++;
- importExistingNode(con, node2, point);
- break;
- case "way":
- iCounter++;
- importExistingArea(con, node2, point);
- break;
- default:
- iCounter += readExistingNodes(con, node2, point);
- break;
- }
- }
- return iCounter;
- }
- public void importExistingNode(Connection con, Node node, int point) throws IOException, SQLException {
- String idrefStr = "";
- String latStr = "";
- String lonStr = "";
- String brandStr = "";
- String nameStr = "";
- String fixmeStr = "";
-
- NamedNodeMap nodeMap = node.getAttributes();
- if (nodeMap != null) {
- for (int j=0; j < nodeMap.getLength(); j++) {
- switch (nodeMap.item(j).getNodeName()) {
- case "id":
- idrefStr = nodeMap.item(j).getNodeValue();
- break;
- case "lat":
- latStr = nodeMap.item(j).getNodeValue();
- break;
- case "lon":
- lonStr = nodeMap.item(j).getNodeValue();
- break;
- default:
- break;
- }
- }
-
- NodeList nodes = node.getChildNodes();
- if (nodes.getLength() == 0) {
- return;
- }
- for (int i = 0; i < nodes.getLength(); i++) {
- Node node2 = nodes.item(i);
- if (node2.getNodeName().equals("tag")) {
- OsmnodeTag tagnode = new OsmnodeTag(node2);
- String value = tagnode.getValue("brand");
- if (value != null) {
- brandStr = value;
- }
- value = tagnode.getValue("name");
- if (value != null) {
- nameStr = value;
- }
- value = tagnode.getValue("name:ja");
- if (value != null) {
- nameStr = value;
- }
- value = tagnode.getValue("fixme");
- if (value != null) {
- fixmeStr = value;
- }
- }
- }
-
- int score = 50;
- if (((point & POINT_BRAND) != 0) && brandStr.equals("")) {
- score = 1;
- }
- if (((point & POINT_NAME) != 0) && brandStr.equals("")) {
- score = 1;
- }
- if (((point & POINT_FIXME) != 0) && !fixmeStr.equals("")) {
- score = 1;
- }
-
- // idref と brandStr をデータベースに格納する
- System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES ("+ idrefStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N");
- try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) {
- ps5.setString(1, idrefStr);
- ps5.setDouble(2, Double.parseDouble(latStr));
- ps5.setDouble(3, Double.parseDouble(lonStr));
- ps5.setInt(4, score);
- ps5.setString(5, nameStr);
- ps5.executeUpdate();
- }
- catch (HsqlException | SQLIntegrityConstraintViolationException e) {
- // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
- // [HsqlException]は、無視する
- // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
- // [SQLIntegrityConstraintViolationException]は、無視する
- }
- }
- }
-
- public void importExistingArea(Connection con, Node node, int point) throws IOException, SQLException {
+ void importExistingArea(Connection con, Node node, int point) throws IOException, SQLException {
String idrefStr = "";
double maxlat = -90.0D;
double maxlon = -180.0D;
@@ -481,7 +312,7 @@
}
}
- public static Position getNdPosition(Connection con, String idref) throws SQLException {
+ static Position getNdPosition(Connection con, String idref) throws SQLException {
PreparedStatement ps8 = con.prepareStatement("SELECT lat,lon FROM AREA_NODE where idref=?");
ps8.setString(1, idref);
try (ResultSet rset8 = ps8.executeQuery()) {
diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java
index dbb0431..9a325da 100644
--- a/src/osm/jp/coverage/fuel/DbExist.java
+++ b/src/osm/jp/coverage/fuel/DbExist.java
@@ -1,49 +1,39 @@
package osm.jp.coverage.fuel;
-import java.io.*;
-import java.net.MalformedURLException;
-import java.net.ProtocolException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
-import javax.xml.parsers.ParserConfigurationException;
import jp.co.areaweb.tools.database.*;
-import org.xml.sax.SAXException;
-import osm.jp.api.Japan;
import osm.jp.api.Osmdb;
public class DbExist extends Osmdb {
/** メイン
* @param args
- * @throws IOException
- * @throws SQLException
- * @throws ClassNotFoundException
- * @throws FileNotFoundException
- * @throws javax.xml.parsers.ParserConfigurationException
- * @throws org.xml.sax.SAXException */
- public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException
+ * @throws Exception
+ */
+ public static void main(String[] args) throws Exception
{
- Connection con = null;
+ Connection hsqldb = null;
try {
// DB.tableを作成
- con = DatabaseTool.openDb("database");
- create(con);
+ hsqldb = DatabaseTool.openDb("database");
+ create(hsqldb);
/**
* 既存のOSMガソリンスタンドを読み込む
* OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする
*/
- (new DbExist()).getJapanCapabilities(con);
+ (new DbExist()).getJapanCapabilities(hsqldb);
- DbExist.export(con);
+ DbExist.export(hsqldb);
}
finally {
- if (con != null) {
- DatabaseTool.closeDb(con);
+ if (hsqldb != null) {
+ DatabaseTool.closeDb(hsqldb);
}
}
}
@@ -52,6 +42,7 @@
Test data:
ノード: エネオス (2015835273) 場所: 35.4367770, 139.4035710
ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no
+ ノード: 出光 (3877535257) 場所: 45.3985390, 141.6882450 brand=no
select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='fuel';
@@ -89,42 +80,27 @@
* select id,nodes,tags from planet_osm_ways;
* }
*
- * @param con
- * @throws MalformedURLException
- * @throws ProtocolException
- * @throws IOException
- * @throws ClassNotFoundException
- * @throws SQLException
- * @throws ParserConfigurationException
- * @throws SAXException
+ * @param hsqldb
+ * @throws Exception
*/
- public void getJapanCapabilities(Connection con) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException {
- for (Japan area : Japan.all) {
- StringBuilder queryText = new StringBuilder();
- queryText.append("(");
- queryText.append(" node[amenity=fuel]("+ area.getSWNE() +");");
- queryText.append(" (way[amenity=fuel]("+ area.getSWNE() +");>;);");
- queryText.append(");");
- queryText.append("out;");
- getQuery(queryText.toString());
-
- // 通常 → ５０ポイント
- // BRANDなし → １ポイント
- // FIXMEあり → １ポイント
- readExistingFile(con, POINT_BRAND | POINT_FIXME);
- }
+ public void getJapanCapabilities(Connection hsqldb) throws Exception {
+
+ // 通常 → ５０ポイント
+ // BRANDなし → １ポイント
+ // FIXMEあり → １ポイント
+ readExistingNodes(hsqldb, "where amenity='fuel'", POINT_BRAND | POINT_FIXME);
}
/**
* 'table.FUEL'の内容をCSV形式にして標準出力に出力する
- * @param con
+ * @param hsqldb
* @throws java.sql.SQLException
*/
- public static void export(Connection con) throws SQLException {
+ public static void export(Connection hsqldb) throws SQLException {
String header = "idref,lat,lon,score";
System.out.println("TABLE: "+ TABLE_NAME);
System.out.println(header);
- PreparedStatement ps8 = con.prepareStatement("SELECT idref,lat,lon,score FROM "+ TABLE_NAME);
+ PreparedStatement ps8 = hsqldb.prepareStatement("SELECT idref,lat,lon,score FROM "+ TABLE_NAME);
try (ResultSet rset8 = ps8.executeQuery()) {
while (rset8.next()) {
String idcode = rset8.getString(1);
diff --git a/src/osm/jp/coverage/test/DbExistTest.java b/src/osm/jp/coverage/test/DbExistTest.java
deleted file mode 100644
index 11a9f2e..0000000
--- a/src/osm/jp/coverage/test/DbExistTest.java
+++ /dev/null
@@ -1,95 +0,0 @@
-package osm.jp.coverage.test;
-
-import osm.jp.api.HttpPOST;
-
-import javax.xml.parsers.*;
-import javax.xml.transform.TransformerException;
-import org.xml.sax.*;
-
-import java.io.*;
-import java.net.MalformedURLException;
-import java.net.ProtocolException;
-import java.sql.Connection;
-import java.sql.SQLException;
-import java.text.SimpleDateFormat;
-
-import jp.co.areaweb.tools.database.*;
-import osm.jp.coverage.fuel.DbExist;
-
-public class DbExistTest {
-
- String filter = "";
- String urlStr = "";
-
- public static boolean DROP = false;
-
- // 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域
- static final int NEER = 150; // 150m(0.15km)
- static boolean update = false; // '-update'オプション postgisデータの更新を行う
- static boolean noget = false; // '-noget'オプション OSM既存データのGETを行わない
-
- public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
-
- /**
- * 既存のOSMバス停を読み込む
- * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、
- * 「HSQLDB.BUSSTOP_EXIST」にSTOREする
- */
- /**
- * メイン
- *
- * java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.DbExistBusstop
- *
- * @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
- {
- Connection conHsql = null;
- try {
- conHsql = DatabaseTool.openDb("database");
- HttpPOST.create(conHsql);
-
- getJapanCapabilitiesFuel(conHsql);
- }
- finally {
- if (conHsql != null) {
- DatabaseTool.closeDb(conHsql);
- }
- }
- }
-
- /**
- *
- * {@code
- *
- * node(35.42,139.39,35.45,139.42);
- * node[junction=yes]["name:en"="Ryosei"];
- * out;
- *
- * }
- *
- * @param conHsql
- * @throws MalformedURLException
- * @throws ProtocolException
- * @throws IOException
- * @throws ClassNotFoundException
- * @throws SQLException
- * @throws ParserConfigurationException
- * @throws SAXException
- */
- public static void getJapanCapabilitiesFuel(Connection conHsql) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException {
- StringBuilder queryText = new StringBuilder();
- queryText.append("node(35.42,139.39,35.45,139.42);");
- queryText.append("node[junction=yes][\"name:en\"=\"Ryosei\"];");
- queryText.append("out;");
- HttpPOST.getQuery(queryText.toString());
- (new DbExist()).readExistingFile(conHsql, 2);
- }
-}
\ No newline at end of file
diff --git a/src/osm/jp/coverage/test/DbTest.java b/src/osm/jp/coverage/test/DbTest.java
deleted file mode 100644
index 712188b..0000000
--- a/src/osm/jp/coverage/test/DbTest.java
+++ /dev/null
@@ -1,173 +0,0 @@
-package osm.jp.coverage.test;
-
-import java.io.*;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import javax.xml.parsers.ParserConfigurationException;
-
-import jp.co.areaweb.tools.database.*;
-import org.xml.sax.SAXException;
-import osm.jp.api.HttpPOST;
-
-public class DbTest {
- public static final String TABLE_NAME = "test";
-
- File inputFile;
- String filter = "";
- int iCounter = 0;
- String urlStr = "";
- Connection con;
- String timeStampStr = null;
- File dir = null;
-
- /** メイン
- * 動作条件； HSQLDBのフォルダを削除した状態で実行すること。
- * @param args
- * @throws IOException
- * @throws SQLException
- * @throws ClassNotFoundException
- * @throws FileNotFoundException
- * @throws javax.xml.parsers.ParserConfigurationException
- * @throws org.xml.sax.SAXException */
- public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException
- {
- // HSQLディレクトリがなければエラー
- File dbdir = new File("database");
- if (!dbdir.isDirectory()) {
- throw new FileNotFoundException("Directory 'database' is not found.");
- }
-
- Connection conHsql = null;
- try {
- conHsql = DatabaseTool.openDb("database");
- HttpPOST.sql(conHsql, "DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE");
- //HttpPOST.sql(conHsql, "DROP INDEX "+ TABLE_NAME +"_index;");
- create(conHsql);
- try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) {
- ps.executeUpdate();
- }
-
- // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。
- importTest(conHsql, 14);
-
- DbTest.export(conHsql);
- }
- finally {
- if (conHsql != null) {
- DatabaseTool.closeDb(conHsql);
- }
- }
- }
-
- /**
- * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する
- * @param conHsql
- * @param areacode
- * @throws FileNotFoundException
- * @throws ClassNotFoundException
- * @throws SQLException
- * @throws IOException
- * @throws ParserConfigurationException
- * @throws SAXException
- */
- public static void importTest (Connection conHsql, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
- int iCounter = 0;
- iCounter += showNodes(conHsql, areacode);
- System.out.println("("+ areacode +") Node数["+ iCounter +"]");
- }
-
- static final double CENTER_LAT = 35.4342443D;
- static final double CENTER_LON = 139.4092180D;
- static final int LOOP = 50;
- static final double SIDE = 0.015D;
-
- /**
- * ノード: 綾西 (368434484) 35.4342443, 139.4092180
- *
- * @param con
- * @param areacode
- * @return
- * @throws IOException
- * @throws SQLException
- */
- public static int showNodes(Connection con, int areacode) throws IOException, SQLException {
- int iCounter = 0;
- double lon = CENTER_LON - SIDE;
- for (int x = 0; x <= LOOP; x++) {
- double lat = CENTER_LAT - SIDE;
- for (int y = 0; y <= LOOP; y++) {
- showGmlPoint(con, lat, lon, areacode, iCounter++);
- lat += SIDE * 2 / LOOP;
- }
- lon += SIDE * 2 / LOOP;
- }
- return iCounter;
- }
-
- /**
- *
- * @param con
- * @param lat
- * @param lon
- * @param areacode
- * @param id
- * @throws IOException
- * @throws SQLException
- */
- public static void showGmlPoint(Connection con, double lat, double lon, int areacode, int id) throws IOException, SQLException {
- try (PreparedStatement ps6 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (lat,lon,fixed,fixed1,area,gmlid,up) VALUES (?,?,0,0,?,?,2)")) {
- ps6.setDouble(1, lat);
- ps6.setDouble(2, lon);
- ps6.setInt(3, areacode);
- ps6.setString(4, Integer.toString(id));
- ps6.executeUpdate();
- }
- }
-
-
- /**
- * 'table.BUS_STOP'を新規に作る
- * 既にテーブルが存在する時には何もしない
- * @param conHsql
- * @throws SQLException
- */
- public static void create(Connection conHsql) throws SQLException {
- String createSt;
-
- // 'table.TEST'を新規に作る
- createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));";
- HttpPOST.sql(conHsql, createSt);
-
- createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);";
- HttpPOST.sql(conHsql, createSt);
- }
-
- /**
- * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する
- * @param con
- */
- public static void export(Connection con) {
- try {
- System.out.println("TABLE: " + TABLE_NAME);
- System.out.println("\"gmlid\",\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\"");
- PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,name,lat,lon,fixed,ifile FROM "+ TABLE_NAME);
- try (ResultSet rset8 = ps8.executeQuery()) {
- while (rset8.next()) {
- String gmlid = rset8.getString("gmlid");
- String name = rset8.getString("name");
- Double lat = rset8.getDouble("lat");
- Double lon = rset8.getDouble("lon");
- int fixed = rset8.getInt("fixed");
- String ifile = rset8.getString("ifile");
- System.out.println("\""+ gmlid +"\",\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\"");
- }
- }
- }
- catch (SQLException e) {
- e.printStackTrace();
- }
- }
-
-}
\ No newline at end of file
diff --git a/src/osm/jp/coverage/test/Test.java b/src/osm/jp/coverage/test/Test.java
deleted file mode 100644
index 0bc244a..0000000
--- a/src/osm/jp/coverage/test/Test.java
+++ /dev/null
@@ -1,127 +0,0 @@
-package osm.jp.coverage.test;
-
-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.*;
-import osm.jp.api.HttpPOST;
-import osm.jp.api.Japan;
-import osm.jp.api.RectArea;
-
-public class Test {
-
- // 近くのノードを探す範囲（KJS2を中心としたNEER×2(ｍ)四方の領域
- static final int NEER = 1000; // 1000m x 2 = 2000m四方
- static final double DISTANCE = 1000.0D; // 1000m 以内
-
- 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 Test(con);
- }
- finally {
- DatabaseTool.closeDb(con);
- }
- }
-
- /**
- * 個別の都道府県「GMLディレクトリ」を処理
- *
- * @param con
- * @throws SQLException
- * @throws FileNotFoundException
- * @throws ClassNotFoundException
- * @throws IOException
- * @throws ParserConfigurationException
- * @throws SAXException
- * @throws TransformerException
- */
- public Test(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException {
-
- String sqlStr1 = "SELECT lat,lon FROM "+ HttpPOST.TABLE_NAME;
- String sqlStr2 = "SELECT gmlid,lat,lon,area FROM "+ DbTest.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)";
- String sqlStr3 = "UPDATE "+ DbTest.TABLE_NAME +" SET fixed1=1 WHERE gmlid=? and area=?";
- String sqlStr4 = "UPDATE "+ DbTest.TABLE_NAME +" SET fixed1=0";
- String sqlStr5 = "UPDATE "+ DbTest.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)";
- try ( PreparedStatement ps2 = con.prepareStatement(sqlStr2);
- PreparedStatement ps1 = con.prepareStatement(sqlStr1);
- PreparedStatement ps3 = con.prepareStatement(sqlStr3);
- PreparedStatement ps4 = con.prepareStatement(sqlStr4);
- PreparedStatement ps5 = con.prepareStatement(sqlStr5))
- {
- System.out.println(sqlStr4);
- ps4.executeUpdate();
-
- try (ResultSet rset1 = ps1.executeQuery()) {
- System.out.println(sqlStr1);
- while (rset1.next()) {
- double lat = rset1.getDouble("lat");
- double lon = rset1.getDouble("lon");
-
- // 指定の緯度経度を中心とする半径1000x2m四方の矩形領域
- RectArea rect = new RectArea(lat, lon, NEER); // 1000m 四方
- ps2.setDouble(1, rect.minlat);
- ps2.setDouble(2, rect.maxlat);
- ps2.setDouble(3, rect.minlon);
- ps2.setDouble(4, rect.maxlon);
- System.out.println(sqlStr2 +" ["+ rect.minlat +", "+ rect.maxlat +", "+ rect.minlon +", "+ rect.maxlon +"]");
- try (ResultSet rset2 = ps2.executeQuery()) {
- while (rset2.next()) {
- String gmlid = rset2.getString("gmlid");
- double lat2 = rset2.getDouble("lat");
- double lon2 = rset2.getDouble("lon");
- int area = rset2.getInt("area");
- double dd = Japan.distanceKm(lat,lon,lat2,lon2);
- System.out.println("D"+"distance = "+ dd +"");
- if ((dd * 1000.0D) < DISTANCE) {
- System.out.println(sqlStr3 +" ["+ gmlid +", "+ area +"]");
- ps3.setString(1, gmlid);
- ps3.setInt(2, area);
- ps3.executeUpdate();
- }
- else {
- System.out.println("out of distance "+ dd +" : NEER=1000");
- }
- }
- }
- }
- }
-
- System.out.println(sqlStr5);
- ps5.executeUpdate();
- }
- }
-}
\ No newline at end of file
diff --git a/src/osm/jp/coverage/test/ToPostgis.java b/src/osm/jp/coverage/test/ToPostgis.java
deleted file mode 100644
index fd3f6c7..0000000
--- a/src/osm/jp/coverage/test/ToPostgis.java
+++ /dev/null
@@ -1,96 +0,0 @@
-package osm.jp.coverage.test;
-
-import java.io.FileNotFoundException;
-import java.io.IOException;
-import java.math.BigDecimal;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import javax.xml.parsers.ParserConfigurationException;
-import jp.co.areaweb.tools.database.DatabaseTool;
-import org.xml.sax.SAXException;
-import osm.jp.api.HttpPOST;
-
-/**
- * HSQLDB.TESTの結果をPOSTGIS.t_testへ反映させる
- * テーブル名： t_test
- * インデックス： ix_test_geom
- * gid PostGISの識別ID
- * name 名称
- * fixed OSMのバス停が周辺に存在するかどうか、存在しない場合は0,存在する場合はその数。
- * geom PostGIS形式の位置情報(4612:)
- * @author yuu
- *
- */
-public class ToPostgis {
- public static void main(String[] argv) throws Exception {
- Connection conHsql = null;
- Connection conPost = null;
- try {
- // DB.tableを作成
- conHsql = DatabaseTool.openDb("database");
- conPost = DatabaseTool.openDb("postgis");
-
- transportNew(conHsql, conPost);
- }
- finally {
- if (conHsql != null) {
- DatabaseTool.closeDb(conHsql);
- }
- if (conPost != null) {
- DatabaseTool.closeDb(conPost);
- }
- }
- }
-
- /**
- * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM BUS_STOP"
- * POSTGIS "insert into t_busstop(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));"
- *
- * @param conHsql
- * @param conPost
- * @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 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,gmlid text,name text,fixed integer,area integer,geom GEOMETRY(POINT, 4612));");
- HttpPOST.sql(conPost, "CREATE INDEX ix_test_geom ON t_test 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,lat,lon,fixed1,area FROM "+ DbTest.TABLE_NAME;
- try (PreparedStatement ps1 = conHsql.prepareStatement(sql)) {
- try (ResultSet rset1 = ps1.executeQuery()) {
- while (rset1.next()) {
- String gmlid = rset1.getString("gmlid");
- int area = rset1.getInt("area");
- int fixed1 = rset1.getInt("fixed1");
- double lat = rset1.getDouble("lat");
- double lon = rset1.getDouble("lon");
-
- int fixed = 0;
- if (fixed1 > 0) {
- fixed = 1;
- }
- String geom = "ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612)";
- String sqlStr = "INSERT INTO t_test (gmlid,fixed,area,geom) VALUES (?,?,?,"+ geom +")";
- System.out.println(sqlStr +" ["+ gmlid +", "+ fixed +", "+ area +"]");
- try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
- ps.setString(1, gmlid);
- ps.setInt(2, fixed);
- ps.setInt(3, area);
- ps.executeUpdate();
- }
- }
- }
- }
- }
-}
diff --git a/test/osm/jp/coverage/DbTest.java b/test/osm/jp/coverage/DbTest.java
new file mode 100644
index 0000000..db48660
--- /dev/null
+++ b/test/osm/jp/coverage/DbTest.java
@@ -0,0 +1,173 @@
+package osm.jp.coverage;
+
+import java.io.*;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import javax.xml.parsers.ParserConfigurationException;
+
+import jp.co.areaweb.tools.database.*;
+import org.xml.sax.SAXException;
+import osm.jp.api.HttpPOST;
+
+public class DbTest {
+ public static final String TABLE_NAME = "test";
+
+ File inputFile;
+ String filter = "";
+ int iCounter = 0;
+ String urlStr = "";
+ Connection con;
+ String timeStampStr = null;
+ File dir = null;
+
+ /** メイン
+ * 動作条件； HSQLDBのフォルダを削除した状態で実行すること。
+ * @param args
+ * @throws IOException
+ * @throws SQLException
+ * @throws ClassNotFoundException
+ * @throws FileNotFoundException
+ * @throws javax.xml.parsers.ParserConfigurationException
+ * @throws org.xml.sax.SAXException */
+ public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException
+ {
+ // HSQLディレクトリがなければエラー
+ File dbdir = new File("database");
+ if (!dbdir.isDirectory()) {
+ throw new FileNotFoundException("Directory 'database' is not found.");
+ }
+
+ Connection conHsql = null;
+ try {
+ conHsql = DatabaseTool.openDb("database");
+ HttpPOST.sql(conHsql, "DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE");
+ //HttpPOST.sql(conHsql, "DROP INDEX "+ TABLE_NAME +"_index;");
+ create(conHsql);
+ try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) {
+ ps.executeUpdate();
+ }
+
+ // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。
+ importTest(conHsql, 14);
+
+ DbTest.export(conHsql);
+ }
+ finally {
+ if (conHsql != null) {
+ DatabaseTool.closeDb(conHsql);
+ }
+ }
+ }
+
+ /**
+ * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する
+ * @param conHsql
+ * @param areacode
+ * @throws FileNotFoundException
+ * @throws ClassNotFoundException
+ * @throws SQLException
+ * @throws IOException
+ * @throws ParserConfigurationException
+ * @throws SAXException
+ */
+ public static void importTest (Connection conHsql, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
+ int iCounter = 0;
+ iCounter += showNodes(conHsql, areacode);
+ System.out.println("("+ areacode +") Node数["+ iCounter +"]");
+ }
+
+ static final double CENTER_LAT = 35.4342443D;
+ static final double CENTER_LON = 139.4092180D;
+ static final int LOOP = 50;
+ static final double SIDE = 0.015D;
+
+ /**
+ * ノード: 綾西 (368434484) 35.4342443, 139.4092180
+ *
+ * @param con
+ * @param areacode
+ * @return
+ * @throws IOException
+ * @throws SQLException
+ */
+ public static int showNodes(Connection con, int areacode) throws IOException, SQLException {
+ int iCounter = 0;
+ double lon = CENTER_LON - SIDE;
+ for (int x = 0; x <= LOOP; x++) {
+ double lat = CENTER_LAT - SIDE;
+ for (int y = 0; y <= LOOP; y++) {
+ showGmlPoint(con, lat, lon, areacode, iCounter++);
+ lat += SIDE * 2 / LOOP;
+ }
+ lon += SIDE * 2 / LOOP;
+ }
+ return iCounter;
+ }
+
+ /**
+ *
+ * @param con
+ * @param lat
+ * @param lon
+ * @param areacode
+ * @param id
+ * @throws IOException
+ * @throws SQLException
+ */
+ public static void showGmlPoint(Connection con, double lat, double lon, int areacode, int id) throws IOException, SQLException {
+ try (PreparedStatement ps6 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (lat,lon,fixed,fixed1,area,gmlid,up) VALUES (?,?,0,0,?,?,2)")) {
+ ps6.setDouble(1, lat);
+ ps6.setDouble(2, lon);
+ ps6.setInt(3, areacode);
+ ps6.setString(4, Integer.toString(id));
+ ps6.executeUpdate();
+ }
+ }
+
+
+ /**
+ * 'table.BUS_STOP'を新規に作る
+ * 既にテーブルが存在する時には何もしない
+ * @param conHsql
+ * @throws SQLException
+ */
+ public static void create(Connection conHsql) throws SQLException {
+ String createSt;
+
+ // 'table.TEST'を新規に作る
+ createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));";
+ HttpPOST.sql(conHsql, createSt);
+
+ createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);";
+ HttpPOST.sql(conHsql, createSt);
+ }
+
+ /**
+ * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する
+ * @param con
+ */
+ public static void export(Connection con) {
+ try {
+ System.out.println("TABLE: " + TABLE_NAME);
+ System.out.println("\"gmlid\",\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\"");
+ PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,name,lat,lon,fixed,ifile FROM "+ TABLE_NAME);
+ try (ResultSet rset8 = ps8.executeQuery()) {
+ while (rset8.next()) {
+ String gmlid = rset8.getString("gmlid");
+ String name = rset8.getString("name");
+ Double lat = rset8.getDouble("lat");
+ Double lon = rset8.getDouble("lon");
+ int fixed = rset8.getInt("fixed");
+ String ifile = rset8.getString("ifile");
+ System.out.println("\""+ gmlid +"\",\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\"");
+ }
+ }
+ }
+ catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+
+}
\ No newline at end of file
diff --git a/test/osm/jp/coverage/Test.java b/test/osm/jp/coverage/Test.java
new file mode 100644
index 0000000..e9fa003
--- /dev/null
+++ b/test/osm/jp/coverage/Test.java
@@ -0,0 +1,127 @@
+package osm.jp.coverage;
+
+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.*;
+import osm.jp.api.HttpPOST;
+import osm.jp.api.Japan;
+import osm.jp.api.RectArea;
+
+public class Test {
+
+ // 近くのノードを探す範囲（KJS2を中心としたNEER×2(ｍ)四方の領域
+ static final int NEER = 1000; // 1000m x 2 = 2000m四方
+ static final double DISTANCE = 1000.0D; // 1000m 以内
+
+ 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 Test(con);
+ }
+ finally {
+ DatabaseTool.closeDb(con);
+ }
+ }
+
+ /**
+ * 個別の都道府県「GMLディレクトリ」を処理
+ *
+ * @param con
+ * @throws SQLException
+ * @throws FileNotFoundException
+ * @throws ClassNotFoundException
+ * @throws IOException
+ * @throws ParserConfigurationException
+ * @throws SAXException
+ * @throws TransformerException
+ */
+ public Test(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException {
+
+ String sqlStr1 = "SELECT lat,lon FROM "+ HttpPOST.TABLE_NAME;
+ String sqlStr2 = "SELECT gmlid,lat,lon,area FROM "+ DbTest.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)";
+ String sqlStr3 = "UPDATE "+ DbTest.TABLE_NAME +" SET fixed1=1 WHERE gmlid=? and area=?";
+ String sqlStr4 = "UPDATE "+ DbTest.TABLE_NAME +" SET fixed1=0";
+ String sqlStr5 = "UPDATE "+ DbTest.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)";
+ try ( PreparedStatement ps2 = con.prepareStatement(sqlStr2);
+ PreparedStatement ps1 = con.prepareStatement(sqlStr1);
+ PreparedStatement ps3 = con.prepareStatement(sqlStr3);
+ PreparedStatement ps4 = con.prepareStatement(sqlStr4);
+ PreparedStatement ps5 = con.prepareStatement(sqlStr5))
+ {
+ System.out.println(sqlStr4);
+ ps4.executeUpdate();
+
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ System.out.println(sqlStr1);
+ while (rset1.next()) {
+ double lat = rset1.getDouble("lat");
+ double lon = rset1.getDouble("lon");
+
+ // 指定の緯度経度を中心とする半径1000x2m四方の矩形領域
+ RectArea rect = new RectArea(lat, lon, NEER); // 1000m 四方
+ ps2.setDouble(1, rect.minlat);
+ ps2.setDouble(2, rect.maxlat);
+ ps2.setDouble(3, rect.minlon);
+ ps2.setDouble(4, rect.maxlon);
+ System.out.println(sqlStr2 +" ["+ rect.minlat +", "+ rect.maxlat +", "+ rect.minlon +", "+ rect.maxlon +"]");
+ try (ResultSet rset2 = ps2.executeQuery()) {
+ while (rset2.next()) {
+ String gmlid = rset2.getString("gmlid");
+ double lat2 = rset2.getDouble("lat");
+ double lon2 = rset2.getDouble("lon");
+ int area = rset2.getInt("area");
+ double dd = Japan.distanceKm(lat,lon,lat2,lon2);
+ System.out.println("D"+"distance = "+ dd +"");
+ if ((dd * 1000.0D) < DISTANCE) {
+ System.out.println(sqlStr3 +" ["+ gmlid +", "+ area +"]");
+ ps3.setString(1, gmlid);
+ ps3.setInt(2, area);
+ ps3.executeUpdate();
+ }
+ else {
+ System.out.println("out of distance "+ dd +" : NEER=1000");
+ }
+ }
+ }
+ }
+ }
+
+ System.out.println(sqlStr5);
+ ps5.executeUpdate();
+ }
+ }
+}
\ No newline at end of file
diff --git a/test/osm/jp/coverage/ToPostgis.java b/test/osm/jp/coverage/ToPostgis.java
new file mode 100644
index 0000000..819641a
--- /dev/null
+++ b/test/osm/jp/coverage/ToPostgis.java
@@ -0,0 +1,96 @@
+package osm.jp.coverage;
+
+import java.io.FileNotFoundException;
+import java.io.IOException;
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import javax.xml.parsers.ParserConfigurationException;
+import jp.co.areaweb.tools.database.DatabaseTool;
+import org.xml.sax.SAXException;
+import osm.jp.api.HttpPOST;
+
+/**
+ * HSQLDB.TESTの結果をPOSTGIS.t_testへ反映させる
+ * テーブル名： t_test
+ * インデックス： ix_test_geom
+ * gid PostGISの識別ID
+ * name 名称
+ * fixed OSMのバス停が周辺に存在するかどうか、存在しない場合は0,存在する場合はその数。
+ * geom PostGIS形式の位置情報(4612:)
+ * @author yuu
+ *
+ */
+public class ToPostgis {
+ public static void main(String[] argv) throws Exception {
+ Connection conHsql = null;
+ Connection conPost = null;
+ try {
+ // DB.tableを作成
+ conHsql = DatabaseTool.openDb("database");
+ conPost = DatabaseTool.openDb("postgis");
+
+ transportNew(conHsql, conPost);
+ }
+ finally {
+ if (conHsql != null) {
+ DatabaseTool.closeDb(conHsql);
+ }
+ if (conPost != null) {
+ DatabaseTool.closeDb(conPost);
+ }
+ }
+ }
+
+ /**
+ * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM BUS_STOP"
+ * POSTGIS "insert into t_busstop(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));"
+ *
+ * @param conHsql
+ * @param conPost
+ * @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 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,gmlid text,name text,fixed integer,area integer,geom GEOMETRY(POINT, 4612));");
+ HttpPOST.sql(conPost, "CREATE INDEX ix_test_geom ON t_test 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,lat,lon,fixed1,area FROM "+ DbTest.TABLE_NAME;
+ try (PreparedStatement ps1 = conHsql.prepareStatement(sql)) {
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ while (rset1.next()) {
+ String gmlid = rset1.getString("gmlid");
+ int area = rset1.getInt("area");
+ int fixed1 = rset1.getInt("fixed1");
+ double lat = rset1.getDouble("lat");
+ double lon = rset1.getDouble("lon");
+
+ int fixed = 0;
+ if (fixed1 > 0) {
+ fixed = 1;
+ }
+ String geom = "ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612)";
+ String sqlStr = "INSERT INTO t_test (gmlid,fixed,area,geom) VALUES (?,?,?,"+ geom +")";
+ System.out.println(sqlStr +" ["+ gmlid +", "+ fixed +", "+ area +"]");
+ try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
+ ps.setString(1, gmlid);
+ ps.setInt(2, fixed);
+ ps.setInt(3, area);
+ ps.executeUpdate();
+ }
+ }
+ }
+ }
+ }
+}
diff --git a/test/osm/jp/coverage/fuel/DbExistTest.java b/test/osm/jp/coverage/fuel/DbExistTest.java
index 63a4e00..e8ac020 100644
--- a/test/osm/jp/coverage/fuel/DbExistTest.java
+++ b/test/osm/jp/coverage/fuel/DbExistTest.java
@@ -1,9 +1,223 @@
package osm.jp.coverage.fuel;
+import java.io.File;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+import jp.co.areaweb.tools.database.DatabaseTool;
+import static org.hamcrest.CoreMatchers.is;
+import org.junit.*;
+import static org.junit.Assert.*;
+import static osm.jp.api.Osmdb.create;
+
/**
*
* @author yuu
*/
public class DbExistTest {
+ @Before
+ public void setUp() throws Exception {
+ File dir = new File("database");
+ if (dir.exists()) {
+ if (dir.isDirectory()) {
+ dir.deleteOnExit();
+ }
+ else {
+ throw new Exception("'database' is not directory.");
+ }
+ }
+ }
+ @After
+ public void tearDown() throws Exception {
+ }
+
+ @Test
+ public void test01_hsqldbの生成確認() {
+ Connection con = null;
+ try {
+ // DB.tableを作成
+ con = DatabaseTool.openDb("database");
+ create(con);
+ }
+ catch (ClassNotFoundException ex) {
+ fail();
+ } catch (SQLException ex) {
+ fail();
+ } catch (IOException ex) {
+ fail();
+ } finally {
+ if (con != null) {
+ DatabaseTool.closeDb(con);
+ }
+ }
+
+ File dir = new File("database");
+ assertTrue(dir.exists());
+ assertTrue(dir.isDirectory());
+
+ Connection hsqldb = null;
+ try {
+ hsqldb = DatabaseTool.openDb("database");
+
+ PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM AREA_NODE");
+ try (ResultSet rset8 = ps8.executeQuery()) {
+ if (rset8.next()) {
+ long cnt = rset8.getLong(1);
+ assertThat(Long.toString(cnt), is("0"));
+ }
+ else {
+ fail();
+ }
+ }
+
+
+ PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST");
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ if (rset1.next()) {
+ long cnt = rset1.getLong(1);
+ assertThat(Long.toString(cnt), is("0"));
+ }
+ else {
+ fail();
+ }
+ }
+
+ } catch (ClassNotFoundException ex) {
+ Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
+ } catch (SQLException ex) {
+ Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
+ } catch (IOException ex) {
+ Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
+ } finally {
+ DatabaseTool.closeDb(hsqldb);
+ }
+ }
+
+ @Test
+ public void test02_fuel() {
+ try {
+ String[] args = new String[0];
+ DbExist.main(args);
+ }
+ catch (Exception ex) {
+ fail();
+ }
+
+ File dir = new File("database");
+ assertTrue(dir.exists());
+ assertTrue(dir.isDirectory());
+
+ Connection hsqldb = null;
+ try {
+ hsqldb = DatabaseTool.openDb("database");
+
+ PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM AREA_NODE");
+ try (ResultSet rset8 = ps8.executeQuery()) {
+ if (rset8.next()) {
+ long cnt = rset8.getLong(1);
+ assertThat(Long.toString(cnt), is("0"));
+ }
+ else {
+ fail();
+ }
+ }
+
+ PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST");
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ if (rset1.next()) {
+ long cnt = rset1.getLong(1);
+ assertThat((cnt > 0), is(true));
+ }
+ else {
+ fail();
+ }
+ }
+
+ // ノード: エネオス (2015835273) 場所: 35.4367770, 139.4035710 brand=yes,name=yes
+ ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='2015835273'");
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ if (rset1.next()) {
+ assertThat(rset1.getInt("score"), is(50));
+ assertThat(checkRenge(rset1, "35.4367770", "139.4035710"), is(true));
+ }
+ else {
+ fail();
+ }
+ }
+
+ // ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no,name=yes
+ ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='1769261234'");
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ if (rset1.next()) {
+ assertThat(rset1.getInt("score"), is(1));
+ assertThat(checkRenge(rset1, "35.4330583", "139.4006876"), is(true));
+ }
+ else {
+ fail();
+ }
+ }
+
+ // ノード: 出光 (3877535257) 場所: 45.3985390, 141.6882450 (稚内) brand=no
+ ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='3877535257'");
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ if (rset1.next()) {
+ assertThat(rset1.getInt("score"), is(1));
+ assertThat(checkRenge(rset1, "45.3985390", "141.6882450"), is(true));
+ }
+ else {
+ fail();
+ }
+ }
+
+ // ノード: 605462353 場所: 24.3465566, 124.1519297 (石垣島) name=no, brand=no
+ ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='605462353'");
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ if (rset1.next()) {
+ assertThat(rset1.getInt("score"), is(1));
+ assertThat(checkRenge(rset1, "24.3465566", "124.1519297"), is(true));
+ }
+ else {
+ fail();
+ }
+ }
+
+ } catch (ClassNotFoundException ex) {
+ Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
+ } catch (SQLException ex) {
+ Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
+ } catch (IOException ex) {
+ Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
+ } finally {
+ DatabaseTool.closeDb(hsqldb);
+ }
+ }
+
+ boolean checkRenge(ResultSet rset, String latStr, String lonStr) throws SQLException {
+ if (checkRenge(rset.getDouble("lat"), latStr)) {
+ if (checkRenge(rset.getDouble("lon"), lonStr)) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ boolean checkRenge(double d1, String str) throws SQLException {
+ double base = Double.parseDouble(str);
+ double up = d1 + 0.00000005D;
+ double down = d1 - 0.00000005D;
+ boolean ret = true;
+ if (Double.compare(base, up) > 0) {
+ ret = false;
+ }
+ if (Double.compare(base, down) < 0) {
+ ret = false;
+ }
+ System.out.println("d1: "+ d1 +" : "+ str +" --> "+ (ret ? "IN" : "out"));
+ return ret;
+ }
}