diff --git a/src/ImportGML_FUEL.sh b/src/ImportGML_FUEL.sh index 1aca98b..d3634d2 100755 --- a/src/ImportGML_FUEL.sh +++ b/src/ImportGML_FUEL.sh @@ -4,5 +4,6 @@ java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbExist java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Fuel java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToPostgis +java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToCartoCSV java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.csv -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.json \ No newline at end of file +java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.json diff --git a/src/ImportGML_FUEL_update.sh b/src/ImportGML_FUEL_update.sh index 4d46ce9..4b4961d 100644 --- a/src/ImportGML_FUEL_update.sh +++ b/src/ImportGML_FUEL_update.sh @@ -3,8 +3,10 @@ java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbFuel -update -drop java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbExist java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Fuel -java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToPostgis +java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToPostgis -update java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToCartoCSV +java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.csv +java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.json # java -cp hsqldb_2.2.9.jar org.hsqldb.util.DatabaseManager # db_driver=org.hsqldb.jdbcDriver diff --git a/src/fuel.activity.violet.html b/src/fuel.activity.violet.html index afcb3bb..615ad10 100644 --- a/src/fuel.activity.violet.html +++ b/src/fuel.activity.violet.html @@ -171,7 +171,7 @@ - + 1 @@ -251,7 +251,7 @@ - + 1 @@ -427,7 +427,7 @@ - + 1 @@ -452,15 +452,86 @@ + + + + + 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 @@ -469,13 +540,13 @@ - + - - - - + + + + 1 @@ -484,13 +555,13 @@ - + - - - - + + + + 1 @@ -499,13 +570,13 @@ - + - - - - + + + + 1 @@ -514,13 +585,13 @@ - + - - - - + + + + 1 @@ -529,13 +600,13 @@ - + - - - - + + + + 1 @@ -544,13 +615,13 @@ - + - - - - + + + + 1 @@ -559,13 +630,13 @@ - + - - - - + + + + 1 @@ -574,31 +645,22 @@ - - - - - - - - 1 - - + - - - - + + + + 1 - + - - - - + + + + 1 @@ -607,13 +669,13 @@ - + - - - - + + + + 1 @@ -622,22 +684,22 @@ - + - - - - + + + + 1 - + - - - - + + + + 1 @@ -646,22 +708,22 @@ - + - - - - + + + + 1 - + - - - - + + + + 1 @@ -670,13 +732,13 @@ - + - - - - + + + + 1 @@ -685,13 +747,13 @@ - + - - - - + + + + 1 @@ -700,13 +762,13 @@ - + - - - - + + + + 1 @@ -715,13 +777,13 @@ - + - - - - + + + + 1 @@ -730,13 +792,13 @@ - + - - - - + + + + 1 @@ -745,15 +807,15 @@ - + - - - - + + + + - + 1 @@ -762,13 +824,13 @@ - + - - - - + + + + 1 @@ -777,22 +839,22 @@ - + - - - - + + + + 1 - + - - - - + + + + 1 @@ -801,13 +863,13 @@ - + - - - - + + + + 1 @@ -816,15 +878,15 @@ - + - - - - + + + + - + 1 @@ -833,13 +895,13 @@ - + - - - - + + + + 1 @@ -848,13 +910,13 @@ - + - - - - + + + + 1 @@ -863,13 +925,13 @@ - + - - - - + + + + 1 @@ -878,13 +940,13 @@ - + - - - - + + + + 1 @@ -893,989 +955,1167 @@ + + + + + + + + 1 + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + + + + + + + + + + + 1 + + + + + + + + + 1 + ]]>

- embedded diagram image + embedded diagram image \ No newline at end of file diff --git a/src/osm/jp/coverage/fuel/Coverage.java b/src/osm/jp/coverage/fuel/Coverage.java index 70110f9..1a1f7e1 100644 --- a/src/osm/jp/coverage/fuel/Coverage.java +++ b/src/osm/jp/coverage/fuel/Coverage.java @@ -1,12 +1,10 @@ package osm.jp.coverage.fuel; -import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; -import java.io.InputStreamReader; import java.io.OutputStreamWriter; import java.io.UnsupportedEncodingException; import java.sql.Connection; diff --git a/src/osm/jp/coverage/fuel/ToCartoCSV.java b/src/osm/jp/coverage/fuel/ToCartoCSV.java index e474855..0e17afb 100644 --- a/src/osm/jp/coverage/fuel/ToCartoCSV.java +++ b/src/osm/jp/coverage/fuel/ToCartoCSV.java @@ -2,15 +2,11 @@ import java.io.BufferedWriter; import java.io.File; -import java.io.FileNotFoundException; import java.io.FileOutputStream; -import java.io.IOException; import java.io.OutputStreamWriter; -import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; -import java.sql.SQLException; import jp.co.areaweb.tools.database.DatabaseTool; diff --git a/src/osm/jp/coverage/fuel/ToPostgis.java b/src/osm/jp/coverage/fuel/ToPostgis.java index 047601a..fc9eac4 100644 --- a/src/osm/jp/coverage/fuel/ToPostgis.java +++ b/src/osm/jp/coverage/fuel/ToPostgis.java @@ -25,7 +25,15 @@ * */ public class ToPostgis { + public static boolean UPDATE = false; + public static void main(String[] argv) throws Exception { + for (String arg : argv) { + if (arg.toUpperCase().equals("-UPDATE")) { + ToPostgis.UPDATE = true; + } + } + Connection conHsql = null; Connection conPost = null; try { @@ -33,6 +41,8 @@ conHsql = DatabaseTool.openDb("database"); conPost = DatabaseTool.openDb("postgis"); + if (!ToPostgis.UPDATE) { + } transport(conHsql, conPost); } @@ -60,7 +70,46 @@ * @throws org.xml.sax.SAXException */ public static void transport (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - try (PreparedStatement ps1 = conHsql.prepareStatement("SELECT idref,area,fixed1,lat,lon FROM FUEL where up=2")) { + if (ToPostgis.UPDATE) { + transportUpdate(conHsql, conPost); + } + else { + transportNew(conHsql, conPost); + } + } + + public static void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + try (PreparedStatement ps2 = conPost.prepareStatement("DELETE FROM t_FUEL")) { + System.out.println("DELETE FROM t_FUEL;"); + ps2.executeUpdate(); + } + toInsert(conHsql, conPost); + } + + 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 (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + long gid = rset1.getLong("gid"); + 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.executeUpdate(); + } + } + } + } + + public static void toInsert (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + String sql = "SELECT idref,area,fixed1,lat,lon FROM FUEL"; + if (ToPostgis.UPDATE) { + sql += " where up=2"; + } + try (PreparedStatement ps1 = conHsql.prepareStatement(sql)) { try (ResultSet rset1 = ps1.executeQuery()) { while (rset1.next()) { String idref = rset1.getString("idref"); @@ -77,19 +126,5 @@ } } } - - 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 (ResultSet rset1 = ps1.executeQuery()) { - while (rset1.next()) { - long gid = rset1.getLong("gid"); - 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.executeUpdate(); - } - } - } } } diff --git a/src/t_FUEL.sql b/src/t_FUEL.sql index 1d8f86b..2a8cf47 100644 --- a/src/t_FUEL.sql +++ b/src/t_FUEL.sql @@ -13,7 +13,7 @@ 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));