第二节.空间数据维护语句的使用

1.  st_astext

查询解译geometry字段，将二进制转换成可获取的字符值

        String sql="SELECT id,name,height,color,width,outlineColor,ST_AsText(geometry) as geom  from  geo_text_tagging  t where  "+where+" limit  "+pagesize+"  offset "+ start;
        List list = jdbcTemplate.queryForList(sql);

2插入语句

建议采用mybaties模式，此处仅做字段说明

  String sql="INSERT INTO geo_text_tagging (geometry,name,height,color,width,outlineColor)  " ;
        sql+="values (GeomFromEWKT('SRID=4326;POINT("+x+" "+y+" "+z+")'),'"+name+"', ";
        sql+=" "+height+",'"+color+"',"+width+",'"+outlineColor+"' )";

3修改

 String sql="update l_model_maintain  set geometry=GeomFromEWKT('SRID=4326;POINT("+x+" "+y+" "+z+")'), height="+height+", heading="+heading+"," +
                " range="+range+", pitch="+pitch+"   where id="+id;

  JSONObject postdata = JSONObject.parseObject(String.valueOf(map.get("postdata")));
        int height=0;
        int range=0;
        int heading=0;
        int pitch=0;
        int id=0;

        String x="";
        if(postdata.containsKey("x")
                &&!StringUtils.isEmpty(postdata.getString("x"))){
            x=postdata.getString("x");
        }
        String y="";
        if(postdata.containsKey("y")
                &&!StringUtils.isEmpty(postdata.getString("y"))){
            y=postdata.getString("y");
        }
        String z="";
        if(postdata.containsKey("z")
                &&!StringUtils.isEmpty(postdata.getString("z"))){
            z=postdata.getString("z");
        }
        if(postdata.containsKey("height")
                &&!StringUtils.isEmpty(postdata.getString("height"))){
            height=Integer.parseInt(postdata.getString("height").toString());
        }

        if(postdata.containsKey("range")
                &&!StringUtils.isEmpty(postdata.getString("range"))){
            range=Integer.parseInt(postdata.getString("range").toString());
        }
        if(postdata.containsKey("heading")
                &&!StringUtils.isEmpty(postdata.getString("heading"))){
            heading=Integer.parseInt(postdata.getString("heading").toString());
        }
        if(postdata.containsKey("pitch")
                &&!StringUtils.isEmpty(postdata.getString("pitch"))){
            pitch=Integer.parseInt(postdata.getString("pitch").toString());
        }
        if(postdata.containsKey("id")
                &&!StringUtils.isEmpty(postdata.getString("id"))){
            id=Integer.parseInt(postdata.getString("id").toString());
        }
        String sql="update l_model_maintain  set geometry=GeomFromEWKT('SRID=4326;POINT("+x+" "+y+" "+z+")'), height="+height+", heading="+heading+"," +
                " range="+range+", pitch="+pitch+"   where id="+id;

        int  list = jdbcTemplate.update(sql);
        if(list >0){
            return true;
        }
        return  false ;

4 删除

 @Override
    public HashMap<String, Object> delete(Map paramMap) {
        HashMap<String, Object> map = new HashMap<String, Object>();
        try{
            JSONObject postdata = JSONObject.parseObject(String.valueOf(paramMap.get("postdata")));
            if(postdata.size()>0){
                if(postdata.containsKey("id")
                        &&!StringUtils.isEmpty(postdata.getString("id"))){
                    int id = Integer.parseInt(postdata.getString("id"));
                    
                    String sql="delete from geo_text_tagging where id="+id;

                    int row = jdbcTemplate.update(sql);
                    if(row>0){
                        map.put("flag", true);
                        map.put("msg", "删除成功");
                    }else{
                        map.put("flag", false);
                        map.put("msg", "删除失败");
                    }
                }
            }
        }catch(Exception e){
            e.printStackTrace();
            map.put("flag", false);
            map.put("msg", "删除失败");
        }
        return map;
    }