diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java index 9bce8e3..0bba21b 100644 --- a/src/osm/jp/coverage/fuel/Fuel.java +++ b/src/osm/jp/coverage/fuel/Fuel.java @@ -77,7 +77,7 @@ String sql0 = "UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=?,removed=?"; try (PreparedStatement ps0 = hsqldb.prepareStatement(sql0)) { - System.out.println(sql0); + System.out.println(sql0+ " <-- 0, false"); ps0.setInt(1, 0); ps0.setBoolean(2, false); ps0.executeUpdate(); diff --git a/src/osm/jp/coverage/fuel/ToPostgis.java b/src/osm/jp/coverage/fuel/ToPostgis.java index a0ac626..bb7bb91 100644 --- a/src/osm/jp/coverage/fuel/ToPostgis.java +++ b/src/osm/jp/coverage/fuel/ToPostgis.java @@ -58,7 +58,7 @@ /** * HSQLDB: "SELECT gmlid,area,fixed,lat,lon FROM FUEL" - * POSTGIS "insert into t_FUEL(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));" + * POSTGIS "insert into t_FUEL(gmlid,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));" * * @param conHsql * @param conPost @@ -107,7 +107,7 @@ } public static void toInsert (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - String sql = "SELECT gmlid,area,fixed1,lat,lon FROM "+ DbFuel.TABLE_NAME; + String sql = "SELECT gmlid,area,fixed1,lat,lon,idref FROM "+ DbFuel.TABLE_NAME; if (ToPostgis.UPDATE) { sql += " where up=2"; } @@ -119,10 +119,16 @@ int fixed1 = rset1.getInt("fixed1"); double lat = rset1.getDouble("lat"); double lon = rset1.getDouble("lon"); + String idref = rset1.getString("idref"); - 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))"; + String sqlStr = "INSERT INTO t_FUEL (gmlid,fixed,area,idref,geom) VALUES ('"+ gmlid +"',"+ fixed1 +","+ area +","+ idref +",idref,ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))"; + System.out.println(sqlStr); + sqlStr = "INSERT INTO t_FUEL (gmlid,fixed,area,idref,geom) VALUES (?,?,?,?,ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))"; try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) { - System.out.println(sqlStr); + ps.setString(1, gmlid); + ps.setInt(2, fixed1); + ps.setInt(3, area); + ps.setString(4, idref); ps.executeUpdate(); } } diff --git a/src/t_FUEL.sql b/src/t_FUEL.sql index a93854b..d3c8758 100644 --- a/src/t_FUEL.sql +++ b/src/t_FUEL.sql @@ -5,24 +5,24 @@ db_passwd=gisuser ---<< osmdb: postgres >>--- db_driver=org.postgresql.Driver -db_url=jdbc:postgresql://localhost:5432/gisdb -db_user=gisuser -db_passwd=gisuser +db_url=jdbc:postgresql://localhost:5432/osmdb +db_user=postgres +db_passwd=******* ------------------------- DROP TABLE t_FUEL; CREATE TABLE t_FUEL ( gid SERIAL PRIMARY KEY, idref text, gmlid text, - fixed integer, area integer, + fixed integer, geom GEOMETRY(POINT, 4612) ); CREATE INDEX ix_t_FUEL_geom ON t_fuel USING GiST (geom); DELETE FROM t_FUEL; -insert into t_FUEL (idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612)); -insert into t_FUEL (idref,fixed,area,geom) VALUES('n000002',0,0, ST_GeomFromText('POINT(136.969047121627 35.102834616747685)', 4612)); -insert into t_FUEL (idref,fixed,area,geom) VALUES('n000003',0,0, ST_GeomFromText('POINT(136.9743151095072 35.10462957171922)', 4612)); +insert into t_FUEL (gmlid,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612)); +insert into t_FUEL (gmlid,fixed,area,geom) VALUES('n000002',0,0, ST_GeomFromText('POINT(136.969047121627 35.102834616747685)', 4612)); +insert into t_FUEL (gmlid,fixed,area,geom) VALUES('n000003',0,0, ST_GeomFromText('POINT(136.9743151095072 35.10462957171922)', 4612)); ---<< database: hsqldb >>---