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