package osm.jp.api;
import java.net.*;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.SQLSyntaxErrorException;
import jp.co.areaweb.tools.database.DatabaseTool;
import org.hsqldb.HsqlException;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
public abstract class Osmdb {
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 {
String createSt;
sql(con, "DROP TABLE IF EXISTS "+ Osmdb.TABLE_NAME +" CASCADE");
//sql(con, "DROP INDEX "+ HttpPOST.TABLE_NAME +"_index;");
sql(con, "DROP TABLE IF EXISTS AREA_NODE CASCADE");
// 'table.FUEL_EXIST'を新規に作る
createSt = "CREATE TABLE "+ Osmdb.TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));";
Db.updateSQL(con, createSt);
createSt = "CREATE INDEX "+ Osmdb.TABLE_NAME +"_index ON "+ Osmdb.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);
}
static void sql(Connection con, String sql) throws SQLException {
System.out.println(sql);
try (PreparedStatement ps = con.prepareStatement(sql)) {
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;
}
}
}
public static void outputWriter(PrintWriter pw, String text) {
System.out.println("\t" + text);
pw.print(text);
}
public static final int POINT_NO = 0; // 評価ポイント無し→ score=50
public static final int POINT_FIXME = 1; // 評価ポイント無し→ score=50
public static final int POINT_BRAND = 2; // 評価ポイント｜brand=null → score=1
public static final int POINT_NAME = 4; // 評価ポイント｜name=null → score=1
/**
* File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。<br>
* その際に、OSMノードを評価し、scoreを算定する
*
* @param hsqldb 反映先のデータベースコネクタ(HSQLDB)
* @param sqlWhere POI条件
* @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name'
* @throws Exception エラー
*/
public void readExisting(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,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);
}
}
PreparedStatement ps2 = osmdb.prepareStatement("select osm_id,brand,disused,name,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon from planet_osm_polygon " + sqlWhere);
try (ResultSet rset2 = ps2.executeQuery()) {
while (rset2.next()) {
counter += importExistingNode(hsqldb, rset2, point);
}
}
System.out.println("Exists Node count = " + counter);
}
finally {
DatabaseTool.closeDb(osmdb);
}
//readAreaNodes(hsqldb, root);
}
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 = "";
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;
}
// 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;
}
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();
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;
}
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();
}
}
}
void importExistingArea(Connection con, Node node, int point) throws IOException, SQLException {
String idrefStr = "";
double maxlat = -90.0D;
double maxlon = -180.0D;
double minlat = 90.0D;
double minlon = 180.0D;
String nameStr = "";
String brandStr = "";
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;
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 = tagnode.getValue("brand");
if (value != null) {
brandStr = value;
}
value = tagnode.getValue("name:ja");
if (value != null) {
nameStr = value;
}
value = tagnode.getValue("name");
if (value != null) {
nameStr = 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;
int score = 50;
if (((point & POINT_BRAND) != 0) && brandStr.equals("")) {
score = 1;
}
if (((point & POINT_NAME) != 0) && nameStr.equals("")) {
score = 1;
}
if (((point & POINT_FIXME) != 0) && !fixmeStr.equals("")) {
score = 1;
}
// idref と nameStr をデータベースに格納する
System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES("+ idrefStr +","+ lat +","+ lon+","+ Integer.toString(score) +",'"+ nameStr +"');");
try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) {
ps5.setString(1, idrefStr);
ps5.setDouble(2, lat);
ps5.setDouble(3, lon);
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]は、無視する
}
}
}
}
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;
}
}
