diff --git a/src/osm/jp/coverage/busstop/Busstop.java b/src/osm/jp/coverage/busstop/Busstop.java index 4f50bf2..d3d6697 100644 --- a/src/osm/jp/coverage/busstop/Busstop.java +++ b/src/osm/jp/coverage/busstop/Busstop.java @@ -164,9 +164,9 @@ readExistingFile(con, existingFile); PreparedStatement ps1 = con.prepareStatement("SELECT idref,name,lat,lon FROM bus_stop WHERE area=?"); - PreparedStatement ps2 = con.prepareStatement("SELECT count(idref) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); + PreparedStatement ps2 = con.prepareStatement("SELECT SUM(score) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)"); PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?"); - PreparedStatement ps4 = con.prepareStatement("SELECT count(idref) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (name = ?)"); + PreparedStatement ps4 = con.prepareStatement("SELECT SUM(score) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (name = ?)"); ps1.setInt(1, areacode); ResultSet rset1 = ps1.executeQuery(); while (rset1.next()) { @@ -184,10 +184,10 @@ ps2.setDouble(4, rect.maxlon); ResultSet rset2 = ps2.executeQuery(); if (rset2.next()) { - int count = rset2.getInt(1); - if (count > 0) { - System.out.println("."+ count); - ps3.setInt(1, count); + int score = rset2.getInt(1); + if (score > 0) { + System.out.println("."+ score); + ps3.setInt(1, score); ps3.setString(2, idref); ps3.executeUpdate(); } @@ -202,9 +202,9 @@ ps4.setString(5, name); ResultSet rset4 = ps4.executeQuery(); if (rset4.next()) { - count = rset4.getInt(1); - System.out.println(".."+ count); - ps3.setInt(1, count); + score = rset4.getInt(1); + System.out.println(".."+ score); + ps3.setInt(1, score); ps3.setString(2, idref); ps3.executeUpdate(); } @@ -275,7 +275,9 @@ String latStr = ""; String lonStr = ""; String nameStr = ""; - PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name) VALUES (?,?,?,?)"); + String fixmeStr = ""; + int score = 0; + PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name, score) VALUES (?,?,?,?,?)"); NamedNodeMap nodeMap = node.getAttributes(); if (null != nodeMap) { @@ -310,18 +312,29 @@ if ((key != null) && key.toLowerCase().equals("name") && (value != null)) { nameStr = value; - break; + } + if ((key != null) && key.toLowerCase().equals("fixme") && (value != null)) { + fixmeStr = value; } } } } + + score = 50; + if (nameStr.equals("")) { + score = 1; + } + if (!fixmeStr.equals("")) { + score = 1; + } // idref と nameStr をデータベースに格納する - System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")"+ nameStr); + System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")["+ Integer.toString(score) +"]"+ nameStr); ps5.setString(1, idrefStr); ps5.setDouble(2, Double.parseDouble(latStr)); ps5.setDouble(3, Double.parseDouble(lonStr)); ps5.setString(4, nameStr); + ps5.setInt(5, score); ps5.executeUpdate(); ps5.close(); } diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index db03143..3ca5331 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -52,7 +52,7 @@ create(con, createSt); drop(con, "existing_data"); - createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));"; + createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));"; create(con, createSt); drop(con, "bus_course");