diff --git a/src/db.fuel.class.violet.html b/src/db.fuel.class.violet.html index d468283..1886a07 100644 --- a/src/db.fuel.class.violet.html +++ b/src/db.fuel.class.violet.html @@ -151,13 +151,14 @@ - table.OSM_EXIST + table.EXIST_FUEL + idref: VARCHAR(12) NOT NULL - lat: DOUBLE - lon: DOUBLE -- score: INT +- score: INT +- removed: boolean = false @@ -312,686 +313,691 @@ - - - - - - - - 1 - - - - - - + - - - - + + + + 1 - + - - - - + + + + 1 - + - - - - + + + + 1 + + + + + + + + 1 + + 0..1 + idref + 0..1 + ]]>

- embedded diagram image + embedded diagram image \ No newline at end of file diff --git a/src/fuel.activity.violet.html b/src/fuel.activity.violet.html index 33842ff..8f6495d 100644 --- a/src/fuel.activity.violet.html +++ b/src/fuel.activity.violet.html @@ -20,7 +20,7 @@ - + 1 @@ -53,7 +53,7 @@ - + 1 @@ -74,7 +74,7 @@ - + 1 @@ -100,379 +100,386 @@ 255 - + - - - 1 - - - - - Create & insert - - - - - - + + 1 - - - - + + + + 1 - - DbExist.main() + + DbExistFuel.main() OSMからGSのノードを読み取って HSQLDB.OSM_EXISTテーブルに格納する - - - - + + + + 1 - - HSQL.DB.FUEL_EXIST + + HSQLDB.FUEL_EXIST - + 255 228 181 255 - - - - + + + + 1 + + + + + 1 + + + + + 国土数値情報：燃料給油所 +./GML_FUEL/P17_15_[01..47].xml + + + - - + + 1 - 国土数値情報：燃料給油所 - - - - - - - - 1 - - - - OpenStreatMap(PBF) node: amenity=fuel - + + 255 + 228 + 181 + 255 + - - - - + + + + 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 @@ -488,154 +495,130 @@ - + - - + + 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() + + + OsmExistFuel.main() OverpassAPIからGSのノードを読み取って HSQLDB.OSM_EXISTテーブルに格納する - - - - + + + + 1 - + 255 255 255 255 - + 0 0 0 255 - - + + OSM OverpassAPI - + + + + + + + 1 + + 255 + 255 + 255 + 255 + + + 0 + 0 + 0 + 255 + + + + removed_fuel.json.txt + + - - + + - - - - + + + + 1 @@ -644,13 +627,13 @@ - + - - - - + + + + 1 @@ -659,13 +642,13 @@ - - - - - - - + + + + + + + 1 @@ -674,67 +657,22 @@ - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - + + + + + + + 1 - - - - - - - + + + + + + + 1 @@ -743,61 +681,31 @@ - - - - - - - - 1 - - - - - - - - - - - - - - + + + + + + + 1 - - - - - - - - 1 - - - - - - - - + - - - - - + + + + + 1 - - - - - - - + + + + + + + 1 @@ -806,13 +714,13 @@ - - - - - - - + + + + + + + 1 @@ -821,13 +729,13 @@ - - - - - - - + + + + + + + 1 @@ -836,28 +744,13 @@ - - - - - - - - 1 - - - - - - - - - - - - - - + + + + + + + 1 @@ -866,15 +759,15 @@ - + - - - - - + + + + + - + 1 @@ -883,13 +776,13 @@ - - - - - - - + + + + + + + 1 @@ -898,22 +791,22 @@ - - - - - - - + + + + + + + 1 - - - - - - - + + + + + + + 1 @@ -922,60 +815,154 @@ - - - - - - - + + + + + + + 1 - + - - - - - - - - - + + + + + + + 1 - + - - - - - + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + + + + + 1 + + + + + + + + + 1 + + + + + + + + + + + + - + 1 - - - - - - - + - - - - + + + + - + 1 @@ -985,1566 +972,1533 @@ - - - - + + + + - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - 1 - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - 1 - - - - - - - - - - - - - - - 1 - - - - - - - - - - - 1 - - - - - - - - - - - - - - + 1 + insert + + + + + + + + + + 1 + + + + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + append + + + + + + + + + + 1 + + + + + update(fixed1) + + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + create & insert + + + + + + + + + + 1 + + + + + create & insert + + + + + + + + + + 1 + + + + + OverpassAPI + + ]]>

