diff --git a/src/fuel.activity.violet.html b/src/fuel.activity.violet.html index 353979b..93fe72a 100644 --- a/src/fuel.activity.violet.html +++ b/src/fuel.activity.violet.html @@ -132,7 +132,7 @@ DbExist.main() OSMからGSのノードを読み取って -HSQLDB.FUEL_EXISTテーブルに格納する +HSQLDB.OSM_EXISTテーブルに格納する @@ -144,442 +144,447 @@ - HSQL.DB.FUEL_EXIST + HSQL.DB.OSM_EXIST - + + 255 + 228 + 181 + 255 + - - - - + + + + 1 - + Create & Insert - - - - + + + + 1 - - - - + + + + 1 - + 国土数値情報：燃料給油所 - - - - + + + + 1 - + OpenStreatMap 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 @@ -587,14 +592,14 @@ - - + + - - - - + + + + 1 @@ -603,13 +608,13 @@ - + - - - - + + + + 1 @@ -618,13 +623,13 @@ - + - - - - + + + + 1 @@ -633,13 +638,13 @@ - + - - - - + + + + 1 @@ -648,13 +653,13 @@ - + - - - - - + + + + + 1 @@ -663,13 +668,13 @@ - - - - - - - + + + + + + + 1 @@ -678,13 +683,13 @@ - + - - - - + + + + 1 @@ -693,13 +698,13 @@ - + - - - - + + + + 1 @@ -708,22 +713,22 @@ - - + + - - - - + + + + 1 - - - - - - - + + + + + + + 1 @@ -732,13 +737,13 @@ - - - - - - - + + + + + + + 1 @@ -747,22 +752,22 @@ - + - - - - - + + + + + 1 - - - - - - - + + + + + + + 1 @@ -771,22 +776,22 @@ - + - - - - - + + + + + 1 - - - - - - - + + + + + + + 1 @@ -795,13 +800,13 @@ - - - - - - - + + + + + + + 1 @@ -810,13 +815,13 @@ - - - - - - - + + + + + + + 1 @@ -825,13 +830,13 @@ - + - - - - + + + + 1 @@ -840,13 +845,13 @@ - - + + - - - - + + + + 1 @@ -855,13 +860,13 @@ - - - - - - - + + + + + + + 1 @@ -870,15 +875,15 @@ - + - - - - - + + + + + - + 1 @@ -887,13 +892,13 @@ - - - - - - - + + + + + + + 1 @@ -902,22 +907,22 @@ - - - - - - - + + + + + + + 1 - - - - - - - + + + + + + + 1 @@ -926,13 +931,13 @@ - - + + - - - - + + + + 1 @@ -941,15 +946,15 @@ - - - - - - - + + + + + + + - + 1 @@ -958,13 +963,13 @@ - - + + - - - - + + + + 1 @@ -973,13 +978,13 @@ - - - - - - - + + + + + + + 1 @@ -988,13 +993,13 @@ - - - - - - - + + + + + + + 1 @@ -1003,13 +1008,13 @@ - - - - - - - + + + + + + + 1 @@ -1018,22 +1023,22 @@ - - - - - - - + + + + + + + 1 - - - - - - - + + + + + + + 1 @@ -1042,13 +1047,13 @@ - - - - - - - + + + + + + + 1 @@ -1057,13 +1062,13 @@ - - - - - - - + + + + + + + 1 @@ -1072,31 +1077,31 @@ - - - - - - - + + + + + + + 1 - - + + - - - - + + + + 1 - - - - - - - + + + + + + + 1 @@ -1105,22 +1110,22 @@ - - - - - - - + + + + + + + 1 - - - - - - - + + + + + + + 1 @@ -1133,1176 +1138,1182 @@ ]]>

