package osm.jp.coverage.busstop;
import osm.jp.api.RectArea;
import osm.jp.api.HttpPOST;
import osm.jp.api.KatakanaToHiragana;
import javax.xml.parsers.*;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.*;
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.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import jp.co.areaweb.tools.csv.CsvFile;
import jp.co.areaweb.tools.csv.CsvRecord;
import jp.co.areaweb.tools.database.*;
public class NagoyaBusstop {
String filter = "";
String urlStr = "";
public static final boolean DB_INIT = false;
// 近くのバス停を探す範囲（バス停を中心としたNEERｍ四方の領域
static final int NEER = 25; // 20m
public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
/**
* メイン
*
* java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop [option]
* OPTION: -check OSMデータ上に既存のバス停が存在するかどうかをチェックする
* @param args
* @throws Exception */
public static void main(String[] args) throws Exception
{
File outFile = new File("update.sql");
if (outFile.isFile()) {
outFile.delete();
}
// HSQLディレクトリがなければ作る
File dbdir = new File("database");
if (!dbdir.isDirectory()) {
dbdir.mkdir();
}
Connection con = DatabaseTool.openDb("database");
NagoyaBusstop.initDb(con);
try {
NagoyaBusstop.clearDb(con);
new NagoyaBusstop(con, new File("TRF0009630..csv"));
// ローカルデータベース内の情報をPostGIS用の'ToPostgis.SQL_FILE_NAME'「./busstop.sql」に出力する
ToPostgis postgis = new ToPostgis();
//postgis.setNagoyaMode(true);
//postgis.outputDb(con);
//postgis.close();
}
finally {
DatabaseTool.closeDb(con);
}
Connection conPost = null;
try {
conPost = DatabaseTool.openDb("postgis");
DoSQL.sqlExecute(conPost, "DROP TABLE nagoya_busstop;");
DoSQL.sqlExecute(conPost, "CREATE TABLE nagoya_busstop (gid SERIAL PRIMARY KEY, name text, kana text, fixed integer, area integer, geom GEOMETRY(POINT, 4612));");
DoSQL.sqlExecute(conPost, "CREATE INDEX ix_nagoya_busstop_geom ON nagoya_busstop USING GiST (geom);");
DoSQL.sqlExecute(conPost, "commit;");
//Do_sqlfiles.sqlExecute(conPost, new File(ToPostgis.SQL_FILE_NAME));
outputDb2Osm(conPost, new File("Nagoya_busstop.osm"));
}
finally {
if (conPost != null) {
DatabaseTool.closeDb(conPost);
}
}
}
/**
*
* @param con
* @param csvFile
* @throws Exception
*/
public NagoyaBusstop(Connection con, File csvFile) throws Exception {
inputFile(con, csvFile);
/**
* 既存のOSMバス停を読み込む
* --> 'existing.xml'
*/
File existingFile = new File("existing.xml");
/**
* インポートしたデータの緯度経度範囲を読み取る
*/
double maxLon = -180.0D;
double minLon = 180.0D;
double maxLat = -90.0D;
double minLat = 90.0D;
PreparedStatement ps8;
ps8 = con.prepareStatement("SELECT lat,lon FROM "+ DbBusstop.TABLE_NAME);
try (ResultSet rset8 = ps8.executeQuery()) {
while (rset8.next()) {
Double lat = rset8.getDouble("lat");
Double lon = rset8.getDouble("lon");
if (lat > maxLat) {
maxLat = lat;
}
if (lon > maxLon) {
maxLon = lon;
}
if (lat < minLat) {
minLat = lat;
}
if (lon < minLon) {
minLon = lon;
}
}
}
/**
* OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する
*/
HttpPOST.getCapabilities("highway", "bus_stop", minLon, maxLon, minLat, maxLat);
readExistingFile(con, existingFile);
HttpPOST.getCapabilities("disused:highway", "bus_stop", minLon, maxLon, minLat, maxLat);
readExistingFile(con, existingFile);
HttpPOST.getCapabilities("public_transport", "platform", minLon, maxLon, minLat, maxLat);
readExistingFile(con, existingFile);
PreparedStatement ps1;
ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM " + DbBusstop.TABLE_NAME);
PreparedStatement ps2 = con.prepareStatement("SELECT count(*) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
PreparedStatement ps3 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed=? WHERE idref=?");
PreparedStatement ps4 = con.prepareStatement("SELECT count(*) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
ResultSet rset1 = ps1.executeQuery();
while (rset1.next()) {
String idref = rset1.getString(1);
String name = rset1.getString("name");
Double lat = rset1.getDouble("lat");
Double lon = rset1.getDouble("lon");
// 指定の緯度経度を中心とする半径5x2m四方の矩形領域
System.out.print(idref + "("+ name + ") ....");
RectArea rect = new RectArea(lat, lon, NEER); // 25m 四方
ps2.setDouble(1, rect.minlat);
ps2.setDouble(2, rect.maxlat);
ps2.setDouble(3, rect.minlon);
ps2.setDouble(4, rect.maxlon);
ResultSet rset2 = ps2.executeQuery();
if (rset2.next()) {
int score = rset2.getInt(1);
if (score > 0) {
score = 100;
}
else {
rect = new RectArea(lat, lon, NEER*2); // 50m 四方
ps4.setDouble(1, rect.minlat);
ps4.setDouble(2, rect.maxlat);
ps4.setDouble(3, rect.minlon);
ps4.setDouble(4, rect.maxlon);
ResultSet rset4 = ps4.executeQuery();
if (rset4.next()) {
score = rset4.getInt(1);
if (score > 0) {
score = 50;
}
}
rset4.close();
}
System.out.println("."+ score);
ps3.setInt(1, score);
ps3.setString(2, idref);
ps3.executeUpdate();
}
rset2.close();
}
rset1.close();
ps4.close();
ps3.close();
ps2.close();
ps1.close();
}
static String[] shiftArgs(String[] args) {
String[] values = new String[args.length - 1];
for (int i=1; i < args.length; i++) {
values[i - 1] = new String(args[i]);
}
return values;
}
public static void readExistingFile (Connection con, File existingFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
int iCounter = 0;
DocumentBuilderFactory factory;
DocumentBuilder builder;
Node root;
iCounter = 0;
factory = DocumentBuilderFactory.newInstance();
builder = factory.newDocumentBuilder();
factory.setIgnoringElementContentWhitespace(true);
factory.setIgnoringComments(true);
factory.setValidating(true);
root = builder.parse(existingFile);
iCounter += readExistingNodes(con, root);
System.out.println("既存バス停数["+ iCounter +"]");
}
static int readExistingNodes(Connection con, Node node) throws IOException, SQLException {
int iCounter = 0;
NodeList nodes = node.getChildNodes();
for (int i = 0; i < nodes.getLength(); i++) {
Node node2 = nodes.item(i);
if (node2.getNodeName().equals("node")) {
iCounter++;
importExistingNode(con, node2);
}
else {
iCounter += readExistingNodes(con, node2);
}
}
return iCounter;
}
static void importExistingNode(Connection con, Node node) throws IOException, SQLException {
String idrefStr = "";
String latStr = "";
String lonStr = "";
String nameStr = "";
String fixmeStr = "";
int score = 0;
NamedNodeMap nodeMap = node.getAttributes();
if (null != nodeMap) {
for (int j=0; j < nodeMap.getLength(); j++) {
if (nodeMap.item(j).getNodeName().equals("id")) {
idrefStr = nodeMap.item(j).getNodeValue();
}
else if (nodeMap.item(j).getNodeName().equals("lat")) {
latStr = nodeMap.item(j).getNodeValue();
}
else if (nodeMap.item(j).getNodeName().equals("lon")) {
lonStr = nodeMap.item(j).getNodeValue();
}
}
NodeList nodes = node.getChildNodes();
for (int i = 0; i < nodes.getLength(); i++) {
Node node2 = nodes.item(i);
if (node2.getNodeName().equals("tag")) {
NamedNodeMap nodeMap2 = node2.getAttributes();
if (null != nodeMap2) {
String key = null;
String value = null;
for (int j=0; j < nodeMap2.getLength(); j++) {
if (nodeMap2.item(j).getNodeName().equals("k")) {
key = nodeMap2.item(j).getNodeValue();
}
else if (nodeMap2.item(j).getNodeName().equals("v")) {
value = nodeMap2.item(j).getNodeValue();
}
}
if ((key != null) && key.toLowerCase().equals("name") && (value != null)) {
nameStr = new String(value);
}
if ((key != null) && key.toLowerCase().equals("fixme") && (value != null)) {
fixmeStr = new String(value);
}
}
}
}
score = 50;
if (nameStr.equals("")) {
score = 1;
}
if (!fixmeStr.equals("")) {
score = 1;
}
// idref と nameStr をデータベースに格納する
PreparedStatement ps1 = con.prepareStatement("SELECT name,score FROM existing_data WHERE idref=?");
ps1.setString(1, idrefStr);
ResultSet rset1 = ps1.executeQuery();
if (rset1.next()) {
int fixed = rset1.getInt("score");
if (fixed < score) {
System.out.println("import existing_data : [id:"+ idrefStr +"] score="+ Integer.toString(score) +" "+ nameStr);
PreparedStatement ps5 = con.prepareStatement("UPDATE existing_data SET score=? WHERE idref=?");
ps5.setInt(1, score);
ps5.setString(2, idrefStr);
ps5.executeUpdate();
ps5.close();
}
}
else {
System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")["+ Integer.toString(score) +"]"+ nameStr);
PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name, score) VALUES (?,?,?,?,?)");
ps5.setString(1, idrefStr);
ps5.setDouble(2, Double.parseDouble(latStr));
ps5.setDouble(3, Double.parseDouble(lonStr));
ps5.setString(4, nameStr);
ps5.setInt(5, score);
ps5.executeUpdate();
ps5.close();
}
rset1.close();
ps1.close();
}
}
/**
* CSVファイルを読み取ってローカルベータベースへ記録する
* @param con
* @param iFile
* @throws Exception
*/
public static void inputFile (Connection con, File iFile) throws Exception {
PreparedStatement ps2 = con.prepareStatement("INSERT INTO "+ DbBusstop.TABLE_NAME +" (idref,name,kana,fixed,area,lat,lon) VALUES (?,?,?,?,?,?,?)");
CsvFile csv = new CsvFile(iFile);
csv.load();
int cnt = 0;
for (Iterator<CsvRecord> i = csv.iterator(); i.hasNext(); ) {
CsvRecord record = (CsvRecord)i.next();
if (cnt > 0) {
String name = (String)record.get(0);
String idStr = (String)record.get(1);
String kana = (String)record.get(5);
String latStr = (String)record.get(6);
String lonStr = (String)record.get(7);
System.out.println(record.toString());
if (!idStr.isEmpty()) {
ps2.setString(1, idStr);
ps2.setString(2, name);
ps2.setString(3, kana);
ps2.setInt(4, 0);
ps2.setInt(5, 0);
ps2.setDouble(6, Double.parseDouble(latStr));
ps2.setDouble(7, Double.parseDouble(lonStr));
ps2.executeUpdate();
}
}
cnt++;
}
ps2.close();
System.out.println("バス停数["+ cnt +"]");
}
public static void clearDb(Connection con) throws SQLException {
Statement stmt = con.createStatement();
long count = stmt.executeUpdate("delete from "+ DbBusstop.TABLE_NAME);
System.out.println("'Database.busstop'から "+ count +" 件のデータを削除しました。");
count = stmt.executeUpdate("delete from existing_data");
System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。");
}
public static void initDb(Connection con) throws SQLException, ClassNotFoundException, IOException, FileNotFoundException, ParserConfigurationException, SAXException {
// DB.tableを作成(初期化)
DbBusstop db = new DbBusstop(con);
db.dropTable();
db.create();
}
public static String[] anaComm(Node briNode) {
String[] rtn = new String[3];
rtn[0] = ""; // corp type
rtn[1] = ""; // course name
rtn[2] = ""; // corp name
NodeList nodes = briNode.getChildNodes();
for (int i=0; i < nodes.getLength(); i++) {
Node node2 = nodes.item(i);
if (node2.getNodeName().equals("ksj:BSC")) {
rtn[0] = node2.getTextContent();
}
else if (node2.getNodeName().equals("ksj:BLN")) {
rtn[1] = node2.getTextContent();
}
else if (node2.getNodeName().equals("ksj:BOC")) {
rtn[2] = node2.getTextContent();
}
}
return rtn;
}
/**
* ローカルデータベース内の情報をOSMファイルに出力する
* @param con
* @param osmFile
* @throws IOException
* @throws SQLException
* @throws TransformerException
* @throws ParserConfigurationException
* @throws SAXException
*/
public static void outputDb2Osm(Connection con, File osmFile) throws IOException, SQLException, SAXException, ParserConfigurationException, TransformerException {
String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));
BufferedWriter ow = null;
PreparedStatement ps8 = con.prepareStatement("SELECT gid,name,kana,ST_Y(geom) AS lat,ST_X(geom) AS lon,fixed FROM nagoya_busstop WHERE fixed=0");
{
// OSM file header
ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));
ow.write("<?xml version='1.0' encoding='UTF-8'?>");
ow.newLine();
ow.write("<osm version='0.6' generator='ConvBusstop'>");
ow.newLine();
double maxLat = -180.0;
double minLat = 180.0;
double maxLon = -180.0;
double minLon = 180.0;
{
ResultSet rset8 = ps8.executeQuery();
while (rset8.next()) {
long idref = rset8.getLong("gid");
String name = rset8.getString("name");
String kana = rset8.getString("kana");
Double lat = rset8.getDouble("lat");
Double lon = rset8.getDouble("lon");
if (lat > maxLat) {
maxLat = lat;
}
if (lon > maxLon) {
maxLon = lon;
}
if (lat < minLat) {
minLat = lat;
}
if (lon < minLon) {
minLon = lon;
}
// OSM node
String osm_node = nodeBusstop(con, idref, name, KatakanaToHiragana.hiraganaToKatakana(kana), lat, lon, timeStampStr);
ow.write(osm_node);
ow.newLine();
}
rset8.close();
}
// OSM file footer
ow.write("</osm>");
ow.newLine();
ow.close();
}
}
public static Element osmTemplateNode = null;
public static String nodeBusstop(Connection con, long gid, String name, String hirakana, Double lat, Double lon, String timeStampStr) throws SAXException, IOException, ParserConfigurationException, TransformerException, SQLException {
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
factory.setIgnoringElementContentWhitespace(true);
factory.setIgnoringComments(true);
factory.setValidating(false);
if (osmTemplateNode == null) {
Document root = factory.newDocumentBuilder().parse(new File("osmTag.xml"));
osmTemplateNode = root.getDocumentElement();
}
Document document = factory.newDocumentBuilder().newDocument(); // Documentの生成
Element node = document.createElement("node");
document.appendChild(node);
node.setAttribute("id", String.valueOf(gid * -1));
node.setAttribute("timestamp", timeStampStr);
node.setAttribute("lat", String.valueOf(lat));
node.setAttribute("lon", String.valueOf(lon));
node.setAttribute("version", "1");
NodeList nodes = osmTemplateNode.getChildNodes();
for (int i = 0; i < nodes.getLength(); i++) {
Node node2 = nodes.item(i);
if (node2.getNodeName().equals("tag")) {
NamedNodeMap nodeMap2 = node2.getAttributes();
if (null != nodeMap2) {
String key = null;
String value = null;
for (int j=0; j < nodeMap2.getLength(); j++) {
if (nodeMap2.item(j).getNodeName().equals("k")) {
key = nodeMap2.item(j).getNodeValue();
}
else if (nodeMap2.item(j).getNodeName().equals("v")) {
value = nodeMap2.item(j).getNodeValue();
}
}
if ((key != null) && (value != null)) {
Element tag = document.createElement("tag");
tag.setAttribute("k", key);
tag.setAttribute("v", value);
node.appendChild(tag);
}
}
}
else if (node2.getNodeName().equals("ksj")) {
NamedNodeMap nodeMap2 = node2.getAttributes();
if (null != nodeMap2) {
for (int j=0; j < nodeMap2.getLength(); j++) {
String nodeName = nodeMap2.item(j).getNodeName();
String nodeValue = nodeMap2.item(j).getNodeValue();
if (nodeName.equals("busStopName")) {
Element tagName = document.createElement("tag");
tagName.setAttribute("k", nodeValue);
tagName.setAttribute("v", name);
node.appendChild(tagName);
}
else if (nodeName.equals("busStopNameKana")) {
Element tagName = document.createElement("tag");
tagName.setAttribute("k", nodeValue);
tagName.setAttribute("v", hirakana);
node.appendChild(tagName);
}
}
}
}
}
StringWriter sw = new StringWriter();
TransformerFactory tfactory = TransformerFactory.newInstance();
Transformer transformer = tfactory.newTransformer();
transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
transformer.transform(new DOMSource(node), new StreamResult(sw));
return sw.toString();
}
}
