diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index 67777d0..d0c3631 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -18,7 +18,7 @@ import osm.jp.api.HttpPOST; public class DbBusstop { - public static final String TABLE_NAME = "bus_stop"; + public static final String TABLE_NAME = "busstop"; File inputFile; String filter = ""; diff --git a/src/osm/jp/coverage/busstop/ExportCSV.java b/src/osm/jp/coverage/busstop/ExportCSV.java new file mode 100644 index 0000000..170d53d --- /dev/null +++ b/src/osm/jp/coverage/busstop/ExportCSV.java @@ -0,0 +1,30 @@ +package osm.jp.coverage.busstop; + +import java.io.File; +import java.io.FileNotFoundException; +import java.io.IOException; +import java.sql.SQLException; +import javax.xml.parsers.ParserConfigurationException; +import org.xml.sax.SAXException; +import osm.jp.postgis.PostgisItem; +import osm.jp.postgis.PostgisItems; + +public class ExportCSV { + public static String tableName; + public static String CSV_FILE_NAME = "t_busstop.csv"; + + public static void main (String[] argv) throws ClassNotFoundException, SQLException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + + PostgisItems items = new PostgisItems(); + items.add(new PostgisItem("gmlid", "gmlid")); + items.add(new PostgisItem("idref", "idref")); + items.add(new PostgisItem("area", "area")); + items.add(new PostgisItem("fixed", "fixed1")); + items.add(new PostgisItem("lat", "lat")); + items.add(new PostgisItem("lon", "lon")); + + ToPostgis model = new ToPostgis(); + model.items = items; + model.toCsv(new File(CSV_FILE_NAME)); + } +} diff --git a/src/osm/jp/coverage/fuel/ExportCSV.java b/src/osm/jp/coverage/fuel/ExportCSV.java new file mode 100644 index 0000000..f48e24d --- /dev/null +++ b/src/osm/jp/coverage/fuel/ExportCSV.java @@ -0,0 +1,30 @@ +package osm.jp.coverage.fuel; + +import java.io.File; +import java.io.FileNotFoundException; +import java.io.IOException; +import java.sql.SQLException; +import javax.xml.parsers.ParserConfigurationException; +import org.xml.sax.SAXException; +import osm.jp.postgis.PostgisItem; +import osm.jp.postgis.PostgisItems; + +public class ExportCSV { + public static String tableName; + public static String CSV_FILE_NAME = "t_fuel.csv"; + + public static void main (String[] argv) throws ClassNotFoundException, SQLException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + + PostgisItems items = new PostgisItems(); + items.add(new PostgisItem("gmlid", "gmlid")); + items.add(new PostgisItem("idref", "idref")); + items.add(new PostgisItem("area", "area")); + items.add(new PostgisItem("fixed", "fixed1")); + items.add(new PostgisItem("lat", "lat")); + items.add(new PostgisItem("lon", "lon")); + + ToPostgis model = new ToPostgis(); + model.items = items; + model.toCsv(new File(CSV_FILE_NAME)); + } +} diff --git a/src/osm/jp/coverage/police/ExportCSV.java b/src/osm/jp/coverage/police/ExportCSV.java new file mode 100644 index 0000000..402ce55 --- /dev/null +++ b/src/osm/jp/coverage/police/ExportCSV.java @@ -0,0 +1,30 @@ +package osm.jp.coverage.police; + +import java.io.File; +import java.io.FileNotFoundException; +import java.io.IOException; +import java.sql.SQLException; +import javax.xml.parsers.ParserConfigurationException; +import org.xml.sax.SAXException; +import osm.jp.postgis.PostgisItem; +import osm.jp.postgis.PostgisItems; + +public class ExportCSV { + public static String tableName; + public static String CSV_FILE_NAME = "t_police.csv"; + + public static void main (String[] argv) throws ClassNotFoundException, SQLException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + + PostgisItems items = new PostgisItems(); + items.add(new PostgisItem("gmlid", "idref")); + items.add(new PostgisItem("area", "area")); + items.add(new PostgisItem("code", "code")); + items.add(new PostgisItem("fixed", "fixed1")); + items.add(new PostgisItem("lat", "lat")); + items.add(new PostgisItem("lon", "lon")); + + ToPostgis model = new ToPostgis(); + model.items = items; + model.toCsv(new File(CSV_FILE_NAME)); + } +} diff --git a/src/osm/jp/coverage/postoffice/ExportCSV.java b/src/osm/jp/coverage/postoffice/ExportCSV.java new file mode 100644 index 0000000..0d78bd1 --- /dev/null +++ b/src/osm/jp/coverage/postoffice/ExportCSV.java @@ -0,0 +1,29 @@ +package osm.jp.coverage.postoffice; + +import java.io.File; +import java.io.FileNotFoundException; +import java.io.IOException; +import java.sql.SQLException; +import javax.xml.parsers.ParserConfigurationException; +import org.xml.sax.SAXException; +import osm.jp.postgis.PostgisItem; +import osm.jp.postgis.PostgisItems; + +public class ExportCSV { + public static String tableName; + public static String CSV_FILE_NAME = "t_postoffice.csv"; + + public static void main (String[] argv) throws ClassNotFoundException, SQLException, IOException, FileNotFoundException, ParserConfigurationException, SAXException { + + PostgisItems items = new PostgisItems(); + items.add(new PostgisItem("gmlid", "idref")); + items.add(new PostgisItem("area", "area")); + items.add(new PostgisItem("fixed", "fixed1")); + items.add(new PostgisItem("lat", "lat")); + items.add(new PostgisItem("lon", "lon")); + + ToPostgis model = new ToPostgis(); + model.items = items; + model.toCsv(new File(CSV_FILE_NAME)); + } +} diff --git a/src/osm/jp/postgis/ExportCSV.java b/src/osm/jp/postgis/ExportCSV.java deleted file mode 100644 index eb93f91..0000000 --- a/src/osm/jp/postgis/ExportCSV.java +++ /dev/null @@ -1,52 +0,0 @@ -package osm.jp.postgis; - -import java.io.BufferedWriter; -import java.io.File; -import java.io.FileOutputStream; -import java.io.OutputStreamWriter; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import jp.co.areaweb.tools.database.DatabaseTool; - -public class ExportCSV { - public static String tableName; - public static String CSV_FILE_NAME = "t_busstop.csv"; - - public static void main (String[] argv) { - tableName = "t_FUEL"; - CSV_FILE_NAME = tableName +".csv"; - try { - outputDb(); - } catch (Exception e) { - e.printStackTrace(); - } - } - - public static void outputDb() throws Exception { - File csvFile = new File(CSV_FILE_NAME); - try (BufferedWriter ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8")); - Connection con = DatabaseTool.openDb("postgis")) - { - // CSV header - System.out.println("gid,geom,score"); - ow.write("gid,geom,score"); - ow.newLine(); - PreparedStatement ps8 = con.prepareStatement("SELECT gid,idref,fixed,area,geom FROM t_busstop"); - try (ResultSet rset8 = ps8.executeQuery()) { - while (rset8.next()) { - String gid = rset8.getString("gid"); - String geom = rset8.getString("geom"); - int score = rset8.getInt("fixed"); - - String osm_node; - osm_node = ""+ gid +","+ geom +","+ score +""; - System.out.println(osm_node); - ow.write(osm_node); - ow.newLine(); - } - } - ow.flush(); - } - } -} diff --git a/src/osm/jp/postgis/PostgisItem.java b/src/osm/jp/postgis/PostgisItem.java index aedc9e3..c6b922a 100644 --- a/src/osm/jp/postgis/PostgisItem.java +++ b/src/osm/jp/postgis/PostgisItem.java @@ -8,7 +8,7 @@ public int type = 0; public PostgisItem(String postName, String localName, int type) { - this.postName = localName; + this.postName = postName; this.localName = localName; this.type = type; } diff --git a/src/osm/jp/postgis/PostgisItems.java b/src/osm/jp/postgis/PostgisItems.java index f8910fc..c8579b2 100644 --- a/src/osm/jp/postgis/PostgisItems.java +++ b/src/osm/jp/postgis/PostgisItems.java @@ -6,6 +6,7 @@ import java.util.ArrayList; public class PostgisItems extends ArrayList { + String geom = ""; String gmlid = ""; String idref = ""; int area = 0; @@ -53,6 +54,39 @@ lon = rset.getDouble("lon"); } + public void setPostResuit(ResultSet rset) throws SQLException { + for (PostgisItem item : this) { + switch (item.postName) { + case "gmlid": + gmlid = rset.getString(item.postName); + break; + case "idref": + idref = rset.getString(item.postName); + break; + case "area": + area = rset.getInt(item.postName); + break; + case "fixed": + fixed = rset.getInt(item.postName); + break; + case "code": + code = rset.getInt(item.postName); + break; + case "geom": + geom = rset.getString(item.postName); + break; + case "lat": + lat = rset.getDouble(item.postName); + break; + case "lon": + lon = rset.getDouble(item.postName); + break; + default: + break; + } + } + } + public String getSqlStr(String tableName) { StringBuilder sb = new StringBuilder(); sb.append("SELECT "); @@ -63,6 +97,91 @@ sb.append("lat,lon FROM "+ tableName); return sb.toString(); } + + public String getValue() { + StringBuilder sb = new StringBuilder(); + boolean top = true; + for (PostgisItem item : this) { + if (top) { + top = false; + } + else { + sb.append(","); + } + switch (item.postName) { + case "gmlid": + sb.append(gmlid); + break; + case "idref": + sb.append(idref); + break; + case "area": + sb.append(area); + break; + case "fixed": + sb.append(fixed); + break; + case "code": + sb.append(code); + break; + case "geom": + sb.append(geom); + break; + case "lat": + sb.append(lat); + break; + case "lon": + sb.append(lon); + break; + default: + break; + } + } + return sb.toString(); + } + + public String getPostSqlStr(String tableName) { + StringBuilder sb = new StringBuilder(); + sb.append("SELECT "); + boolean top = true; + for (PostgisItem item : this) { + if (top) { + top = false; + } + else { + sb.append(","); + } + switch (item.postName) { + case "lat": + sb.append("ST_Y(geom) AS lat"); + break; + case "lon": + sb.append("ST_X(geom) AS lon"); + break; + default: + sb.append(item.postName); + break; + } + } + sb.append(" FROM t_"+ tableName); + sb.append(" ORDER BY area, gmlid"); + return sb.toString(); + } + + public String getCsvHeader() { + StringBuilder sb = new StringBuilder(); + boolean top = true; + for (PostgisItem item : this) { + if (top) { + top = false; + } + else { + sb.append(","); + } + sb.append(item.postName); + } + return sb.toString(); + } public String getGeomStr() { return String.format( diff --git a/src/osm/jp/postgis/ToPostgis.java b/src/osm/jp/postgis/ToPostgis.java index 42af81f..b91068b 100644 --- a/src/osm/jp/postgis/ToPostgis.java +++ b/src/osm/jp/postgis/ToPostgis.java @@ -1,8 +1,11 @@ package osm.jp.postgis; +import java.io.BufferedWriter; +import java.io.File; import java.io.FileNotFoundException; +import java.io.FileOutputStream; import java.io.IOException; -import java.math.BigDecimal; +import java.io.OutputStreamWriter; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; @@ -108,30 +111,54 @@ throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { String sql = items.getSqlStr(tableName); - try (PreparedStatement ps1 = conHsql.prepareStatement(sql)) { - try (ResultSet rset1 = ps1.executeQuery()) { - while (rset1.next()) { - items.setResuit(rset1); - - String sqlStr = "INSERT INTO t_"+ tableName - +" (gmlid,idref,fixed,area,code,geom) " - + "VALUES (?,?,?,?,?, ST_GeomFromText('POINT(? ?)',4612))"; - try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) { - printMark(); - ps.setString(1, items.gmlid); - ps.setString(2, items.idref); - ps.setInt(3, items.fixed); - ps.setInt(4, items.area); - ps.setInt(5, items.code); - ps.setDouble(6, items.lon); - ps.setDouble(7, items.lat); - ps.executeUpdate(); - } + try (PreparedStatement ps1 = conHsql.prepareStatement(sql); + ResultSet rset1 = ps1.executeQuery()) { + while (rset1.next()) { + items.setResuit(rset1); + + String sqlStr = "INSERT INTO t_"+ tableName + +" (gmlid,idref,fixed,area,code,geom) " + + "VALUES (?,?,?,?,?, ST_GeomFromText('POINT(? ?)',4612))"; + try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) { + printMark(); + ps.setString(1, items.gmlid); + ps.setString(2, items.idref); + ps.setInt(3, items.fixed); + ps.setInt(4, items.area); + ps.setInt(5, items.code); + ps.setDouble(6, items.lon); + ps.setDouble(7, items.lat); + ps.executeUpdate(); } } } } + public void toCsv (File csvFile) + throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException + { + try (Connection conPost = DatabaseTool.openDb("postgis"); + BufferedWriter ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"))) + { + String header = items.getCsvHeader(); + ow.write(header); + ow.newLine(); + + String sql = items.getPostSqlStr(tableName); + PreparedStatement ps8 = conPost.prepareStatement(sql); + try (ResultSet rset8 = ps8.executeQuery()) { + while (rset8.next()) { + items.setPostResuit(rset8); + String osm_node = items.getValue(); + System.out.println(osm_node); + ow.write(osm_node); + ow.newLine(); + } + } + ow.flush(); + } + } + public static int outCnt = 0; public static void printMark() { System.out.print(".");