Newer
Older
osmCoverage / src / osm / jp / postgis / ToGeoJSON.java
@hayashi hayashi on 5 May 2018 3 KB 6;派出所
  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 ToGeoJSON(String tableName) {
  48. this.tableName = tableName;
  49. }
  50.  
  51. public void outputDb(Connection con, String whereStr, File outputFile, boolean slim) throws Exception {
  52. try (BufferedWriter ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile), "UTF-8"))) {
  53. String sql = "SELECT row_to_json(feature) FROM ("
  54. + "select 'Feature' As type, "
  55. + "ST_AsGeoJSON(ST_Transform(t_"+ this.tableName +".geom,4326))::json As geometry, "
  56. + "row_to_json(("
  57. + "SELECT p FROM (SELECT t_"+ this.tableName +".idref, t_"+ this.tableName +".fixed) AS p"
  58. + ")) AS properties From t_"+ this.tableName +" "+ whereStr +" order by area,idref) As feature";
  59. if (slim) {
  60. sql = "SELECT row_to_json(feature) FROM ("
  61. + "select 'Feature' As type, "
  62. + "ST_AsGeoJSON(ST_Transform(t_"+ this.tableName +".geom,4326))::json As geometry From t_"+ this.tableName +" "
  63. + whereStr +" order by area,idref) As feature";
  64. }
  65. System.out.println(sql);
  66.  
  67. String line = "{" +
  68. "\"type\": \"FeatureCollection\"," +
  69. "\"features\": [";
  70. System.out.println(line);
  71. ow.write(line);
  72. ow.newLine();
  73.  
  74. PreparedStatement ps8 = con.prepareStatement(sql);
  75. try (ResultSet rset8 = ps8.executeQuery()) {
  76. boolean top = true;
  77. while (rset8.next()) {
  78. if (top) {
  79. top = false;
  80. }
  81. else {
  82. line = ",";
  83. System.out.println(line);
  84. ow.write(line);
  85. ow.newLine();
  86. }
  87. line = rset8.getString(1);
  88. System.out.print(line);
  89. ow.write(line);
  90. }
  91. }
  92. System.out.println();
  93. ow.newLine();
  94. line = "]}";
  95. System.out.println(line);
  96. ow.write(line);
  97. ow.newLine();
  98. ow.flush();
  99. }
  100. }
  101. }