diff --git a/src/db.fuel.class.violet.html b/src/db.fuel.class.violet.html index 3fc4275..80a9737 100644 --- a/src/db.fuel.class.violet.html +++ b/src/db.fuel.class.violet.html @@ -155,7 +155,7 @@ + idref: VARCHAR(12) NOT NULL -- gid: VARCHAR +- gmlid: VARCHAR(12) - area: INT - lat: DOUBLE - lon: DOUBLE @@ -189,7 +189,7 @@ table.FUEL - + gid: VARCHAR + + gmlid: VARCHAR(12) + area: INT - idref: VARCHAR(12) NOT NULL - lat: DOUBLE @@ -245,6 +245,7 @@ + gid: SERIAL PRIMARY KEY idref: text fixed: integer + gmlid: text area: integer geom: GEOMETRY(POINT, 4612) @@ -372,684 +373,700 @@ ]]>

- embedded diagram image + embedded diagram image \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/ToCartoCSV.java b/src/osm/jp/coverage/fuel/ToCartoCSV.java index d47dd33..90692f6 100644 --- a/src/osm/jp/coverage/fuel/ToCartoCSV.java +++ b/src/osm/jp/coverage/fuel/ToCartoCSV.java @@ -36,15 +36,16 @@ ow.write(line); ow.newLine(); - PreparedStatement ps8 = con.prepareStatement("SELECT idref,geom,fixed FROM t_fuel"); + PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,area,geom,fixed FROM t_fuel"); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { - String idref = rset8.getString("idref"); + String gmlid = rset8.getString("gmlid"); String geom = rset8.getString("geom"); int score = rset8.getInt("fixed"); + int area = rset8.getInt("area"); score = (score > 1 ? 2 : score); - line = ""+ idref +","+ score +","+ geom +""; + line = ""+ gmlid +","+ area +","+ score +","+ geom +""; System.out.println(line); ow.write(line); ow.newLine(); diff --git a/src/osm/jp/coverage/fuel/ToPostgis.java b/src/osm/jp/coverage/fuel/ToPostgis.java index 7f7843a..a0ac626 100644 --- a/src/osm/jp/coverage/fuel/ToPostgis.java +++ b/src/osm/jp/coverage/fuel/ToPostgis.java @@ -89,15 +89,17 @@ public static void transportUpdate (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { toInsert(conHsql, conPost); - try ( PreparedStatement ps1 = conHsql.prepareStatement("SELECT gid,fixed1 FROM FUEL where up=1"); - PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_FUEL set fixed=? WHERE gid=?")) { + try ( PreparedStatement ps1 = conHsql.prepareStatement("SELECT gmlid,area,fixed1 FROM FUEL where up=1"); + PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_FUEL set fixed=? WHERE gmlid=? AND area=?")) { try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { - long gid = rset1.getLong("gid"); + String gmlid = rset1.getString("gmlid"); + int area = rset1.getInt("area"); int fixed1 = rset1.getInt("fixed1"); ps2.setInt(1, fixed1); - ps2.setLong(2, gid); - System.out.println("UPDATE t_FUEL set fixed="+ fixed1 +" WHERE gid="+ gid); + ps2.setString(2, gmlid); + ps2.setInt(3, area); + System.out.println("UPDATE t_FUEL set fixed="+ fixed1 +" WHERE gmlid="+ gmlid +" AND area="+ area); ps2.executeUpdate(); } } @@ -118,7 +120,7 @@ double lat = rset1.getDouble("lat"); double lon = rset1.getDouble("lon"); - String sqlStr = "INSERT INTO t_FUEL (idref,fixed,area,geom) VALUES ('"+ gmlid +"',"+ fixed1 +","+ area +",ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))"; + String sqlStr = "INSERT INTO t_FUEL (gmlid,fixed,area,geom) VALUES ('"+ gmlid +"',"+ fixed1 +","+ area +",ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))"; try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) { System.out.println(sqlStr); ps.executeUpdate(); diff --git a/src/postgis.properties b/src/postgis.properties index 04ba2fe..5886ee0 100644 --- a/src/postgis.properties +++ b/src/postgis.properties @@ -1,4 +1,4 @@ db_driver=org.postgresql.Driver -db_url=jdbc:postgresql://surveyor.mydns.jp:5432/gisdb +db_url=jdbc:postgresql://localhost:5432/gisdb db_user=gisuser db_passwd=gisuser diff --git a/src/t_FUEL.sql b/src/t_FUEL.sql index 2a8cf47..a93854b 100644 --- a/src/t_FUEL.sql +++ b/src/t_FUEL.sql @@ -3,11 +3,17 @@ db_url=jdbc:postgresql://surveyor.mydns.jp:5432/gisdb db_user=gisuser db_passwd=gisuser +---<< osmdb: postgres >>--- +db_driver=org.postgresql.Driver +db_url=jdbc:postgresql://localhost:5432/gisdb +db_user=gisuser +db_passwd=gisuser ------------------------- DROP TABLE t_FUEL; CREATE TABLE t_FUEL ( gid SERIAL PRIMARY KEY, idref text, + gmlid text, fixed integer, area integer, geom GEOMETRY(POINT, 4612)