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);