package osm.jp.api;
import java.net.*;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.hsqldb.HsqlException;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import osm.jp.coverage.busstop.DbBusstop;
import static osm.jp.coverage.busstop.DbExistBusstop.DROP;
/**
* Java HTTP クライアントサンプル - HttpURLConnection 版 -
*
* @author 68user http://X68000.q-e-d.net/~68user/
*/
public class HttpPOST {
//public static String host = "http://api06.dev.openstreetmap.org";
//public static String host = "http://api.openstreetmap.org";
public static String host = "http://overpass-api.de";
public static final String EXIST_FILE = "exist.osm.xml";
public static final String TABLE_NAME = "OSM_EXIST";
public static void main(String[] args) throws MalformedURLException, ProtocolException, IOException {
double minlat = 35.13d;
double maxlat = 35.66d;
double minlon = 138.99d;
double maxlon = 139.79d;
//getCapabilities(new File("output.xml"), "highway", "bus_stop", minlat, maxlat, minlon, maxlon);
//getCapabilities(new File("output.xml"), "highway", "disused:bus_stop", minlat, maxlat, minlon, maxlon);
//getCapabilities(new File("output.xml"), "amenity", "bus_station", minlat, maxlat, minlon, maxlon);
//getCapabilities(new File("output.xml"), "public_transport", "platform", minlat, maxlat, minlon, maxlon);
getCapabilities("public_transport", "stop_position", minlat, maxlat, minlon, maxlon, "node");
getCapabilities("amenity", "fuel", minlat, maxlat, minlon, maxlon, "way");
}
/**
* 'HSQLDB.table.OSM_EXIST'を新規に作る
* 'HSQLDB.table.AREA_NODE'を新規に作る
* 既にテーブルが存在する時にはERROR
* @param con
* @throws SQLException
*/
public static void create(Connection con) throws SQLException {
create(con, false);
}
public static void create(Connection con, boolean withdrop) throws SQLException {
String createSt;
if (withdrop) {
DbBusstop.drop(con, HttpPOST.TABLE_NAME);
DbBusstop.drop(con, "AREA_NODE");
}
// 'table.FUEL_EXIST'を新規に作る
createSt = "CREATE TABLE "+ HttpPOST.TABLE_NAME +" (idref VARCHAR(12) NOT NULL, lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));";
Db.updateSQL(con, createSt);
createSt = "CREATE INDEX "+ HttpPOST.TABLE_NAME +"_index ON "+ HttpPOST.TABLE_NAME +" (lat, lon);";
Db.updateSQL(con, createSt);
// 'table.AREA_NODE'を新規に作る
createSt = "CREATE TABLE AREA_NODE (idref VARCHAR(12) NOT NULL, pid VARCHAR(12), lat DOUBLE, lon DOUBLE);";
Db.updateSQL(con, createSt);
}
public static void getCapabilities(String key, String value, double minLat, double maxLat, double minLon, double maxLon) throws MalformedURLException, ProtocolException, IOException {
getCapabilities(key, value, minLat, maxLat, minLon, maxLon, "node");
}
public static void getCapabilities(String key, String value, double minLat, double maxLat, double minLon, double maxLon, String type) throws MalformedURLException, ProtocolException, IOException {
StringBuilder queryText = new StringBuilder();
queryText.append("<osm-script timeout=\"900\" element-limit=\"1073741824\">");
queryText.append(" <union>");
queryText.append(" <query type=\""+ type +"\">");
queryText.append(" <has-kv k=\""+ key +"\" v=\""+ value +"\"/>");
queryText.append(" <bbox-query s=\"" + minLat + "\" n=\"" + maxLat + "\" w=\"" + minLon + "\" e=\"" + maxLon + "\"/>");
queryText.append(" </query>");
queryText.append(" </union>");
queryText.append(" <print/>");
queryText.append("</osm-script>");
getQuery(queryText.toString());
}
/**
*
* @param queryText クエリテキスト（Overpass_API/Overpass_QL）
* @throws MalformedURLException
* @throws ProtocolException
* @throws IOException
*/
public static void getQuery(String queryText) throws MalformedURLException, ProtocolException, IOException {
System.out.println(host + "/api/interpreter");
URL url = new URL(host + "/api/interpreter");
int responsecode = 0;
do {
HttpURLConnection urlconn = (HttpURLConnection)url.openConnection();
try {
urlconn.setRequestMethod("POST");
urlconn.setDoOutput(true); // POSTのデータを後ろに付ける
urlconn.setInstanceFollowRedirects(false); // 勝手にリダイレクトさせない
urlconn.setRequestProperty("Accept-Language", "ja;q=0.7,en;q=0.3");
urlconn.setRequestProperty("Content-Type","text/xml;charset=utf-8");
urlconn.connect();
try (PrintWriter pw = new PrintWriter(new BufferedWriter(new OutputStreamWriter(urlconn.getOutputStream(), "utf-8")))) {
outputWriter(pw, queryText);
pw.flush();
}
try {
TimeUnit.SECONDS.sleep(1);
} catch (InterruptedException e) {}
responsecode = urlconn.getResponseCode();
System.out.println("レスポンスコード[" + responsecode + "] " +
"レスポンスメッセージ[" + urlconn.getResponseMessage() + "]");
Map<String,List<String>> headers = urlconn.getHeaderFields();
for (Map.Entry<String, List<String>> bar : headers.entrySet()) {
System.out.print("\t" + bar.getKey() +"\t: "); // キーを取得
List<String> 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]
try {
TimeUnit.MINUTES.sleep(5);
} catch (InterruptedException e) {}
}
else {
System.out.println("\n---- ボディ ----");
File oFile = new File(HttpPOST.EXIST_FILE);
oFile.deleteOnExit();
try (BufferedWriter hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(oFile), "UTF-8"))) {
try (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();
}
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);
}
public static void readExistingFile (Connection con) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
int iCounter = 0;
DocumentBuilderFactory factory;
DocumentBuilder builder;
iCounter = 0;
factory = DocumentBuilderFactory.newInstance();
builder = factory.newDocumentBuilder();
factory.setIgnoringElementContentWhitespace(true);
factory.setIgnoringComments(true);
factory.setValidating(true);
Node root = builder.parse(new File(HttpPOST.EXIST_FILE));
readAreaNodes(con, root);
iCounter += readExistingNodes(con, root);
System.out.println("既存ノード数["+ iCounter +"]");
}
static int readAreaNodes(Connection con, Node node) 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++;
importAreaNode(con, node2);
break;
default:
iCounter += readAreaNodes(con, node2);
break;
}
}
return iCounter;
}
static void importAreaNode(Connection con, Node node) throws IOException, SQLException {
String idrefStr = "";
String latStr = "";
String lonStr = "";
NodeList nodes = node.getChildNodes();
if (nodes.getLength() > 0) {
return;
}
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;
}
}
// idref と brandStr をデータベースに格納する
System.out.println("insert into AREA_NODE(idref,lat,lon) values("+ idrefStr +","+ latStr +","+ lonStr+")");
try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO AREA_NODE (idref,lat,lon) VALUES (?,?,?)")) {
ps5.setString(1, idrefStr);
ps5.setDouble(2, Double.parseDouble(latStr));
ps5.setDouble(3, Double.parseDouble(lonStr));
ps5.executeUpdate();
}
}
}
static int readExistingNodes(Connection con, Node node) 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);
break;
case "way":
iCounter++;
importExistingArea(con, node2);
break;
default:
iCounter += readExistingNodes(con, node2);
break;
}
}
return iCounter;
}
static void importExistingArea(Connection con, Node node) throws IOException, SQLException {
String idrefStr = "";
double maxlat = -90.0D;
double maxlon = -180.0D;
double minlat = 90.0D;
double minlon = 180.0D;
String brandStr = "";
String fixmeStr = "";
int score = 0;
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;
default:
break;
}
}
NodeList nodes = node.getChildNodes();
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 = null;
value = tagnode.getValue("brand");
if (value != null) {
brandStr = value;
}
value = tagnode.getValue("fixme");
if (value != null) {
fixmeStr = value;
}
}
else if (node2.getNodeName().equals("nd")) {
OsmnodeNd ndnode = new OsmnodeNd(node2);
String ref = ndnode.getRef();
Position pos = getNdPosition(con, ref);
if (pos != null) {
minlat = (pos.lat < minlat ? pos.lat : minlat);
minlon = (pos.lon < minlon ? pos.lon : minlon);
maxlat = (pos.lat > maxlat ? pos.lat : maxlat);
maxlon = (pos.lon > maxlon ? pos.lon : maxlon);
}
}
}
if ((maxlat != -90.0D) && (maxlon != -180.0D) && (minlon != 180.0D) && (minlat != 90.0D)) {
double lat = (maxlat + minlat) / 2;
double lon = (maxlon + minlon) / 2;
score = 50;
if (brandStr.equals("")) {
score = 1;
}
if (!fixmeStr.equals("")) {
score = 1;
}
// idref と nameStr をデータベースに格納する
System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score) VALUES("+ idrefStr +","+ lat +","+ lon+","+ Integer.toString(score) +");");
try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon, score) VALUES (?,?,?,?)")) {
ps5.setString(1, idrefStr);
ps5.setDouble(2, lat);
ps5.setDouble(3, lon);
ps5.setInt(4, score);
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]は、無視する
}
}
}
}
static void importExistingNode(Connection con, Node node) throws IOException, SQLException {
String idrefStr = "";
String latStr = "";
String lonStr = "";
String brandStr = "";
String fixmeStr = "";
int score = 0;
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 = null;
value = tagnode.getValue("brand");
if (value != null) {
brandStr = value;
}
value = tagnode.getValue("fixme");
if (value != null) {
fixmeStr = value;
}
}
}
score = 50;
if (brandStr.equals("")) {
score = 1;
}
if (!fixmeStr.equals("")) {
score = 1;
}
// idref と brandStr をデータベースに格納する
System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon, score) VALUES ("+ idrefStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +")N");
try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon, score) VALUES (?,?,?,?)")) {
ps5.setString(1, idrefStr);
ps5.setDouble(2, Double.parseDouble(latStr));
ps5.setDouble(3, Double.parseDouble(lonStr));
ps5.setInt(4, score);
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 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()) {
while (rset8.next()) {
Double lat = rset8.getDouble(1);
Double lon = rset8.getDouble(2);
return new Position(lat,lon);
}
}
return null;
}
}
