java程序解析jts的geometry类型并入PG数据库

场景

GIS开发，会有需要将jts包中的geometry类型数据存入pg（postgis扩展后）数据库的需求。

工程是springboot，mybatis作为持久层框架。

解决方案

1. pg的geometry字段对应的类型为geometry类型，比如：

2. 定义geometry的typeHandler，在mapper.xml中声明使用

背景知识

postgis的geometry字段存储的数据在内部是二进制数据（即PostgreSQL的bytea数据类型），WKB/EWKB也是二进制数据类型。字符集为ascii。

但是通常我们查询数据库时，geometry数据会表示为HEXEWKB数据类型（EWKB对应的十六进制形式）。例如执行以下查询：

SELECT ST_GeomFromEWKT('POINT (1 1)'); 
-- 或者直接从一个有geometry的表里查询出来一个geometry
select geom from i_table limit 1; -- geom就是geometry类型的字段

得到的结果为：

这和执行以下两个查询得到的结果是一样的：

SELECT ST_AsHEXEWKB(ST_GeomFromEWKT('POINT (1 1)')); 
SELECT encode(ST_AsEWKB(ST_GeomFromEWKT('POINT (1 1)')), 'hex'); 

 详情可以查看这篇文章：

PostGIS空间数据类型的组织与表达（一）：Geometry - 知乎

所以存储时需要将jts的geometry对象转换为ewkb的二进制格式即可存入pg的geometry字段。

查询时也只需要将二进制转换为geometry即可。

实现

typehandler

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.WKBReader;
import org.locationtech.jts.io.WKBWriter;import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class GeometryTypeHandler implements TypeHandler<Geometry> {@Overridepublic void setParameter(PreparedStatement preparedStatement, int i, Geometry geometry, JdbcType jdbcType) throws SQLException {int dimension = geometry.getDimension();dimension = (dimension == 2 || dimension == 3) ? dimension : 2;WKBWriter wkbWriter = new WKBWriter(dimension, true);final byte[] write = wkbWriter.write(geometry);preparedStatement.setBytes(i, write);}@Overridepublic Geometry getResult(ResultSet resultSet, String s) throws SQLException {final byte[] bytes = resultSet.getBytes(s);return bytes2Geometry(bytes);}@Overridepublic Geometry getResult(ResultSet resultSet, int i) throws SQLException {final byte[] bytes = resultSet.getBytes(i);return bytes2Geometry(bytes);}@Overridepublic Geometry getResult(CallableStatement callableStatement, int i) throws SQLException {final byte[] bytes = callableStatement.getBytes(i);return bytes2Geometry(bytes);}private Geometry bytes2Geometry(byte[] bytes) {WKBReader wkbReader = new WKBReader();try {return wkbReader.read(bytes);} catch (Exception e) {e.printStackTrace();}return null;}
}

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.jfqqq.vector.mapper.IGeomMapper"><resultMap id="resultMap" type="com.jfqqq.vector.entity.MyGeom"><result property="gid" column="gid"/><result property="version" column="version"/><result property="geom" column="geom"typeHandler="com.jfqqq.vector.typeHandler.GeometryTypeHandler"/><result property="createTime" column="create_time"/></resultMap><sql id="SELECT_BASE_COLUMN">gid, version, st_asewkb(geom) as geom, create_time</sql><insert id="insert" parameterType="com.jfqqq.vector.entity.MyGeom" useGeneratedKeys="true" keyProperty="gid">insert into i_geometry(version, geom, create_time)values (#{version},#{geom,typeHandler=com.jfqqq.vector.typeHandler.GeometryTypeHandler},now())</insert><select id="queryByBoxAndVersion"  resultMap="resultMap">select <include refid="SELECT_BASE_COLUMN" /> from i_geometrywhere version = #{version} and st_intersects(geom, st_makeenvelope(${bbox.minX}, ${bbox.minY}, ${bbox.maxX}, ${bbox.maxY}, 4326)) = true;</select>
</mapper>