- embedded diagram image + embedded diagram image \ No newline at end of file diff --git a/src/osm/jp/api/HttpPOST.java b/src/osm/jp/api/HttpPOST.java index 215c00c..7306f51 100644 --- a/src/osm/jp/api/HttpPOST.java +++ b/src/osm/jp/api/HttpPOST.java @@ -16,15 +16,13 @@ * * @author 68user http://X68000.q-e-d.net/~68user/ */ -public abstract class HttpPOST { - //public static String host = "http://api06.dev.openstreetmap.org"; - //public static String host = "http://api.openstreetmap.org"; +public class HttpPOST { public static String host = "http://overpass-api.de"; public static final String EXIST_FILE = "exist.osm.xml"; - public static final String TABLE_NAME = "OSM_EXIST"; + + public String tableName = null; + public Connection hsqldb = null; - /* - */ public static void main(String[] args) throws MalformedURLException, ProtocolException, IOException { double minlat = 35.00d; double maxlat = 36.00d; @@ -39,34 +37,38 @@ getCapabilities("amenity", "fuel", minlat, maxlat, minlon, maxlon, "node"); } + public HttpPOST(Connection hsqldb, String tableName) { + this.tableName = tableName; + this.hsqldb = hsqldb; + } + /** * '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 "+ HttpPOST.TABLE_NAME +" CASCADE"); - //sql(con, "DROP INDEX "+ HttpPOST.TABLE_NAME +"_index;"); - sql(con, "DROP TABLE IF EXISTS AREA_NODE CASCADE"); + sql("DROP TABLE IF EXISTS "+ this.tableName +" CASCADE"); + sql("DROP TABLE IF EXISTS AREA_NODE CASCADE"); // 'table.FUEL_EXIST'を新規に作る - createSt = "CREATE TABLE "+ HttpPOST.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 "+ HttpPOST.TABLE_NAME +"_index ON "+ HttpPOST.TABLE_NAME +" (lat, lon);"; - Db.updateSQL(con, createSt); + createSt = "CREATE TABLE "+ this.tableName +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));"; + Db.updateSQL(hsqldb, createSt); + createSt = "CREATE INDEX "+ this.tableName +"_index ON "+ this.tableName +" (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); } - public static void sql(Connection con, String sql) throws SQLException { + public void sql(String sql) throws SQLException { System.out.println(sql); - try (PreparedStatement ps = con.prepareStatement(sql)) { + try (PreparedStatement ps = this.hsqldb.prepareStatement(sql)) { ps.executeUpdate(); } catch (SQLSyntaxErrorException e) { @@ -77,8 +79,6 @@ } } - /* - */ public static void getCapabilities(String key, String value, double minLat, double maxLat, double minLon, double maxLon) throws MalformedURLException, ProtocolException, IOException { getCapabilities(key, value, minLat, maxLat, minLon, maxLon, "node"); } @@ -202,295 +202,7 @@ * param con 反映先のデータベースコネクタ(HSQLDB) * param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name' */ - /* - - public void readExistingFile (Connection con, int point) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - DocumentBuilderFactory factory; - DocumentBuilder builder; - - factory = DocumentBuilderFactory.newInstance(); - builder = factory.newDocumentBuilder(); - factory.setIgnoringElementContentWhitespace(true); - factory.setIgnoringComments(true); - factory.setValidating(true); - - Node root = builder.parse(new File(HttpPOST.EXIST_FILE)); - - readAreaNodes(con, root); - int iCounter = readExistingNodes(con, root, point); - System.out.println("既存ノード数["+ iCounter +"]"); - } - */ - - /* - - public int readAreaNodes(Connection con, Node node) throws IOException, SQLException { - int iCounter = 0; - - NodeList nodes = node.getChildNodes(); - for (int i = 0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - switch (node2.getNodeName()) { - case "node": - iCounter++; - importAreaNode(con, node2); - break; - default: - iCounter += readAreaNodes(con, node2); - break; - } - } - return iCounter; - } - */ - - /* - - public void importAreaNode(Connection con, Node node) throws IOException, SQLException { - String idrefStr = ""; - String latStr = ""; - String lonStr = ""; - - NodeList nodes = node.getChildNodes(); - if (nodes.getLength() > 0) { - return; - } - - 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; - case "lat": - latStr = nodeMap.item(j).getNodeValue(); - break; - case "lon": - lonStr = nodeMap.item(j).getNodeValue(); - break; - default: - break; - } - } - - // idref と brandStr をデータベースに格納する - System.out.println("insert into AREA_NODE(idref,lat,lon) values("+ idrefStr +","+ latStr +","+ lonStr+")"); - try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO AREA_NODE (idref,lat,lon) VALUES (?,?,?)")) { - ps5.setString(1, idrefStr); - ps5.setDouble(2, Double.parseDouble(latStr)); - ps5.setDouble(3, Double.parseDouble(lonStr)); - ps5.executeUpdate(); - } - } - } - */ - - /* - - public int readExistingNodes(Connection con, Node node, int point) throws IOException, SQLException { - int iCounter = 0; - - NodeList nodes = node.getChildNodes(); - for (int i = 0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - switch (node2.getNodeName()) { - case "node": - iCounter++; - importExistingNode(con, node2, point); - break; - case "way": - iCounter++; - importExistingArea(con, node2, point); - break; - default: - iCounter += readExistingNodes(con, node2, point); - break; - } - } - return iCounter; - } - */ - - /* - - public void importExistingNode(Connection con, Node node, int point) throws IOException, SQLException { - String idrefStr = ""; - String latStr = ""; - String lonStr = ""; - String brandStr = ""; - String nameStr = ""; - 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; - case "lat": - latStr = nodeMap.item(j).getNodeValue(); - break; - case "lon": - lonStr = nodeMap.item(j).getNodeValue(); - break; - default: - break; - } - } - - NodeList nodes = node.getChildNodes(); - if (nodes.getLength() == 0) { - return; - } - 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"); - if (value != null) { - nameStr = value; - } - value = tagnode.getValue("name:ja"); - if (value != null) { - nameStr = value; - } - value = tagnode.getValue("fixme"); - if (value != null) { - fixmeStr = value; - } - } - } - - int score = 50; - if (((point & POINT_BRAND) != 0) && brandStr.equals("")) { - score = 1; - } - if (((point & POINT_NAME) != 0) && brandStr.equals("")) { - score = 1; - } - if (((point & POINT_FIXME) != 0) && !fixmeStr.equals("")) { - score = 1; - } - - // idref と brandStr をデータベースに格納する - System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES ("+ idrefStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N"); - try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) { - ps5.setString(1, idrefStr); - ps5.setDouble(2, Double.parseDouble(latStr)); - ps5.setDouble(3, Double.parseDouble(lonStr)); - 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]は、無視する - } - } - } - */ - - /* - public 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_BRAND) != 0) && brandStr.equals("")) { - score = 1; - } - if (((point & POINT_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]は、無視する - } - } - } - } - */ /** * diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index 7626644..40de060 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -15,7 +15,7 @@ import org.w3c.dom.NodeList; public abstract class Osmdb { - public String TABLE_NAME = "OSM_EXIST"; + public String TABLE_NAME = "EXIST_osm"; Connection hsqldb = null; // hsqldb DatabaseTool.openDb("database"); public Osmdb(Connection hsqldb, String tableName) { @@ -39,7 +39,7 @@ sql("DROP TABLE IF EXISTS AREA_NODE CASCADE"); // 'table.OSM_EXIST'を新規に作る - createSt = "CREATE TABLE "+ TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));"; + createSt = "CREATE TABLE "+ TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref), removed BOOLEAN DEFAULT FALSE NOT NULL);"; Db.updateSQL(hsqldb, createSt); createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat, lon);"; Db.updateSQL(hsqldb, createSt); @@ -267,8 +267,8 @@ double lat = Double.valueOf(latStr); double lon = Double.valueOf(lonStr); int score = score(point, tags); - //counter += importExistingNode(hsqldb, osmdb, rset1, point); - counter += insertExistingNode(idref, lat, lon, score, ""); + boolean removed = true; + counter += insertExistingNode(idref, lat, lon, score, "", removed); } } System.out.println("Exists Node count = " + counter); @@ -338,7 +338,6 @@ return score; } - /** * * @param idref @@ -351,11 +350,28 @@ * @throws SQLException */ public int insertExistingNode(String idref, double lat, double lon, int score, String name) throws IOException, SQLException { + return insertExistingNode(idref, lat, lon, score, name, false); + } + + /** + * + * @param idref + * @param lat + * @param lon + * @param score + * @param name + * @param removed + * @return + * @throws IOException + * @throws SQLException + */ + public int insertExistingNode(String idref, double lat, double lon, int score, String name, boolean removed) 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, idref, lat, lon, score, name); System.out.println(sql); + sql = String.format("DELETE FROM %s WHERE idref=?", TABLE_NAME); try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) { ps5.setString(1, idref); @@ -368,13 +384,14 @@ // [SQLIntegrityConstraintViolationException]は、無視する } - sql = String.format("INSERT INTO %s (idref,lat,lon,score,name) VALUES (?,?,?,?,?)", TABLE_NAME); + sql = String.format("INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (?,?,?,?,?,?)", TABLE_NAME); try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) { ps5.setString(1, idref); ps5.setDouble(2, lat); ps5.setDouble(3, lon); ps5.setInt(4, score); ps5.setString(5, name); + ps5.setBoolean(6, removed); ps5.executeUpdate(); return 1; } diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index 5ba9ce4..d914c6d 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -1,6 +1,5 @@ package osm.jp.coverage.busstop; import osm.jp.api.RectArea; -import osm.jp.api.HttpPOST; import javax.xml.parsers.*; import javax.xml.transform.TransformerException; @@ -87,7 +86,7 @@ * @throws TransformerException */ public Busstop(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ HttpPOST.TABLE_NAME; + String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ DbExistBusstop.EXIST_TABLE_NAME; String sqlStr2 = "SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"; String sqlStr3 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?"; String sqlStr4 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"; diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index 528ce27..67777d0 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -49,7 +49,8 @@ Connection conHsql = null; try { conHsql = DatabaseTool.openDb("database"); - HttpPOST.sql(conHsql, "DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); + HttpPOST httpPOST = new HttpPOST(conHsql, TABLE_NAME); + httpPOST.sql("DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); create(conHsql); /** @@ -332,13 +333,14 @@ */ public static void create(Connection conHsql) throws SQLException { String createSt; + HttpPOST httpPOST = new HttpPOST(conHsql, TABLE_NAME); // 'table.BUS_STOP'を新規に作る createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));"; - HttpPOST.sql(conHsql, createSt); + httpPOST.sql(createSt); createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);"; - HttpPOST.sql(conHsql, createSt); + httpPOST.sql(createSt); } /** diff --git a/src/osm/jp/coverage/busstop/ToPostgis.java b/src/osm/jp/coverage/busstop/ToPostgis.java index 63f310c..12e16b6 100644 --- a/src/osm/jp/coverage/busstop/ToPostgis.java +++ b/src/osm/jp/coverage/busstop/ToPostgis.java @@ -59,10 +59,10 @@ * @throws org.xml.sax.SAXException */ public static void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - HttpPOST.sql(conPost, "DROP TABLE IF EXISTS t_busstop CASCADE;"); - HttpPOST.sql(conPost, "CREATE TABLE t_busstop (gid SERIAL PRIMARY KEY,gmlid text,name text,fixed integer,area integer,geom GEOMETRY(POINT, 4612));"); - HttpPOST.sql(conPost, "CREATE INDEX ix_busstop_geom ON t_busstop USING GiST (geom);"); - + 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,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/fuel/DbExistFuel.java b/src/osm/jp/coverage/fuel/DbExistFuel.java index 4e99242..02a7a09 100644 --- a/src/osm/jp/coverage/fuel/DbExistFuel.java +++ b/src/osm/jp/coverage/fuel/DbExistFuel.java @@ -5,7 +5,7 @@ import osm.jp.api.Osmdb; public class DbExistFuel extends Osmdb { - public static final String FUEL_EXIST = "FUEL_EXIST"; + public static final String EXIST_TABLENAME = "FUEL_EXIST"; /** メイン * @param args @@ -36,7 +36,7 @@ } public DbExistFuel(Connection hsqldb) { - super(hsqldb, FUEL_EXIST); + super(hsqldb, EXIST_TABLENAME); } /* diff --git a/src/osm/jp/coverage/fuel/Dockerfile.txt b/src/osm/jp/coverage/fuel/Dockerfile.txt new file mode 100644 index 0000000..0a36f94 --- /dev/null +++ b/src/osm/jp/coverage/fuel/Dockerfile.txt @@ -0,0 +1,37 @@ +#-------------------------------------------------------------------------- +# cd ~/workspace/osmCoverageBin +# docker build -t haya4/coverage_fuel . +# docker run -it -v $(pwd)/.:/mnt/share haya4/coverage_fuel bash +#----- +FROM ubuntu:16.04 + +# SETUP +RUN apt-get -qq update && \ + apt-get -y upgrade +RUN \ + apt-get -qq update && \ + apt-get -yqq install openjdk-8-jdk +RUN apt-get -yqq install unzip +RUN apt-get -yqq install curl +RUN apt-get -yqq install git +RUN mkdir /mnt/share +RUN mkdir /root/workspace + +RUN cd /root/workspace/ && git clone http://surveyor.mydns.jp/gitbucket/git/yuu/osmCoverageBin.git +#RUN cd /root/workspace/osmCoverageBin && git remote add gitbucket http://surveyor.mydns.jp/gitbucket/git/yuu/osmCoverageBin.git +#git fetch gitbucket +#git checkout -b master + +#COPY ./database.properties /root/osmCoverageBin +#COPY ./osmdb.properties /root/osmCoverageBin +#COPY ./postgis.properties /root/osmCoverageBin + +RUN curl --user yuu:yuu8844 -o /root/workspace/osmCoverageBin/hayashi.jar http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/hayashi_0225.jar +RUN curl --user yuu:yuu8844 -o /root/workspace/osmCoverageBin/postgresql.jar http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/postgresql-9.4.1212.jar +RUN curl --user yuu:yuu8844 -o /root/workspace/osmCoverageBin/hsqldb.jar http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/hsqldb_2.2.9.jar +RUN curl --user yuu:yuu8844 -o /root/workspace/osmCoverageBin/osmCoverage.jar http://192.168.0.26:8080/job/osmCoverage/lastSuccessfulBuild/artifact/osmCoverage.jar +RUN curl --user yuu:yuu8844 -o /root/workspace/osmCoverageBin/osmCoverage.jar http://192.168.0.26:8080/job/osmCoverage/lastSuccessfulBuild/artifact/osmCoverage.jar + +VOLUME /mnt/share + +WORKDIR /root/workspace/workspace \ 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 89f3f94..e9c879c 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -13,7 +13,6 @@ import java.sql.SQLException; import java.text.SimpleDateFormat; import jp.co.areaweb.tools.database.*; -import osm.jp.api.HttpPOST; import osm.jp.api.Japan; public class Fuel { @@ -71,7 +70,7 @@ */ public Fuel(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { String sql4 = "UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=0"; - String sql1 = "SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME; + String sql1 = "SELECT idref,lat,lon,score FROM "+ DbExistFuel.EXIST_TABLENAME; String sql2 = "SELECT idref,lat,lon,fixed, area FROM "+ DbFuel.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)"; String sql3 = "UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=? WHERE idref=? and area=?"; String sql5 = "UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; diff --git a/src/osm/jp/coverage/fuel/OsmExistFuel.java b/src/osm/jp/coverage/fuel/OsmExistFuel.java index c6688f1..4c39af6 100644 --- a/src/osm/jp/coverage/fuel/OsmExistFuel.java +++ b/src/osm/jp/coverage/fuel/OsmExistFuel.java @@ -53,7 +53,7 @@ } public OsmExistFuel(Connection hsqldb) { - super(hsqldb, DbExistFuel.FUEL_EXIST); + super(hsqldb, DbExistFuel.EXIST_TABLENAME); } } \ No newline at end of file diff --git a/src/osm/jp/coverage/police/ToPostgis.java b/src/osm/jp/coverage/police/ToPostgis.java index 43a6b51..ea35c67 100644 --- a/src/osm/jp/coverage/police/ToPostgis.java +++ b/src/osm/jp/coverage/police/ToPostgis.java @@ -46,9 +46,10 @@ @Override public void transportNew(Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - HttpPOST.sql(conPost, "DROP TABLE IF EXISTS t_"+ DbPolice.TABLE_NAME +" CASCADE;"); - HttpPOST.sql(conPost, "CREATE TABLE t_"+ DbPolice.TABLE_NAME +" (gid SERIAL PRIMARY KEY, idref varchar(24), fixed integer,area integer, code integer,geom GEOMETRY(POINT, 4612));"); - HttpPOST.sql(conPost, "CREATE INDEX ix_"+ DbPolice.TABLE_NAME +"_geom ON t_"+ DbPolice.TABLE_NAME +" USING GiST (geom);"); + HttpPOST httpPOST = new HttpPOST(conPost, null); + httpPOST.sql("DROP TABLE IF EXISTS t_"+ DbPolice.TABLE_NAME +" CASCADE;"); + httpPOST.sql("CREATE TABLE t_"+ DbPolice.TABLE_NAME +" (gid SERIAL PRIMARY KEY, idref varchar(24), fixed integer,area integer, code integer,geom GEOMETRY(POINT, 4612));"); + httpPOST.sql("CREATE INDEX ix_"+ DbPolice.TABLE_NAME +"_geom ON t_"+ DbPolice.TABLE_NAME +" USING GiST (geom);"); toInsert(conHsql, conPost); } diff --git a/src/osm/jp/coverage/postoffice/Postoffice.java b/src/osm/jp/coverage/postoffice/Postoffice.java index 389ce01..3bc4d9c 100644 --- a/src/osm/jp/coverage/postoffice/Postoffice.java +++ b/src/osm/jp/coverage/postoffice/Postoffice.java @@ -68,7 +68,7 @@ */ public Postoffice(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { String sql4 = "UPDATE "+ DbPostoffice.TABLE_NAME +" SET fixed1=0"; - String sql1 = "SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME; + String sql1 = "SELECT idref,lat,lon,score FROM "+ DbExistPostoffice.EXIST_TABLE_NAME; String sql2 = "SELECT idref,lat,lon,fixed, area FROM "+ DbPostoffice.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)"; String sql3 = "UPDATE "+ DbPostoffice.TABLE_NAME +" SET fixed1=? WHERE idref=? and area=?"; String sql5 = "UPDATE "+ DbPostoffice.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; diff --git a/src/osm/jp/postgis/ToPostgis.java b/src/osm/jp/postgis/ToPostgis.java index 9d57c49..a1d45ae 100644 --- a/src/osm/jp/postgis/ToPostgis.java +++ b/src/osm/jp/postgis/ToPostgis.java @@ -76,9 +76,10 @@ } public void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - HttpPOST.sql(conPost, "DROP TABLE IF EXISTS t_"+ tableName +" CASCADE;"); - HttpPOST.sql(conPost, "CREATE TABLE t_"+ tableName +" (gid SERIAL PRIMARY KEY, idref varchar(24), fixed integer,area integer,geom GEOMETRY(POINT, 4612));"); - HttpPOST.sql(conPost, "CREATE INDEX ix_"+ tableName +"_geom ON t_"+ tableName +" USING GiST (geom);"); + HttpPOST posgre = new HttpPOST(conPost, null); + posgre.sql("DROP TABLE IF EXISTS t_"+ tableName +" CASCADE;"); + posgre.sql("CREATE TABLE t_"+ tableName +" (gid SERIAL PRIMARY KEY, idref varchar(24), fixed integer,area integer,geom GEOMETRY(POINT, 4612));"); + posgre.sql("CREATE INDEX ix_"+ tableName +"_geom ON t_"+ tableName +" USING GiST (geom);"); toInsert(conHsql, conPost); } diff --git a/test/osm/jp/coverage/DbTest.java b/test/osm/jp/coverage/DbTest.java index db48660..1593905 100644 --- a/test/osm/jp/coverage/DbTest.java +++ b/test/osm/jp/coverage/DbTest.java @@ -42,7 +42,8 @@ Connection conHsql = null; try { conHsql = DatabaseTool.openDb("database"); - HttpPOST.sql(conHsql, "DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); + HttpPOST hsql = new HttpPOST(conHsql, null); + hsql.sql("DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); //HttpPOST.sql(conHsql, "DROP INDEX "+ TABLE_NAME +"_index;"); create(conHsql); try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) { @@ -135,13 +136,14 @@ */ public static void create(Connection conHsql) throws SQLException { String createSt; + HttpPOST hsql = new HttpPOST(conHsql, null); // 'table.TEST'を新規に作る createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));"; - HttpPOST.sql(conHsql, createSt); + hsql.sql(createSt); createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);"; - HttpPOST.sql(conHsql, createSt); + hsql.sql(createSt); } /** diff --git a/test/osm/jp/coverage/Test.java b/test/osm/jp/coverage/Test.java index acd2ca7..925510b 100644 --- a/test/osm/jp/coverage/Test.java +++ b/test/osm/jp/coverage/Test.java @@ -21,6 +21,7 @@ // 近くのノードを探す範囲（KJS2を中心としたNEER×2(ｍ)四方の領域 static final int NEER = 1000; // 1000m x 2 = 2000m四方 static final double DISTANCE = 1000.0D; // 1000m 以内 + static final String EXIST_TABLE_NAME = "test_EXIST"; public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); @@ -72,7 +73,7 @@ */ public Test(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - String sqlStr1 = "SELECT lat,lon FROM "+ HttpPOST.TABLE_NAME; + String sqlStr1 = "SELECT lat,lon FROM "+ EXIST_TABLE_NAME; String sqlStr2 = "SELECT gmlid,lat,lon,area FROM "+ DbTest.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"; String sqlStr3 = "UPDATE "+ DbTest.TABLE_NAME +" SET fixed1=1 WHERE gmlid=? and area=?"; String sqlStr4 = "UPDATE "+ DbTest.TABLE_NAME +" SET fixed1=0"; diff --git a/test/osm/jp/coverage/ToPostgis.java b/test/osm/jp/coverage/ToPostgis.java index 819641a..c1ac6f2 100644 --- a/test/osm/jp/coverage/ToPostgis.java +++ b/test/osm/jp/coverage/ToPostgis.java @@ -58,10 +58,10 @@ * @throws org.xml.sax.SAXException */ public static void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - HttpPOST.sql(conPost, "DROP TABLE IF EXISTS t_test CASCADE;"); - HttpPOST.sql(conPost, "CREATE TABLE t_test (gid SERIAL PRIMARY KEY,gmlid text,name text,fixed integer,area integer,geom GEOMETRY(POINT, 4612));"); - HttpPOST.sql(conPost, "CREATE INDEX ix_test_geom ON t_test USING GiST (geom);"); - + HttpPOST postgres = new HttpPOST(conPost, null); + postgres.sql("DROP TABLE IF EXISTS t_test CASCADE;"); + postgres.sql("CREATE TABLE t_test (gid SERIAL PRIMARY KEY,gmlid text,name text,fixed integer,area integer,geom GEOMETRY(POINT, 4612));"); + postgres.sql("CREATE INDEX ix_test_geom ON t_test USING GiST (geom);"); toInsert(conHsql, conPost); } diff --git a/test/osm/jp/coverage/busstop/BusstopTest.java b/test/osm/jp/coverage/busstop/BusstopTest.java index 380487e..1bdbe7d 100644 --- a/test/osm/jp/coverage/busstop/BusstopTest.java +++ b/test/osm/jp/coverage/busstop/BusstopTest.java @@ -13,7 +13,6 @@ import static org.hamcrest.CoreMatchers.is; import org.junit.*; import static org.junit.Assert.*; -import osm.jp.api.HttpPOST; import osm.jp.api.Japan; import osm.jp.api.RectArea; import osm.jp.coverage.PoiTest; @@ -24,6 +23,7 @@ * @author yuu */ public class BusstopTest extends PoiTest { + @Before public void setUp() throws Exception { } @@ -179,7 +179,7 @@ Connection hsqldb = null; try { hsqldb = DatabaseTool.openDb("database"); - String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ HttpPOST.TABLE_NAME; + String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ DbExistBusstop.EXIST_TABLE_NAME; String sqlStr2 = "SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"; String sqlStr3 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?"; System.out.println(sqlStr1); diff --git a/test/osm/jp/coverage/fuel/FuelTest.java b/test/osm/jp/coverage/fuel/FuelTest.java index 5e0d8c2..18f73e0 100644 --- a/test/osm/jp/coverage/fuel/FuelTest.java +++ b/test/osm/jp/coverage/fuel/FuelTest.java @@ -11,7 +11,6 @@ import org.junit.*; import static org.junit.Assert.*; import org.junit.runners.MethodSorters; -import osm.jp.api.HttpPOST; import osm.jp.coverage.PoiTest; @FixMethodOrder (MethodSorters.NAME_ASCENDING) @@ -82,7 +81,7 @@ @SuppressWarnings("UseSpecificCatch") public void test91_removed() { Connection hsqldb = null; - String sql1 = String.format("SELECT * FROM %s where idref='%s'", HttpPOST.TABLE_NAME, "5338111023"); + String sql1 = String.format("SELECT * FROM %s where idref='%s'", DbExistFuel.EXIST_TABLENAME, "5338111023"); try { hsqldb = DatabaseTool.openDb("database"); try (PreparedStatement ps1 = hsqldb.prepareStatement(sql1)){