package osm.jp.api;
import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
public abstract class OsmExist extends Osmdb {
public ArrayList<OsmnodeNode> nodelist = new ArrayList<>();
public OsmExist(Connection hsqldb, String tableName) {
super(hsqldb, tableName);
}
/**
* 'HSQLDB.table.OSM_EXIST'を新規に作る
* 'HSQLDB.table.AREA_NODE'を新規に作る
* 既にテーブルが存在する時にはERROR
* @throws SQLException
*/
@Override
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);
}
/*
Test data:
ノード: エネオス (2015835273) 場所: 35.4367770, 139.403571TABLE_NAME0
ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no
ノード: 出光 (3877535257) 場所: 45.3985390, 141.6882450 brand=no
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
where amenity='fuel';
ウェイ: 出光 (161877397) ノード
1738352013
1738351984
1738352019
1738352024
1738352017
1738352013
select osm_id,amenity,brand,name,way_area,
ST_Astext(ST_Transform(way,4326)) ,
ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,
ST_X(ST_Transform(ST_Centroid(way),4326)) as lon
from planet_osm_polygon
where amenity='fuel';
161877397;"fuel";"出光";"出光";1415.14;"POLYGON((139.402982078119 35.4372453832977,139.403208992559 35.4373490207424,139.4033330499 35.4371591650393,139.403407160911 35.4370741177365,139.403148446109 35.4369273706731,139.402982078119 35.4372453832977))"
ST_Centroid(way)
*/
/**
*
* <pre>{@code
* (
* node[disused:amenity=fuel](35.42,139.39,35.45,139.42);
* way[amenity=fuel](35.42,139.39,35.45,139.42);
* node[amenity=fuel](35.42,139.39,35.45,139.42);
* (way[amenity=fuel](35.42,139.39,35.45,139.42);>;);
* );
* out;
* }</pre>
*
* <pre>{@code
* select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='fuel';
* }</pre>
*
* <pre>{@code
* select osm_id,amenity,brand,name,way_area,ST_Astext(ST_Transform(way,4326)) from planet_osm_polygon where amenity='fuel';
*
* 168977587;"fuel";"JA";"兼城SS";1378;"POLYGON((127.692529751123 26.1483225993078,127.692852156479 26.1482644594179,127.692800683013 26.1478020809547,127.692690280065 26.1478324815483,127.692623984397 26.1480452048431,127.692529751123 26.1483225993078))"
*
*
* select id,nodes,tags from planet_osm_ways;
* }</pre>
*
* @param hsqldb
* @param osmdb
* @param features
* @return XML
* @throws Exception
*/
/**
* File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。<br>
* その際に、OSMノードを評価し、scoreを算定する
*
* @param features
* @param boxes
* @throws Exception
*/
public void getJapanCapabilities(ArrayList<Feature> features, Japan[] boxes) throws Exception {
/*
```
(node(changed:"2018-05-20T09:00:00Z")(34.0,138.0,36.0,140.0);)->.a;
(node(newer:"2018-05-20T09:00:00Z")(34.0,138.0,36.0,140.0);)->.b;
(way(changed:"2018-05-20T09:00:00Z")(34.0,138.0,36.0,140.0);)->.c;
(way(newer:"2018-05-20T09:00:00Z")(34.0,138.0,36.0,140.0);)->.d;
(
node.a.b["amenity"="fuel"];
node.a.b["disused:amenity"="fuel"];
node.a.b["abandoned:amenity"="fuel"];
node.a.b["demolished:amenity"="fuel"];
node.a.b["historic:amenity"="fuel"];
node.a.b["was:amenity"="fuel"];
node.a.b["removed:amenity"="fuel"];
node.a.b["no:amenity"="fuel"];
way.c.d["amenity"="fuel"];
way.c.d["disused:amenity"="fuel"];
way.c.d["abandoned:amenity"="fuel"];
way.c.d["demolished:amenity"="fuel"];
way.c.d["historic:amenity"="fuel"];
way.c.d["was:amenity"="fuel"];
way.c.d["removed:amenity"="fuel"];
way.c.d["no:amenity"="fuel"];
);
(._;>;);
out meta;
```
*/
Calendar now = Calendar.getInstance();
now.add(Calendar.DATE, -7); // before 1 week
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String startDate = sdf.format(now.getTime()) +"T09:00:00Z";
StringBuilder sb = new StringBuilder();
for (Japan box : boxes) {
String boxq = String.format("(%2.1f,%3.1f,%2.1f,%3.1f)", box.minLat, box.minLon, box.maxLat, box.maxLon);
sb.append(String.format("(node(changed:\"%s\")%s;)->.a;\n", startDate, boxq));
sb.append(String.format("(node(newer:\"%s\")%s;)->.b;\n", startDate, boxq));
sb.append(String.format("(way(changed:\"%s\")%s;)->.c;\n", startDate, boxq));
sb.append(String.format("(way(newer:\"%s\")%s;)->.d;\n", startDate, boxq));
sb.append("(");
int point = 0;
for (Feature f : features) {
if (f.node == Feature.NODE) {
sb.append("node.a.b");
}
else if (f.node == Feature.AREA) {
sb.append("way.c.d");
}
sb.append(String.format("[\"%s\"=\"%s\"];\n", f.key, f.v));
point += f.point;
}
sb.append(");\n");
sb.append("(._;>;);\n");
sb.append("out body;");
/*--------------------------------------------
Overpass API を実行して XMLを受信する
---------------------------------------------*/
HttpPOST.getQuery(sb.toString());
File xmlFile = new File(HttpPOST.EXIST_FILE);
/*--------------------------------------------
受信したXMLファイルをパースする
---------------------------------------------*/
DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
Document document = documentBuilder.parse(xmlFile);
Node osmNode = document.getDocumentElement();
Node itemNodes = osmNode.getFirstChild();
while(itemNodes != null) {
String nodeName = itemNodes.getNodeName();
switch (nodeName) {
case "node":
OsmnodeNode osmnode = new OsmnodeNode(itemNodes);
nodelist.add(osmnode);
if (osmnode.tags.size() > 0) {
insertExistingNode(osmnode.id, Double.parseDouble(osmnode.latStr), Double.parseDouble(osmnode.lonStr), score(point, osmnode.tags), "", osmnode.isRemoved());
}
break;
case "way":
OsmnodeArea osmway = new OsmnodeArea(itemNodes);
osmway.setPosition(nodelist);
if (osmway.tags.size() > 0) {
insertExistingNode(osmway.id, Double.parseDouble(osmway.latStr), Double.parseDouble(osmway.lonStr), score(point, osmway.tags), "", osmway.isRemoved());
}
break;
}
itemNodes = itemNodes.getNextSibling();
}
}
}
/**
*
* @param point
* @param tags
* @return
*/
int score(int point, ArrayList<OsmnodeTag> tags) {
int score = 50;
if (tags == null) {
return 0;
}
boolean brandYes = false;
boolean busYes = false;
boolean fixmeYes = false;
boolean nameYes = false;
for (OsmnodeTag tag : tags) {
if (tag.key.startsWith("fixme")) {
fixmeYes = true;
}
else if (tag.key.equals("bus")) {
if (tag.value.equals("yes")) {
busYes = true;
}
}
else if (tag.key.equals("brand")) {
brandYes = true;
}
else if (tag.key.startsWith("name")) {
nameYes = 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;
}
}
