diff --git a/src/fuel.activity.violet.html b/src/fuel.activity.violet.html index 93fe72a..33842ff 100644 --- a/src/fuel.activity.violet.html +++ b/src/fuel.activity.violet.html @@ -20,7 +20,7 @@ - + 1 @@ -39,7 +39,7 @@ - + 1 @@ -114,7 +114,7 @@ - + 1 @@ -123,7 +123,7 @@ - + 1 @@ -137,14 +137,14 @@ - + 1 - HSQL.DB.OSM_EXIST + HSQL.DB.FUEL_EXIST 255 @@ -153,453 +153,489 @@ 255 - + - - - 1 - - - - - Create & Insert - - - - - - + + 1 + + + + + 1 + + + + + 国土数値情報：燃料給油所 + + + - - + + 1 - 国土数値情報：燃料給油所 - - - - - - - - 1 - - - - - OpenStreatMap + OpenStreatMap(PBF) node: amenity=fuel - + - - - - + + + + 1 - - - - + + + + 1 - - - - + + + + 1 - + Fuel.main() FUEL_EXISTのscoreを集計して FUELのfixedをUPDATE - - - - + + + + 1 - + update (fixed) - - - - + + + + 1 - - - - + + + + 1 - + POSTGIS.t_FUEL - + 255 228 181 255 - - - - + + + + 1 - - - - + + + + 1 - - - - + + + + 1 - + ToPostgis.main() HSQLDB.FUELの結果を POSTGIS.FUELへ反映させる - - - - + + + + 1 - + Insert or Update - - - - + + + + 1 - - - - + + + + 1 - + 255 255 255 255 - + 0 0 0 255 - - + + DbFuel.main(-update) PostGIS.t_FUELを読み取って HSQLDB.FUELテーブルに格納する - - - - + + + + 1 - - - + + + - - - - + + + + 1 - - - + + + - - - - + + + + 1 - + 255 255 255 255 - + 0 0 0 255 - - + + Create & insert - - - - + + + + 1 - + 255 255 255 255 - + 0 0 0 255 - + - - - - + + + + 1 - - - - + + + + ToCartoCSV.main() - - - - + + + + 1 - - - + + + - - - - + + + + 1 - - - - + + + + t_fuel.csv - + 255 228 181 255 - - - - + + + + 1 - - - + + + - - - - + + + + 1 - + 255 255 255 255 - + 0 0 0 255 - - + + Coverage (JSON) Coverage (CSV) - - - - + + + + 1 - + 255 255 255 255 - + 0 0 0 255 - + - - - - + + + + 1 - - - + + + - - - - + + + + 1 - - - - + + + + Coverage.json Coverage.csv - + 255 228 181 255 - - - - + + + + 1 - + 255 255 255 255 - + 0 0 0 255 - + - - - - + + + + 1 - + 255 255 255 255 - + 0 0 0 255 - - + + ToGeoJSON.main() - - - - + + + + 1 - - - - + + + + // GeoJSON fuel0.json fuel1.json fuel2.json - + 255 228 181 255 + + + + + 1 + + 255 + 255 + 255 + 255 + + + 0 + 0 + 0 + 255 + + + + OsmExist.main() +OverpassAPIからGSのノードを読み取って +HSQLDB.OSM_EXISTテーブルに格納する + + + + + + + 1 + + 255 + 255 + 255 + 255 + + + 0 + 0 + 0 + 255 + + + + OSM + OverpassAPI + + + - - + + - - - - + + + + 1 @@ -608,13 +644,13 @@ - + - - - - + + + + 1 @@ -623,13 +659,13 @@ - + - - - - + + + + 1 @@ -638,13 +674,13 @@ - + - - - - + + + + 1 @@ -653,43 +689,13 @@ - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - + - - - - + + + + 1 @@ -698,13 +704,13 @@ - + - - - - + + + + 1 @@ -713,22 +719,22 @@ - - + + - - - - + + + + 1 - - - - - - - + + + + + + + 1 @@ -737,13 +743,13 @@ - - - - - - - + + + + + + + 1 @@ -752,22 +758,22 @@ - + - - - - - + + + + + 1 - - - - - - - + + + + + + + 1 @@ -776,22 +782,22 @@ - + - - - - - + + + + + 1 - - - - - - - + + + + + + + 1 @@ -800,13 +806,13 @@ - - - - - - - + + + + + + + 1 @@ -815,13 +821,13 @@ - - - - - - - + + + + + + + 1 @@ -830,13 +836,13 @@ - + - - - - + + + + 1 @@ -845,28 +851,13 @@ - - - - - - - - 1 - - - - - - - - - - - - - - + + + + + + + 1 @@ -875,30 +866,30 @@ + + + + + + + + + + 1 + + + + + + + - - - - - - - - - 1 - - - - - - - - - - - - - - + + + + + + 1 @@ -907,22 +898,22 @@ - - - - - - - + + + + + + + 1 - - - - - - - + + + + + + + 1 @@ -931,13 +922,13 @@ - - + + - - - - + + + + 1 @@ -946,15 +937,15 @@ - - - - - - - + + + + + + + - + 1 @@ -963,13 +954,13 @@ - - + + - - - - + + + + 1 @@ -978,13 +969,13 @@ - - - - - - - + + + + + + + 1 @@ -993,13 +984,13 @@ - - - - - - - + + + + + + + 1 @@ -1008,13 +999,13 @@ - - - - - - - + + + + + + + 1 @@ -1023,22 +1014,22 @@ - - - - - - - + + + + + + + 1 - - - - - - - + + + + + + + 1 @@ -1047,13 +1038,13 @@ - - - - - - - + + + + + + + 1 @@ -1062,13 +1053,13 @@ - - - - - - - + + + + + + + 1 @@ -1077,31 +1068,31 @@ - - - - - - - + + + + + + + 1 - - + + - - - - + + + + 1 - - - - - - - + + + + + + + 1 @@ -1110,22 +1101,22 @@ - - - - - - - + + + + + + + 1 - - - - - - - + + + + + + + 1 @@ -1134,1186 +1125,1426 @@ + + + + + + + + 1 + + + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + + + ]]>

