diff --git a/src/osm/jp/coverage/police/DbPolice.java b/src/osm/jp/coverage/police/DbPolice.java new file mode 100644 index 0000000..5d572b1 --- /dev/null +++ b/src/osm/jp/coverage/police/DbPolice.java @@ -0,0 +1,405 @@ +package osm.jp.coverage.police; + +import osm.jp.coverage.busstop.*; +import java.io.*; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import javax.xml.parsers.DocumentBuilder; +import javax.xml.parsers.DocumentBuilderFactory; +import javax.xml.parsers.ParserConfigurationException; + +import jp.co.areaweb.tools.database.*; +import org.w3c.dom.NamedNodeMap; +import org.w3c.dom.Node; +import org.w3c.dom.NodeList; +import org.xml.sax.SAXException; +import osm.jp.api.HttpPOST; + +public class DbPolice { + public static final String TABLE_NAME = "bus_stop"; + + File inputFile; + String filter = ""; + int iCounter = 0; + String urlStr = ""; + Connection con; + String timeStampStr = null; + File dir = null; + + /** メイン + * 動作条件； HSQLDBのフォルダを削除した状態で実行すること。 + * フォルダを削除しないで実行する場合は「-DROP」オプションを使うこと + * @param args + * @throws IOException + * @throws SQLException + * @throws ClassNotFoundException + * @throws FileNotFoundException + * @throws javax.xml.parsers.ParserConfigurationException + * @throws org.xml.sax.SAXException */ + public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException + { + // HSQLディレクトリがなければエラー + File dbdir = new File("database"); + if (!dbdir.isDirectory()) { + throw new FileNotFoundException("Directory 'database' is not found."); + } + + Connection conHsql = null; + try { + conHsql = DatabaseTool.openDb("database"); + HttpPOST.sql(conHsql, "DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE"); + create(conHsql); + + /** + * 都道府県ごとのGMLディレクトリの処理 + */ + int fcounter = 0; + File dir = new File("GML_BUSSTOP"); + for (File gmldir : dir.listFiles()) { + if (checkGMLdir(gmldir)) { + // GMLディレクトリを処理する + int areacode = Integer.parseInt(gmldir.getName().substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)); + File[] files = gmldir.listFiles(); + for (File iFile : files) { + // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。 + if (checkFile(iFile, areacode)) { + importBusstop(conHsql, iFile, areacode); + fcounter++; + } + } + } + } + System.out.println("["+ fcounter +"]つのGMLファイルをインポートしました。"); + + DbPolice.export(conHsql); + } + finally { + if (conHsql != null) { + DatabaseTool.closeDb(conHsql); + } + } + } + + /** + * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する + * @param conHsql + * @param iFile + * @param areacode + * @throws FileNotFoundException + * @throws ClassNotFoundException + * @throws SQLException + * @throws IOException + * @throws ParserConfigurationException + * @throws SAXException + */ + public static void importBusstop (Connection conHsql, File iFile, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { + int iCounter = 0; + String timeStampStr = null; + + String iStr = iFile.getName(); + + DocumentBuilderFactory factory; + DocumentBuilder builder; + Node root; + + factory = DocumentBuilderFactory.newInstance(); + builder = factory.newDocumentBuilder(); + factory.setIgnoringElementContentWhitespace(true); + factory.setIgnoringComments(true); + factory.setValidating(true); + root = builder.parse(iFile); + + iCounter += showNodes(conHsql, root, iStr.substring(0, iStr.length() - 4), areacode); + System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); + } + + /** + * + * @param con + * @param node + * @param iFileName // ソースファイル名（拡張子を含まない） + * @param areacode + * @return + * @throws IOException + * @throws SQLException + */ + public static int showNodes(Connection con, Node node, String iFileName, int areacode) throws IOException, SQLException { + int iCounter = 0; + NodeList nodes = node.getChildNodes(); + for (int i=0; i + * 35.14591397 139.10569573 + * + * + * @param con + * @param node + * @param areacode + * @throws IOException + * @throws SQLException + */ + public static void showGmlPoint(Connection con, Node node, int areacode) throws IOException, SQLException { + String positionStr = ""; + String latStr = ""; + String lonStr = ""; + String idStr = ""; + + NamedNodeMap nodeMap = node.getAttributes(); + if ( null != nodeMap ) { + for ( int j=0; j + * + * 城堀 + * + * + * 1 + * 箱根登山バス + * 小01 + * + * + * + * + * 1 + * 箱根登山バス + * 湯07 + * + * + * + * + * 1 + * 箱根登山バス + * 湯11 + * + * + * + * + * @param con + * @param node + * @param iFileName // ソースファイル名（拡張子を含まない） + * @param areacode + * @throws IOException + * @throws SQLException + */ + public static void showBusStop(Connection con, Node node, String iFileName, int areacode) throws IOException, SQLException { + String gmlidStr = ""; + String nameStr = ""; + try (PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE (gmlid=? and area=?)")) { + ArrayList bris = new ArrayList<>(); + NodeList nodes = node.getChildNodes(); + for (int i=0; i < nodes.getLength(); i++) { + Node node2 = nodes.item(i); + switch (node2.getNodeName()) { + case "ksj:position": + NamedNodeMap nodeMap = node2.getAttributes(); + if (null != nodeMap) { + for ( int j=0; j < nodeMap.getLength(); j++ ) { + if (nodeMap.item(j).getNodeName().equals("xlink:href")) { + gmlidStr = nodeMap.item(j).getNodeValue(); + gmlidStr = gmlidStr.substring(1); + System.out.println("found gmlid='"+ gmlidStr +"'"); + break; + } + } + } break; + case "ksj:busStopName": + nameStr = node2.getTextContent(); + break; + case "ksj:busRouteInformation": + String[] rtn = anaCommJGD(node2); + if (rtn != null) { + bris.add(rtn); + } break; + default: + break; + } + } + + // gmlid と nameStr をデータベースに格納する + ps2.setString(1, nameStr); + ps2.setString(2, iFileName); + ps2.setString(3, gmlidStr); + ps2.setInt(4, areacode); + System.out.println("UPDATE bus_stop SET name='"+ nameStr +"',ifile='"+ iFileName +"' WHERE (gmlid='"+ gmlidStr +"' and area="+ areacode +")"); + ps2.executeUpdate(); + } + } + + /** + * + * + * + * 1 + * 箱根登山バス + * 小01 + * + * + * + * @param briNode + * @return + */ + public static String[] anaCommJGD(Node briNode) { + String[] rtn = new String[3]; + int vcnt = 0; + + NodeList nodes2 = briNode.getChildNodes(); + for (int i=0; i < nodes2.getLength(); i++) { + Node node2 = nodes2.item(i); + if (node2.getNodeName().equals("ksj:BusRouteInformation")) { + NodeList nodes3 = node2.getChildNodes(); + for (int j=0; j < nodes3.getLength(); j++) { + Node node3 = nodes3.item(j); + switch (node3.getNodeName()) { + case "ksj:busType": + rtn[0] = node3.getTextContent(); + vcnt++; + break; + case "ksj:busLineName": + rtn[1] = node3.getTextContent(); + vcnt++; + break; + case "ksj:busOperationCompany": + rtn[2] = node3.getTextContent(); + vcnt++; + break; + default: + break; + } + } + } + } + + if (vcnt > 0) { + return rtn; + } + return null; + } + + /** + * 'table.BUS_STOP'を新規に作る + * 既にテーブルが存在する時には何もしない + * @param conHsql + * @throws SQLException + */ + public static void create(Connection conHsql) throws SQLException { + String createSt; + + // 'table.BUS_STOP'を新規に作る + createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));"; + HttpPOST.sql(conHsql, createSt); + + createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);"; + HttpPOST.sql(conHsql, createSt); + } + + /** + * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する + * @param con + */ + public static void export(Connection con) { + try { + System.out.println("TABLE: BUS_STOP"); + System.out.println("\"gmlid\",\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\""); + PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,name,lat,lon,fixed,ifile FROM bus_stop"); + try (ResultSet rset8 = ps8.executeQuery()) { + while (rset8.next()) { + String gmlid = rset8.getString("gmlid"); + String name = rset8.getString("name"); + Double lat = rset8.getDouble("lat"); + Double lon = rset8.getDouble("lon"); + int fixed = rset8.getInt("fixed"); + String ifile = rset8.getString("ifile"); + System.out.println("\""+ gmlid +"\",\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\""); + } + } + } + catch (SQLException e) { + e.printStackTrace(); + } + } + + /** + * 数値地図情報のGMLデータファイルかどうかを見極める + * @param f + * @return + */ + static boolean checkFile(File f, int areacode) { + String name = f.getName(); + if (!name.startsWith(GML_DIR_PREFIX)) { + return false; + } + if (!name.toUpperCase().endsWith(".XML")) { + return false; + } + return Integer.parseInt(name.substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)) == areacode; + } + + /** + * 数値地図情報のデータディレクトリかどうかを見極める + * @param f + * @return + */ + public static boolean checkGMLdir(File f) { + if (!f.isDirectory()) { + return false; + } + String name = f.getName(); + if (!name.startsWith(GML_DIR_PREFIX)) { + return false; + } + return name.toUpperCase().endsWith(GML_DIR_PRIFIX); + } + + public static final String GML_DIR_PREFIX = "P11-10_"; + public static final String GML_DIR_PRIFIX = "_GML"; +} \ No newline at end of file diff --git a/src/osmCoverage_PoliceGet b/src/osmCoverage_PoliceGet new file mode 100644 index 0000000..5fd68cc --- /dev/null +++ b/src/osmCoverage_PoliceGet @@ -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.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 + + +#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/police.activity.violet.html b/src/police.activity.violet.html new file mode 100644 index 0000000..d817fc8 --- /dev/null +++ b/src/police.activity.violet.html @@ -0,0 +1,1898 @@ + + + + + + + + + This file was generated with Violet UML Editor 2.1.0. +   ( View Source / Download Violet ) +
+
+ +
+
+ embedded diagram image + + \ No newline at end of file