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