- 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 a29beca..5c07f86 100644 --- a/src/osm/jp/api/HttpPOST.java +++ b/src/osm/jp/api/HttpPOST.java @@ -25,13 +25,14 @@ * * @author 68user http://X68000.q-e-d.net/~68user/ */ -public class HttpPOST { +public abstract class HttpPOST { //public static String host = "http://api06.dev.openstreetmap.org"; //public static String host = "http://api.openstreetmap.org"; 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 static void main(String[] args) throws MalformedURLException, ProtocolException, IOException { double minlat = 35.13d; double maxlat = 35.66d; @@ -44,6 +45,7 @@ getCapabilities("public_transport", "stop_position", minlat, maxlat, minlon, maxlon, "node"); getCapabilities("amenity", "fuel", minlat, maxlat, minlon, maxlon, "way"); } + */ /** * 'HSQLDB.table.OSM_EXIST'を新規に作る @@ -83,7 +85,8 @@ } } - + /* + */ 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"); } @@ -210,13 +213,11 @@ * @throws ParserConfigurationException * @throws SAXException */ - public static void readExistingFile (Connection con, int point) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; + public void readExistingFile (Connection con, int point) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { DocumentBuilderFactory factory; DocumentBuilder builder; - iCounter = 0; factory = DocumentBuilderFactory.newInstance(); builder = factory.newDocumentBuilder(); factory.setIgnoringElementContentWhitespace(true); @@ -226,11 +227,11 @@ Node root = builder.parse(new File(HttpPOST.EXIST_FILE)); readAreaNodes(con, root); - iCounter += readExistingNodes(con, root, point); + int iCounter = readExistingNodes(con, root, point); System.out.println("既存ノード数["+ iCounter +"]"); } - static int readAreaNodes(Connection con, Node node) throws IOException, SQLException { + public int readAreaNodes(Connection con, Node node) throws IOException, SQLException { int iCounter = 0; NodeList nodes = node.getChildNodes(); @@ -249,7 +250,7 @@ return iCounter; } - static void importAreaNode(Connection con, Node node) throws IOException, SQLException { + public void importAreaNode(Connection con, Node node) throws IOException, SQLException { String idrefStr = ""; String latStr = ""; String lonStr = ""; @@ -288,7 +289,7 @@ } } - static int readExistingNodes(Connection con, Node node, int point) throws IOException, SQLException { + public int readExistingNodes(Connection con, Node node, int point) throws IOException, SQLException { int iCounter = 0; NodeList nodes = node.getChildNodes(); @@ -311,7 +312,90 @@ return iCounter; } - static void importExistingArea(Connection con, Node node, int point) throws IOException, SQLException { + 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; @@ -320,7 +404,6 @@ String nameStr = ""; String brandStr = ""; String fixmeStr = ""; - int score = 0; NamedNodeMap nodeMap = node.getAttributes(); if (nodeMap != null) { @@ -339,8 +422,7 @@ Node node2 = nodes.item(i); if (node2.getNodeName().equals("tag")) { OsmnodeTag tagnode = new OsmnodeTag(node2); - String value = null; - value = tagnode.getValue("brand"); + String value = tagnode.getValue("brand"); if (value != null) { brandStr = value; } @@ -372,12 +454,12 @@ 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; - score = 50; - if (((point & POINT_NAME) != 0) && nameStr.equals("")) { + if (((point & POINT_BRAND) != 0) && brandStr.equals("")) { score = 1; } - if (((point & POINT_BRAND) != 0) && brandStr.equals("")) { + if (((point & POINT_NAME) != 0) && nameStr.equals("")) { score = 1; } if (((point & POINT_FIXME) != 0) && !fixmeStr.equals("")) { @@ -403,92 +485,6 @@ } } } - - - static void importExistingNode(Connection con, Node node, int point) throws IOException, SQLException { - String idrefStr = ""; - String latStr = ""; - String lonStr = ""; - String brandStr = ""; - String nameStr = ""; - String fixmeStr = ""; - int score = 0; - - 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 = null; - 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; - } - } - } - - score = 50; - if (((point & POINT_NAME) != 0) && nameStr.equals("")) { - score = 1; - } - if (((point & POINT_BRAND) != 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 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 91e6d75..edf86e4 100644 --- a/src/osm/jp/coverage/busstop/DbExistBusstop.java +++ b/src/osm/jp/coverage/busstop/DbExistBusstop.java @@ -11,12 +11,22 @@ import java.net.MalformedURLException; import java.net.ProtocolException; import java.sql.Connection; +import java.sql.PreparedStatement; import java.sql.SQLException; +import java.sql.SQLIntegrityConstraintViolationException; import java.text.SimpleDateFormat; import jp.co.areaweb.tools.database.*; +import org.hsqldb.HsqlException; +import org.w3c.dom.NamedNodeMap; +import org.w3c.dom.Node; +import org.w3c.dom.NodeList; +import static osm.jp.api.HttpPOST.POINT_BRAND; +import static osm.jp.api.HttpPOST.POINT_FIXME; +import static osm.jp.api.HttpPOST.TABLE_NAME; +import osm.jp.api.OsmnodeTag; -public class DbExistBusstop { +public class DbExistBusstop extends HttpPOST { String filter = ""; String urlStr = ""; @@ -61,7 +71,7 @@ * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、 * 「HSQLDB.BUSSTOP_EXIST」にSTOREする */ - getJapanCapabilities(conHsql); + (new DbExistBusstop()).getJapanCapabilities(conHsql); } finally { if (conHsql != null) { @@ -84,9 +94,6 @@ * ); * out; * - * - * - * * } * * @param conHsql @@ -98,7 +105,7 @@ * @throws ParserConfigurationException * @throws SAXException */ - public static void getJapanCapabilities(Connection conHsql) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { + public void getJapanCapabilities(Connection conHsql) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { for (Japan area : Japan.all) { StringBuilder queryText = new StringBuilder(); queryText.append("("); @@ -111,7 +118,11 @@ queryText.append(");"); queryText.append("out;"); HttpPOST.getQuery(queryText.toString()); - HttpPOST.readExistingFile(conHsql, HttpPOST.POINT_NAME & HttpPOST.POINT_FIXME); + + // 通常 → ５０ポイント + // NAMEなし → １ポイント + // FIXMEあり → １ポイント + readExistingFile(conHsql, HttpPOST.POINT_NAME & HttpPOST.POINT_FIXME); } } } \ No newline at end of file diff --git a/src/osm/jp/coverage/busstop/NagoyaBusstop.java b/src/osm/jp/coverage/busstop/NagoyaBusstop.java index 5e97cbe..a1f1859 100644 --- a/src/osm/jp/coverage/busstop/NagoyaBusstop.java +++ b/src/osm/jp/coverage/busstop/NagoyaBusstop.java @@ -204,6 +204,7 @@ ps2.close(); ps1.close(); } + static String[] shiftArgs(String[] args) { String[] values = new String[args.length - 1]; diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java index b424786..53e615c 100644 --- a/src/osm/jp/coverage/fuel/DbExist.java +++ b/src/osm/jp/coverage/fuel/DbExist.java @@ -7,14 +7,22 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.SQLIntegrityConstraintViolationException; import javax.xml.parsers.ParserConfigurationException; import jp.co.areaweb.tools.database.*; +import org.hsqldb.HsqlException; +import org.w3c.dom.NamedNodeMap; +import org.w3c.dom.Node; +import org.w3c.dom.NodeList; import org.xml.sax.SAXException; import osm.jp.api.HttpPOST; import osm.jp.api.Japan; +import osm.jp.api.OsmnodeNd; +import osm.jp.api.OsmnodeTag; +import osm.jp.api.Position; -public class DbExist { +public class DbExist extends HttpPOST { /** メイン * @param args @@ -36,7 +44,7 @@ * 既存のOSMガソリンスタンドを読み込む * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.FUEL_EXIST」にSTOREする */ - getJapanCapabilities(con); + (new DbExist()).getJapanCapabilities(con); DbExist.export(con); @@ -84,7 +92,7 @@ * @throws ParserConfigurationException * @throws SAXException */ - public static void getJapanCapabilities(Connection con) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { + public void getJapanCapabilities(Connection con) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { for (Japan area : Japan.all) { StringBuilder queryText = new StringBuilder(); queryText.append("("); @@ -92,8 +100,12 @@ queryText.append(" (way[amenity=fuel]("+ area.getSWNE() +");>;);"); queryText.append(");"); queryText.append("out;"); - HttpPOST.getQuery(queryText.toString()); - HttpPOST.readExistingFile(con, HttpPOST.POINT_BRAND & HttpPOST.POINT_FIXME); + getQuery(queryText.toString()); + + // 通常 → ５０ポイント + // BRANDなし → １ポイント + // FIXMEあり → １ポイント + readExistingFile(con, HttpPOST.POINT_BRAND & HttpPOST.POINT_FIXME); } } @@ -117,4 +129,5 @@ } } } + } \ 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 7f939b4..bd97d3a 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -1,6 +1,5 @@ package osm.jp.coverage.fuel; import osm.jp.api.RectArea; -import osm.jp.coverage.busstop.*; import javax.xml.parsers.*; import javax.xml.transform.TransformerException; @@ -91,10 +90,9 @@ int score = rset1.getInt("score"); String idref = null; int area = 0; - int fixed = 0; // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 - RectArea rect = new RectArea(lat, lon, NEER); // 300m 四方 + RectArea rect = new RectArea(lat, lon, NEER); // 400m 四方 ps2.setDouble(1, rect.minlat); ps2.setDouble(2, rect.maxlat); ps2.setDouble(3, rect.minlon); diff --git a/src/osm/jp/coverage/test/DbExistTest.java b/src/osm/jp/coverage/test/DbExistTest.java index 6e663ce..11a9f2e 100644 --- a/src/osm/jp/coverage/test/DbExistTest.java +++ b/src/osm/jp/coverage/test/DbExistTest.java @@ -1,7 +1,6 @@ package osm.jp.coverage.test; import osm.jp.api.HttpPOST; -import osm.jp.api.Japan; import javax.xml.parsers.*; import javax.xml.transform.TransformerException; @@ -15,6 +14,7 @@ import java.text.SimpleDateFormat; import jp.co.areaweb.tools.database.*; +import osm.jp.coverage.fuel.DbExist; public class DbExistTest { @@ -56,7 +56,7 @@ conHsql = DatabaseTool.openDb("database"); HttpPOST.create(conHsql); - getJapanCapabilities(conHsql); + getJapanCapabilitiesFuel(conHsql); } finally { if (conHsql != null) { @@ -84,12 +84,12 @@ * @throws ParserConfigurationException * @throws SAXException */ - public static void getJapanCapabilities(Connection conHsql) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { + public static void getJapanCapabilitiesFuel(Connection conHsql) throws MalformedURLException, ProtocolException, IOException, ClassNotFoundException, SQLException, ParserConfigurationException, SAXException { StringBuilder queryText = new StringBuilder(); queryText.append("node(35.42,139.39,35.45,139.42);"); queryText.append("node[junction=yes][\"name:en\"=\"Ryosei\"];"); queryText.append("out;"); HttpPOST.getQuery(queryText.toString()); - HttpPOST.readExistingFile(conHsql, 2); + (new DbExist()).readExistingFile(conHsql, 2); } } \ No newline at end of file