diff --git a/src/coverage_busstop_update.sh b/src/coverage_busstop_update.sh index 13d4681..69e631d 100755 --- a/src/coverage_busstop_update.sh +++ b/src/coverage_busstop_update.sh @@ -9,7 +9,8 @@ #java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.DbExistBusstop -drop java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.DbExistBusstop -java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.Busstop -update +java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.Busstop + java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar osm.jp.postgis.Do_sqlfiles -update java -cp .:osmCoverage.jar:hayashi_0225.jar:postgresql-9.4.1212.jar:hsqldb_2.2.9.jar osm.jp.coverage.busstop.Coverage coverage.csv git commit -a -m "coverage update $(date +%Y-%m-%d_%H:%M)" diff --git a/src/osm/jp/api/HttpPOST.java b/src/osm/jp/api/HttpPOST.java index f0c54dc..47e8cf0 100644 --- a/src/osm/jp/api/HttpPOST.java +++ b/src/osm/jp/api/HttpPOST.java @@ -184,7 +184,18 @@ pw.print(text); } - public static void readExistingFile (Connection con) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + /** + * + * @param con + * @param point 1: 'brand' 2:'name' + * @throws FileNotFoundException + * @throws ClassNotFoundException + * @throws SQLException + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + */ + public static void readExistingFile (Connection con, int point) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { int iCounter = 0; DocumentBuilderFactory factory; @@ -200,7 +211,7 @@ Node root = builder.parse(new File(HttpPOST.EXIST_FILE)); readAreaNodes(con, root); - iCounter += readExistingNodes(con, root); + iCounter += readExistingNodes(con, root, point); System.out.println("既存ノード数["+ iCounter +"]"); } @@ -262,7 +273,7 @@ } } - static int readExistingNodes(Connection con, Node node) throws IOException, SQLException { + static int readExistingNodes(Connection con, Node node, int point) throws IOException, SQLException { int iCounter = 0; NodeList nodes = node.getChildNodes(); @@ -271,21 +282,21 @@ switch (node2.getNodeName()) { case "node": iCounter++; - importExistingNode(con, node2); + importExistingNode(con, node2, point); break; case "way": iCounter++; - importExistingArea(con, node2); + importExistingArea(con, node2, point); break; default: - iCounter += readExistingNodes(con, node2); + iCounter += readExistingNodes(con, node2, point); break; } } return iCounter; } - static void importExistingArea(Connection con, Node node) throws IOException, SQLException { + static void importExistingArea(Connection con, Node node, int point) throws IOException, SQLException { String idrefStr = ""; double maxlat = -90.0D; double maxlon = -180.0D; @@ -348,10 +359,10 @@ double lon = (maxlon + minlon) / 2; score = 50; - if (nameStr.equals("")) { + if ((point == 2) && nameStr.equals("")) { score = 1; } - if (brandStr.equals("")) { + if ((point == 1) && brandStr.equals("")) { score = 1; } if (!fixmeStr.equals("")) { @@ -379,11 +390,12 @@ } - static void importExistingNode(Connection con, Node node) throws IOException, SQLException { + 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; @@ -418,6 +430,14 @@ 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; @@ -426,7 +446,10 @@ } score = 50; - if (brandStr.equals("")) { + if ((point == 2) && nameStr.equals("")) { + score = 1; + } + if ((point == 1) && brandStr.equals("")) { score = 1; } if (!fixmeStr.equals("")) { diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index 2c5b2d1..f976aa5 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -58,13 +58,13 @@ * @throws TransformerException */ public Busstop(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { - try ( PreparedStatement ps2 = con.prepareStatement("SELECT gmlid,lat,lon,fixed, area FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); - PreparedStatement ps1 = con.prepareStatement("SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME); + try ( PreparedStatement ps2 = con.prepareStatement("SELECT gmlid,lat,lon,fixed,fixed1,area FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); + PreparedStatement ps1 = con.prepareStatement("SELECT idref,lat,lon,score,name FROM "+ HttpPOST.TABLE_NAME); PreparedStatement ps3 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?"); PreparedStatement ps4 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"); PreparedStatement ps5 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)" )) { - System.out.println("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed=0"); + System.out.println("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0"); ps4.executeUpdate(); System.out.println("SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME); @@ -74,13 +74,17 @@ double lat = rset1.getDouble("lat"); double lon = rset1.getDouble("lon"); int score = rset1.getInt("score"); + String name = rset1.getString("name"); + if ((name != null) && !name.trim().isEmpty()) { + score = 50; + } String gmlid = null; int area = 0; int fixed1 = 0; // 指定の緯度経度を中心とする半径150x2m四方の矩形領域 RectArea rect = new RectArea(lat, lon, NEER); // 300m 四方 - System.out.println("SELECT gmlid,lat,lon,fixed1,area FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); + //System.out.println("SELECT gmlid,lat,lon,fixed1,area FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); ps2.setDouble(1, rect.minlat); ps2.setDouble(2, rect.maxlat); ps2.setDouble(3, rect.minlon); @@ -100,8 +104,9 @@ } } if (gmlid != null) { + score += fixed1; System.out.println("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1="+ score +" WHERE gmlid="+ gmlid +" and area=" + area); - ps3.setInt(1, fixed1 + score); + ps3.setInt(1, score); ps3.setString(2, gmlid); ps3.setInt(3, area); ps3.executeUpdate(); diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index 9c083b8..9dbae80 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -158,61 +158,6 @@ return iCounter; } - /* - public static void showGmPoint(Connection con, Node node) throws IOException, SQLException { - String positionStr = ""; - String latStr = ""; - String lonStr = ""; - String idStr = ""; - - NamedNodeMap nodeMap = node.getAttributes(); - if ( null != nodeMap ) { - for ( int j=0; j * 35.14591397 139.10569573 @@ -248,10 +193,10 @@ latStr = str4Ary[0]; lonStr = str4Ary[1]; - try (PreparedStatement ps6 = con.prepareStatement("INSERT INTO bus_stop (lat,lon,fixed,area,gmlid,up) VALUES (?,?,0,?,?,2)")) { + try (PreparedStatement ps6 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (lat,lon,fixed,fixed1,area,gmlid,up) VALUES (?,?,0,0,?,?,2)")) { double lat = Double.parseDouble(latStr); double lon = Double.parseDouble(lonStr); - System.out.println("INSERT INTO bus_stop (lat,lon,fixed,area,gmlid,up) VALUES ('"+ latStr +"','"+ lonStr +"',0,"+ areacode +",'"+ idStr +"',2)"); + System.out.println("INSERT INTO "+ TABLE_NAME +" (lat,lon,fixed,fixed1,area,gmlid,up) VALUES ('"+ latStr +"','"+ lonStr +"',0,0,"+ areacode +",'"+ idStr +"',2)"); ps6.setDouble(1, lat); ps6.setDouble(2, lon); @@ -262,74 +207,6 @@ } } } - - /** - * - * @param con - * @param node - * @param iFileName // ソースファイル名（拡張子を含まない） - * @throws IOException - * @throws SQLException - */ - /* - public static void showED01(Connection con, Node node, String iFileName) throws IOException, SQLException { - String gmlidStr = ""; - String nameStr = ""; - PreparedStatement ps1 = con.prepareStatement("SELECT gmlid FROM bus_stop WHERE gmlid=?"); - PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (gmlid,name,ifile) VALUES (?,?,?)"); - try { - ArrayList bris = new ArrayList<>(); - NodeList nodes = node.getChildNodes(); - for (int i=0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - switch (node2.getNodeName()) { - case "ksj:POS": - NamedNodeMap nodeMap = node2.getAttributes(); - if (null != nodeMap) { - for ( int j=0; j < nodeMap.getLength(); j++ ) { - if (nodeMap.item(j).getNodeName().equals("gmlid")) { - gmlidStr = nodeMap.item(j).getNodeValue(); - System.out.println("found gmlid='"+ gmlidStr +"'"); - break; - } - } - } break; - case "ksj:BSN": - nameStr = node2.getTextContent(); - break; - case "ksj:BRI": - String[] rtn = anaComm(node2); - if (rtn != null) { - bris.add(rtn); - } break; - default: - break; - } - } - - // gmlid と nameStr をデータベースに格納する - boolean insert = true; - ps1.setString(1, gmlidStr); - try (ResultSet rset = ps1.executeQuery()) { - if (rset.next()) { - insert = false; - } - } - - if (insert) { - ps2.setString(1, gmlidStr); - ps2.setString(2, nameStr); - ps2.setString(3, iFileName); - System.out.println("INSERT INTO bus_stop (gmlid,name,ifile) VALUES ('"+ gmlidStr +"','"+ nameStr +"','"+ iFileName +"')"); - ps2.executeUpdate(); - } - } - finally { - ps1.close(); - ps2.close(); - } - } - */ /** * @@ -409,34 +286,6 @@ } } - /* - public static String[] anaComm(Node briNode) { - String[] rtn = new String[3]; - rtn[0] = ""; // corp type - rtn[1] = ""; // course name - rtn[2] = ""; // corp name - - NodeList nodes = briNode.getChildNodes(); - for (int i=0; i < nodes.getLength(); i++) { - Node node2 = nodes.item(i); - switch (node2.getNodeName()) { - case "ksj:BSC": - rtn[0] = node2.getTextContent(); - break; - case "ksj:BLN": - rtn[1] = node2.getTextContent(); - break; - case "ksj:BOC": - rtn[2] = node2.getTextContent(); - break; - default: - break; - } - } - return rtn; - } - */ - /** * * @@ -497,14 +346,8 @@ String createSt; // 'table.BUS_STOP'を新規に作る - createSt = "CREATE TABLE bus_stop (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT bus_stop_pk PRIMARY KEY(gmlid, area));"; + 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));"; create(conHsql, createSt); - - //createSt = "CREATE TABLE existing_data (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(gmlid, lat, lon));"; - //create(conHsql, createSt); - - //createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);"; - //create(conHsql, createSt); } public static void create(Connection con, String createsql) throws SQLException { @@ -530,9 +373,7 @@ * @throws org.xml.sax.SAXException */ public static void dropTable (Connection conHsql) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - drop(conHsql, "bus_stop"); - //drop(conHsql, "existing_data"); - //drop(conHsql, "coverage"); + drop(conHsql, TABLE_NAME); } public static void drop(Connection con, String tableName) throws SQLException { diff --git a/src/osm/jp/coverage/busstop/DbExistBusstop.java b/src/osm/jp/coverage/busstop/DbExistBusstop.java index f436d1d..23a43c9 100644 --- a/src/osm/jp/coverage/busstop/DbExistBusstop.java +++ b/src/osm/jp/coverage/busstop/DbExistBusstop.java @@ -115,7 +115,7 @@ queryText.append(");"); queryText.append("out;"); HttpPOST.getQuery(queryText.toString()); - HttpPOST.readExistingFile(conHsql); + HttpPOST.readExistingFile(conHsql, 2); } } } \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java index 492d365..469f5be 100644 --- a/src/osm/jp/coverage/fuel/DbExist.java +++ b/src/osm/jp/coverage/fuel/DbExist.java @@ -93,7 +93,7 @@ queryText.append(");"); queryText.append("out;"); HttpPOST.getQuery(queryText.toString()); - HttpPOST.readExistingFile(con); + HttpPOST.readExistingFile(con, 1); } }