diff --git a/src/osm/jp/coverage/police/DbExist.java b/src/osm/jp/coverage/police/DbExist.java new file mode 100644 index 0000000..723af9a --- /dev/null +++ b/src/osm/jp/coverage/police/DbExist.java @@ -0,0 +1,219 @@ +package osm.jp.coverage.police; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import jp.co.areaweb.tools.csv.CsvRecord; +import jp.co.areaweb.tools.database.*; +import osm.jp.api.Osmdb; + +public class DbExist extends Osmdb { + + /** メイン + * @param args + * @throws Exception + */ + public static void main(String[] args) throws Exception + { + Connection hsqldb = null; + try { + // DB.tableを作成 + hsqldb = DatabaseTool.openDb("database"); + create(hsqldb); + + /** + * 既存のOSM:amenity=policeを読み込む + * OSM OverPassAPI を使って、既存のGSデータを取得して、「HSQLDB.OSM_EXIST」にSTOREする + */ + (new DbExist()).getJapanCapabilities(hsqldb); + + DbExist.export(hsqldb); + } + finally { + if (hsqldb != null) { + DatabaseTool.closeDb(hsqldb); + } + } + } + + /* + Test data: + + select osm_id,amenity,brand,disused,name, + ST_Y(ST_Transform(way,4326)) as lat, + ST_X(ST_Transform(way,4326)) as lon + from planet_osm_point + where amenity='police'; + + select osm_id,amenity,brand,name,way_area, + ST_Astext(ST_Transform(way,4326)) , + ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat, + ST_X(ST_Transform(ST_Centroid(way),4326)) as lon + from planet_osm_polygon + where amenity='police'; + + ST_Centroid(way) + + */ + /** + * + * 
{@code 
+     *  (
+     *    node[disused:amenity=police](35.42,139.39,35.45,139.42);
+     *    way[disused:amenity=police](35.42,139.39,35.45,139.42);
+     *    node[amenity=police](35.42,139.39,35.45,139.42);
+     *    (way[amenity=police](35.42,139.39,35.45,139.42);>;);
+     *  );
+     *  out;
+     * }
+ * + * 
{@code 
+     *  select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='police';
+     * }
+ * + * 
{@code 
+     *  select osm_id,amenity,brand,name,way_area,ST_Astext(ST_Transform(way,4326)) from planet_osm_polygon where amenity='police';
+     * 
+     *  select id,nodes,tags from planet_osm_ways;
+     * }
+ * + * @param hsqldb + * @throws Exception + */ + public void getJapanCapabilities(Connection hsqldb) throws Exception { + // 通常 → ５０ポイント + // FIXMEあり → １ポイント + readExisting(hsqldb, "where amenity='police'", POINT_NO); + readExistingSub(hsqldb, "disused:amenity", "police", POINT_NO); + readExistingSub(hsqldb, "abandoned:amenity", "police", POINT_NO); + readExistingSub(hsqldb, "demolished:amenity", "police", POINT_NO); + readExistingSub(hsqldb, "historic:amenity", "police", POINT_NO); + readExistingSub(hsqldb, "was:amenity", "police", POINT_NO); + readExistingSub(hsqldb, "removed:amenity", "police", POINT_NO); + readExistingSub(hsqldb, "no:amenity", "police", POINT_NO); + } + + /** + * 'table.POLICE'の内容をCSV形式にして標準出力に出力する + * @param hsqldb + * @throws java.sql.SQLException + */ + public static void export(Connection hsqldb) throws SQLException { + String header = "idref,lat,lon,score"; + System.out.println(header); + PreparedStatement ps8 = hsqldb.prepareStatement("SELECT idref,lat,lon,score FROM "+ TABLE_NAME); + try (ResultSet rset8 = ps8.executeQuery()) { + while (rset8.next()) { + String idcode = rset8.getString(1); + Double lat = rset8.getDouble(2); + Double lon = rset8.getDouble(3); + int score = rset8.getInt(4); + System.out.println("OSM: "+ idcode +","+ lat +","+ lon +","+ score); + } + } + } + + /** + * File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。
+ * その際に、OSMノードを評価し、scoreを算定する + * + * @param hsqldb 反映先のデータベースコネクタ(HSQLDB) + * @param sqlWhere POI条件 + * @param point 評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand' 2:'name' + * @throws Exception エラー + */ + public void readExisting(Connection hsqldb, String sqlWhere, int point) throws Exception { + Connection osmdb = null; + long counter = 0L; + try { + osmdb = DatabaseTool.openDb("osmdb"); + String sqlNode = "select osm_id,brand,disused,name,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon from planet_osm_point " + sqlWhere; + PreparedStatement ps1 = osmdb.prepareStatement(sqlNode); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + counter += importExistingNode(hsqldb, osmdb, rset1, point); + } + } + String sqlArea = "select osm_id,brand,disused,name,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon from planet_osm_polygon " + sqlWhere; + PreparedStatement ps2 = osmdb.prepareStatement(sqlArea); + try (ResultSet rset2 = ps2.executeQuery()) { + while (rset2.next()) { + counter += importExistingNode(hsqldb, osmdb, rset2, point); + } + } + + System.out.println("Exists Node count = " + counter); + } + finally { + DatabaseTool.closeDb(osmdb); + } + } + + /** + * + * SELECT + planet_osm_point.osm_id, + planet_osm_point.amenity, + planet_osm_point.brand, + planet_osm_nodes.id, + planet_osm_nodes.tags + FROM + public.planet_osm_point, + public.planet_osm_nodes + WHERE + planet_osm_point.osm_id = planet_osm_nodes.id and 'disused:amenity'=ANY(planet_osm_nodes.tags) and 'fuel'=ANY(planet_osm_nodes.tags); + * + * @param hsqldb + * @param kStr + * @param vStr + * @param point + * @throws Exception + */ + public void readExistingSub(Connection hsqldb, String kStr, String vStr, int point) throws Exception { + Connection osmdb = null; + long counter = 0L; + try { + osmdb = DatabaseTool.openDb("osmdb"); + + PreparedStatement ps1 = osmdb.prepareStatement("SELECT osm_id,brand,name,disused,tags,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon FROM planet_osm_point,planet_osm_nodes WHERE osm_id=id AND '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)"); + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + String tags = rset1.getString("tags"); + if (tags != null) { + tags = tags.substring(1); + tags = tags.substring(0, tags.length()-1); + CsvRecord csv = new CsvRecord(); + csv.analizeRecord(tags); + + boolean key = false; + for (String str : csv) { + if (key) { + if (str.equals(vStr)) { + counter += importExistingNode(hsqldb, osmdb, rset1, point); + break; + } + key = false; + } + else { + if (str.equals(kStr)) { + key = true; + } + } + } + } + } + } + PreparedStatement ps2 = osmdb.prepareStatement("SELECT osm_id,brand,disused,name,tags,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon FROM planet_osm_polygon,planet_osm_nodes WHERE osm_id=id AND '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)"); + try (ResultSet rset2 = ps2.executeQuery()) { + while (rset2.next()) { + counter += importExistingNode(hsqldb, osmdb, rset2, point); + } + } + System.out.println("Exists Node count = " + counter); + } + finally { + DatabaseTool.closeDb(osmdb); + } + } +} \ No newline at end of file diff --git a/src/osm/jp/coverage/police/Police.java b/src/osm/jp/coverage/police/Police.java new file mode 100644 index 0000000..905a2d5 --- /dev/null +++ b/src/osm/jp/coverage/police/Police.java @@ -0,0 +1,129 @@ +package osm.jp.coverage.police; + +import osm.jp.api.RectArea; + +import javax.xml.parsers.*; +import javax.xml.transform.TransformerException; + +import org.xml.sax.*; + +import java.io.*; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.SimpleDateFormat; +import jp.co.areaweb.tools.database.*; +import osm.jp.api.HttpPOST; +import osm.jp.api.Japan; + +public class Police { + + String filter = ""; + String urlStr = ""; + + public static final boolean DB_INIT = false; + + // 近くのノードを探す範囲（KJS2を中心としたNEER×2(ｍ)四方の領域 + static final int NEER = 200; // 200m x 2 = 400m四方 + + public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); + + /** + * メイン + * + * java -cp .:osmCoverage.jar:hsqldb_2.2.9.jar osm.jp.coverage.police.Police [option] + * + * @param args + * @throws IOException + * @throws SQLException + * @throws ClassNotFoundException + * @throws FileNotFoundException + * @throws TransformerException + * @throws SAXException + * @throws ParserConfigurationException + */ + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException + { + Connection con = DatabaseTool.openDb("database"); + try { + new Police(con); + } + finally { + DatabaseTool.closeDb(con); + } + } + + /** + * HSQL_DB.OSM_EXITのscoreを集計して、HSQL_DB.POLICEのfixedに反映させる + * + * @param con + * @throws SQLException + * @throws FileNotFoundException + * @throws ClassNotFoundException + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + * @throws TransformerException + */ + 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 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)"; + try ( PreparedStatement ps2 = con.prepareStatement(sql2); + PreparedStatement ps1 = con.prepareStatement(sql1); + PreparedStatement ps3 = con.prepareStatement(sql3); + PreparedStatement ps4 = con.prepareStatement(sql4); + PreparedStatement ps5 = con.prepareStatement(sql5)) + { + System.out.println(sql4); + ps4.executeUpdate(); + + try (ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + String osmid = rset1.getString("idref"); + double lat = rset1.getDouble("lat"); + double lon = rset1.getDouble("lon"); + int score = rset1.getInt("score"); + String idref = null; + int area = 0; + + // 指定の緯度経度を中心とする半径100x2m四方の矩形領域 + RectArea rect = new RectArea(lat, lon, NEER); // 400m 四方 + ps2.setDouble(1, rect.minlat); + ps2.setDouble(2, rect.maxlat); + ps2.setDouble(3, rect.minlon); + ps2.setDouble(4, rect.maxlon); + try (ResultSet rset2 = ps2.executeQuery()) { + double distance = 999999.9D; + while (rset2.next()) { + double lat2 = rset2.getDouble("lat"); + double lon2 = rset2.getDouble("lon"); + double dd = Japan.distanceKm(lat,lat2,lon,lon2); + if (dd < distance) { + distance = dd; + idref = rset2.getString("idref"); + area = rset2.getInt("area"); + } + } + } + if (idref != null) { + System.out.println("UPDATE "+ DbPolice.TABLE_NAME +" SET fixed1="+ score +" WHERE idref="+ idref +" and area=" + area); + ps3.setInt(1, score); + ps3.setString(2, idref); + ps3.setInt(3, area); + ps3.executeUpdate(); + } + else { + System.out.println("NOT FOUND! idref="+ osmid); + } + } + } + + System.out.println("UPDATE "+ DbPolice.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)"); + ps5.executeUpdate(); + } + } +} \ No newline at end of file diff --git a/src/osm/jp/coverage/police/ToPostgis.java b/src/osm/jp/coverage/police/ToPostgis.java new file mode 100644 index 0000000..28b00a0 --- /dev/null +++ b/src/osm/jp/coverage/police/ToPostgis.java @@ -0,0 +1,34 @@ +package osm.jp.coverage.police; + +/** + * 国土数値情報から読み取ったデータをPostGISへインサートするためのSQLファイルに出力する。 + * 出力ファイル名： "t_POLICE.sql" + * テーブル名： t_POLICE + * インデックス： ix_t_POLICE_geom + * gid PostGISの識別ID + * idstr 国土数値情報のノードID + * fixed OSMのNodeが周辺に存在するかどうか、存在しない場合は0,存在する場合はそのScoreの合計。 + * area 都道府県コード + * geom PostGIS形式の位置情報(4612:) + * @author yuu + * + */ +public class ToPostgis extends osm.jp.postgis.ToPostgis +{ + public static void main(String[] argv) throws Exception { + boolean update = false; + for (String arg : argv) { + if (arg.toUpperCase().equals("-UPDATE")) { + update = true; + } + } + + ToPostgis model = new ToPostgis(update); + model.transport(); + } + + public ToPostgis(boolean update) { + super(DbPolice.TABLE_NAME, update); + } + +} diff --git a/src/osmCoverage_PoliceGet b/src/osmCoverage_PoliceGet index 5fd68cc..ad46a09 100644 --- a/src/osmCoverage_PoliceGet +++ b/src/osmCoverage_PoliceGet @@ -22,10 +22,10 @@ mkdir database # make to PostGIS -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.busstop.DbBusstop -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.busstop.DbExistBusstop -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.busstop.Busstop -java -cp .:osmCoverage.jar:hayashi.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.busstop.ToPostgis +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 diff --git a/src/police.activity.violet.html b/src/police.activity.violet.html index d817fc8..6cb4144 100644 --- a/src/police.activity.violet.html +++ b/src/police.activity.violet.html @@ -91,7 +91,7 @@ - HSQLDB.BUS_STOP + HSQLDB.POLICE 255 @@ -226,7 +226,7 @@ - + 1 @@ -898,1001 +898,1023 @@ ]]>

- embedded diagram image + embedded diagram image \ No newline at end of file