Fork: 0
coverage / osmCoverage
Transfer to URL with SHA Find file
Newer
Older
osmCoverage / src / osm / jp / postgis / PostgisItems.java
@hayashi hayashi on 7 Jul 2018 5 KB export csv file
Raw Blame History 
  1. package osm.jp.postgis;
  2.  
  3. import java.math.BigDecimal;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.util.ArrayList;
  7.  
  8. public class PostgisItems extends ArrayList<PostgisItem> {
  9.     String geom = "";
  10.     String gmlid = "";
  11.     String idref = "";
  12.     int area = 0;
  13.     int fixed = 0;
  14.     int code = 0;
  15.     double lat = 0.0D;
  16.     double lon = 0.0D;
  17.     
  18.     public static void main() {
  19.         PostgisItems items = new PostgisItems();
  20.         items.add(new PostgisItem("gmlid", "gmlid"));
  21.         items.add(new PostgisItem("idref", "idref"));
  22.         items.add(new PostgisItem("area", "area"));
  23.         items.add(new PostgisItem("fixed", "fixed1"));
  24.         items.add(new PostgisItem("code", "code"));
  25.     }
  26.     
  27.     public PostgisItems() {
  28.         super();
  29.     }
  30.  
  31.     public void setResuit(ResultSet rset) throws SQLException {
  32.         for (PostgisItem item : this) {
  33.             switch (item.postName) {
  34.                 case "gmlid":
  35.                     gmlid = rset.getString(item.localName);
  36.                     break;
  37.                 case "idref":
  38.                     idref = rset.getString(item.localName);
  39.                     break;
  40.                 case "area":
  41.                     area = rset.getInt(item.localName);
  42.                     break;
  43.                 case "fixed":
  44.                     fixed = rset.getInt(item.localName);
  45.                     break;
  46.                 case "code":
  47.                     code = rset.getInt(item.localName);
  48.                     break;
  49.                 default:
  50.                     break;
  51.             }
  52.         }
  53.         lat = rset.getDouble("lat");
  54.         lon = rset.getDouble("lon");
  55.     }
  56.     
  57.     public void setPostResuit(ResultSet rset) throws SQLException {
  58.         for (PostgisItem item : this) {
  59.             switch (item.postName) {
  60.                 case "gmlid":
  61.                     gmlid = rset.getString(item.postName);
  62.                     break;
  63.                 case "idref":
  64.                     idref = rset.getString(item.postName);
  65.                     break;
  66.                 case "area":
  67.                     area = rset.getInt(item.postName);
  68.                     break;
  69.                 case "fixed":
  70.                     fixed = rset.getInt(item.postName);
  71.                     break;
  72.                 case "code":
  73.                     code = rset.getInt(item.postName);
  74.                     break;
  75.                 case "geom":
  76.                     geom = rset.getString(item.postName);
  77.                     break;
  78.                 case "lat":
  79.                     lat = rset.getDouble(item.postName);
  80.                     break;
  81.                 case "lon":
  82.                     lon = rset.getDouble(item.postName);
  83.                     break;
  84.                 default:
  85.                     break;
  86.             }
  87.         }
  88.     }
  89.  
  90.     public String getSqlStr(String tableName) {
  91.         StringBuilder sb = new StringBuilder();
  92.         sb.append("SELECT ");
  93.         for (PostgisItem item : this) {
  94.             sb.append(item.localName);
  95.             sb.append(",");
  96.         }
  97.         sb.append("lat,lon FROM "+ tableName);
  98.         return sb.toString();
  99.     }
  100.  
  101.     public String getValue() {
  102.         StringBuilder sb = new StringBuilder();
  103.         boolean top = true;
  104.         for (PostgisItem item : this) {
  105.             if (top) {
  106.                 top = false;
  107.             }
  108.             else {
  109.                 sb.append(",");
  110.             }
  111.             switch (item.postName) {
  112.                 case "gmlid":
  113.                     sb.append(gmlid);
  114.                     break;
  115.                 case "idref":
  116.                     sb.append(idref);
  117.                     break;
  118.                 case "area":
  119.                     sb.append(area);
  120.                     break;
  121.                 case "fixed":
  122.                     sb.append(fixed);
  123.                     break;
  124.                 case "code":
  125.                     sb.append(code);
  126.                     break;
  127.                 case "geom":
  128.                     sb.append(geom);
  129.                     break;
  130.                 case "lat":
  131.                     sb.append(lat);
  132.                     break;
  133.                 case "lon":
  134.                     sb.append(lon);
  135.                     break;
  136.                 default:
  137.                     break;
  138.             }
  139.         }
  140.         return sb.toString();
  141.     }
  142.     
  143.     public String getPostSqlStr(String tableName) {
  144.         StringBuilder sb = new StringBuilder();
  145.         sb.append("SELECT ");
  146.         boolean top = true;
  147.         for (PostgisItem item : this) {
  148.             if (top) {
  149.                 top = false;
  150.             }
  151.             else {
  152.                 sb.append(",");
  153.             }
  154.             switch (item.postName) {
  155.                 case "lat":
  156.                     sb.append("ST_Y(geom) AS lat");
  157.                     break;
  158.                 case "lon":
  159.                     sb.append("ST_X(geom) AS lon");
  160.                     break;
  161.                 default:
  162.                     sb.append(item.postName);
  163.                     break;
  164.             }
  165.         }
  166.         sb.append(" FROM t_"+ tableName);
  167.         sb.append(" ORDER BY area, gmlid");
  168.         return sb.toString();
  169.     }
  170.     
  171.     public String getCsvHeader() {
  172.         StringBuilder sb = new StringBuilder();
  173.         boolean top = true;
  174.         for (PostgisItem item : this) {
  175.             if (top) {
  176.                 top = false;
  177.             }
  178.             else {
  179.                 sb.append(",");
  180.             }
  181.             sb.append(item.postName);
  182.         }
  183.         return sb.toString();
  184.     }
  185.     
  186.     public String getGeomStr() {
  187.         return String.format(
  188.             "ST_GeomFromText('POINT(%s %s)', 4612)", 
  189.             BigDecimal.valueOf(lon).toPlainString(), 
  190.             BigDecimal.valueOf(lat).toPlainString()
  191.         );
  192.     }
  193. }