diff --git a/src/osm/jp/postgis/CoverageAll.java b/src/osm/jp/postgis/CoverageAll.java index 6073827..6164641 100644 --- a/src/osm/jp/postgis/CoverageAll.java +++ b/src/osm/jp/postgis/CoverageAll.java @@ -92,7 +92,9 @@ if (i == 0) { // 全国の総計 String whereStr = ((where == null) ? "" : String.format("WHERE (%s)", where)); - PreparedStatement ps2 = conPost.prepareStatement(String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr)); + String sql = String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr); + System.out.println(sql); + PreparedStatement ps2 = conPost.prepareStatement(sql); ResultSet rset2 = ps2.executeQuery(); if (rset2.next()) { cover.denominator = rset2.getLong(1); @@ -100,7 +102,9 @@ rset2.close(); whereStr = "WHERE (fixed > 0)" + ((where == null) ? "" : String.format(" and (%s)", where)); - ps2 = conPost.prepareStatement(String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr)); + sql = String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr); + System.out.println(sql); + ps2 = conPost.prepareStatement(sql); rset2 = ps2.executeQuery(); if (rset2.next()) { cover.molecule = rset2.getLong(1); @@ -110,7 +114,9 @@ else { // 都道府県毎の小計 String whereStr = "WHERE (area=?)" + ((where == null) ? "" : String.format(" and (%s)", where)); - PreparedStatement ps2 = conPost.prepareStatement(String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr)); + String sql = String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr); + System.out.println(sql); + PreparedStatement ps2 = conPost.prepareStatement(sql); ps2.setInt(1, i); ResultSet rset2 = ps2.executeQuery(); if (rset2.next()) { diff --git a/src/osmCoverage_PoliceGet b/src/osmCoverage_PoliceGet deleted file mode 100644 index ad46a09..0000000 --- a/src/osmCoverage_PoliceGet +++ /dev/null @@ -1,34 +0,0 @@ -# dummy -# -# cd /home/yuu/workspace/osmCoverageBin -pwd - -rm -rf database -rm -rf GML_POLICE -rm -f *.jar - - -# git http://surveyor.mydns.jp/gitbucket/git/yuu/osmCoverageBin.git - -curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/hsqldb_2.2.9.jar -curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/postgresql-9.4.1212.jar -curl --user yuu:yuu8844 -O http://localhost:8080/job/hayashiLib/lastSuccessfulBuild/artifact/dist/hayashi.jar -curl --user yuu:yuu8844 -O http://localhost:8080/job/osmCoverage/lastSuccessfulBuild/artifact/osmCoverage.jar - -curl --user yuu:yuu8844 -O http://localhost:8080/job/KSJ_GML/lastSuccessfulBuild/artifact/GML_POLICE.tgz -tar xvzf GML_POLICE.tgz - -rm -rf database -mkdir database - -# make to PostGIS -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.DbPolice -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.DbExist -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Police -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.ToPostgis - - -#git add -f GML_FUEL/*.json GML_FUEL/*.csv t_fuel.carto.csv -#git commit -a -m "coverage fuel update $(date +%Y-%m-%d_%H:%M)" -#git push origin HEAD:master - diff --git a/src/osmCoverage_PoliceGet.sh b/src/osmCoverage_PoliceGet.sh new file mode 100644 index 0000000..ad46a09 --- /dev/null +++ b/src/osmCoverage_PoliceGet.sh @@ -0,0 +1,34 @@ +# dummy +# +# cd /home/yuu/workspace/osmCoverageBin +pwd + +rm -rf database +rm -rf GML_POLICE +rm -f *.jar + + +# git http://surveyor.mydns.jp/gitbucket/git/yuu/osmCoverageBin.git + +curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/hsqldb_2.2.9.jar +curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/postgresql-9.4.1212.jar +curl --user yuu:yuu8844 -O http://localhost:8080/job/hayashiLib/lastSuccessfulBuild/artifact/dist/hayashi.jar +curl --user yuu:yuu8844 -O http://localhost:8080/job/osmCoverage/lastSuccessfulBuild/artifact/osmCoverage.jar + +curl --user yuu:yuu8844 -O http://localhost:8080/job/KSJ_GML/lastSuccessfulBuild/artifact/GML_POLICE.tgz +tar xvzf GML_POLICE.tgz + +rm -rf database +mkdir database + +# make to PostGIS +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.DbPolice +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.DbExist +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Police +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.ToPostgis + + +#git add -f GML_FUEL/*.json GML_FUEL/*.csv t_fuel.carto.csv +#git commit -a -m "coverage fuel update $(date +%Y-%m-%d_%H:%M)" +#git push origin HEAD:master + diff --git a/src/osmCoverage_PoliceJson b/src/osmCoverage_PoliceJson deleted file mode 100644 index b9bd0dd..0000000 --- a/src/osmCoverage_PoliceJson +++ /dev/null @@ -1,46 +0,0 @@ -### dummy -pwd - -# git http://surveyor.mydns.jp/gitbucket/git/yuu/osmCoverageBin.git - -curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/hsqldb_2.2.9.jar -curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/postgresql-9.4.1212.jar -curl --user yuu:yuu8844 -O http://localhost:8080/job/hayashiLib/lastSuccessfulBuild/artifact/dist/hayashi.jar -curl --user yuu:yuu8844 -O http://localhost:8080/job/osmCoverage/lastSuccessfulBuild/artifact/osmCoverage.jar - -### export from PostGIS -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.ToGeoJSON - -ls -l -rm -rf vtile -mkdir -m +w vtile -mv ./GML_POLICE/police?.json vtile -tar czvf policeJSON.tgz vtile/*.json - -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.ToGeoJSON -slim - -# output file "coverage.police2.csv" and "coverage.police2.json" -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage2 - -# output file "coverage.police4.csv" and "coverage.police4.json" -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage4 - -# output file "coverage.police5.csv" and "coverage.police5.json" -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage5 - -ls -l -mv coverage.police.json GML_POLICE -mv coverage.police.csv GML_POLICE -mv police?.json GML_POLICE -mv police.json GML_POLICE -tar czvf coverage.policeJSON.tgz GML_POLICE/*.json GML_POLICE/*.csv - -git config --global user.name "hayashi" -git config --global user.email hayashi.yuu@gmail.com -git add *.json -git add GML_POLICE/*.json -git commit --allow-empty -m "coverage police update $(date +%Y-%m-%d_%H:%M)" - -git remote -v -#git push gitbucket HEAD:master - diff --git a/src/osmCoverage_PoliceJson.sh b/src/osmCoverage_PoliceJson.sh new file mode 100644 index 0000000..b9bd0dd --- /dev/null +++ b/src/osmCoverage_PoliceJson.sh @@ -0,0 +1,46 @@ +### dummy +pwd + +# git http://surveyor.mydns.jp/gitbucket/git/yuu/osmCoverageBin.git + +curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/hsqldb_2.2.9.jar +curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/gitbucket/yuu/osmCoverage/raw/master/lib/postgresql-9.4.1212.jar +curl --user yuu:yuu8844 -O http://localhost:8080/job/hayashiLib/lastSuccessfulBuild/artifact/dist/hayashi.jar +curl --user yuu:yuu8844 -O http://localhost:8080/job/osmCoverage/lastSuccessfulBuild/artifact/osmCoverage.jar + +### export from PostGIS +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.ToGeoJSON + +ls -l +rm -rf vtile +mkdir -m +w vtile +mv ./GML_POLICE/police?.json vtile +tar czvf policeJSON.tgz vtile/*.json + +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.ToGeoJSON -slim + +# output file "coverage.police2.csv" and "coverage.police2.json" +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage2 + +# output file "coverage.police4.csv" and "coverage.police4.json" +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage4 + +# output file "coverage.police5.csv" and "coverage.police5.json" +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage5 + +ls -l +mv coverage.police.json GML_POLICE +mv coverage.police.csv GML_POLICE +mv police?.json GML_POLICE +mv police.json GML_POLICE +tar czvf coverage.policeJSON.tgz GML_POLICE/*.json GML_POLICE/*.csv + +git config --global user.name "hayashi" +git config --global user.email hayashi.yuu@gmail.com +git add *.json +git add GML_POLICE/*.json +git commit --allow-empty -m "coverage police update $(date +%Y-%m-%d_%H:%M)" + +git remote -v +#git push gitbucket HEAD:master +