package osm.jp.api;
import hayashi.yuu.tools.json.JsonTool;
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 javax.json.Json;
import javax.json.JsonArray;
import javax.json.JsonBuilderFactory;
import javax.json.JsonObject;
import javax.json.JsonObjectBuilder;
import javax.json.JsonValue;
import javax.xml.parsers.ParserConfigurationException;
import jp.co.areaweb.tools.csv.CsvRecord;
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;
import org.xml.sax.SAXException;
import osm.jp.coverage.busstop.DbBusstop;
public abstract class Osmdb {
public String TABLE_NAME = "EXIST_osm";
public String tableName = TABLE_NAME;
public Connection hsqldb = null; // hsqldb DatabaseTool.openDb("database");
public Osmdb(Connection hsqldb, String tableName) {
this.hsqldb = hsqldb;
if (tableName != null) {
this.tableName = tableName;
}
}
/**
* 'HSQLDB.table.OSM_EXIST'を新規に作る
* 'HSQLDB.table.AREA_NODE'を新規に作る
* 既にテーブルが存在する時にはERROR
* @throws SQLException
*/
public void create() throws SQLException {
String createSt;
sql("DROP TABLE IF EXISTS "+ tableName +" CASCADE");
sql("DROP INDEX IF EXISTS "+ tableName +"_index;");
sql("DROP TABLE IF EXISTS AREA_NODE CASCADE");
// 'table.OSM_EXIST'を新規に作る
createSt = "CREATE TABLE "+ tableName
+ " ("
+ "idref VARCHAR(12) NOT NULL, "
+ "name VARCHAR(128), "
+ "lat DOUBLE, "
+ "lon DOUBLE, "
+ "score INT, "
+ "gmlid VARCHAR(12), "
+ "area INT, "
+ "PRIMARY KEY(idref), "
+ "removed BOOLEAN DEFAULT FALSE NOT NULL"
+ ");";
Db.updateSQL(hsqldb, createSt);
createSt = "CREATE INDEX "+ tableName +"_index ON "+ tableName
+ " (lat, lon);";
Db.updateSQL(hsqldb, createSt);
// 'table.AREA_NODE'を新規に作る
createSt = "CREATE TABLE AREA_NODE "
+ "("
+ "idref VARCHAR(12) NOT NULL, "
+ "pid VARCHAR(12), "
+ "lat DOUBLE, "
+ "lon DOUBLE"
+ ");";
Db.updateSQL(hsqldb, createSt);
}
void sql(String sql) throws SQLException {
System.out.println(sql);
try (PreparedStatement ps = hsqldb.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; // 評価ポイント｜!(fixme=null) → score=1
public static final int POINT_NO_BRAND = 2; // 評価ポイント｜brand=null → score=1
public static final int POINT_NO_NAME = 4; // 評価ポイント｜name=null → score=1
public static final int POINT_BUS_NO = 8; // 評価ポイント｜!(bus=yes) → score=0
/**
* 'table.OSM_EXIST'の内容をCSV形式にして標準出力に出力する
* @throws java.sql.SQLException
*/
public void export() throws SQLException {
String header = "idref,lat,lon,score";
System.out.println("TABLE: "+ tableName);
System.out.println(header);
PreparedStatement ps8 = hsqldb.prepareStatement(
"SELECT idref,lat,lon,score,removed FROM "+ tableName
);
try (ResultSet rset8 = ps8.executeQuery()) {
while (rset8.next()) {
String idcode = rset8.getString(1);
Double lat = rset8.getDouble(2);
Double lon = rset8.getDouble(3);
int score = rset8.getInt(4);
boolean removed = rset8.getBoolean(5);
String out = String.format(
"OSM: %s,%2.5f,%3.5f,%d,%b",
idcode, lat, lon, score, removed
);
System.out.println(out);
}
}
}
public int importExistingNode(Connection osmdb, ResultSet rset, int point, boolean removed) 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_NO_BRAND) != 0) && isNull(brandStr)) {
score = 1;
}
if (((point & POINT_NO_NAME) != 0) && isNull(nameStr)) {
score = 1;
}
boolean fixme = false;
boolean busYes = false;
if (((point & (POINT_FIXME | POINT_BUS_NO)) != 0)) {
PreparedStatement ps8 = osmdb.prepareStatement(
"SELECT * FROM planet_osm_nodes where id='"+ osmidStr +"'"
);
try (ResultSet rset8 = ps8.executeQuery()) {
while (rset8.next()) {
String tags = rset8.getString("tags");
if (tags != null) {
tags = tags.substring(1);
tags = tags.substring(0, tags.length()-1);
CsvRecord csv = new CsvRecord();
csv.analizeRecord(tags);
boolean key = true;
boolean bus = false;
for (String str : csv) {
if (key) {
if (str.startsWith("fixme")) {
fixme = true;
}
if (str.equals("bus")) {
bus = true;
}
key = false;
}
else {
if (bus) {
if (str.equals("yes")) {
busYes = true;
}
bus = false;
}
key = true;
}
}
//System.out.println(tags);
}
}
}
if (((point & POINT_FIXME) != 0) && fixme) {
score = 1;
}
if (((point & POINT_BUS_NO) != 0) && !busYes) {
score = 0;
}
}
if (score > 0) {
// idref と brandStr をデータベースに格納する
System.out.println(
"INSERT INTO "+ tableName
+ " (idref,lat,lon,score,name,removed) VALUES ("
+ osmidStr +","
+ latStr +","
+ lonStr +","
+ Integer.toString(score) +","
+ "'"+ nameStr +"',"
+ "false"
+ ")N"
);
try (PreparedStatement ps5 = hsqldb.prepareStatement(
"INSERT INTO "+ tableName +" (idref,lat,lon,score,name,removed) 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.setBoolean(6, removed);
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;
}
/**
* File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。<br>
* その際に、OSMノードを評価し、scoreを算定する
*
* @param sqlWhere POI条件
* @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name'
* @param removed
* @throws Exception エラー
*/
public void readExisting(String sqlWhere, int point, boolean removed) throws Exception {
long counter = 0L;
try (Connection osmdb = DatabaseTool.openDb("osmdb")) {
String sqlSelect = "osm_id,"
+ "brand,"
+ "disused,"
+ "name,"
+ "ST_Y(ST_Transform(way,4326)) as lat,"
+ "ST_X(ST_Transform(way,4326)) as lon";
String sqlNode = String.format(
"SELECT %s FROM planet_osm_point %s",
sqlSelect,
sqlWhere
);
PreparedStatement ps1 = osmdb.prepareStatement(sqlNode);
try (ResultSet rset1 = ps1.executeQuery()) {
while (rset1.next()) {
counter += importExistingNode(osmdb, rset1, point, removed);
}
}
String sqlArea = String.format(
"SELECT %s FROM planet_osm_polygon %s",
"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",
sqlWhere
);
PreparedStatement ps2 = osmdb.prepareStatement(sqlArea);
try (ResultSet rset2 = ps2.executeQuery()) {
while (rset2.next()) {
counter += importExistingNode(osmdb, rset2, point, removed);
}
}
System.out.println("Exists Node count = " + counter);
}
}
/**
*
* SELECT
* planet_osm_nodes.id,
* planet_osm_nodes.tags
* planet_osm_nodes.way --> lat & lon
* FROM
* public.planet_osm_point,
* public.planet_osm_nodes
* WHERE (
* planet_osm_point.osm_id = planet_osm_nodes.id
* and 'disused:amenity'=ANY(planet_osm_nodes.tags)
* and 'fuel'=ANY(planet_osm_nodes.tags)
* )
*
* @param kStr
* @param vStr
* @param point
* @throws Exception
*/
public void readExistingSub(String kStr, String vStr, int point) throws Exception {
readExistingSub(kStr, vStr, point, true);
}
public void readExistingSub(String kStr, String vStr, int point, boolean removed) throws Exception {
JsonBuilderFactory factory = Json.createBuilderFactory(null);
JsonObjectBuilder builder = factory.createObjectBuilder();
builder.add("k",kStr);
builder.add("v", vStr);
JsonObject tag = builder.build();
readExistingSub(tag, point, removed);
}
/**
*
* @param tag {k: string, v: string}
* @param point
* @throws Exception
*/
public void readExistingSub(JsonObject tag,int point) throws Exception {
readExistingSub(tag, point, true);
}
/**
*
* @param tag {k: string, v: string}
* @param point
* @param removed
* @throws Exception
*/
public void readExistingSub(JsonObject tag,int point, boolean removed) throws Exception {
long counter = 0L;
try (Connection osmdb = DatabaseTool.openDb("osmdb")) {
String sqlSelect = "SELECT tags,id,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon";
String sqlWhere = String.format(
"WHERE (osm_id=id AND '%s'=ANY(tags) AND '%s'=ANY(tags))",
tag.getString("k"), tag.getString("v")
);
String sql = String.format(
"%s FROM planet_osm_point,planet_osm_nodes %s", sqlSelect, sqlWhere
);
System.out.println(sql);
PreparedStatement ps1 = osmdb.prepareStatement(sql);
try (ResultSet rset1 = ps1.executeQuery()) {
while (rset1.next()) {
String tags = rset1.getString("tags");
String idref = rset1.getString("id");
String latStr = rset1.getString("lat");
String lonStr = rset1.getString("lon");
double lat = Double.valueOf(latStr);
double lon = Double.valueOf(lonStr);
int score = score(point, tags);
counter += insertExistingNode(idref, lat, lon, score, "", removed);
}
}
System.out.println("Exists Node count = " + counter);
}
}
/**
*
* @param array tags: [{k: string, v: string}]
* @param point
* @throws Exception
*/
public void readExistingSub(JsonArray array,int point) throws Exception {
readExistingSub(array,point, true);
}
/**
*
* @param array tags: [{k: string, v: string}]
* @param point
* @param removed
* @throws Exception
*/
public void readExistingSub(JsonArray array,int point, boolean removed) throws Exception {
long counter = 0L;
try (Connection osmdb = DatabaseTool.openDb("osmdb")) {
String sqlSelect = "SELECT "
+ "tags,"
+ "id,"
+ "ST_Y(ST_Transform(way,4326)) as lat,"
+ "ST_X(ST_Transform(way,4326)) as lon";
String sqlSS = "";
for (JsonValue v : array) {
JsonObject tag = (JsonObject)v;
sqlSS += String.format(
" AND ('%s'=ANY(tags) AND '%s'=ANY(tags))",
tag.getString("k"), tag.getString("v")
);
}
String sqlWhere = String.format("WHERE (osm_id=id %s)", sqlSS);
String sql = String.format(
"%s FROM planet_osm_point,planet_osm_nodes %s",
sqlSelect, sqlWhere
);
System.out.println(sql);
PreparedStatement ps1 = osmdb.prepareStatement(sql);
try (ResultSet rset1 = ps1.executeQuery()) {
while (rset1.next()) {
String tags = rset1.getString("tags");
String idref = rset1.getString("id");
String latStr = rset1.getString("lat");
String lonStr = rset1.getString("lon");
double lat = Double.valueOf(latStr);
double lon = Double.valueOf(lonStr);
int score = score(point, tags);
counter += insertExistingNode(idref, lat, lon, score, "", removed);
}
}
System.out.println("Exists Node count = " + counter);
}
}
int score(int point, String tags) {
int score = 50;
if (tags == null) {
return score;
}
boolean brandYes = false;
boolean busYes = false;
boolean fixmeYes = false;
boolean nameYes = false;
tags = tags.substring(1);
tags = tags.substring(0, tags.length()-1);
CsvRecord csv = new CsvRecord();
csv.analizeRecord(tags);
boolean key = true;
boolean bus = false;
for (String str : csv) {
if (key) {
if (str.startsWith("fixme")) {
fixmeYes = true;
}
else if (str.equals("bus")) {
bus = true;
}
else if (str.equals("brand")) {
brandYes = true;
}
else if (str.startsWith("name")) {
nameYes = true;
}
key = false;
}
else {
if (bus) {
if (str.equals("yes")) {
busYes = true;
}
bus = false;
}
key = true;
}
}
if (((point & POINT_NO_BRAND) != 0) && !brandYes) {
score = 1;
}
if (((point & POINT_NO_NAME) != 0) && !nameYes) {
score = 1;
}
if (((point & POINT_FIXME) != 0) && fixmeYes) {
score = 1;
}
if (((point & POINT_BUS_NO) != 0) && !busYes) {
score = 0;
}
return score;
}
/**
*
* @param idref
* @param lat
* @param lon
* @param score
* @param name
* @return
* @throws IOException
* @throws SQLException
*/
public int insertExistingNode(String idref, double lat, double lon, int score, String name) throws IOException, SQLException {
return insertExistingNode(idref, lat, lon, score, name, false);
}
/**
*
* @param idref
* @param lat
* @param lon
* @param score
* @param name
* @param removed
* @return
* @throws IOException
* @throws SQLException
*/
public int insertExistingNode(String idref, double lat, double lon, int score, String name, boolean removed) throws IOException, SQLException {
// idref と brandStr をデータベースに格納する
/*
*/
String sql = String.format("DELETE FROM %s WHERE idref=?", tableName);
try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) {
ps5.setString(1, idref);
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]は、無視する
}
sql = String.format(
"INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (%s,%2.7f,%3.7f,%d,%s,%s)",
tableName,
idref, lat, lon, score, name, (removed ? "true":"false")
);
System.out.println(sql);
sql = String.format(
"INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (?,?,?,?,?,?)",
tableName
);
try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) {
ps5.setString(1, idref);
ps5.setDouble(2, lat);
ps5.setDouble(3, lon);
ps5.setInt(4, score);
ps5.setString(5, name);
ps5.setBoolean(6, removed);
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;
}
/**
* REMOVEDファイルからデータを揉み込む
*
* @param revovedFile
* @throws FileNotFoundException
* @throws IOException
* @throws SQLException
*/
public void loadRemoved(File revovedFile) throws FileNotFoundException, IOException, SQLException {
if (revovedFile == null) {
throw new FileNotFoundException();
}
if (!revovedFile.exists()) {
throw new FileNotFoundException();
}
if (!revovedFile.isFile()) {
throw new FileNotFoundException();
}
try (PreparedStatement ps = hsqldb.prepareStatement(
"UPDATE "
+ tableName
+ " SET idref=?,removed=true,fixed1=100"
+ " WHERE gmlid=? AND area=?"
)) {
LineNumberReader reader = new LineNumberReader(
new InputStreamReader(new FileInputStream(revovedFile))
);
String lineStr;
while ((lineStr = reader.readLine()) != null) {
if (lineStr == null) {
continue;
}
if (lineStr.trim().length() < 3) {
continue;
}
JsonObject json = JsonTool.parse(lineStr);
String gmlid = json.getString("gmlid");
String idref = json.getString("idref");
int area = json.getInt("area");
ps.setString(1, idref);
ps.setString(2, gmlid);
ps.setInt(3, area);
ps.executeUpdate();
}
}
}
/**
* removedデータをファイルに出力する
*
* @param removedFile
* @throws SQLException
* @throws java.io.IOException
*/
public void outputRemoved(File removedFile) throws SQLException, IOException {
String whereStr = "WHERE (removed=?)";
String fromStr = "FROM "+ DbBusstop.TABLE_NAME;
String sortStr = "ORDER BY area,gmlid";
String sql = String.format("SELECT * %s %s %s", fromStr, whereStr, sortStr);
try (FileWriter fw = new FileWriter(removedFile);
PreparedStatement ps1 = hsqldb.prepareStatement(sql))
{
System.out.println(sql);
ps1.setBoolean(1, true);
try (ResultSet rset1 = ps1.executeQuery()) {
while (rset1.next()) {
String gmlid = rset1.getString("gmlid");
int area = rset1.getInt("area");
String idref = rset1.getString("idref");
JsonObjectBuilder jsonBuilder = Json.createObjectBuilder();
jsonBuilder.add("gmlid", gmlid);
jsonBuilder.add("idref", (idref==null ? "" : idref));
jsonBuilder.add("area", area);
JsonObject value = jsonBuilder.build();
String line = value.toString();
fw.write(line);
fw.write("\n");
fw.flush();
}
}
fw.close();
}
}
boolean isNull(String vstr) {
if (vstr == null) {
return true;
}
return (vstr.trim().length() < 1);
}
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();
}
}
}
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;
}
public void dropTable () throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
String sqlStr = "DROP TABLE IF EXISTS "+ tableName;
try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) {
System.out.println(sqlStr);
ps.executeUpdate();
}
}
}
