package osm.jp.coverage.fuel;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import jp.co.areaweb.tools.csv.CsvRecord;
import jp.co.areaweb.tools.database.*;
import org.hsqldb.HsqlException;
import osm.jp.api.Osmdb;
public class DbExist extends Osmdb {
/** メイン
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception
{
Connection hsqldb = null;
try {
// DB.tableを作成
hsqldb = DatabaseTool.openDb("database");
create(hsqldb);
/**
* 既存のOSMガソリンスタンドを読み込む
* OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする
*/
(new DbExist()).getJapanCapabilities(hsqldb);
DbExist.export(hsqldb);
}
finally {
if (hsqldb != null) {
DatabaseTool.closeDb(hsqldb);
}
}
}
/*
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,
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
* @throws Exception
*/
public void getJapanCapabilities(Connection hsqldb) throws Exception {
// 通常 → ５０ポイント
// BRANDなし → １ポイント
// FIXMEあり → １ポイント
readExisting(hsqldb, "where amenity='fuel'", POINT_NO_BRAND | POINT_FIXME);
readExistingSub(hsqldb, "disused:amenity", "fuel", POINT_NO);
readExistingSub(hsqldb, "abandoned:amenity", "fuel", POINT_NO);
readExistingSub(hsqldb, "demolished:amenity", "fuel", POINT_NO);
readExistingSub(hsqldb, "historic:amenity", "fuel", POINT_NO);
readExistingSub(hsqldb, "was:amenity", "fuel", POINT_NO);
readExistingSub(hsqldb, "removed:amenity", "fuel", POINT_NO);
readExistingSub(hsqldb, "no:amenity", "fuel", POINT_NO);
}
/**
* 'table.FUEL'の内容をCSV形式にして標準出力に出力する
* @param hsqldb
* @throws java.sql.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 = hsqldb.prepareStatement("SELECT idref,lat,lon,score FROM "+ TABLE_NAME);
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);
System.out.println("OSM: "+ idcode +","+ lat +","+ lon +","+ score);
}
}
}
/**
* 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");
String sqlNode = "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;
PreparedStatement ps1 = osmdb.prepareStatement(sqlNode);
try (ResultSet rset1 = ps1.executeQuery()) {
while (rset1.next()) {
counter += importExistingNode(hsqldb, osmdb, rset1, point);
}
}
String sqlArea = "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;
PreparedStatement ps2 = osmdb.prepareStatement(sqlArea);
try (ResultSet rset2 = ps2.executeQuery()) {
while (rset2.next()) {
counter += importExistingNode(hsqldb, osmdb, rset2, point);
}
}
System.out.println("Exists Node count = " + counter);
}
finally {
DatabaseTool.closeDb(osmdb);
}
}
/**
*
* SELECT
planet_osm_point.osm_id,
planet_osm_point.amenity,
planet_osm_point.brand,
planet_osm_nodes.id,
planet_osm_nodes.tags
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 hsqldb
* @param kStr
* @param vStr
* @param point
* @throws Exception
*/
public void readExistingSub(Connection hsqldb, String kStr, String vStr, int point) throws Exception {
Connection osmdb = null;
long counter = 0L;
try {
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))", kStr, vStr);
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 += importExistingNode(hsqldb, osmdb, rset1, point);
counter += insertExistingNode(hsqldb, idref, lat, lon, score, "");
}
}
System.out.println("Exists Node count = " + counter);
}
finally {
DatabaseTool.closeDb(osmdb);
}
}
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 hsqldb
* @param idref
* @param lat
* @param lon
* @param score
* @param name
* @return
* @throws IOException
* @throws SQLException
*/
public int insertExistingNode(Connection hsqldb, String idref, double lat, double lon, int score, String name) throws IOException, SQLException {
// idref と brandStr をデータベースに格納する
String sql = String.format("INSERT INTO %s (idref,lat,lon,score,name) VALUES (%s,%2.7f,%3.7f,%d,%s)",
TABLE_NAME,
idref, lat, lon, score, name);
System.out.println(sql);
sql = String.format("INSERT INTO %s (idref,lat,lon,score,name) VALUES (?,?,?,?,?)", TABLE_NAME);
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.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;
}
}
