Newer
Older
osmCoverage / src / osm / jp / postgis / ToGeoJSON.java
@hayashi hayashi on 13 May 2018 3 KB overraide
  1. package osm.jp.postgis;
  2.  
  3. import java.io.BufferedWriter;
  4. import java.io.File;
  5. import java.io.FileOutputStream;
  6. import java.io.OutputStreamWriter;
  7. import java.sql.Connection;
  8. import java.sql.PreparedStatement;
  9. import java.sql.ResultSet;
  10.  
  11. /**
  12. * PostGISデータをGeoJSONファイルに出力する。
  13. * 出力ファイル名: "fuel.json"
  14. * テーブル名: t_fuel
  15. * fixed OSMの周辺に存在するかどうか、存在しない場合は0,存在する場合は1。ブランド有りは50。
  16. * geom PostGIS形式の位置情報(4612:)
  17. * SELECT row_to_json(feature) FROM (
  18. select 'Feature' As type, ST_AsGeoJSON(ST_Transform(t_fuel.geom,4326))::json As geometry, row_to_json((
  19. SELECT p FROM (SELECT t_fuel.idref, t_fuel.fixed) AS p)) AS properties From t_fuel) As feature
  20. *
  21. * @author yuu
  22. *
  23. */
  24. public class ToGeoJSON {
  25. /*
  26. public static void main (String[] argv) throws Exception {
  27. boolean slim = false;
  28. for (String arg : argv) {
  29. if (arg.equals("-slim")) {
  30. slim = true;
  31. }
  32. }
  33. ToGeoJSON obj = new ToGeoJSON(DbPostoffice.TABLE_NAME);
  34. Connection con = DatabaseTool.openDb("postgis");
  35. try {
  36. obj.outputDb(con, "", new File("postoffice.json"), false);
  37. obj.outputDb(con, "WHERE fixed=0", new File("postoffice0.json"), slim);
  38. obj.outputDb(con, "WHERE fixed>0 and fixed<50", new File("postoffice1.json"), slim);
  39. obj.outputDb(con, "WHERE fixed>49", new File("postoffice2.json"), slim);
  40. }
  41. finally {
  42. DatabaseTool.closeDb(con);
  43. }
  44. }
  45. */
  46. public String tableName;
  47. public String sqlForm1 = "SELECT row_to_json(feature) FROM ("
  48. + "select 'Feature' As type, "
  49. + "ST_AsGeoJSON(ST_Transform(t_%s.geom,4326))::json As geometry, "
  50. + "row_to_json(("
  51. + "SELECT p FROM (SELECT t_%s.idref, t_%s.fixed) AS p"
  52. + ")) AS properties From t_%s %s order by area,idref) As feature";
  53. public String sqlForm2 = "SELECT row_to_json(feature) FROM ("
  54. + "select 'Feature' As type, "
  55. + "ST_AsGeoJSON(ST_Transform(t_%s.geom,4326))::json As geometry From t_%s %s order by area,idref) As feature";
  56. public ToGeoJSON(String tableName) {
  57. this.tableName = tableName;
  58. }
  59.  
  60. public void outputDb(Connection con, String whereStr, File outputFile, boolean slim) throws Exception {
  61. try (BufferedWriter ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile), "UTF-8"))) {
  62. String sql = String.format(sqlForm1, tableName,tableName,tableName,tableName,whereStr);
  63. if (slim) {
  64. sql = String.format(sqlForm2, tableName,tableName,whereStr);
  65. }
  66. System.out.println(sql);
  67.  
  68. String line = "{" +
  69. "\"type\": \"FeatureCollection\"," +
  70. "\"features\": [";
  71. System.out.println(line);
  72. ow.write(line);
  73. ow.newLine();
  74.  
  75. PreparedStatement ps8 = con.prepareStatement(sql);
  76. try (ResultSet rset8 = ps8.executeQuery()) {
  77. boolean top = true;
  78. while (rset8.next()) {
  79. if (top) {
  80. top = false;
  81. }
  82. else {
  83. line = ",";
  84. System.out.println(line);
  85. ow.write(line);
  86. ow.newLine();
  87. }
  88. line = rset8.getString(1);
  89. System.out.print(line);
  90. ow.write(line);
  91. }
  92. }
  93. System.out.println();
  94. ow.newLine();
  95. line = "]}";
  96. System.out.println(line);
  97. ow.write(line);
  98. ow.newLine();
  99. ow.flush();
  100. }
  101. }
  102. }