+ * その際に、OSMノードを評価し、scoreを算定する + * @param con 反映先のデータベースコネクタ(HSQLDB) + * @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name' + * @throws FileNotFoundException File(HttpPOST.EXIST_FILE)が存在しない + * @throws ClassNotFoundException + * @throws SQLException データベースエラー + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + */ + public void readExistingFile (Connection con, int point) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + + DocumentBuilderFactory factory; + DocumentBuilder builder; + + factory = DocumentBuilderFactory.newInstance(); + builder = factory.newDocumentBuilder(); + factory.setIgnoringElementContentWhitespace(true); + factory.setIgnoringComments(true); + factory.setValidating(true); + + Node root = builder.parse(new File(Osmdb.EXIST_FILE)); + + readAreaNodes(con, root); + int iCounter = readExistingNodes(con, root, point); + System.out.println("既存ノード数["+ iCounter +"]"); + } + + public 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; + } + + public 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(); + } + } + } + + public int readExistingNodes(Connection con, Node node, int point) 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++; + importExistingNode(con, node2, point); + break; + case "way": + iCounter++; + importExistingArea(con, node2, point); + break; + default: + iCounter += readExistingNodes(con, node2, point); + break; + } + } + return iCounter; + } + + public void importExistingNode(Connection con, Node node, int point) throws IOException, SQLException { + String idrefStr = ""; + String latStr = ""; + String lonStr = ""; + String brandStr = ""; + String nameStr = ""; + String fixmeStr = ""; + + 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; + } + } + + NodeList nodes = node.getChildNodes(); + if (nodes.getLength() == 0) { + return; + } + for (int i = 0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + if (node2.getNodeName().equals("tag")) { + OsmnodeTag tagnode = new OsmnodeTag(node2); + String value = tagnode.getValue("brand"); + if (value != null) { + brandStr = value; + } + value = tagnode.getValue("name"); + if (value != null) { + nameStr = value; + } + value = tagnode.getValue("name:ja"); + if (value != null) { + nameStr = value; + } + value = tagnode.getValue("fixme"); + if (value != null) { + fixmeStr = value; + } + } + } + + int score = 50; + if (((point & POINT_BRAND) != 0) && brandStr.equals("")) { + score = 1; + } + if (((point & POINT_NAME) != 0) && brandStr.equals("")) { + score = 1; + } + if (((point & POINT_FIXME) != 0) && !fixmeStr.equals("")) { + score = 1; + } + + // idref と brandStr をデータベースに格納する + System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES ("+ idrefStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N"); + try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) { + ps5.setString(1, idrefStr); + ps5.setDouble(2, Double.parseDouble(latStr)); + ps5.setDouble(3, Double.parseDouble(lonStr)); + ps5.setInt(4, score); + ps5.setString(5, nameStr); + 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]は、無視する + } + } + } + + public void importExistingArea(Connection con, Node node, int point) throws IOException, SQLException { + String idrefStr = ""; + double maxlat = -90.0D; + double maxlon = -180.0D; + double minlat = 90.0D; + double minlon = 180.0D; + String nameStr = ""; + String brandStr = ""; + String fixmeStr = ""; + + 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; + default: + break; + } + } + + NodeList nodes = node.getChildNodes(); + for (int i = 0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + if (node2.getNodeName().equals("tag")) { + OsmnodeTag tagnode = new OsmnodeTag(node2); + String value = tagnode.getValue("brand"); + if (value != null) { + brandStr = value; + } + value = tagnode.getValue("name:ja"); + if (value != null) { + nameStr = value; + } + value = tagnode.getValue("name"); + if (value != null) { + nameStr = value; + } + value = tagnode.getValue("fixme"); + if (value != null) { + fixmeStr = value; + } + } + else if (node2.getNodeName().equals("nd")) { + OsmnodeNd ndnode = new OsmnodeNd(node2); + String ref = ndnode.getRef(); + Position pos = getNdPosition(con, ref); + if (pos != null) { + minlat = (pos.lat < minlat ? pos.lat : minlat); + minlon = (pos.lon < minlon ? pos.lon : minlon); + maxlat = (pos.lat > maxlat ? pos.lat : maxlat); + maxlon = (pos.lon > maxlon ? pos.lon : maxlon); + } + } + } + if ((maxlat != -90.0D) && (maxlon != -180.0D) && (minlon != 180.0D) && (minlat != 90.0D)) { + double lat = (maxlat + minlat) / 2; + double lon = (maxlon + minlon) / 2; + int score = 50; + + if (((point & POINT_BRAND) != 0) && brandStr.equals("")) { + score = 1; + } + if (((point & POINT_NAME) != 0) && nameStr.equals("")) { + score = 1; + } + if (((point & POINT_FIXME) != 0) && !fixmeStr.equals("")) { + score = 1; + } + + // idref と nameStr をデータベースに格納する + System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES("+ idrefStr +","+ lat +","+ lon+","+ Integer.toString(score) +",'"+ nameStr +"');"); + try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) { + ps5.setString(1, idrefStr); + ps5.setDouble(2, lat); + ps5.setDouble(3, lon); + ps5.setInt(4, score); + ps5.setString(5, nameStr); + 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]は、無視する + } + } + } + } + + public 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; + } +} diff --git a/src/osm/jp/api/OsmnodeNd.java b/src/osm/jp/api/OsmnodeNd.java index 97e5153..d439c6c 100644 --- a/src/osm/jp/api/OsmnodeNd.java +++ b/src/osm/jp/api/OsmnodeNd.java @@ -5,7 +5,6 @@ */ package osm.jp.api; -import java.sql.Connection; import org.w3c.dom.NamedNodeMap; import org.w3c.dom.Node; diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java index 5ca88e6..dbb0431 100644 --- a/src/osm/jp/coverage/fuel/DbExist.java +++ b/src/osm/jp/coverage/fuel/DbExist.java @@ -11,10 +11,10 @@ import jp.co.areaweb.tools.database.*; import org.xml.sax.SAXException; -import osm.jp.api.HttpPOST; import osm.jp.api.Japan; +import osm.jp.api.Osmdb; -public class DbExist extends HttpPOST { +public class DbExist extends Osmdb { /** メイン * @param args @@ -30,7 +30,7 @@ try { // DB.tableを作成 con = DatabaseTool.openDb("database"); - HttpPOST.create(con); + create(con); /** * 既存のOSMガソリンスタンドを読み込む @@ -48,6 +48,24 @@ } } + /* + Test data: + ノード: エネオス (2015835273) 場所: 35.4367770, 139.4035710 + ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no + select osm_id,amenity,brand,disused,name 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)) 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))" + + */ /** * *
{@code
@@ -56,25 +74,21 @@
* (way[amenity=fuel](35.42,139.39,35.45,139.42);>;);
* );
* out;
+ * }
*
- * {@code
+ * select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='fuel';
* }
*
+ * {@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;
+ * }
+ *
* @param con
* @throws MalformedURLException
* @throws ProtocolException
@@ -97,7 +111,7 @@
// 通常 → ５０ポイント
// BRANDなし → １ポイント
// FIXMEあり → １ポイント
- readExistingFile(con, HttpPOST.POINT_BRAND | HttpPOST.POINT_FIXME);
+ readExistingFile(con, POINT_BRAND | POINT_FIXME);
}
}
@@ -108,9 +122,9 @@
*/
public static void export(Connection con) throws SQLException {
String header = "idref,lat,lon,score";
- System.out.println("TABLE: "+ HttpPOST.TABLE_NAME);
+ System.out.println("TABLE: "+ TABLE_NAME);
System.out.println(header);
- PreparedStatement ps8 = con.prepareStatement("SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME);
+ PreparedStatement ps8 = con.prepareStatement("SELECT idref,lat,lon,score FROM "+ TABLE_NAME);
try (ResultSet rset8 = ps8.executeQuery()) {
while (rset8.next()) {
String idcode = rset8.getString(1);
diff --git a/src/osmdb.class.violet.html b/src/osmdb.class.violet.html
new file mode 100644
index 0000000..51b8238
--- /dev/null
+++ b/src/osmdb.class.violet.html
@@ -0,0 +1,1504 @@
+
+
+
+
+
+
+
+
+ This file was generated with Violet UML Editor 2.1.0.
+ ( View Source / Download Violet )
+ +
+ +
+
+
+
+
\ No newline at end of file
diff --git a/test/osm/jp/coverage/fuel/DbExistTest.java b/test/osm/jp/coverage/fuel/DbExistTest.java
new file mode 100644
index 0000000..63a4e00
--- /dev/null
+++ b/test/osm/jp/coverage/fuel/DbExistTest.java
@@ -0,0 +1,9 @@
+package osm.jp.coverage.fuel;
+
+/**
+ *
+ * @author yuu
+ */
+public class DbExistTest {
+
+}