接上文，我们的数据里是有几何类型的，点和面。
我们在navicat中运行一条sql：SELECT geom FROM v6_time_cnty_pts_utf_wgs84 LIMIT 1
查出的结果geom是0101000020E6100000A165DD3F16C55B4089963C9E96814340
这种格式是geohash编码的。
再运行另一条sql：SELECT st_astext(geom) FROM v6_time_cnty_pts_utf_wgs84 LIMIT 1
POINT(111.079483 39.012409)
这种是WKT格式。
都是空间数据的存储格式，WKT比geohash编码要直观。
在mybatis中，直接查geometry对象，返回的是字符类型的geohash，但是交互的时候，我们不能返一串geohash码。对于后端来说，我们应该直接操作geometry类，给前端提供json数组。
还用之前的程序，看看怎么对geometry对象进行增删改查。
Springboot2+mybatis+postgresql+typehandler+jts；
一、引入依赖
Jts是空间处理jar包，功能很全很强大，这个jar包的maven依赖，我们之前已经在pom中引用了。
再加一个解析json的jar包。
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.6.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.history</groupId> <artifactId>gismap</artifactId> <version>0.0.1-SNAPSHOT</version> <name>gismap</name> <description>Demo project for Spring Boot</description> <properties> <!-- 标注一下编码为utf8,jdk版本为1.8--> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.2</version> <!-- <scope>runtime</scope>--> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.4</version> </dependency> <!--解析json的--> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> </dependency> <dependency> <groupId>com.fasterxml.jackson.datatype</groupId> <artifactId>jackson-datatype-joda</artifactId> </dependency> <dependency> <groupId>com.fasterxml.jackson.module</groupId> <artifactId>jackson-module-parameter-names</artifactId> </dependency> <!-- 分页插件 --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.5</version> </dependency> <!-- alibaba的druid数据库连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.9</version> </dependency> <!-- 解析几何geometry对象用的--> <dependency> <groupId>com.vividsolutions</groupId> <artifactId>jts</artifactId> <version>1.13</version> </dependency> <!--引入alibaba的json处理jar包--> <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
二、增加mybatis自定义类
新建一个包：D:\gismap\java\gismap\src\main\java\com\history\gismap\mybatis
在这个package包下新建一个类，D:\gismap\java\gismap\src\main\java\com\history\gismap\mybatis\GeometryTypeHandler.java，扩展mybatis的typehandler，WKBReader.hexToBytes(pGgeometry.getValue())就是从解码geohash，获取geometry类。
package com.history.gismap.mybatis; import com.vividsolutions.jts.geom.Geometry; import com.vividsolutions.jts.io.ParseException; import com.vividsolutions.jts.io.WKBReader; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.MappedTypes; import org.postgresql.util.PGobject; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; @MappedTypes(Geometry.class) public class GeometryTypeHandler extends BaseTypeHandler<Geometry> { public void setNonNullParameter(PreparedStatement preparedStatement, int i, Geometry geometry, JdbcType jdbcType) throws SQLException { PGobject pGobject=new PGobject(); pGobject.setValue(geometry.toString()); pGobject.setType("geometry"); preparedStatement.setObject(i,pGobject); } public Geometry getNullableResult(ResultSet resultSet, String columnName) throws SQLException { PGobject pGgeometry= (PGobject) resultSet.getObject(columnName); if(pGgeometry==null){ return null; }else{ WKBReader wkbReader=new WKBReader(); try { return wkbReader.read(WKBReader.hexToBytes(pGgeometry.getValue())); } catch (ParseException e) { e.printStackTrace(); return null; } } } public Geometry getNullableResult(ResultSet resultSet, int columnIndex) throws SQLException { PGobject pGgeometry= (PGobject) resultSet.getObject(columnIndex); if(pGgeometry==null){ return null; }else{ WKBReader wkbReader=new WKBReader(); try { return wkbReader.read(WKBReader.hexToBytes(pGgeometry.getValue())); } catch (ParseException e) { e.printStackTrace(); return null; } } } public Geometry getNullableResult(CallableStatement callableStatement, int i) throws SQLException { PGobject pGgeometry= (PGobject) callableStatement.getObject(i); if(pGgeometry==null){ return null; }else{ WKBReader wkbReader=new WKBReader(); try { return wkbReader.read(WKBReader.hexToBytes(pGgeometry.getValue())); } catch (ParseException e) { e.printStackTrace(); return null; } } } }
三、修改model
加一个geometry属性。
D:\gismap\java\gismap\src\main\java\com\history\gismap\model\PointModel.java
package com.history.gismap.model; import com.vividsolutions.jts.geom.Geometry; import lombok.Getter; import lombok.Setter; import lombok.ToString; @Getter @Setter @ToString public class PointModel { private Integer gId; private String nameCh; private Geometry geometry; }
四、修改dao
这个文件没啥要改的。
D:\gismap\java\gismap\src\main\java\com\history\gismap\dao\MapDao.java
package com.history.gismap.dao; import com.history.gismap.model.PointModel; import org.apache.ibatis.annotations.Param; import org.springframework.stereotype.Service; import java.util.List; @Service public interface MapDao { List<PointModel> getCntyPoint(@Param("gId") Integer gId); int insertCntyPoint(PointModel pointModel); int updateCntyPoint(PointModel pointModel); int deleteCntyPoint(@Param("gId") Integer gId); }
五、修改mapper
主要是加上typeHandler="com.history.gismap.mybatis.GeometryTypeHandler"
D:\gismap\java\gismap\src\main\resources\mapper\HistoryGISMapper.xml
<?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.history.gismap.dao.MapDao" > <resultMap id="pointModelResult" type="com.history.gismap.model.PointModel"> <result property="gId" column="gid" jdbcType="BIGINT"/> <result property="nameCh" column="name_ch" jdbcType="VARCHAR"/> <result property="geometry" column="geom" typeHandler="com.history.gismap.mybatis.GeometryTypeHandler"/> </resultMap> <sql id="BASE_TABLE"> v6_time_cnty_pts_utf_wgs84 </sql> <sql id="BASE_COLUMN"> gid,name_ch,geom </sql> <select id="getCntyPoint" resultMap="pointModelResult"> SELECT <include refid="BASE_COLUMN"></include> FROM <include refid="BASE_TABLE"/> WHERE gid=#{gId} </select> <insert id="insertCntyPoint" parameterType="com.history.gismap.model.PointModel"> INSERT INTO <include refid="BASE_TABLE"/> <trim prefix="(" suffix=")" suffixOverrides=","> <if test="gId != null"> gid, </if> <if test="nameCh != null"> name_ch, </if> <if test="geometry != null"> geom, </if> </trim> <trim prefix="VALUES(" suffix=")" suffixOverrides=","> <if test="gId != null"> #{gId, jdbcType=BIGINT}, </if> <if test="nameCh != null"> #{nameCh, jdbcType=VARCHAR}, </if> <if test="geometry != null"> #{geometry,typeHandler=com.history.gismap.mybatis.GeometryTypeHandler} </if> </trim> </insert> <update id="updateCntyPoint" parameterType="com.history.gismap.model.PointModel"> UPDATE <include refid="BASE_TABLE"/> SET name_ch=#{nameCh}, geom=#{geometry,typeHandler=com.history.gismap.mybatis.GeometryTypeHandler} WHERE gid=#{gId} </update> <delete id="deleteCntyPoint" parameterType="com.history.gismap.model.PointModel"> DELETE FROM <include refid="BASE_TABLE"/> WHERE gid=#{gId} </delete> </mapper>
六、修改service
Service没改。
D:\gismap\java\gismap\src\main\java\com\history\gismap\service\MapService.java
package com.history.gismap.service; import com.history.gismap.model.PointModel; import org.springframework.stereotype.Service; import java.util.List; public interface MapService { List<PointModel> getCntyPointByGid(Integer gId); int addCntyPoint(PointModel pointModel); int modifyCntyPoint(PointModel pointModel); int removeCntyPoint(Integer gId); }
impl也没有变动。
D:\gismap\java\gismap\src\main\java\com\history\gismap\service\impl\MapServiceImpl.java
package com.history.gismap.service.impl; import com.history.gismap.dao.MapDao; import com.history.gismap.model.PointModel; import com.history.gismap.service.MapService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class MapServiceImpl implements MapService { @Autowired private MapDao mapDao; @Override public List<PointModel> getCntyPointByGid(Integer gId){ return mapDao.getCntyPoint(gId); } @Override public int addCntyPoint(PointModel pointModel){ return mapDao.insertCntyPoint(pointModel); } @Override public int modifyCntyPoint(PointModel pointModel){ return mapDao.updateCntyPoint(pointModel); } @Override public int removeCntyPoint(Integer gId){ return mapDao.deleteCntyPoint(gId); } }
七、修改controller
为了便于前端读写，我们要把geometry对象转化成geojson。
这个改动比较大，查询返回结果、增加入参、修改入参都改成了json格式，WKTReader用来读取WKT文本，mybatis自定义引擎中引入的WKBReader是用来读geohash文本的。
package com.history.gismap.controller; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.alibaba.fastjson.JSONPath; import com.history.gismap.model.PointModel; import com.history.gismap.service.MapService; import com.vividsolutions.jts.geom.Coordinate; import com.vividsolutions.jts.geom.Geometry; import com.vividsolutions.jts.geom.GeometryFactory; import com.vividsolutions.jts.geom.Point; import com.vividsolutions.jts.io.ParseException; import com.vividsolutions.jts.io.WKTReader; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.*; @Controller @RequestMapping(value = "/history") public class MapController { @Autowired private MapService mapService; @ResponseBody @GetMapping("/pointmodel") public JSONObject getPoint(@RequestParam("gid") Integer gId){ PointModel pointModel=mapService.getCntyPointByGid(gId).get(0); JSONObject jsonObject=new JSONObject(); jsonObject.put("gid",pointModel.getGId()); jsonObject.put("namech",pointModel.getNameCh()); JSONObject geometry=new JSONObject(); geometry.put("type",pointModel.getGeometry().getGeometryType()); JSONArray coordinateArray=new JSONArray(); Coordinate[] coordinates=pointModel.getGeometry().getCoordinates(); JSONObject coor=new JSONObject(); coor.put("longitude",coordinates[0].x); coor.put("latitude",coordinates[0].y); coordinateArray.add(coor); geometry.put("coordinate",coordinateArray); jsonObject.put("geometry",geometry); return jsonObject; } @ResponseBody @PostMapping("/add") public int addPoint(@RequestBody JSONObject request){ PointModel pointModel=new PointModel(); pointModel.setGId((Integer) JSONPath.eval(request,"$.gId")); pointModel.setNameCh((String) JSONPath.eval(request,"$.nameCh")); String pointStr= (String) JSONPath.eval(request,"$.point"); GeometryFactory geometryFactory = new GeometryFactory(); WKTReader reader = new WKTReader( geometryFactory ); try { Geometry point = (Point) reader.read(pointStr); pointModel.setGeometry(point); } catch (ParseException e) { e.printStackTrace(); } return mapService.addCntyPoint(pointModel); } @ResponseBody @PostMapping("/modify") public int update(@RequestBody JSONObject request){ PointModel pointModel=new PointModel(); pointModel.setGId((Integer) JSONPath.eval(request,"$.gId")); pointModel.setNameCh((String) JSONPath.eval(request,"$.nameCh")); String pointStr= (String) JSONPath.eval(request,"$.point"); GeometryFactory geometryFactory = new GeometryFactory(); WKTReader reader = new WKTReader( geometryFactory ); try { Geometry point = (Point) reader.read(pointStr); pointModel.setGeometry(point); } catch (ParseException e) { e.printStackTrace(); } return mapService.modifyCntyPoint(pointModel); } @ResponseBody @GetMapping("/remove") public int removetPoint(@RequestParam("gid") Integer gId){ return mapService.removeCntyPoint(gId); } }
八、启动测试
启动工程D:\gismap\java\gismap\src\main\java\com\history\gismap\GismapApplication.java，用postman看下。
先看下查询的结果：
访问http://localhost:8080/history/pointmodel?gid=1
{
"gid": 1,
"geometry": {
"coordinate": [
{
"latitude": 39.012409,
"longitude": 111.079483
}
],
"type": "Point"
},
"namech": "保德州"
}
新增结果：
http://localhost:8080/history/add
post一下。
{
"gId":14357,
"nameCh":"test",
"point":"POINT (109.013388 32.715519)"
}
修改结果：
http://localhost:8080/history/modify
{
"gId":14357,
"nameCh":"test",
"point":"POINT (0 0)"
}
九、提交git，从git上pull分支
处理geometry对象就到这里了。
把程序提交到git上。
本例程序在https://github.com/yimengyao13/gismap.git上，但是为了不和之前的程序冲突，所以新建了一个分支geometry。
切换新分支，右下角Git:master，点开Git Branchs——+New Branch;
写上新分支名称，也就是geometry，勾选Checkout branch，点击ok。
这样就可以在线上pull代码下来了。
VCS——Git——Pull
先刷新下，选择分支，pull。
这样就可以看代码了。
接下来要把这个工程完善，因为三张表的其他属性都要加载进来，而且web显示，不能仅仅读数据库，这个太慢了，要把数据加载到内存中，以便于快速读取。
一样一样来。
先把整个增删改查完善后，再进行内存加载。