diff --git a/src/coverage_POLICE.sh b/src/coverage_POLICE.sh new file mode 100755 index 0000000..b8f3e60 --- /dev/null +++ b/src/coverage_POLICE.sh @@ -0,0 +1,60 @@ +# dummy +cd /home/yuu/workspace/osmCoverageBin +pwd + +git config --global user.name "hayashi" +git config --global user.email hayashi.yuu@gmail.com +git fetch +git pull gitbucket master + +#rm -rf GML_POSTOFFICE +#mkdir GML_POSTOFFICE +#cd GML_POSTOFFICE +#curl --user yuu:yuu8844 -O http://surveyor.mydns.jp/coverage/GML_POLICE/P18-12-??.xml + +#cd .. +pwd +curl --user yuu:yuu8844 -O http://localhost:8080/job/osmCoverage/lastSuccessfulBuild/artifact/osmCoverage.jar +cp ../hayashi/dist/hayashi.jar . + + +# 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 + + + +# +# +# osmCoverage_PoliceJson +# + +## export from PostGIS to JSON +### export from PostGIS +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage2 +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage4 +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.police.Coverage5 +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 +cp ./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 + +tar czvf coverage.postofficeJSON.tgz ./GML_POLICE/*.json ./GML_POLICE/*.csv + +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 master + + +# +# +# osmCoverage_PostofficeTile +# diff --git a/src/coverage_POLICE_after.sh b/src/coverage_POLICE_after.sh new file mode 100644 index 0000000..21f443a --- /dev/null +++ b/src/coverage_POLICE_after.sh @@ -0,0 +1,92 @@ +# make Vector Tile +export PATH="/home/linuxbrew/.linuxbrew/bin:$PATH" +export MANPATH="/home/linuxbrew/.linuxbrew/share/man:$MANPATH" +export INFOPATH="/home/linuxbrew/.linuxbrew/share/info:$INFOPATH" +export CXX=g++-5 +#brew install tippecanoe + +rm -rf vtile +mkdir vtile + +curl --user yuu:yuu8844 -O http://localhost:8080/job/osmCoverage_PoliceJson/lastSuccessfulBuild/artifact/policeJSON.tgz + +tar zxvf policeJSON.tgz + +#----------- +# ~/vtile +# +cd vtile +ls -l + +tippecanoe -l police_map0 -rg -z16 -Z6 -o police0.mbtiles police0.json +tippecanoe -l police_map1 -rg -z16 -Z6 -o police1.mbtiles police1.json +tippecanoe -l police_map2 -rg -z16 -Z6 -o police2.mbtiles policw2.json + +git clone git://github.com/mapbox/mbutil.git + +#----------- +# ~/vtile/mbutil +# +cd mbutil +./mb-util --image_format=pbf ../police0.mbtiles tilePolice0 +./mb-util --image_format=pbf ../police1.mbtiles tilePolice1 +./mb-util --image_format=pbf ../police2.mbtiles tilePolice2 + +pwd +ls -l + +#----------- +# ~/vtile/mbutil/tilePolice0 +# +cd tilePolice0 +pwd +ls -l +gzip -d -v -r -S .pbf * +find . -type f -exec mv -v '{}' '{}'.pbf \; + +#----------- +# ~/vtile/mbutil +# +cd .. +pwd +ls -l +tar czvf ../../tilePolice0.tgz tilePolice0 +pwd +ls -l + +#----------- +# ~/vtile/mbutil/tilePolice1 +# +cd tilePolice1 +pwd +ls -l +gzip -d -v -r -S .pbf * +find . -type f -exec mv -v '{}' '{}'.pbf \; + +#----------- +# ~/vtile/mbutil +# +cd .. +pwd +ls -l +tar czvf ../../tilePolice1.tgz tilePolice1 +pwd + +#----------- +# ~/vtile/mbutil/tilePolice2 +# +cd tilePolice2 +pwd +ls -l +gzip -d -v -r -S .pbf * +find . -type f -exec mv -v '{}' '{}'.pbf \; + +#----------- +# ~/vtile/mbutil +# +cd .. +pwd +ls -l +tar czvf ../../tilePolice2.tgz tilePolice2 + +#----------- diff --git a/src/coverage_POSTOFFICE.sh b/src/coverage_POSTOFFICE.sh index 5301313..1b99898 100644 --- a/src/coverage_POSTOFFICE.sh +++ b/src/coverage_POSTOFFICE.sh @@ -42,7 +42,7 @@ cp ./GML_POSTOFFICE/postoffice?.json vtile tar czvf postofficeJSON.tgz vtile/*.json -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToGeoJSON -slim +java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.postoffice.ToGeoJSON -slim tar czvf coverage.postofficeJSON.tgz ./GML_POSTOFFICE/*.json ./GML_POSTOFFICE/*.csv diff --git a/src/db.police.class.violet.html b/src/db.police.class.violet.html new file mode 100644 index 0000000..af71a34 --- /dev/null +++ b/src/db.police.class.violet.html @@ -0,0 +1,890 @@ + + + + + + + + + This file was generated with Violet UML Editor 2.1.0. +   ( View Source / Download Violet ) +
+
+ +
+
+ embedded diagram image + + \ No newline at end of file diff --git a/src/osm/jp/api/Osmdb.java b/src/osm/jp/api/Osmdb.java index d236058..dac71ad 100644 --- a/src/osm/jp/api/Osmdb.java +++ b/src/osm/jp/api/Osmdb.java @@ -46,7 +46,7 @@ //sql(con, "DROP INDEX "+ HttpPOST.TABLE_NAME +"_index;"); sql(con, "DROP TABLE IF EXISTS AREA_NODE CASCADE"); - // 'table.FUEL_EXIST'を新規に作る + // 'table.OSM_EXIST'を新規に作る createSt = "CREATE TABLE "+ Osmdb.TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));"; Db.updateSQL(con, createSt); createSt = "CREATE INDEX "+ Osmdb.TABLE_NAME +"_index ON "+ Osmdb.TABLE_NAME +" (lat, lon);"; @@ -83,7 +83,7 @@ /** - * 'table.FUEL'の内容をCSV形式にして標準出力に出力する + * 'table.OSM_EXIST'の内容をCSV形式にして標準出力に出力する * @param hsqldb * @throws java.sql.SQLException */ diff --git a/src/osm/jp/coverage/police/DbPolice.java b/src/osm/jp/coverage/police/DbPolice.java index a65e110..a981345 100644 --- a/src/osm/jp/coverage/police/DbPolice.java +++ b/src/osm/jp/coverage/police/DbPolice.java @@ -61,12 +61,14 @@ File folder = new File("GML_POLICE"); for (File xmlfile : folder.listFiles()) { // GMLディレクトリを処理する - int areacode = Integer.parseInt(xmlfile.getName().substring(GML_XML_PREFIX.length(), GML_XML_PREFIX.length()+2)); + if (xmlfile.getName().toUpperCase().endsWith(".XML")) { + int areacode = Integer.parseInt(xmlfile.getName().substring(GML_XML_PREFIX.length(), GML_XML_PREFIX.length()+2)); - // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。 - if (checkFile(xmlfile, areacode)) { - importPOI(conHsql, xmlfile, areacode); - fcounter++; + // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。 + if (checkFile(xmlfile, areacode)) { + importPOI(conHsql, xmlfile, areacode); + fcounter++; + } } } System.out.println("["+ fcounter +"]つのGMLファイルをインポートしました。"); @@ -251,7 +253,7 @@ ps.setInt(6, areacode); // 都道府県コード ps.setInt(7, 2); // up ps.setInt(8, 0); // fixed1 - System.out.println("INSERT INTO "+ TABLE_NAME +"(gid=0, fixed=0, idref='"+ gmlidStr +"', name='"+ nameStr +"', area="+ 0 +", up=2, fixed1=0)"); + System.out.println("INSERT INTO "+ TABLE_NAME +"(gid=0, fixed=0, idref='"+ gmlidStr +"', name='"+ nameStr +"', area="+ areacode +", up=2, fixed1=0)"); ps.executeUpdate(); } } @@ -265,10 +267,22 @@ public static void create(Connection conHsql) throws SQLException { String createSt; - createSt = "CREATE TABLE "+ TABLE_NAME2 +" (idref VARCHAR(12) NOT NULL, area INT, lat DOUBLE, lon DOUBLE)"; + createSt = "DROP INDEX "+ TABLE_NAME +".id1 IF EXISTS"; + Db.updateSQL(conHsql, createSt); + + createSt = "DROP INDEX "+ TABLE_NAME2 +".id2 IF EXISTS"; + Db.updateSQL(conHsql, createSt); + + createSt = "DROP TABLE "+ TABLE_NAME +" IF EXISTS"; + Db.updateSQL(conHsql, createSt); + + createSt = "DROP TABLE "+ TABLE_NAME2 +" IF EXISTS"; + Db.updateSQL(conHsql, createSt); + + createSt = "CREATE TABLE "+ TABLE_NAME2 +" (idref VARCHAR(12) NOT NULL, area INT, lat DOUBLE, lon DOUBLE)"; Db.updateSQL(conHsql, createSt); - createSt = "CREATE UNIQUE INDEX id2 ON "+ TABLE_NAME2 +" (idref, area)"; + createSt = "CREATE INDEX id2 ON "+ TABLE_NAME2 +" (idref, area)"; Db.updateSQL(conHsql, createSt); createSt = "CREATE TABLE "+ TABLE_NAME +" (gid INT, idref VARCHAR(12) NOT NULL, name VARCHAR(255), code INT, area INT, lat DOUBLE, lon DOUBLE, up INT, fixed INT, fixed1 INT)"; diff --git a/src/osm/jp/coverage/police/Police.java b/src/osm/jp/coverage/police/Police.java index d9c384e..4ba782e 100644 --- a/src/osm/jp/coverage/police/Police.java +++ b/src/osm/jp/coverage/police/Police.java @@ -16,6 +16,7 @@ import jp.co.areaweb.tools.database.*; import osm.jp.api.HttpPOST; import osm.jp.api.Japan; +import osm.jp.api.Osmdb; public class Police { @@ -68,7 +69,7 @@ */ public Police(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException { String sql4 = "UPDATE "+ DbPolice.TABLE_NAME +" SET fixed1=0"; - String sql1 = "SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME; + String sql1 = "SELECT idref,lat,lon,score FROM "+ Osmdb.TABLE_NAME; String sql2 = "SELECT idref,lat,lon,fixed, area FROM "+ DbPolice.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)"; String sql3 = "UPDATE "+ DbPolice.TABLE_NAME +" SET fixed1=? WHERE idref=? and area=?"; String sql5 = "UPDATE "+ DbPolice.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"; diff --git a/test/osm/jp/coverage/police/DbExistTest.java b/test/osm/jp/coverage/police/DbExistTest.java index c69a303..7df1ab4 100644 --- a/test/osm/jp/coverage/police/DbExistTest.java +++ b/test/osm/jp/coverage/police/DbExistTest.java @@ -83,10 +83,16 @@ } // ウェイ: 192565027 海老名警察署 場所: 35.4454680, 139.3915960 - ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='161877397'"); + ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='192565027'"); try (ResultSet rset1 = ps1.executeQuery()) { if (rset1.next()) { assertThat(rset1.getInt("score"), is(50)); + double lat = rset1.getDouble("lat"); + assertTrue(lat > 35.44); + assertTrue(lat < 35.45); + double lon = rset1.getDouble("lon"); + assertTrue(lon > 139.38); + assertTrue(lon < 140.00); assertThat(checkRenge(rset1, "35.4454680", "139.3915960"), is(true)); } else { @@ -119,8 +125,8 @@ boolean checkRenge(double d1, String str) throws SQLException { double base = Double.parseDouble(str); - double up = d1 + 0.0000005D; - double down = d1 - 0.0000005D; + double up = d1 + 0.0001D; + double down = d1 - 0.0001D; boolean ret = true; if (Double.compare(base, up) > 0) { ret = false; diff --git a/test/osm/jp/coverage/police/DbPoliceTest.java b/test/osm/jp/coverage/police/DbPoliceTest.java index 517cd58..fb02c8a 100644 --- a/test/osm/jp/coverage/police/DbPoliceTest.java +++ b/test/osm/jp/coverage/police/DbPoliceTest.java @@ -40,18 +40,6 @@ ex.printStackTrace(); fail(ex.toString()); } - } - - @Test - public void test02_police() { - try { - String[] args = new String[0]; - DbPolice.main(args); - } - catch (IOException | ClassNotFoundException | SQLException | ParserConfigurationException | SAXException ex) { - ex.printStackTrace(); - fail(ex.toString()); - } File dir = new File("database"); assertTrue(dir.exists()); diff --git a/test/osm/jp/coverage/police/PoliceTest.java b/test/osm/jp/coverage/police/PoliceTest.java index c8d2f05..9bf4ba1 100644 --- a/test/osm/jp/coverage/police/PoliceTest.java +++ b/test/osm/jp/coverage/police/PoliceTest.java @@ -1,20 +1,13 @@ package osm.jp.coverage.police; import java.io.IOException; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; import java.sql.SQLException; -import java.util.logging.Level; -import java.util.logging.Logger; import javax.xml.parsers.ParserConfigurationException; import javax.xml.transform.TransformerException; -import jp.co.areaweb.tools.database.DatabaseTool; import org.junit.*; import static org.junit.Assert.*; import org.junit.runners.MethodSorters; import org.xml.sax.SAXException; -import osm.jp.api.HttpPOST; import osm.jp.coverage.PoiTest; @FixMethodOrder (MethodSorters.NAME_ASCENDING) @@ -28,7 +21,7 @@ } @Test - public void test00_Fuel_main() { + public void test00_Police_main() { try { String[] args = new String[0]; Police.main(args); @@ -40,22 +33,22 @@ } /** - * OSM: removed:amenity=fuel 35.4305614, 139.3662339 - * GML: 14, n00375 + * OSM: amenity=police ノード: 海老名警察署 (1420826312) 場所: 35.4454680, 139.3915960 + * GML: pt_psn51 14, n00375 */ @Test public void test01_dataread() { - double lat = 35.43035D; // 35.43035 35.4305614, 139.3662339 - double lon = 139.36622D; //139.36622 + double lat = 35.4454680D; + double lon = 139.3915960D; String idrefs = getRefid(DbPolice.TABLE_NAME, lat, lon, 50); System.out.println(idrefs); } - + /** * OSM: 5338111023 removed:amenity=fuel 35.4305614, 139.3662339 * GML: 14, n00375 */ - @Test + //@Test public void test91_removed() { double lat = 35.43035D; // 35.43035 35.4305614, 139.3662339 double lon = 139.36622D; //139.36622 @@ -67,7 +60,7 @@ * OSM: ノード: 出光 (1600343871) 35.4214195, 139.3600994 * GML: 14, n00375 */ - @Test + //@Test public void test92_normal() { double lat = 35.4214195D; double lon = 139.3600994D;