diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java
index 1b5137e..014d515 100644
--- a/src/osm/jp/coverage/fuel/DbExist.java
+++ b/src/osm/jp/coverage/fuel/DbExist.java
@@ -1,11 +1,14 @@
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 {
@@ -194,38 +197,18 @@
try {
osmdb = DatabaseTool.openDb("osmdb");
- PreparedStatement ps1 = osmdb.prepareStatement("SELECT osm_id,brand,name,disused,tags,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon FROM planet_osm_point,planet_osm_nodes WHERE osm_id=id AND '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)");
+ PreparedStatement ps1 = osmdb.prepareStatement("SELECT id,tags,lat,lon FROM planet_osm_nodes WHERE '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)");
try (ResultSet rset1 = ps1.executeQuery()) {
while (rset1.next()) {
String tags = rset1.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 = false;
- for (String str : csv) {
- if (key) {
- if (str.equals(vStr)) {
- counter += importExistingNode(hsqldb, osmdb, rset1, point);
- break;
- }
- key = false;
- }
- else {
- if (str.equals(kStr)) {
- key = true;
- }
- }
- }
- }
- }
- }
- PreparedStatement ps2 = osmdb.prepareStatement("SELECT osm_id,brand,disused,name,tags,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon FROM planet_osm_polygon,planet_osm_nodes WHERE osm_id=id AND '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)");
- try (ResultSet rset2 = ps2.executeQuery()) {
- while (rset2.next()) {
- counter += importExistingNode(hsqldb, osmdb, rset2, point);
+ 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);
@@ -234,4 +217,102 @@
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;
+ }
}
\ No newline at end of file
diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java
index 93501ee..ef3e7f0 100644
--- a/src/osm/jp/coverage/fuel/Fuel.java
+++ b/src/osm/jp/coverage/fuel/Fuel.java
@@ -97,6 +97,10 @@
String idref = null;
int area = 0;
+ if (rset1.getString("idref").equals("5338111023")) {
+ int dummy = 0;
+ }
+
// 指定の緯度経度を中心とする半径100x2m四方の矩形領域
RectArea rect = new RectArea(lat, lon, NEER); // 400m 四方
ps2.setDouble(1, rect.minlat);
@@ -117,6 +121,7 @@
}
}
if (idref != null) {
+ System.out.println(sql3);
System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1="+ score +" WHERE idref="+ idref +" and area=" + area);
ps3.setInt(1, score);
ps3.setString(2, idref);