- embedded diagram image + embedded diagram image \ No newline at end of file diff --git a/src/osm/jp/api/OsmExist.java b/src/osm/jp/api/OsmExist.java index c5ed8c7..bda1ceb 100644 --- a/src/osm/jp/api/OsmExist.java +++ b/src/osm/jp/api/OsmExist.java @@ -13,20 +13,10 @@ public abstract class OsmExist extends Osmdb { public ArrayList nodelist = new ArrayList<>(); - /* - public static void main(String[] args) throws MalformedURLException, ProtocolException, IOException { - double minlat = 35.13d; - double maxlat = 35.66d; - double minlon = 138.99d; - double maxlon = 139.79d; - //getCapabilities(new File("output.xml"), "highway", "bus_stop", minlat, maxlat, minlon, maxlon); - //getCapabilities(new File("output.xml"), "highway", "disused:bus_stop", minlat, maxlat, minlon, maxlon); - //getCapabilities(new File("output.xml"), "amenity", "bus_station", minlat, maxlat, minlon, maxlon); - //getCapabilities(new File("output.xml"), "public_transport", "platform", minlat, maxlat, minlon, maxlon); - getCapabilities("public_transport", "stop_position", minlat, maxlat, minlon, maxlon, "node"); - getCapabilities("amenity", "fuel", minlat, maxlat, minlon, maxlon, "way"); + + public OsmExist(Connection hsqldb, String tableName) { + super(hsqldb, tableName); } - */ /* Test data: @@ -94,12 +84,11 @@ * File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。
* その際に、OSMノードを評価し、scoreを算定する * - * @param hsqldb * @param features * @param boxes * @throws Exception */ - public void getJapanCapabilities(Connection hsqldb, ArrayList features, Japan[] boxes) throws Exception { + public void getJapanCapabilities(ArrayList features, Japan[] boxes) throws Exception { /* ``` @@ -178,14 +167,14 @@ OsmnodeNode osmnode = new OsmnodeNode(itemNodes); nodelist.add(osmnode); if (osmnode.tags.size() > 0) { - insertExistingNode(hsqldb, osmnode.id, Double.parseDouble(osmnode.latStr), Double.parseDouble(osmnode.lonStr), score(point, osmnode.tags), ""); + insertExistingNode(osmnode.id, Double.parseDouble(osmnode.latStr), Double.parseDouble(osmnode.lonStr), score(point, osmnode.tags), ""); } break; case "way": OsmnodeArea osmway = new OsmnodeArea(itemNodes); osmway.setPosition(nodelist); if (osmway.tags.size() > 0) { - insertExistingNode(hsqldb, osmway.id, Double.parseDouble(osmway.latStr), Double.parseDouble(osmway.lonStr), score(point, osmway.tags), ""); + insertExistingNode(osmway.id, Double.parseDouble(osmway.latStr), Double.parseDouble(osmway.lonStr), score(point, osmway.tags), ""); } break; } diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index 26c65a2..7626644 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -15,51 +15,43 @@ import org.w3c.dom.NodeList; public abstract class Osmdb { - public static final String TABLE_NAME = "OSM_EXIST"; + public String TABLE_NAME = "OSM_EXIST"; + Connection hsqldb = null; // hsqldb DatabaseTool.openDb("database"); - /* - public static void main(String[] args) throws MalformedURLException, ProtocolException, IOException { - double minlat = 35.13d; - double maxlat = 35.66d; - double minlon = 138.99d; - double maxlon = 139.79d; - //getCapabilities(new File("output.xml"), "highway", "bus_stop", minlat, maxlat, minlon, maxlon); - //getCapabilities(new File("output.xml"), "highway", "disused:bus_stop", minlat, maxlat, minlon, maxlon); - //getCapabilities(new File("output.xml"), "amenity", "bus_station", minlat, maxlat, minlon, maxlon); - //getCapabilities(new File("output.xml"), "public_transport", "platform", minlat, maxlat, minlon, maxlon); - getCapabilities("public_transport", "stop_position", minlat, maxlat, minlon, maxlon, "node"); - getCapabilities("amenity", "fuel", minlat, maxlat, minlon, maxlon, "way"); + public Osmdb(Connection hsqldb, String tableName) { + this.hsqldb = hsqldb; + if (tableName != null) { + this.TABLE_NAME = tableName; + } } - */ /** * 'HSQLDB.table.OSM_EXIST'を新規に作る * 'HSQLDB.table.AREA_NODE'を新規に作る * 既にテーブルが存在する時にはERROR - * @param con * @throws SQLException */ - public static void create(Connection con) throws SQLException { + public void create() throws SQLException { String createSt; - sql(con, "DROP TABLE IF EXISTS "+ Osmdb.TABLE_NAME +" CASCADE"); - sql(con, "DROP INDEX IF EXISTS "+ Osmdb.TABLE_NAME +"_index;"); - sql(con, "DROP TABLE IF EXISTS AREA_NODE CASCADE"); + sql("DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); + sql("DROP INDEX IF EXISTS "+ TABLE_NAME +"_index;"); + sql("DROP TABLE IF EXISTS AREA_NODE CASCADE"); // 'table.OSM_EXIST'を新規に作る - createSt = "CREATE TABLE "+ Osmdb.TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));"; - Db.updateSQL(con, createSt); - createSt = "CREATE INDEX "+ Osmdb.TABLE_NAME +"_index ON "+ Osmdb.TABLE_NAME +" (lat, lon);"; - Db.updateSQL(con, createSt); + createSt = "CREATE TABLE "+ TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));"; + Db.updateSQL(hsqldb, createSt); + createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat, lon);"; + Db.updateSQL(hsqldb, createSt); // 'table.AREA_NODE'を新規に作る createSt = "CREATE TABLE AREA_NODE (idref VARCHAR(12) NOT NULL, pid VARCHAR(12), lat DOUBLE, lon DOUBLE);"; - Db.updateSQL(con, createSt); + Db.updateSQL(hsqldb, createSt); } - static void sql(Connection con, String sql) throws SQLException { + void sql(String sql) throws SQLException { System.out.println(sql); - try (PreparedStatement ps = con.prepareStatement(sql)) { + try (PreparedStatement ps = hsqldb.prepareStatement(sql)) { ps.executeUpdate(); } catch (SQLSyntaxErrorException e) { @@ -84,10 +76,9 @@ /** * 'table.OSM_EXIST'の内容をCSV形式にして標準出力に出力する - * @param hsqldb * @throws java.sql.SQLException */ - public static void export(Connection hsqldb) throws SQLException { + public void export() throws SQLException { String header = "idref,lat,lon,score"; System.out.println("TABLE: "+ TABLE_NAME); System.out.println(header); @@ -104,7 +95,7 @@ } - public int importExistingNode(Connection hsqldb, Connection osmdb, ResultSet rset, int point) throws IOException, SQLException { + public int importExistingNode(Connection osmdb, ResultSet rset, int point) throws IOException, SQLException { String osmidStr = rset.getString("osm_id"); String latStr = rset.getString("lat"); @@ -197,12 +188,11 @@ * File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。
* その際に、OSMノードを評価し、scoreを算定する * - * @param hsqldb 反映先のデータベースコネクタ(HSQLDB) * @param sqlWhere POI条件 * @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name' * @throws Exception エラー */ - public void readExisting(Connection hsqldb, String sqlWhere, int point) throws Exception { + public void readExisting(String sqlWhere, int point) throws Exception { Connection osmdb = null; long counter = 0L; try { @@ -215,7 +205,7 @@ PreparedStatement ps1 = osmdb.prepareStatement(sqlNode); try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { - counter += importExistingNode(hsqldb, osmdb, rset1, point); + counter += importExistingNode(osmdb, rset1, point); } } @@ -226,7 +216,7 @@ PreparedStatement ps2 = osmdb.prepareStatement(sqlArea); try (ResultSet rset2 = ps2.executeQuery()) { while (rset2.next()) { - counter += importExistingNode(hsqldb, osmdb, rset2, point); + counter += importExistingNode(osmdb, rset2, point); } } @@ -252,13 +242,12 @@ * 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 { + public void readExistingSub(String kStr, String vStr, int point) throws Exception { Connection osmdb = null; long counter = 0L; try { @@ -279,7 +268,7 @@ double lon = Double.valueOf(lonStr); int score = score(point, tags); //counter += importExistingNode(hsqldb, osmdb, rset1, point); - counter += insertExistingNode(hsqldb, idref, lat, lon, score, ""); + counter += insertExistingNode(idref, lat, lon, score, ""); } } System.out.println("Exists Node count = " + counter); @@ -352,7 +341,6 @@ /** * - * @param hsqldb * @param idref * @param lat * @param lon @@ -362,7 +350,7 @@ * @throws IOException * @throws SQLException */ - public int insertExistingNode(Connection hsqldb, String idref, double lat, double lon, int score, String name) throws IOException, SQLException { + public int insertExistingNode(String idref, double lat, double lon, int score, String name) throws IOException, SQLException { // idref と brandStr をデータベースに格納する String sql = String.format("INSERT INTO %s (idref,lat,lon,score,name) VALUES (%s,%2.7f,%3.7f,%d,%s)", TABLE_NAME, @@ -463,102 +451,6 @@ } } } - - - - /* - - 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_NO_BRAND) != 0) && brandStr.equals("")) { - score = 1; - } - if (((point & POINT_NO_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]は、無視する - } - } - } - } - */ static Position getNdPosition(Connection con, String idref) throws SQLException { PreparedStatement ps8 = con.prepareStatement("SELECT lat,lon FROM AREA_NODE where idref=?"); diff --git a/src/osm/jp/coverage/busstop/DbExistBusstop.java b/src/osm/jp/coverage/busstop/DbExistBusstop.java index ca10a48..9cb09ca 100644 --- a/src/osm/jp/coverage/busstop/DbExistBusstop.java +++ b/src/osm/jp/coverage/busstop/DbExistBusstop.java @@ -16,6 +16,7 @@ import osm.jp.api.Osmdb; public class DbExistBusstop extends Osmdb { + public static final String EXIST_TABLE_NAME = "BUSSTOP_EXIST"; /** * 既存のOSMバス停を読み込む @@ -38,24 +39,29 @@ * @throws ParserConfigurationException */ public static void main(String[] args) throws Exception { - Connection conHsql = null; + Connection hsqldb = null; try { - conHsql = DatabaseTool.openDb("database"); - create(conHsql); + hsqldb = DatabaseTool.openDb("database"); /** * 既存のOSMバス停を読み込む * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、 * 「HSQLDB.BUSSTOP_EXIST」にSTOREする */ - (new DbExistBusstop()).getJapanCapabilities(conHsql); + DbExistBusstop osmExist = new DbExistBusstop(hsqldb); + osmExist.create(); + osmExist.getJapanCapabilities(); } finally { - if (conHsql != null) { - DatabaseTool.closeDb(conHsql); + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); } } } + + public DbExistBusstop(Connection hsqldb) { + super(hsqldb, DbExistBusstop.EXIST_TABLE_NAME); + } /** * @@ -73,7 +79,6 @@ * * } * - * @param conHsql * @throws MalformedURLException * @throws ProtocolException * @throws IOException @@ -82,21 +87,21 @@ * @throws ParserConfigurationException * @throws SAXException */ - public void getJapanCapabilities(Connection conHsql) throws Exception { + public void getJapanCapabilities() throws Exception { // 通常 → ５０ポイント // NAMEなし → １ポイント // FIXMEあり → １ポイント - readExisting(conHsql, null, POINT_NO_NAME | POINT_FIXME); - readExistingSub(conHsql, "disused:highway", "bus_stop", POINT_NO); - readExistingSub(conHsql, "abandoned:highway", "bus_stop", POINT_NO); - readExistingSub(conHsql, "removed:highway", "bus_stop", POINT_NO); - readExistingSub(conHsql, "no:highway", "bus_stop", POINT_NO); + readExisting(null, POINT_NO_NAME | POINT_FIXME); + readExistingSub("disused:highway", "bus_stop", POINT_NO); + readExistingSub("abandoned:highway", "bus_stop", POINT_NO); + readExistingSub("removed:highway", "bus_stop", POINT_NO); + readExistingSub("no:highway", "bus_stop", POINT_NO); - readExistingSub(conHsql, "public_transport", "platform", POINT_BUS_NO | POINT_NO_NAME | POINT_FIXME); - readExistingSub(conHsql, "disused:public_transport", "platform", POINT_BUS_NO); - readExistingSub(conHsql, "abandoned:public_transport", "platform", POINT_BUS_NO); - readExistingSub(conHsql, "removed:public_transport", "platform", POINT_BUS_NO); - readExistingSub(conHsql, "no:public_transport", "platform", POINT_BUS_NO); + readExistingSub("public_transport", "platform", POINT_BUS_NO | POINT_NO_NAME | POINT_FIXME); + readExistingSub("disused:public_transport", "platform", POINT_BUS_NO); + readExistingSub("abandoned:public_transport", "platform", POINT_BUS_NO); + readExistingSub("removed:public_transport", "platform", POINT_BUS_NO); + readExistingSub("no:public_transport", "platform", POINT_BUS_NO); } // ノード: 厚木ナイロン (1995040609) 場所: 35.4433312, 139.3932098 public_transport=stop_position,bus=yes @@ -108,13 +113,12 @@ * File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。
* その際に、OSMノードを評価し、scoreを算定する * - * @param hsqldb 反映先のデータベースコネクタ(HSQLDB) * @param sqlWhere POI条件 * @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name' * @throws Exception エラー */ @Override - public void readExisting(Connection hsqldb, String sqlWhere, int point) throws Exception { + public void readExisting(String sqlWhere, int point) throws Exception { Connection osmdb = null; long counter = 0L; try { @@ -128,7 +132,7 @@ PreparedStatement ps0 = osmdb.prepareStatement(sql + ",ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + whereText0.toString()); try (ResultSet rset1 = ps0.executeQuery()) { while (rset1.next()) { - counter += importExistingNode(hsqldb, osmdb, rset1, 0); + counter += importExistingNode(osmdb, rset1, 0); } } @@ -139,7 +143,7 @@ PreparedStatement ps1 = osmdb.prepareStatement(sql + ",ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + whereText1.toString()); try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { - counter += importExistingNode(hsqldb, osmdb, rset1, point); + counter += importExistingNode(osmdb, rset1, point); } } @@ -150,7 +154,7 @@ PreparedStatement ps = osmdb.prepareStatement(sql + ",ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + whereText.toString()); try (ResultSet rset1 = ps.executeQuery()) { while (rset1.next()) { - counter += importExistingNode(hsqldb, osmdb, rset1, point | POINT_BUS_NO); + counter += importExistingNode(osmdb, rset1, point | POINT_BUS_NO); } } @@ -160,7 +164,7 @@ PreparedStatement ps2 = osmdb.prepareStatement(sql +",ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon from planet_osm_polygon " + whereText2.toString()); try (ResultSet rset2 = ps2.executeQuery()) { while (rset2.next()) { - counter += importExistingNode(hsqldb, osmdb, rset2, point); + counter += importExistingNode(osmdb, rset2, point); } } @@ -169,8 +173,5 @@ finally { DatabaseTool.closeDb(osmdb); } - - //readAreaNodes(hsqldb, root); - } } \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java deleted file mode 100644 index 5d4a40c..0000000 --- a/src/osm/jp/coverage/fuel/DbExist.java +++ /dev/null @@ -1,108 +0,0 @@ -package osm.jp.coverage.fuel; - -import java.sql.Connection; -import jp.co.areaweb.tools.database.*; -import osm.jp.api.Osmdb; - -public class DbExist extends Osmdb { - - /** メイン - * @param args - * @throws Exception - */ - public static void main(String[] args) throws Exception - { - Connection hsqldb = null; - try { - // DB.tableを作成 - hsqldb = DatabaseTool.openDb("database"); - create(hsqldb); - - /** - * 既存のOSMガソリンスタンドを読み込む - * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする - */ - (new DbExist()).getJapanCapabilities(hsqldb); - - DbExist.export(hsqldb); - } - finally { - if (hsqldb != null) { - DatabaseTool.closeDb(hsqldb); - } - } - } - - /* - Test data: - ノード: エネオス (2015835273) 場所: 35.4367770, 139.403571TABLE_NAME0 - ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no - ノード: 出光 (3877535257) 場所: 45.3985390, 141.6882450 brand=no - - select osm_id,amenity,brand,disused,name, - ST_Y(ST_Transform(way,4326)) as lat, - ST_X(ST_Transform(way,4326)) as lon - 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)) , - ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat, - ST_X(ST_Transform(ST_Centroid(way),4326)) as lon - 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))" - ST_Centroid(way) - - */ - /** - * - * 
{@code 
-     *  (
-     *    node[disused:amenity=fuel](35.42,139.39,35.45,139.42);
-     *    way[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);>;);
-     *  );
-     *  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 hsqldb - * @throws Exception - */ - public void getJapanCapabilities(Connection hsqldb) throws Exception { - // 通常 → ５０ポイント - // BRANDなし → １ポイント - // FIXMEあり → １ポイント - readExisting(hsqldb, "where amenity='fuel'", POINT_NO_BRAND | POINT_FIXME); - readExistingSub(hsqldb, "disused:amenity", "fuel", POINT_NO); - readExistingSub(hsqldb, "abandoned:amenity", "fuel", POINT_NO); - readExistingSub(hsqldb, "demolished:amenity", "fuel", POINT_NO); - readExistingSub(hsqldb, "historic:amenity", "fuel", POINT_NO); - readExistingSub(hsqldb, "was:amenity", "fuel", POINT_NO); - readExistingSub(hsqldb, "removed:amenity", "fuel", POINT_NO); - readExistingSub(hsqldb, "no:amenity", "fuel", POINT_NO); - } -} \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/DbExistFuel.java b/src/osm/jp/coverage/fuel/DbExistFuel.java new file mode 100644 index 0000000..4e99242 --- /dev/null +++ b/src/osm/jp/coverage/fuel/DbExistFuel.java @@ -0,0 +1,113 @@ +package osm.jp.coverage.fuel; + +import java.sql.Connection; +import jp.co.areaweb.tools.database.*; +import osm.jp.api.Osmdb; + +public class DbExistFuel extends Osmdb { + public static final String FUEL_EXIST = "FUEL_EXIST"; + + /** メイン + * @param args + * @throws Exception + */ + public static void main(String[] args) throws Exception + { + Connection hsqldb = null; + try { + // DB.tableを作成 + hsqldb = DatabaseTool.openDb("database"); + DbExistFuel dbExist = new DbExistFuel(hsqldb); + dbExist.create(); + + /** + * 既存のOSMガソリンスタンドを読み込む + * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする + */ + dbExist.getJapanCapabilities(); + + dbExist.export(); + } + finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + } + + public DbExistFuel(Connection hsqldb) { + super(hsqldb, FUEL_EXIST); + } + + /* + Test data: + ノード: エネオス (2015835273) 場所: 35.4367770, 139.403571TABLE_NAME0 + ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no + ノード: 出光 (3877535257) 場所: 45.3985390, 141.6882450 brand=no + + select osm_id,amenity,brand,disused,name, + ST_Y(ST_Transform(way,4326)) as lat, + ST_X(ST_Transform(way,4326)) as lon + 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)) , + ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat, + ST_X(ST_Transform(ST_Centroid(way),4326)) as lon + 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))" + ST_Centroid(way) + + */ + /** + * + * 
{@code 
+     *  (
+     *    node[disused:amenity=fuel](35.42,139.39,35.45,139.42);
+     *    way[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);>;);
+     *  );
+     *  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;
+     * }
+ * + * @throws Exception + */ + public void getJapanCapabilities() throws Exception { + // 通常 → ５０ポイント + // BRANDなし → １ポイント + // FIXMEあり → １ポイント + readExisting("where amenity='fuel'", POINT_NO_BRAND | POINT_FIXME); + readExistingSub("disused:amenity", "fuel", POINT_NO); + readExistingSub("abandoned:amenity", "fuel", POINT_NO); + readExistingSub("demolished:amenity", "fuel", POINT_NO); + readExistingSub("historic:amenity", "fuel", POINT_NO); + readExistingSub("was:amenity", "fuel", POINT_NO); + readExistingSub("removed:amenity", "fuel", POINT_NO); + readExistingSub("no:amenity", "fuel", POINT_NO); + } +} \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index 36b8077..89f3f94 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -49,7 +49,6 @@ } Connection con = DatabaseTool.openDb("database"); - try { new Fuel(con); } diff --git a/src/osm/jp/coverage/fuel/OsmExist.java b/src/osm/jp/coverage/fuel/OsmExist.java deleted file mode 100644 index f80bcb5..0000000 --- a/src/osm/jp/coverage/fuel/OsmExist.java +++ /dev/null @@ -1,55 +0,0 @@ -package osm.jp.coverage.fuel; - -import java.sql.Connection; -import java.util.ArrayList; -import jp.co.areaweb.tools.database.*; -import osm.jp.api.Feature; -import osm.jp.api.Japan; - -public class OsmExist extends osm.jp.api.OsmExist { - - /** メイン - * @param args - * @throws Exception - */ - public static void main(String[] args) throws Exception - { - Connection hsqldb = null; - try { - // DB.tableを作成 - hsqldb = DatabaseTool.openDb("database"); - - ArrayList features = new ArrayList<>(); - features.add(new Feature(Feature.NODE, "amenity", "fuel", POINT_NO_BRAND | POINT_FIXME)); - features.add(new Feature(Feature.NODE, "disused:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.NODE, "abandoned:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.NODE, "demolished:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.NODE, "historic:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.NODE, "was:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.NODE, "removed:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.NODE, "no:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.AREA, "amenity", "fuel", POINT_NO_BRAND | POINT_FIXME)); - features.add(new Feature(Feature.AREA, "disused:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.AREA, "abandoned:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.AREA, "demolished:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.AREA, "historic:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.AREA, "was:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.AREA, "removed:amenity", "fuel", POINT_NO)); - features.add(new Feature(Feature.AREA, "no:amenity", "fuel", POINT_NO)); - - /** - * 既存のOSMガソリンスタンドを読み込む - * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする - */ - (new OsmExist()).getJapanCapabilities(hsqldb, features, Japan.all2); - - OsmExist.export(hsqldb); - } - finally { - if (hsqldb != null) { - DatabaseTool.closeDb(hsqldb); - } - } - } - -} \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/OsmExistFuel.java b/src/osm/jp/coverage/fuel/OsmExistFuel.java new file mode 100644 index 0000000..c6688f1 --- /dev/null +++ b/src/osm/jp/coverage/fuel/OsmExistFuel.java @@ -0,0 +1,59 @@ +package osm.jp.coverage.fuel; + +import java.sql.Connection; +import java.util.ArrayList; +import jp.co.areaweb.tools.database.*; +import osm.jp.api.Feature; +import osm.jp.api.Japan; + +public class OsmExistFuel extends osm.jp.api.OsmExist { + + /** メイン + * @param args + * @throws Exception + */ + public static void main(String[] args) throws Exception + { + Connection hsqldb = null; + try { + // DB.tableを作成 + hsqldb = DatabaseTool.openDb("database"); + + ArrayList features = new ArrayList<>(); + features.add(new Feature(Feature.NODE, "amenity", "fuel", POINT_NO_BRAND | POINT_FIXME)); + features.add(new Feature(Feature.NODE, "disused:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "abandoned:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "demolished:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "historic:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "was:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "removed:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.NODE, "no:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "amenity", "fuel", POINT_NO_BRAND | POINT_FIXME)); + features.add(new Feature(Feature.AREA, "disused:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "abandoned:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "demolished:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "historic:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "was:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "removed:amenity", "fuel", POINT_NO)); + features.add(new Feature(Feature.AREA, "no:amenity", "fuel", POINT_NO)); + + /** + * 既存のOSMガソリンスタンドを読み込む + * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする + */ + OsmExistFuel osmExist = new OsmExistFuel(hsqldb); + osmExist.getJapanCapabilities(features, Japan.all2); + osmExist.export(); + } + finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + } + + public OsmExistFuel(Connection hsqldb) { + super(hsqldb, DbExistFuel.FUEL_EXIST); + } + +} \ No newline at end of file diff --git a/src/osm/jp/coverage/police/DbExist.java b/src/osm/jp/coverage/police/DbExist.java deleted file mode 100644 index cbff489..0000000 --- a/src/osm/jp/coverage/police/DbExist.java +++ /dev/null @@ -1,93 +0,0 @@ -package osm.jp.coverage.police; - -import java.sql.Connection; -import jp.co.areaweb.tools.database.*; -import osm.jp.api.Osmdb; - -public class DbExist extends Osmdb { - - /** メイン - * @param args - * @throws Exception - */ - public static void main(String[] args) throws Exception - { - Connection hsqldb = null; - try { - // DB.tableを作成 - hsqldb = DatabaseTool.openDb("database"); - create(hsqldb); - - /** - * 既存のOSM:amenity=policeを読み込む - * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.OSM_EXIST」にSTOREする - */ - (new DbExist()).getJapanCapabilities(hsqldb); - - DbExist.export(hsqldb); - } - finally { - if (hsqldb != null) { - DatabaseTool.closeDb(hsqldb); - } - } - } - - /* - Test data: - - select osm_id,amenity,brand,disused,name, - ST_Y(ST_Transform(way,4326)) as lat, - ST_X(ST_Transform(way,4326)) as lon - from planet_osm_point - where amenity='police'; - - select osm_id,amenity,brand,name,way_area, - ST_Astext(ST_Transform(way,4326)) , - ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat, - ST_X(ST_Transform(ST_Centroid(way),4326)) as lon - from planet_osm_polygon - where amenity='police'; - - ST_Centroid(way) - - */ - /** - * - * 
{@code 
-     *  (
-     *    node[disused:amenity=police](35.42,139.39,35.45,139.42);
-     *    way[disused:amenity=police](35.42,139.39,35.45,139.42);
-     *    node[amenity=police](35.42,139.39,35.45,139.42);
-     *    (way[amenity=police](35.42,139.39,35.45,139.42);>;);
-     *  );
-     *  out;
-     * }
- * - * 
{@code 
-     *  select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='police';
-     * }
- * - * 
{@code 
-     *  select osm_id,amenity,brand,name,way_area,ST_Astext(ST_Transform(way,4326)) from planet_osm_polygon where amenity='police';
-     * 
-     *  select id,nodes,tags from planet_osm_ways;
-     * }
- * - * @param hsqldb - * @throws Exception - */ - public void getJapanCapabilities(Connection hsqldb) throws Exception { - // 通常 → ５０ポイント - // FIXMEあり → １ポイント - readExisting(hsqldb, "where amenity='police'", POINT_FIXME); - readExistingSub(hsqldb, "disused:amenity", "police", POINT_NO); - readExistingSub(hsqldb, "abandoned:amenity", "police", POINT_NO); - readExistingSub(hsqldb, "demolished:amenity", "police", POINT_NO); - readExistingSub(hsqldb, "historic:amenity", "police", POINT_NO); - readExistingSub(hsqldb, "was:amenity", "police", POINT_NO); - readExistingSub(hsqldb, "removed:amenity", "police", POINT_NO); - readExistingSub(hsqldb, "no:amenity", "police", POINT_NO); - } - -} \ No newline at end of file diff --git a/src/osm/jp/coverage/police/DbExistPolice.java b/src/osm/jp/coverage/police/DbExistPolice.java new file mode 100644 index 0000000..3af05c4 --- /dev/null +++ b/src/osm/jp/coverage/police/DbExistPolice.java @@ -0,0 +1,97 @@ +package osm.jp.coverage.police; + +import java.sql.Connection; +import jp.co.areaweb.tools.database.*; +import osm.jp.api.Osmdb; + +public class DbExistPolice extends Osmdb { + public static final String EXIST_TABLE_NAME = "POLICE_EXIST"; + + /** メイン + * @param args + * @throws Exception + */ + public static void main(String[] args) throws Exception + { + Connection hsqldb = null; + try { + // DB.tableを作成 + hsqldb = DatabaseTool.openDb("database"); + + /** + * 既存のOSM:amenity=policeを読み込む + * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.OSM_EXIST」にSTOREする + */ + DbExistPolice osmExist = new DbExistPolice(hsqldb); + osmExist.create(); + osmExist.getJapanCapabilities(); + osmExist.export(); + } + finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + } + + public DbExistPolice(Connection hsqldb) { + super(hsqldb, EXIST_TABLE_NAME); + } + + /* + Test data: + + select osm_id,amenity,brand,disused,name, + ST_Y(ST_Transform(way,4326)) as lat, + ST_X(ST_Transform(way,4326)) as lon + from planet_osm_point + where amenity='police'; + + select osm_id,amenity,brand,name,way_area, + ST_Astext(ST_Transform(way,4326)) , + ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat, + ST_X(ST_Transform(ST_Centroid(way),4326)) as lon + from planet_osm_polygon + where amenity='police'; + + ST_Centroid(way) + + */ + /** + * + * 
{@code 
+     *  (
+     *    node[disused:amenity=police](35.42,139.39,35.45,139.42);
+     *    way[disused:amenity=police](35.42,139.39,35.45,139.42);
+     *    node[amenity=police](35.42,139.39,35.45,139.42);
+     *    (way[amenity=police](35.42,139.39,35.45,139.42);>;);
+     *  );
+     *  out;
+     * }
+ * + * 
{@code 
+     *  select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='police';
+     * }
+ * + * 
{@code 
+     *  select osm_id,amenity,brand,name,way_area,ST_Astext(ST_Transform(way,4326)) from planet_osm_polygon where amenity='police';
+     * 
+     *  select id,nodes,tags from planet_osm_ways;
+     * }
+ * + * @throws Exception + */ + public void getJapanCapabilities() throws Exception { + // 通常 → ５０ポイント + // FIXMEあり → １ポイント + readExisting("where amenity='police'", POINT_FIXME); + readExistingSub("disused:amenity", "police", POINT_NO); + readExistingSub("abandoned:amenity", "police", POINT_NO); + readExistingSub("demolished:amenity", "police", POINT_NO); + readExistingSub("historic:amenity", "police", POINT_NO); + readExistingSub("was:amenity", "police", POINT_NO); + readExistingSub("removed:amenity", "police", POINT_NO); + readExistingSub("no:amenity", "police", POINT_NO); + } + +} \ No newline at end of file diff --git a/src/osm/jp/coverage/police/Police.java b/src/osm/jp/coverage/police/Police.java index 27f2cc2..be96a32 100644 --- a/src/osm/jp/coverage/police/Police.java +++ b/src/osm/jp/coverage/police/Police.java @@ -15,7 +15,6 @@ import java.text.SimpleDateFormat; import jp.co.areaweb.tools.database.*; import osm.jp.api.Japan; -import osm.jp.api.Osmdb; public class Police { @@ -97,7 +96,7 @@ } } - String sql1 = String.format("SELECT idref,lat,lon,score FROM %s", Osmdb.TABLE_NAME); + String sql1 = String.format("SELECT idref,lat,lon,score FROM %s", DbExistPolice.EXIST_TABLE_NAME); String sql2 = String.format("SELECT idref,lat,lon,fixed,area FROM %s WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)", DbPolice.TABLE_NAME); String sql3 = String.format("UPDATE %s SET fixed1=? WHERE idref=? and area=?", DbPolice.TABLE_NAME); System.out.println(sql1); diff --git a/src/osm/jp/coverage/postoffice/DbExist.java b/src/osm/jp/coverage/postoffice/DbExist.java deleted file mode 100644 index a7500ea..0000000 --- a/src/osm/jp/coverage/postoffice/DbExist.java +++ /dev/null @@ -1,77 +0,0 @@ -package osm.jp.coverage.postoffice; - -import java.sql.Connection; -import jp.co.areaweb.tools.database.*; -import osm.jp.api.Osmdb; - -public class DbExist extends Osmdb { - - /** メイン - * @param args - * @throws Exception - */ - public static void main(String[] args) throws Exception - { - Connection hsqldb = null; - try { - // DB.tableを作成 - hsqldb = DatabaseTool.openDb("database"); - create(hsqldb); - - /** - * 既存のOSM郵便局を読み込む - * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.OSM_EXIST」にSTOREする - */ - (new DbExist()).getJapanCapabilities(hsqldb); - - DbExist.export(hsqldb); - } - finally { - if (hsqldb != null) { - DatabaseTool.closeDb(hsqldb); - } - } - } - - /** - * - * 
{@code 
-     *  (
-     *    node[disused:amenity=post_office](35.42,139.39,35.45,139.42);
-     *    way[disused:amenity=post_office](35.42,139.39,35.45,139.42);
-     *    node[amenity=post_office](35.42,139.39,35.45,139.42);
-     *    (way[amenity=post_office](35.42,139.39,35.45,139.42);>;);
-     *  );
-     *  out;
-     * }
- * - * 
{@code 
-     *  select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='post_office';
-     * }
- * - * 
{@code 
-     *  select osm_id,amenity,brand,name,way_area,ST_Astext(ST_Transform(way,4326)) from planet_osm_polygon where amenity='post_office';
-     * 
-     * 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 hsqldb - * @throws Exception - */ - public void getJapanCapabilities(Connection hsqldb) throws Exception { - // 通常 → ５０ポイント - // FIXMEあり → １ポイント - readExisting(hsqldb, "where amenity='post_office'", POINT_FIXME); - readExistingSub(hsqldb, "disused:amenity", "post_office", POINT_NO); - readExistingSub(hsqldb, "abandoned:amenity", "post_office", POINT_NO); - readExistingSub(hsqldb, "demolished:amenity", "post_office", POINT_NO); - readExistingSub(hsqldb, "historic:amenity", "post_office", POINT_NO); - readExistingSub(hsqldb, "was:amenity", "post_office", POINT_NO); - readExistingSub(hsqldb, "removed:amenity", "post_office", POINT_NO); - readExistingSub(hsqldb, "no:amenity", "post_office", POINT_NO); - } - -} \ No newline at end of file diff --git a/src/osm/jp/coverage/postoffice/DbExistPostoffice.java b/src/osm/jp/coverage/postoffice/DbExistPostoffice.java new file mode 100644 index 0000000..f8a5bf3 --- /dev/null +++ b/src/osm/jp/coverage/postoffice/DbExistPostoffice.java @@ -0,0 +1,80 @@ +package osm.jp.coverage.postoffice; + +import java.sql.Connection; +import jp.co.areaweb.tools.database.*; +import osm.jp.api.Osmdb; + +public class DbExistPostoffice extends Osmdb { + public static final String EXIST_TABLE_NAME = "POSTOFFICE_EXIST"; + + /** メイン + * @param args + * @throws Exception + */ + public static void main(String[] args) throws Exception + { + Connection hsqldb = null; + try { + // DB.tableを作成 + hsqldb = DatabaseTool.openDb("database"); + + /** + * 既存のOSM郵便局を読み込む + * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.OSM_EXIST」にSTOREする + */ + DbExistPostoffice osmExist = new DbExistPostoffice(hsqldb); + osmExist.create(); + osmExist.getJapanCapabilities(); + } + finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + } + + public DbExistPostoffice(Connection hsqldb) { + super(hsqldb, EXIST_TABLE_NAME); + } + + /** + * + * 
{@code 
+     *  (
+     *    node[disused:amenity=post_office](35.42,139.39,35.45,139.42);
+     *    way[disused:amenity=post_office](35.42,139.39,35.45,139.42);
+     *    node[amenity=post_office](35.42,139.39,35.45,139.42);
+     *    (way[amenity=post_office](35.42,139.39,35.45,139.42);>;);
+     *  );
+     *  out;
+     * }
+ * + * 
{@code 
+     *  select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='post_office';
+     * }
+ * + * 
{@code 
+     *  select osm_id,amenity,brand,name,way_area,ST_Astext(ST_Transform(way,4326)) from planet_osm_polygon where amenity='post_office';
+     * 
+     * 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;
+     * }
+ * + * @throws Exception + */ + public void getJapanCapabilities() throws Exception { + // 通常 → ５０ポイント + // FIXMEあり → １ポイント + readExisting("where amenity='post_office'", POINT_FIXME); + readExistingSub("disused:amenity", "post_office", POINT_NO); + readExistingSub("abandoned:amenity", "post_office", POINT_NO); + readExistingSub("demolished:amenity", "post_office", POINT_NO); + readExistingSub("historic:amenity", "post_office", POINT_NO); + readExistingSub("was:amenity", "post_office", POINT_NO); + readExistingSub("removed:amenity", "post_office", POINT_NO); + readExistingSub("no:amenity", "post_office", POINT_NO); + } + +} \ No newline at end of file diff --git a/test/osm/jp/coverage/OsmExistTest.java b/test/osm/jp/coverage/OsmExistTest.java index 2af1e83..5c77972 100644 --- a/test/osm/jp/coverage/OsmExistTest.java +++ b/test/osm/jp/coverage/OsmExistTest.java @@ -8,7 +8,7 @@ import org.junit.BeforeClass; import org.junit.Test; import osm.jp.api.HttpPOST; -import osm.jp.coverage.fuel.OsmExist; +import osm.jp.coverage.fuel.OsmExistFuel; public class OsmExistTest { @@ -63,7 +63,7 @@ @Test public void test01_main() throws Exception { - OsmExist.main(new String[0]); + OsmExistFuel.main(new String[0]); } } \ No newline at end of file diff --git a/test/osm/jp/coverage/busstop/DbExistBusstopTest.java b/test/osm/jp/coverage/busstop/DbExistBusstopTest.java index 0934bff..f14658d 100644 --- a/test/osm/jp/coverage/busstop/DbExistBusstopTest.java +++ b/test/osm/jp/coverage/busstop/DbExistBusstopTest.java @@ -12,7 +12,6 @@ import static org.hamcrest.CoreMatchers.is; import org.junit.*; import static org.junit.Assert.*; -import static osm.jp.api.Osmdb.create; /** * @@ -36,7 +35,8 @@ try { // DB.tableを作成 con = DatabaseTool.openDb("database"); - create(con); + DbExistBusstop osmExist = new DbExistBusstop(con); + osmExist.create(); } finally { if (con != null) { DatabaseTool.closeDb(con); diff --git a/test/osm/jp/coverage/fuel/DbExistTest.java b/test/osm/jp/coverage/fuel/DbExistTest.java index ada38c3..f52cacd 100644 --- a/test/osm/jp/coverage/fuel/DbExistTest.java +++ b/test/osm/jp/coverage/fuel/DbExistTest.java @@ -19,7 +19,6 @@ import static osm.jp.api.Osmdb.POINT_FIXME; import static osm.jp.api.Osmdb.POINT_NO; import static osm.jp.api.Osmdb.POINT_NO_BRAND; -import static osm.jp.api.Osmdb.create; @FixMethodOrder (MethodSorters.NAME_ASCENDING) public class DbExistTest { @@ -42,7 +41,8 @@ // DB.tableを作成 init(); con = DatabaseTool.openDb("database"); - create(con); + DbExistFuel dbExistFuel = new DbExistFuel(con); + dbExistFuel.create(); } catch (Exception ex) { fail(); } finally { @@ -97,7 +97,7 @@ try { init(); String[] args = new String[0]; - DbExist.main(args); + DbExistFuel.main(args); } catch (Exception ex) { fail(ex.toString()); @@ -285,9 +285,9 @@ * 既存のOSMガソリンスタンドを読み込む * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする */ - (new OsmExist()).getJapanCapabilities(hsqldb, features, testBoxes); - - OsmExist.export(hsqldb); + OsmExistFuel osmExistFuel = new OsmExistFuel(hsqldb); + osmExistFuel.getJapanCapabilities(features, testBoxes); + osmExistFuel.export(); } finally { if (hsqldb != null) { diff --git a/test/osm/jp/coverage/police/DbExistTest.java b/test/osm/jp/coverage/police/DbExistTest.java index 7df1ab4..25c7cd5 100644 --- a/test/osm/jp/coverage/police/DbExistTest.java +++ b/test/osm/jp/coverage/police/DbExistTest.java @@ -32,7 +32,7 @@ try { init(); String[] args = new String[0]; - DbExist.main(args); + DbExistPolice.main(args); } catch (Exception ex) { fail(ex.toString()); diff --git a/test/osm/jp/coverage/postoffice/DbExistTest.java b/test/osm/jp/coverage/postoffice/DbExistTest.java index 1d70be5..ad2c42a 100644 --- a/test/osm/jp/coverage/postoffice/DbExistTest.java +++ b/test/osm/jp/coverage/postoffice/DbExistTest.java @@ -13,7 +13,6 @@ import org.junit.*; import static org.junit.Assert.*; import org.junit.runners.MethodSorters; -import static osm.jp.api.Osmdb.create; /** * @@ -31,7 +30,7 @@ @Test public void test00_hsqldbの生成確認() { - Connection con = null; + Connection hsqldb = null; try { File dir = new File("database"); if (dir.exists()) { @@ -44,14 +43,15 @@ } // DB.tableを作成 - con = DatabaseTool.openDb("database"); - create(con); + hsqldb = DatabaseTool.openDb("database"); + DbExistPostoffice osmExist = new DbExistPostoffice(hsqldb); + osmExist.create(); } catch (Exception ex) { fail(ex.toString()); } finally { - if (con != null) { - DatabaseTool.closeDb(con); + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); } } } @@ -100,7 +100,7 @@ public void test02_fuel() { try { String[] args = new String[0]; - DbExist.main(args); + DbExistPostoffice.main(args); } catch (Exception ex) { fail(ex.toString());