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 > #{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 < #{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;
}