diff --git a/nbproject/build-impl.xml b/nbproject/build-impl.xml index d30546b..1e28d55 100644 --- a/nbproject/build-impl.xml +++ b/nbproject/build-impl.xml @@ -903,14 +903,6 @@ - - - - - - - - @@ -1396,14 +1388,6 @@ - - - - - - - - diff --git a/nbproject/genfiles.properties b/nbproject/genfiles.properties index e74ced1..6869014 100644 --- a/nbproject/genfiles.properties +++ b/nbproject/genfiles.properties @@ -1,8 +1,8 @@ -nbbuild.xml.data.CRC32=7469a291 +nbbuild.xml.data.CRC32=354430f8 nbbuild.xml.script.CRC32=e7ea1557 nbbuild.xml.stylesheet.CRC32=8064a381@1.80.1.48 # This file is used by a NetBeans-based IDE to track changes in generated files such as build-impl.xml. # Do not edit this file. You may delete it but then the IDE will never regenerate such files for you. -nbproject/build-impl.xml.data.CRC32=7469a291 -nbproject/build-impl.xml.script.CRC32=9170c4d3 +nbproject/build-impl.xml.data.CRC32=354430f8 +nbproject/build-impl.xml.script.CRC32=b6dace46 nbproject/build-impl.xml.stylesheet.CRC32=830a3534@1.80.1.48 diff --git a/nbproject/project.properties b/nbproject/project.properties index 4bd79d2..f621fe8 100644 --- a/nbproject/project.properties +++ b/nbproject/project.properties @@ -31,6 +31,7 @@ dist.javadoc.dir=${dist.dir}/javadoc endorsed.classpath= excludes= +file.reference.hayashi_0225.jar=lib/hayashi_0225.jar file.reference.hsqldb_2.2.9.jar=lib\\hsqldb_2.2.9.jar file.reference.javax.json.jar=lib/javax.json.jar file.reference.osmCoverage-src=src @@ -42,8 +43,8 @@ javac.classpath=\ ${file.reference.hsqldb_2.2.9.jar}:\ ${file.reference.postgresql-9.4.1212.jar}:\ - ${reference.hayashi.jar}:\ - ${file.reference.javax.json.jar} + ${file.reference.javax.json.jar}:\ + ${file.reference.hayashi_0225.jar} # Space-separated list of extra javac options javac.compilerargs= javac.deprecation=false @@ -92,8 +93,6 @@ meta.inf.dir=${src.dir}/META-INF mkdist.disabled=false platform.active=default_platform -project.hayashi=../hayashi -reference.hayashi.jar=${project.hayashi}/dist/hayashi.jar run.classpath=\ ${javac.classpath}:\ ${build.classes.dir} diff --git a/nbproject/project.xml b/nbproject/project.xml index 24c2e39..935a364 100644 --- a/nbproject/project.xml +++ b/nbproject/project.xml @@ -11,15 +11,6 @@ - - - hayashi - jar - - jar - clean - jar - - + diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index 731c765..a592e2c 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -7,6 +7,12 @@ import java.sql.SQLException; import java.sql.SQLIntegrityConstraintViolationException; import java.sql.SQLSyntaxErrorException; +import javax.json.Json; +import javax.json.JsonArray; +import javax.json.JsonBuilderFactory; +import javax.json.JsonObject; +import javax.json.JsonObjectBuilder; +import javax.json.JsonValue; import jp.co.areaweb.tools.csv.CsvRecord; import jp.co.areaweb.tools.database.DatabaseTool; import org.hsqldb.HsqlException; @@ -250,13 +256,28 @@ * @throws Exception */ public void readExistingSub(String kStr, String vStr, int point) throws Exception { + JsonBuilderFactory factory = Json.createBuilderFactory(null); + JsonObjectBuilder builder = factory.createObjectBuilder(); + builder.add("k",kStr); + builder.add("v", vStr); + JsonObject tag = builder.build(); + readExistingSub(tag, point); + } + + /** + * + * @param tag {k: string, v: string} + * @param point + * @throws Exception + */ + public void readExistingSub(JsonObject tag,int point) throws Exception { boolean removed = true; Connection osmdb = null; long counter = 0L; try { osmdb = DatabaseTool.openDb("osmdb"); String sqlSelect = "SELECT tags,id,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon"; - String sqlWhere = String.format("WHERE (osm_id=id AND '%s'=ANY(tags) AND '%s'=ANY(tags))", kStr, vStr); + String sqlWhere = String.format("WHERE (osm_id=id AND '%s'=ANY(tags) AND '%s'=ANY(tags))", tag.getString("k"), tag.getString("v")); String sql = String.format("%s FROM planet_osm_point,planet_osm_nodes %s", sqlSelect, sqlWhere); System.out.println(sql); @@ -279,7 +300,49 @@ DatabaseTool.closeDb(osmdb); } } - + + /** + * + * @param array tags: [{k: string, v: string}] + * @param point + * @throws Exception + */ + public void readExistingSub(JsonArray array,int point) throws Exception { + boolean removed = true; + Connection osmdb = null; + long counter = 0L; + try { + osmdb = DatabaseTool.openDb("osmdb"); + String sqlSelect = "SELECT tags,id,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon"; + String sqlSS = ""; + for (JsonValue v : array) { + JsonObject tag = (JsonObject)v; + sqlSS += String.format(" AND ('%s'=ANY(tags) AND '%s'=ANY(tags))", tag.getString("k"), tag.getString("v")); + } + String sqlWhere = String.format("WHERE (osm_id=id %s)", sqlSS); + String sql = String.format("%s FROM planet_osm_point,planet_osm_nodes %s", sqlSelect, sqlWhere); + System.out.println(sql); + + PreparedStatement ps1 = osmdb.prepareStatement(sql); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + String tags = rset1.getString("tags"); + 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 += insertExistingNode(idref, lat, lon, score, "", removed); + } + } + System.out.println("Exists Node count = " + counter); + } + finally { + DatabaseTool.closeDb(osmdb); + } + } + int score(int point, String tags) { int score = 50; if (tags == null) { diff --git a/src/osm/jp/coverage/busstop/ToPostgis.java b/src/osm/jp/coverage/busstop/ToPostgis.java index 84218ed..069b472 100644 --- a/src/osm/jp/coverage/busstop/ToPostgis.java +++ b/src/osm/jp/coverage/busstop/ToPostgis.java @@ -62,7 +62,15 @@ public static void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { HttpPOST httpPOST = new HttpPOST(conPost, null); httpPOST.sql("DROP TABLE IF EXISTS t_busstop CASCADE;"); - httpPOST.sql("CREATE TABLE t_busstop (gid SERIAL PRIMARY KEY, gmlid text, idref text, name text, fixed integer, area integer, geom GEOMETRY(POINT, 4612));"); + httpPOST.sql("CREATE TABLE t_busstop (" + + "gid SERIAL PRIMARY KEY, " + + "gmlid text, " + + "idref text, " + + "name text, " + + "fixed integer, " + + "area integer, " + + "geom GEOMETRY(POINT, 4612)" + + ");"); httpPOST.sql("CREATE INDEX ix_busstop_geom ON t_busstop USING GiST (geom);"); toInsert(conHsql, conPost); } diff --git a/src/osm/jp/coverage/police/DbPolice.java b/src/osm/jp/coverage/police/DbPolice.java index 72a5c4d..eeacf7d 100644 --- a/src/osm/jp/coverage/police/DbPolice.java +++ b/src/osm/jp/coverage/police/DbPolice.java @@ -1,6 +1,5 @@ package osm.jp.coverage.police; -import hayashi.tools.files.DeleteDir; import java.io.*; import java.sql.Connection; import java.sql.PreparedStatement; diff --git a/src/osm/jp/coverage/postoffice/DbPostoffice.java b/src/osm/jp/coverage/postoffice/DbPostoffice.java index 0e518f0..b37e1dc 100644 --- a/src/osm/jp/coverage/postoffice/DbPostoffice.java +++ b/src/osm/jp/coverage/postoffice/DbPostoffice.java @@ -1,6 +1,5 @@ package osm.jp.coverage.postoffice; -import hayashi.tools.files.DeleteDir; import java.io.*; import java.sql.Connection; import java.sql.PreparedStatement; diff --git a/src/osm/jp/postgis/ToGeoJSON.java b/src/osm/jp/postgis/ToGeoJSON.java index 81f6117..90f50d6 100644 --- a/src/osm/jp/postgis/ToGeoJSON.java +++ b/src/osm/jp/postgis/ToGeoJSON.java @@ -85,7 +85,7 @@ else { line = ","; System.out.println(line); - ow.write(line); + ow.write(line); ow.newLine(); } line = rset8.getString(1); diff --git a/test/osm/jp/coverage/busstop/DbExistBusstopTest.java b/test/osm/jp/coverage/busstop/DbExistBusstopTest.java index f14658d..30132ff 100644 --- a/test/osm/jp/coverage/busstop/DbExistBusstopTest.java +++ b/test/osm/jp/coverage/busstop/DbExistBusstopTest.java @@ -8,10 +8,19 @@ import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; +import javax.json.Json; +import javax.json.JsonArray; +import javax.json.JsonArrayBuilder; +import javax.json.JsonBuilderFactory; +import javax.json.JsonObject; +import javax.json.JsonObjectBuilder; import jp.co.areaweb.tools.database.DatabaseTool; import static org.hamcrest.CoreMatchers.is; import org.junit.*; import static org.junit.Assert.*; +import static osm.jp.api.Osmdb.POINT_BUS_NO; +import static osm.jp.api.Osmdb.POINT_FIXME; +import static osm.jp.api.Osmdb.POINT_NO_NAME; /** * @@ -193,6 +202,38 @@ } } + @Test + public void test03DbExistBusstopTest_busstop() { + Connection hsqldb = null; + try { + hsqldb = DatabaseTool.openDb("database"); + DbExistBusstop osmExist = new DbExistBusstop(hsqldb); + JsonBuilderFactory factory = Json.createBuilderFactory(null); + JsonArrayBuilder abuilder = factory.createArrayBuilder(); + + JsonObjectBuilder builder = factory.createObjectBuilder(); + builder.add("k","public_transport"); + builder.add("v", "platform"); + JsonObject tag = builder.build(); + abuilder.add(tag); + + builder = factory.createObjectBuilder(); + builder.add("k","bus"); + builder.add("v", "yes"); + tag = builder.build(); + abuilder.add(tag); + + osmExist.readExistingSub(abuilder.build(), POINT_BUS_NO | POINT_NO_NAME | POINT_FIXME); + } catch (Exception ex) { + fail(ex.toString()); + } finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + + } + boolean checkRenge(ResultSet rset, String latStr, String lonStr) throws SQLException { if (checkRenge(rset.getDouble("lat"), latStr)) { if (checkRenge(rset.getDouble("lon"), lonStr)) {