第一节.空间数据维护

1.postgis空间数据介绍

 PostgreSQL 是一种对象-关系型数据库管理系统（ORDBMS），也是功能最强大、特性最丰富和最复杂的自由软件数据库系统。它起源于伯克利（BSD）的数据库功能最强大、特性最丰富和最复杂的研究计划，是最重要的开源数据库产品开发项目之一， 有着非常广泛的用户。

2 保存空间点位信息

请求参数详情

构造请求的 geometry参数，传入点位信息

  "geometry":"SRID=4326;POINT(111.7991 39.7364)",

如下：

{
 
   "name":"sad ", 
   "geometry":"SRID=4326;POINT(111.7991 39.7364)",
    "x":111.7991,
    "y":39.7364

}

3 后台保存

后台的控制层和一般的保存是一致的，需要变化的就是mybaties配置文件

 @LogAnnotation(firstLevel = "基础数据", secondLevel = "空间点", noteLevel = "新增")
    @PostMapping(value = "save")
    public Map save(@RequestBody TBaseGeompoint TBaseGeompoint, HttpServletRequest request) {
        Map map = new HashMap(4);
        try{
            Map  mapuser= getParametersUserinfo(request);
            String userid=mapuser.get("userid").toString();
            String name=TBaseGeompoint.getName();// 前端是空 不允许录入到后台 ，此处一定有数据

            boolean  iswd= FilterUtils.verification(name);// 特殊字符校验 如果是 文章内容注意不要校验
            if(!iswd){
                map.put("flag", false);
                map.put("msg", StatusCode.C00511.getMsg());
                map.put("status",StatusCode.C00511.getCode());
                return map;
            }
            //数据验证 该字段是否已经存在数据内 存在的话 就不能录入
            Map mapName = new HashMap(4);
            mapName.put("name",name);
            mapName.put("id",null);
            Long eq= objServices.countById(mapName);
            if(eq !=0){
                map.put("flag", false);
                map.put("msg", StatusCode.C00510.getMsg());
                map.put("status", StatusCode.C00510.getCode());
                return map;
            }
            // 处理 最大值
            TBaseGeompoint objMax=objServices.selectByOrderNumMax();
           if(null==objMax){
                TBaseGeompoint.setOrderNum(1);// 如果是第一个值默认给1
            }else{
               if(null==objMax.getOrderNum()){
                   objMax.setOrderNum(1);
               }
              int ordernUm=objMax.getOrderNum()+1;// 如果是后续新增 增加1
              TBaseGeompoint.setOrderNum(ordernUm);
            }
           TBaseGeompoint.setCreateUser(Integer.parseInt(userid));
            TBaseGeompoint.setCreateTime(new Date());
            TBaseGeompoint.setDeleteFlag(1);
            int  i =objServices.save(TBaseGeompoint);
            if (i == 1) {
                map.put("flag", true);
                map.put("msg", StatusCode.C00200.getMsg());
                map.put("status", StatusCode.C00200.getCode());
            } else {
                map.put("flag", false);
                map.put("msg", StatusCode.C00201.getMsg());
                map.put("status", StatusCode.C00201.getCode());
            }
        } catch (Exception e) {
            logger.error(StatusCode.C00400.getMsg(), e);
            map.put("flag", false);
            map.put("msg", StatusCode.C00400.getMsg());
            map.put("status", StatusCode.C00400.getCode());
        }
        return map;
    }

接口层

    /**
     * 测试空间点保存
     *
     * @param TBaseGeompoint
     * @return
     */
    public int  save(TBaseGeompoint TBaseGeompoint);

实现层

   @Override
    public  int  save(TBaseGeompoint TBaseGeompoint) {
        return mapper.insertSelective(TBaseGeompoint);//insertSelective
    }

mybaties 实现注意事项

geometry就是varchar 类型

保存如下：

此时注意mybaties需要改造 此处的空间函数

GeomFromEWKT(#{geometry,jdbcType=VARCHAR}),

代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yinghui.soft.webgeo.baseGeomPoint.mapper.TBaseGeompointMapper">
  <resultMap id="BaseResultMap" type="com.yinghui.soft.webgeo.baseGeomPoint.model.TBaseGeompoint">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="geometry" jdbcType="VARCHAR" property="geometry" />
    <result column="order_num" jdbcType="INTEGER" property="orderNum" />
    <result column="x" jdbcType="NUMERIC" property="x" />
    <result column="y" jdbcType="NUMERIC" property="y" />
    <result column="delete_flag" jdbcType="INTEGER" property="deleteFlag" />
    <result column="create_user" jdbcType="INTEGER" property="createUser" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />

  </resultMap>
  <resultMap extends="BaseResultMap" id="TObjectMap" type="com.yinghui.soft.webgeo.baseGeomPoint.model.TBaseGeompoint">
    <result column="createuser_name" jdbcType="VARCHAR" property="username"/>
  </resultMap>


  <sql id="Base_Column_List">
    id, name, geometry, order_num, delete_flag, create_user, create_time,x,y
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_base_geompoint
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_base_geompoint
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.yinghui.soft.webgeo.baseGeomPoint.model.TBaseGeompoint">
    insert into t_base_geompoint ( name, geometry,
      order_num, delete_flag, create_user, 
      create_time,x,y)
    values ( #{name,jdbcType=VARCHAR},GeomFromEWKT(#{geometry,jdbcType=VARCHAR}),
      #{orderNum,jdbcType=INTEGER}, #{deleteFlag,jdbcType=INTEGER}, #{createUser,jdbcType=INTEGER}, 
      #{createTime,jdbcType=TIMESTAMP}})
  </insert>
  <insert id="insertSelective" parameterType="com.yinghui.soft.webgeo.baseGeomPoint.model.TBaseGeompoint">
    insert into t_base_geompoint
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="name != null">
        name,
      </if>
      <if test="geometry != null">
        geometry,
      </if>
      <if test="orderNum != null">
        order_num,
      </if>
      <if test="deleteFlag != null">
        delete_flag,
      </if>
      <if test="createUser != null">
        create_user,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="x != null">
        x,
      </if>
      <if test="y != null">
        y,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
        <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="geometry != null">
         GeomFromEWKT(#{geometry,jdbcType=VARCHAR}),
      </if>
      <if test="orderNum != null">
        #{orderNum,jdbcType=INTEGER},
      </if>
      <if test="deleteFlag != null">
        #{deleteFlag,jdbcType=INTEGER},
      </if>
      <if test="createUser != null">
        #{createUser,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="x != null">
        #{x,jdbcType=NUMERIC},
      </if>
      <if test="y != null">
        #{y,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.yinghui.soft.webgeo.baseGeomPoint.model.TBaseGeompoint">
    update t_base_geompoint
    <set>
      <if test="name != null">
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="geometry != null">
        geometry = GeomFromEWKT(#{geometry,jdbcType=VARCHAR}),
      </if>
      <if test="orderNum != null">
        order_num = #{orderNum,jdbcType=INTEGER},
      </if>
      <if test="deleteFlag != null">
        delete_flag = #{deleteFlag,jdbcType=INTEGER},
      </if>
      <if test="createUser != null">
        create_user = #{createUser,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="x != null">
        x = #{x,jdbcType=NUMERIC},
      </if>
      <if test="y != null">
        y = #{y,jdbcType=NUMERIC},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.yinghui.soft.webgeo.baseGeomPoint.model.TBaseGeompoint">
    update t_base_geompoint
    set name = #{name,jdbcType=VARCHAR},
      geometry = #{geometry,jdbcType=VARCHAR},
      order_num = #{orderNum,jdbcType=INTEGER},
      delete_flag = #{deleteFlag,jdbcType=INTEGER},
      create_user = #{createUser,jdbcType=INTEGER},
      create_time = #{createTime,jdbcType=TIMESTAMP}
      x = #{x,jdbcType=NUMERIC}
      y = #{y,jdbcType=NUMERIC}
    where id = #{id,jdbcType=INTEGER}
  </update>

  <sql id="findPage_where">
    <where>
      <!-- 查询 -->
      <if test="name != null">
        AND (tsn.name like '%${name}%')
      </if>
      <if test="deleteFlag != null">
        AND  tsn.delete_flag = #{deleteFlag}
      </if>

    </where>
  </sql>
  <sql id="limit">
    <if test="offset!=null and limit!=null">
      limit #{limit}  offset  #{offset}
    </if>
  </sql>

  <select id="findPage_count" resultType="java.lang.Long" useCache="false">
    SELECT count(1) FROM  t_base_geompoint  tsn
    left join t_user b on  tsn.create_user = b.id
    <include refid="findPage_where"/>
  </select>

  <select id="findPage" resultMap="TObjectMap" useCache="false">
      SELECT tsn.id ,name,ST_AsText(geometry) as geometry,tsn.x,tsn.y,tsn.order_num,tsn.delete_flag,tsn.create_user,tsn.create_time,
    b.user_name as createuser_name,tsn.delete_flag

    FROM t_base_geompoint tsn left join t_user b on  tsn.create_user = b.id
    <include refid="findPage_where"/>

    order   by  order_num asc
    <include refid="limit"/>
  </select>
  <!--修改校验-->
  <select id="countById" parameterType="Map" resultType="java.lang.Long">
    select   count(1)
    from t_base_geompoint  tsn
    where  1=1
    <if test="id != null">
      AND id NOT IN (#{id,jdbcType=INTEGER})
    </if>
    AND tsn.name='${name}'
  </select>

  <select id="selectByOrderNumMax" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_base_geompoint
    order by order_num DESC
    LIMIT  1  OFFSET 0
  </select>
  <select id="selectByOrderNumNext" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_base_geompoint
    where  order_num  &gt; #{order_num,jdbcType=INTEGER}   and delete_flag=1  order by order_num asc
    LIMIT  1  OFFSET 0
  </select>
  <select id="selectByOrderNumUp" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_base_geompoint
    where  order_num  &lt; #{order_num,jdbcType=INTEGER}  and delete_flag=1  order by order_num DESC
    LIMIT  1  OFFSET 0
  </select>
</mapper>

4 查询空间数据

注意格式化geometry字段，采用空间函数 st_astext

  <select id="findPage" resultMap="TObjectMap" useCache="false">
      SELECT tsn.id ,name,ST_AsText(geometry) as geometry,tsn.x,tsn.y,tsn.order_num,tsn.delete_flag,tsn.create_user,tsn.create_time,
    b.user_name as createuser_name,tsn.delete_flag

    FROM t_base_geompoint tsn left join t_user b on  tsn.create_user = b.id
    <include refid="findPage_where"/>

    order   by  order_num asc
    <include refid="limit"/>
  </select>

5 修改空间数据

注意空间函数

传入参数 主键ID ,  geometry字段格式

代码如下

 <update id="updateByPrimaryKeySelective" parameterType="com.yinghui.soft.webgeo.baseGeomPoint.model.TBaseGeompoint">
    update t_base_geompoint
    <set>
      <if test="name != null">
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="geometry != null">
        geometry = GeomFromEWKT(#{geometry,jdbcType=VARCHAR}),
      </if>
      <if test="orderNum != null">
        order_num = #{orderNum,jdbcType=INTEGER},
      </if>
      <if test="deleteFlag != null">
        delete_flag = #{deleteFlag,jdbcType=INTEGER},
      </if>
      <if test="createUser != null">
        create_user = #{createUser,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="x != null">
        x = #{x,jdbcType=NUMERIC},
      </if>
      <if test="y != null">
        y = #{y,jdbcType=NUMERIC},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>

控制层和一般的请求没区别

  /**
     *  修改
     */
    @LogAnnotation(firstLevel = "基础数据", secondLevel = "空间点", noteLevel = "修改")
    @PostMapping(value = "update")
    public Map update(@RequestBody TBaseGeompoint TBaseGeompoint, HttpServletRequest request) {
        Map map = new HashMap(4);
        try{
            Map  mapuser= getParametersUserinfo(request);
            String userid=mapuser.get("userid").toString();


           String name=TBaseGeompoint.getName();// 前端是空 不允许录入到后台 ，此处一定有数据
            // 等保 需要校验
            boolean  iswd= FilterUtils.verification(name);// 特殊字符校验 如果是 文章内容注意不要校验
            if(!iswd){
                map.put("flag", false);
                map.put("msg", StatusCode.C00511.getMsg());
                map.put("status",StatusCode.C00511.getCode());
                return map;
            }

            //数据验证
            Map mapName = new HashMap(4);
            mapName.put("name",TBaseGeompoint.getName());
            mapName.put("id",TBaseGeompoint.getId());// 排除自己，再自己之外不能有重复的名字
            Long eq= objServices.countById(mapName);
            if(eq !=0){
                map.put("flag", false);
                map.put("msg", StatusCode.C00509.getMsg());
                map.put("status", StatusCode.C00509.getCode());
                return map;
            }

            TBaseGeompoint.setCreateUser(Integer.parseInt(userid));
            TBaseGeompoint.setCreateTime(new Date());
            //  TBaseGeompoint.setDeleteFlag(1);
            int  i =objServices.update(TBaseGeompoint);
            if (i == 1) {
                map.put("flag", true);
                map.put("msg", StatusCode.C00202.getMsg());
                map.put("status", StatusCode.C00202.getCode());
            } else {
                map.put("flag", false);
                map.put("msg", StatusCode.C00203.getMsg());
                map.put("status", StatusCode.C00203.getCode());
            }
        } catch (Exception e) {
            logger.error(StatusCode.C00400.getMsg(), e);
            map.put("flag", false);
            map.put("msg", StatusCode.C00400.getMsg());
            map.put("status", StatusCode.C00400.getCode());
        }
        return map;
    }