diff --git a/nbproject/project.properties b/nbproject/project.properties index d18e1df..33d757b 100644 --- a/nbproject/project.properties +++ b/nbproject/project.properties @@ -39,7 +39,9 @@ javac.classpath=\ ${file.reference.hayashi_0225.jar}:\ ${file.reference.hsqldb_2.2.9.jar}:\ - ${file.reference.postgresql-9.4.1212.jar} + ${file.reference.postgresql-9.4.1212.jar}:\ + ${libs.Java-EE-GlassFish-v3-Prelude.classpath}:\ + ${libs.javaee-api-7.0.classpath} # Space-separated list of extra javac options javac.compilerargs= javac.deprecation=false diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index 59c1d0f..5392729 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -8,7 +8,6 @@ import java.sql.SQLIntegrityConstraintViolationException; import java.sql.SQLSyntaxErrorException; import jp.co.areaweb.tools.csv.CsvRecord; -import jp.co.areaweb.tools.database.DatabaseTool; import org.hsqldb.HsqlException; 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 b5e8fa6..cd14d37 100644 --- a/src/osm/jp/coverage/fuel/DbExist.java +++ b/src/osm/jp/coverage/fuel/DbExist.java @@ -4,7 +4,6 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; - import jp.co.areaweb.tools.database.*; import osm.jp.api.Osmdb; @@ -73,6 +72,8 @@ * *
{@code
* (
+ * node[disused:amenity=fuel](35.42,139.39,35.45,139.42);
+ * way[disused: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);>;);
* );
@@ -96,11 +97,11 @@
* @throws Exception
*/
public void getJapanCapabilities(Connection hsqldb) throws Exception {
-
// 通常 → 50ポイント
// BRANDなし → 1ポイント
// FIXMEあり → 1ポイント
- readExisting(hsqldb, "where amenity='fuel'", POINT_BRAND | POINT_FIXME);
+ readExisting(hsqldb, "\"where amenity='fuel'\"", POINT_BRAND | POINT_FIXME);
+ readExistingSub(hsqldb, "disused:amenity", "fuel", POINT_BRAND | POINT_FIXME);
}
/**
@@ -159,4 +160,53 @@
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");
+
+ PreparedStatement ps1 = osmdb.prepareStatement("SELECT osm_id, brand, name, disused, tags FROM planet_osm_point, planet_osm_nodes WHERE osm_id=id and ? = ANY (tags) and ? = ANY (tags)");
+ ps1.setString(1, kStr);
+ ps1.setString(2, vStr);
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ while (rset1.next()) {
+ counter += importExistingNode(hsqldb, osmdb, rset1, point);
+ }
+ }
+ PreparedStatement ps2 = osmdb.prepareStatement("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, planet_osm_nodes WHERE osm_id=id and ? = ANY (tags) and ? = ANY (tags)");
+ ps2.setString(1, kStr);
+ ps2.setString(2, vStr);
+ 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);
+ }
+ }
}
\ No newline at end of file
diff --git a/test/osm/jp/coverage/fuel/DbExistTest.java b/test/osm/jp/coverage/fuel/DbExistTest.java
index ad851f4..fac61e3 100644
--- a/test/osm/jp/coverage/fuel/DbExistTest.java
+++ b/test/osm/jp/coverage/fuel/DbExistTest.java
@@ -197,6 +197,18 @@
}
}
+ // node: "DISUSED:amenity=fuel" (302164578) https://www.openstreetmap.org/node/302164578
+ // ノード: エネオス (302164578) 場所: 35.5688084, 139.7300300
+ ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='302164578'");
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ if (rset1.next()) {
+ assertThat(rset1.getInt("score"), is(50));
+ assertThat(checkRenge(rset1, "35.5688084", "139.7300300"), is(true));
+ }
+ else {
+ fail();
+ }
+ }
} catch (ClassNotFoundException ex) {
Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {