diff --git a/doc/NagoyaBusstop.qgs b/doc/NagoyaBusstop.qgs index f2a8276..9b8d357 100644 --- a/doc/NagoyaBusstop.qgs +++ b/doc/NagoyaBusstop.qgs @@ -1,8 +1,6 @@ - + - - @@ -16,16 +14,16 @@ meters - 15233446.85589521564543247 - 4183206.5351748620159924 - 15234360.58094646222889423 - 4184210.3740612999536097 + 15239659.8577087540179491 + 4185493.37688044784590602 + 15240774.82267118059098721 + 4186497.2157668867148459 0 1 - +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs + +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs 3857 3857 EPSG:3857 @@ -35,12 +33,12 @@ false - 0 + nagoya_busstop20170225161030940 @@ -59,14 +57,8 @@ - + - - -20037508.33999999985098839 - -20037508.33999999985098839 - 20037508.33999999985098839 - 20037508.33999999985098839 - OCM_Public_Transport20170225161054008 <GDAL_WMS> <Service name="TMS"> @@ -89,13 +81,15 @@ <Cache /> </GDAL_WMS> + + OCM Public Transport - +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs + +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs 3857 3857 EPSG:3857 @@ -128,15 +122,11 @@ 0 - - - 136.794921875 - 35.04031753540039773 - 137.04507446289099448 - 35.26501846313480115 - + nagoya_busstop20170225161030940 - dbname='gisdb' host=surveyor.mydns.jp port=5432 user='gisuser' password='gisuser' sslmode=disable key='gid' srid=4612 type=Point table="public"."nagoya_busstop" (geom) sql= + dbname='gisdb' host=surveyor.mydns.jp port=5432 user='gisuser' password='gisuser' sslmode=disable key='gid' srid=4612 type=POINT table="public"."nagoya_busstop" (geom) sql= + + @@ -156,128 +146,116 @@ postgres - - - - - - - - - + - + - + - + - + - + - - - + + + - + - - + - + - + - - + + - + - - + - + - + - - + + - + - - + - + - + - - + + - + - + - - + - + - + - - - + + + @@ -287,7 +265,6 @@ - @@ -304,6 +281,8 @@ + + @@ -312,28 +291,35 @@ + + - - + + + - + - + + + - + - + + + @@ -343,7 +329,7 @@ - + @@ -351,8 +337,8 @@ - - + + @@ -360,6 +346,8 @@ + + @@ -375,10 +363,14 @@ + + + + @@ -390,6 +382,8 @@ + + @@ -398,10 +392,14 @@ + + + + @@ -409,6 +407,8 @@ + + @@ -456,79 +456,38 @@ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + . + + 0 . + generatedlayout - - - - + + + + - - - - - - . - - 0 - . - - 0 - generatedlayout - - - - - + + + + + + + + + + + + + + + + + + @@ -568,5 +527,4 @@ false - diff --git "a/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.odt" "b/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.odt" new file mode 100644 index 0000000..c1aeada --- /dev/null +++ "b/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.odt" Binary files differ diff --git "a/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.pdf" "b/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.pdf" new file mode 100644 index 0000000..568005a --- /dev/null +++ "b/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.pdf" Binary files differ diff --git "a/doc/osmCoverage\343\201\256\344\275\277\343\201\204\346\226\271.odt" "b/doc/osmCoverage\343\201\256\344\275\277\343\201\204\346\226\271.odt" new file mode 100644 index 0000000..171ac8b --- /dev/null +++ "b/doc/osmCoverage\343\201\256\344\275\277\343\201\204\346\226\271.odt" Binary files differ diff --git a/src/Nagoya_busstop.sql b/src/Nagoya_busstop.sql index 6d9d472..f31d20d 100644 --- a/src/Nagoya_busstop.sql +++ b/src/Nagoya_busstop.sql @@ -1,6 +1,10 @@ --- DROP TABLE t_busstop; +DROP TABLE nagoya_busstop; CREATE TABLE nagoya_busstop (gid SERIAL PRIMARY KEY, name text, kana text, fixed integer, area integer, geom GEOMETRY(POINT, 4612)); CREATE INDEX ix_nagoya_busstop_geom ON nagoya_busstop USING GiST (geom); +select count(*) from nagoya_busstop; +select count(*) from nagoya_busstop where fixed=0; +select count(*) from nagoya_busstop where fixed>=50 and fixed<100; +select count(*) from nagoya_busstop where fixed>=100; insert into nagoya_busstop(name,kana,fixed,area,geom) VALUES('相川一丁目','アイカワイッチョウメ',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612)); insert into nagoya_busstop(name,kana,fixed,area,geom) VALUES('相川一丁目','アイカワイッチョウメ',0,0, ST_GeomFromText('POINT(136.969047121627 35.102834616747685)', 4612)); diff --git a/src/osm/jp/coverage/busstop/NagoyaBusstop.java b/src/osm/jp/coverage/busstop/NagoyaBusstop.java index b3d5dc3..d97ac91 100644 --- a/src/osm/jp/coverage/busstop/NagoyaBusstop.java +++ b/src/osm/jp/coverage/busstop/NagoyaBusstop.java @@ -1,6 +1,13 @@ package osm.jp.coverage.busstop; import osm.jp.api.HttpPOST; 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.*; @@ -11,6 +18,8 @@ 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; @@ -65,7 +74,6 @@ * @throws Exception */ public NagoyaBusstop(Connection con, File csvFile) throws Exception { - BufferedWriter ow = null; ToPostgis postgis = null; Connection conPost = null; @@ -73,7 +81,6 @@ if (outFile.isFile()) { outFile.delete(); } - ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), "UTF-8")); try { NagoyaBusstop.clearDb(con); @@ -119,7 +126,7 @@ */ HttpPOST.getCapabilities(existingFile, "highway", "bus_stop", minLat, maxLat, minLon, maxLon); readExistingFile(con, existingFile); - HttpPOST.getCapabilities(existingFile, "highway", "disused:bus_stop", minLat, maxLat, minLon, maxLon); + HttpPOST.getCapabilities(existingFile, "disused:highway", "bus_stop", minLat, maxLat, minLon, maxLon); readExistingFile(con, existingFile); HttpPOST.getCapabilities(existingFile, "public_transport", "platform", minLat, maxLat, minLon, maxLon); readExistingFile(con, existingFile); @@ -186,8 +193,6 @@ if (conPost != null) { DatabaseTool.closeDb(conPost); } - ow.flush(); - ow.close(); } } @@ -325,42 +330,7 @@ } /** - * - */ - public static void updateFile (Connection conHsql, Connection conPost, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - int iCounter = 0; - PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) LON, ST_Y(geom) LAT FROM t_busstop WHERE area=?"); - PreparedStatement ps2 = conHsql.prepareStatement("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES (?,?,?,?,?,?)"); - - // idref と nameStr をデータベースに格納する - ps1.setInt(1, areacode); - ResultSet rset1 = ps1.executeQuery(); - while (rset1.next()) { - iCounter++; - int idref = rset1.getInt("gid"); - String nameStr = rset1.getString("name"); - int fixed = rset1.getInt("fixed"); - double lat = rset1.getDouble(4); - double lon = rset1.getDouble(5); - - ps2.setInt(1, idref); - ps2.setString(2, nameStr); - ps2.setInt(3, fixed); - ps2.setInt(4, areacode); - ps2.setDouble(5, lon); - ps2.setDouble(6, lat); - //System.out.println("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES ("+ idref +",'"+ nameStr +"',"+ fixed +","+ areacode +","+ lat +","+ lon +")"); - ps2.executeUpdate(); - } - rset1.close(); - ps1.close(); - ps2.close(); - - System.out.println("("+ areacode +") バス停数["+ iCounter +"]"); - } - - /** - * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する + * CSVファイルを読み取ってローカルベータベースへ記録する * @param con * @param iFile * @throws Exception @@ -440,4 +410,157 @@ } return rtn; } + + /** + * ローカルデータベース内の情報をOSMファイルに出力する + * @param con + * @param iCode + * @throws IOException + * @throws SQLException + * @throws TransformerException + * @throws ParserConfigurationException + * @throws SAXException + */ + public static void outputDb2html(Connection con, File dir) throws IOException, SQLException, SAXException, ParserConfigurationException, TransformerException { + String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis())); + String iCode = dir.getName(); + + BufferedWriter ow = null; + BufferedWriter gw = null; + + PreparedStatement ps8 = con.prepareStatement("SELECT idref,name,kana,lat,lon,fixed FROM bus_stop WHERE fixed=0"); + { + File osmFile = new File(dir, "Nagoya_busstop.osm"); + + int stopCount = 0; + int fixedCount = 0; + int unfixedCount = 0; + + // OSM file header + ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8")); + ow.write(""); + ow.newLine(); + ow.write(""); + 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()) { + stopCount++; + String idref = rset8.getString("idref"); + String name = rset8.getString("name"); + String kana = rset8.getString("kana"); + Double lat = rset8.getDouble("lat"); + Double lon = rset8.getDouble("lon"); + int fixed = rset8.getInt("fixed"); + + if (lat > maxLat) { + maxLat = lat; + } + if (lon > maxLon) { + maxLon = lon; + } + if (lat < minLat) { + minLat = lat; + } + if (lon < minLon) { + minLon = lon; + } + + unfixedCount++; + + // OSM node + String osm_node = nodeBusstop(con, idref, name, lat, lon, timeStampStr); + ow.write(osm_node); + ow.newLine(); + } + rset8.close(); + } + + // OSM file footer + ow.write(""); + ow.newLine(); + ow.close(); + } + } + + public static Element osmTemplateNode = null; + + public static String nodeBusstop(Connection con, String idref, String name, Double lat, Double lon, String timeStampStr) throws SAXException, IOException, ParserConfigurationException, TransformerException, SQLException { + int nodeid = Integer.parseInt(idref.substring(1)) * -1; + + DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); + factory.setIgnoringElementContentWhitespace(true); + factory.setIgnoringComments(true); + factory.setValidating(true); + + 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(nodeid)); + node.setAttribute("timestamp", timeStampStr); + node.setAttribute("lat", String.valueOf(lat)); + node.setAttribute("lon", String.valueOf(lon)); + + + 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); + } + } + } + } + } + + 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(); + } } \ No newline at end of file diff --git a/src/osm/jp/postgis/Do_sqlfiles.java b/src/osm/jp/postgis/Do_sqlfiles.java index 7768e6d..1e487aa 100644 --- a/src/osm/jp/postgis/Do_sqlfiles.java +++ b/src/osm/jp/postgis/Do_sqlfiles.java @@ -5,7 +5,6 @@ import java.sql.Statement; import jp.co.areaweb.tools.database.*; -import osm.jp.coverage.busstop.Busstop; public class Do_sqlfiles { static String sqlFileName = "busstop.sql"; diff --git a/src/osmTag.xml b/src/osmTag.xml new file mode 100644 index 0000000..9577e74 --- /dev/null +++ b/src/osmTag.xml @@ -0,0 +1,8 @@ + + + +　 +　 +　 +　 +