mybatis类型转换器处理PostGis数据库geometry类型转换

本文介绍如何在MyBatis框架下实现GeoJSON与WKB格式的空间数据转换。通过自定义类型转换器，实现PostGIS数据库中geometry类型与Java实体类中GeoJSON字符串之间的映射。 在入库和查询中需要自动的让geometry的类型和实体类中的类型映射。

实体类中接收参数是String类型（geoJson）

PostGis中的geometry类型是十六进制的扩展WKB类型（EWKB），

文档Chapter 4. Data Management

说明输入格式是EWKB二进制格式，或者EWKB和EWKT文本格式。输出格式为二进制EWKB或文本格式HEXEWKB

以下接收到文本格式HEXEWKB处理

虽然Postgis数据库中提供类类型转换函数，能转换各种类型postgis常用命令_yaoct的博客-CSDN博客

但是基于mybatis框架查询时，就需要用java代码来转换。初步方案时mybatis中的类型转换器。

先引入java处理GIS的库，这里用的是Geotools库。

1.java代码中数据类型的转换 geotools的maven引入：

<properties>
    <geotools.version>27.0</geotools.version>
</properties>
<repositories>
    <repository>
        <id>osgeo</id>
        <name>OSGeo Release Repository</name>
        <url>https://repo.osgeo.org/repository/release/</url>
        <snapshots>
            <enabled>false</enabled>
        </snapshots>
        <releases>
            <enabled>true</enabled>
        </releases>
    </repository>
</repositories>
<dependencies>
    <!--geotool https://docs.geotools.org/latest/userguide/tutorial/quickstart/maven.html-->
    <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-geojson</artifactId>
        <version>${geotools.version}</version>
    </dependency>
    <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-main</artifactId>
        <version>${geotools.version}</version>
    </dependency>
    <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-opengis</artifactId>
        <version>${geotools.version}</version>
    </dependency>
    <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-referencing</artifactId>
        <version>${geotools.version}</version>
    </dependency>
    <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-metadata</artifactId>
        <version>${geotools.version}</version>
    </dependency>
    <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-epsg-hsql</artifactId>
        <version>${geotools.version}</version>
    </dependency>
</dependencies>

AI写代码

Geotools工具类转换WKB和Geojson

WKBReader reader = new WKBReader( ); Geometry geometry = reader.read(WKBReader.hexToBytes("0101000020E61000002C39382229FD5D4085716007088C3E40")); // 设置保留6位小数，否则GeometryJSON默认保留4位小数 GeometryJSON geometryJson = new GeometryJSON(7); String s = geometryJson.toString(geometry); System.out.println(s);

    //{"type":"Point","coordinates":[119.9556356,30.5469975]}
    //EWKB->转geojson丢失信息
    Geometry read = geometryJson.read("{\"type\":\"Point\",\"coordinates\":[119.9556356,30.5469975]}");
    System.out.println(read.toString());
    WKBWriter wkbWriter = new WKBWriter();
    byte[] write = wkbWriter.write(geometry);
    String s1 = WKBWriter.toHex(write);
    System.out.println(s1);

AI写代码

2.mybatis-plus类型转换器 这里框架用的是mybatis-plus,所有使用转换时，mybatis-plus中的实体类也要配置一些转换注解，其他和mybaitis中的xml配置属性相同。参考

MyBatis Plus 自动类型转换之TypeHandler

@Data @ToString @TableName (value = "test_table",autoResultMap = true) @ApiModel ("test") public class TestTable implements Serializable {

private static final long serialVersionUID =  8881418345724766899L;

@TableId(value = "id")
private Integer id;

@ApiModelProperty(value = "二进制WKB数据")
@TableField(value = "position",typeHandler = WKB2GeoJsonTypeHandler.class)
@JsonRawValue
private String position;

@TableField("name")
private String name;

} AI写代码

mybatis-plus配置添加转换器包路径，类似mybatis，之后用于写xml中sql

mybatis-plus: type-handlers-package: com.zjzy.mapper.typehandler configuration: map-underscore-to-camel-case: true auto-mapping-behavior: full log-impl: org.apache.ibatis.logging.stdout.StdOutImpl mapper-locations: classpath*:mapper/**/*Mapper.xml AI写代码 3.mybatis类型转换器转换geometry格式 因为缺少JDBC映射，数据库开始报错：‘类型为 geometry, 但表达式的类型为 character varying’

参考：Mybatis-plus读取和保存Postgis geometry数据 - 简书

应该是缺少JDBC-type类型。引入响应的postGIS驱动

添加maven包：

    <!-- 这里会补充mybatis缺少的JDBC-Type如postGis中的geometry类型 -->
    <dependency>
        <groupId>net.postgis</groupId>
        <artifactId>postgis-jdbc</artifactId>
        <version>2.5.0</version>
    </dependency>

AI写代码 类型转换器代码：

package com.zjzy.mapper.typehandler;

import com.zjzy.mapper.CommonCodeMapper; import com.zjzy.util.SpringContextUtil; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.MappedJdbcTypes; import org.apache.ibatis.type.MappedTypes; import org.apache.ibatis.type.TypeHandler; import org.geotools.geojson.geom.GeometryJSON; import org.geotools.geometry.jts.WKBReader; import org.locationtech.jts.geom.Geometry; import org.locationtech.jts.io.ParseException; import org.postgis.PGgeometry;

import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Set;

/**

• @version 2.0

• @description

• @Author yaoct

• @create 2021/12/22 16:19 */ @Slf4j public class WKB2GeoJsonTypeHandler implements TypeHandler {

  /**

  • 插入数据，转换,geoJson2EWKB

  • @param ps

  • @param i

  • @param parameter

  • @param jdbcType

  • @throws SQLException */ @Override public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException { //EWKB是pg数据库自定义的类型，所以需要用数据库函数,插入数据库一般次数较少，这样并不会对数据库造成过大负担 // CommonCodeMapper mapper = SpringContextUtil.getBean(CommonCodeMapper.class); // String EWKB = mapper.geo2EWKB(parameter); // PGgeometry ewkb = new PGgeometry(EWKB); // ps.setObject(i,ewkb);

    //通过geoTool转换WKB GeometryJSON geometryJson = new GeometryJSON(7); Geometry read = null; try { read = geometryJson.read(parameter); } catch (IOException e) { e.printStackTrace(); } // System.out.println(read.toString()); read.setSRID(4326); // GeometryJSON geometryJson = new GeometryJSON(7); WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true); byte[] write = wkbWriter.write(read); String s1 = WKBWriter.toHex(write); PGgeometry ewkb = new PGgeometry(s1); ps.setObject(i,ewkb); }

  /**

  • 取出数据转换,WKB->Geojson
  • @param rs
  • @param columnName
  • @return
  • @throws SQLException */ @Override public String getResult(ResultSet rs, String columnName) throws SQLException { String WKB = rs.getString(columnName); if(WKB==null){ return null; } WKBReader reader = new WKBReader( ); Geometry geometry = null; try { geometry = reader.read(WKBReader.hexToBytes(WKB)); } catch (ParseException e) { //转换失败 return null; } //转换成4326 try { int srid = geometry.getSRID(); CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true); CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:4326",true); MathTransform transform = CRS.findMathTransform(sourceCRS, targetCRS,true); geometry = JTS.transform(geometry, transform); } catch (Exception e) { e.printStackTrace(); } // 设置保留6位小数，否则GeometryJSON默认保留4位小数 GeometryJSON geometryJson = new GeometryJSON(7); return geometryJson.toString(geometry); }

  @Override public String getResult(ResultSet rs, int columnIndex) throws SQLException { return null; }

  @Override public String getResult(CallableStatement cs, int columnIndex) throws SQLException { return null; } } AI写代码

@Component public class SpringContextUtil implements ApplicationContextAware {

private static ApplicationContext applicationContext;

@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
    SpringContextUtil.applicationContext = applicationContext;
}

//获取applicationContext
public static ApplicationContext getApplicationContext() {
    return applicationContext;
}

//通过name获取 Bean.
public static Object getBean(String name) {
    return getApplicationContext().getBean(name);
}

//通过class获取Bean.
public static <T> T getBean(Class<T> clazz) {
    return getApplicationContext().getBean(clazz);
}

//通过name,以及Clazz返回指定的Bean
public static <T> T getBean(String name, Class<T> clazz) {
    return getApplicationContext().getBean(name, clazz);
}

//关闭springboot
public static void close(){
    ((ConfigurableApplicationContext)(getApplicationContext())).close();
}

//得到当前线程的HttpServletResponse,可在静态方法中得到
public static HttpServletResponse getHttpServletResponse(){
    return ((ServletRequestAttributes) RequestContextHolder.currentRequestAttributes()).getResponse();
}

//得到当前线程的HttpServletRequest,可在静态方法中得到
public static HttpServletRequest getHttpServletRequest(){
    return ((ServletRequestAttributes) RequestContextHolder.currentRequestAttributes()).getRequest();
}

} AI写代码

@Repository public interface CommonCodeMapper extends BaseMapper {

/**
 * EWKB是pg数据库自定义的类型，所以需要用数据库函数
 * @param geoJson
 * @return
 */
@Select("select ST_GeomFromGeoJSON(#{geoJson})")
String geo2EWKB(String geoJson);

} AI写代码

=============================mybatis中使用================================

在mapper类中使用示例：www.jianshu.com/p/646518998…

/**

• 使用ResultMap */ @Results(id = "userMap", value = { //可以使用这种方式来处理字段名和数据库表字段名不一致的情况 @Result(column = "username", property = "username", jdbcType=JdbcType.VARCHAR,typeHandler = WKB2GeoJsonTypeHandler.class), @Result(column = "passwd", property = "passwd", jdbcType=JdbcType.VARCHAR), @Result(column = "birth_day", property = "birthDay", jdbcType=JdbcType.VARCHAR) }) @Select("SELECT * FROM t_user WHERE id=#{id}") User loadByIdResultMap(Long id);

/**

• 引用其他的Result */ @ResultMap("userMap") @Select("SELECT * FROM t_user WHERE id=#{id}") User loadByIdResultMapReference(Long id); AI写代码

在xml中使用示例示例：

<resultMap id="locationResultMap" type="com.zjzy.model.po.ExaminationApplication">
    <result typeHandler="com.zjzy.mapper.typehandler.WKB2GeoJsonTypeHandler" column="dig_position" javaType="String"
            property="digPosition"/>
</resultMap>

<select id="selectExaminationApplicationByPage" resultMap="locationResultMap">
</select>

AI写代码 注意：

1.postgresql的JDBC实现中（驱动），数据自定义的类型对应java类型一般都通过实现

org.postgresql.util.PGobject。

2.对应数据库geometry类型的java类不在PG的JDBC驱动的实现类中，因为geometry类型在PG的扩展postgis中，所以响应的要引入postGIS这部分的驱动，

3. PG的JDBC驱动底层也是通过SOCKET连接org.postgresql.core.PGStream#createSocket

4.原生jdbc接口在java.sql包中。

5.一定要实现org.postgresql.util.PGobject#getValue方法因为，org.postgresql.jdbc.PgPreparedStatement#setPGobject底层调用org.postgresql.jdbc.PgPreparedStatement#setString(int, java.lang.String, int)时

要用到org.postgresql.util.PGobject#getValue方法

==============================2022.12.12=========================== 以下方式不用导入 net.postgis包

package com.zjzy.mapper.typehandler;

import com.alibaba.fastjson.JSONObject; import com.zjzy.util.GisUtil; import com.zjzy.util.SpringContextUtil; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.TypeHandler; import org.geotools.geojson.geom.GeometryJSON; import org.geotools.geometry.jts.JTS; import org.geotools.geometry.jts.WKBReader; import org.geotools.referencing.CRS; import org.locationtech.jts.geom.Geometry; import org.locationtech.jts.io.ByteOrderValues; import org.locationtech.jts.io.ParseException; import org.locationtech.jts.io.WKBWriter; import org.locationtech.jts.io.WKTWriter; import org.opengis.referencing.crs.CoordinateReferenceSystem; import org.opengis.referencing.operation.MathTransform; import org.postgis.PGbox2d; import org.postgis.PGgeometry; import org.postgresql.util.PGobject; import org.springframework.core.env.Environment;

import java.io.IOException; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;

/**

• @version 1.0

• @description

• @Author yaoct

• @create 2021/12/22 16:19 */ @Slf4j public class WKB2GeoJsonTypeHandler implements TypeHandler {

  /**

  • 插入数据，转换,geoJson2EWKB

  • @param ps

  • @param i

  • @param parameter

  • @param jdbcType

  • @throws SQLException */ @Override public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException { //EWKB是pg数据库自定义的类型，所以需要用数据库函数,插入数据库一般次数较少，这样并不会对数据库造成过大负担 // CommonCodeMapper mapper = SpringContextUtil.getBean(CommonCodeMapper.class); // String EWKB = mapper.geo2EWKB(parameter); // PGgeometry ewkb = new PGgeometry(EWKB); // ps.setObject(i,ewkb);

    //通过geoTool转换WKB GeometryJSON geometryJson = new GeometryJSON(7); Geometry geometry = null; try { geometry = geometryJson.read(parameter); } catch (IOException e) { e.printStackTrace(); } // System.out.println(read.toString()); geometry.setSRID(4326); // GeometryJSON geometryJson = new GeometryJSON(7);

    //方式1.基于net.postgis // WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true); // byte[] write = wkbWriter.write(geometry); // String s1 = WKBWriter.toHex(write); // PGgeometry ewkb = new PGgeometry(s1);//EWKB比WKB多了SRID头字符串，PGgeometry.toString()会自动添加SRID // ps.setObject(i,ewkb);

    //方式2.设置ewkb,Postgresql驱动会先去数据库 pg_catalog.pg_type 查出geometry的类型id，这里的geometry字符串对应数据库pg_catalog.pg_type表中的geometry //以下方式不用导入net.postgis包 // PGobject pGobject = new PGobject(); // pGobject.setType("geometry"); // pGobject.setValue(s1); // ps.setObject(i,pGobject);

    //方式3.设置拼接ewkt //以下方式不用导入net.postgis包，ewkb太长会超出sql限制，以下转换为ewkt PGobject pGobject = new PGobject(); pGobject.setType("geometry"); String pre="SRID="+4326+";"; pGobject.setValue(pre+GisUtil.Geometry2WKT(geometry)); ps.setObject(i,pGobject); }

  /**

  • 取出数据转换,WKB->Geojson

  • @param rs

  • @param columnName

  • @return

  • @throws SQLException */ @Override public String getResult(ResultSet rs, String columnName) throws SQLException { String WKB = rs.getString(columnName); if(WKB==null){ return null; } WKBReader reader = new WKBReader( ); Geometry geometry = null; try { geometry = reader.read(WKBReader.hexToBytes(WKB)); } catch (ParseException e) { //转换失败 return null; } //转换成4326 try { int srid = geometry.getSRID(); if(srid==0)srid=4549;//默认4549 int targetSrid=4326;//默认4326 Environment environment = SpringContextUtil.getBean(Environment.class); if(environment!=null){ String sridTarget = environment.getProperty("sridTarget"); if(sridTarget!=null){ try { targetSrid=Integer.valueOf(sridTarget); } catch (Exception e){ //转换失败 } } }

     CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true);
     CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:"+targetSrid,true);
     MathTransform transform = CRS.findMathTransform(sourceCRS, targetCRS,true);
     geometry = JTS.transform(geometry, transform);
    

    } catch (Exception e) { e.printStackTrace(); } // 设置保留6位小数，否则GeometryJSON默认保留4位小数 GeometryJSON geometryJson = new GeometryJSON(7); String json = geometryJson.toString(geometry); JSONObject jsonObject = JSONObject.parseObject(json); jsonObject.put("srid",geometry.getSRID()); return jsonObject.toJSONString(); }

  @Override public String getResult(ResultSet rs, int columnIndex) throws SQLException { return null; }

  @Override public String getResult(CallableStatement cs, int columnIndex) throws SQLException { return null; }

} AI写代码

package com.zjzy.util;

import org.geotools.geojson.geom.GeometryJSON; import org.geotools.geometry.jts.JTS; import org.geotools.geometry.jts.WKBReader; import org.geotools.referencing.CRS; import org.geotools.referencing.GeodeticCalculator; import org.geotools.referencing.crs.DefaultGeographicCRS; import org.locationtech.jts.geom.Geometry; import org.locationtech.jts.io.*; import org.opengis.referencing.FactoryException; import org.opengis.referencing.crs.CoordinateReferenceSystem; import org.opengis.referencing.operation.MathTransform; import org.opengis.referencing.operation.TransformException;

import java.io.IOException;

/**

• @Author yaoct

• @Date 2022/9/16 10:55

• @Version 1.0

• @description */ public class GisUtil {

  /**

  • WKB字符串转Geometry格式
  • @param WKB
  • @return */ public static Geometry WKB2Geometry(String WKB){ WKBReader reader = new WKBReader(); Geometry geometry = null; try { geometry = reader.read(WKBReader.hexToBytes(WKB)); } catch (ParseException e) { //转换失败 throw new RuntimeException("转换失败",e); } return geometry; }

  /**

  • Geometry格式转WKB字符串
  • @param geometry
  • @return */ public static String Geometry2WKB(Geometry geometry){ WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true); byte[] write = wkbWriter.write(geometry); String ret = WKBWriter.toHex(write); return ret; }

  /**

  • WKT字符串转Geometry格式
  • @param WKT
  • @return */ public static Geometry WKT2Geometry(String WKT){ WKTReader reader = new WKTReader(); Geometry geometry = null; try { geometry = reader.read(WKT); } catch (ParseException e) { //转换失败 throw new RuntimeException("转换失败",e); } return geometry; }

  /**

  • Geometry格式转WKT字符串
  • @param geometry
  • @return */ public static String Geometry2WKT(Geometry geometry){ WKTWriter wktWriter = new WKTWriter(); String wkt= wktWriter.write(geometry); return wkt; }

  /**

  • 转换Geometry坐标
  • @param geometry
  • @param targetSrid 新坐标
  • @return */ public static Geometry coordinateTransfer(Geometry geometry, int targetSrid){ int originSrid = geometry.getSRID(); CoordinateReferenceSystem sourceCRS = null; CoordinateReferenceSystem targetCRS = null; MathTransform transform=null; try { sourceCRS = CRS.decode("EPSG:"+originSrid,true); targetCRS = CRS.decode("EPSG:"+targetSrid,true); transform = CRS.findMathTransform(sourceCRS, targetCRS,true); } catch (FactoryException e) { throw new RuntimeException("坐标错误",e); } Geometry ret = null; try { ret = JTS.transform(geometry, transform); } catch (TransformException e) { throw new RuntimeException("坐标转换错误",e); } return ret; }

  /**

  • Geometry->geoJson
  • @param geometry
  • @return */ public static String geometry2GeoJson(Geometry geometry){ // 设置保留6位小数，否则GeometryJSON默认保留4位小数 GeometryJSON geometryJson = new GeometryJSON(7); String json = geometryJson.toString(geometry); // JSONObject jsonObject = JSONObject.parseObject(json); // jsonObject.put("srid",geometry.getSRID()); // return jsonObject.toJSONString(); return json; }

  /**

  • geoJson->Geometry
  • @param geoJson
  • @return */ public static Geometry geoJson2Geometry(String geoJson){ GeometryJSON geometryJson = new GeometryJSON(7); Geometry ret=null; try { Geometry read = geometryJson.read(geoJson); } catch (IOException e) { throw new RuntimeException("转换失败",e); } return ret; }

  /**

  • 计算两点间的距离
  • @param lon1
  • @param lat1
  • @param lon2
  • @param lat2
  • @return */ public static double calculateDistance(double lon1,double lat1,double lon2,double lat2){ //CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true); GeodeticCalculator geodeticCalculator = new GeodeticCalculator(DefaultGeographicCRS.WGS84); // 起点经纬度 geodeticCalculator.setStartingGeographicPoint(lon1,lat1); // 末点经纬度 geodeticCalculator.setDestinationGeographicPoint(lon2,lat2); // 计算距离，单位：米 double distance = geodeticCalculator.getOrthodromicDistance(); return distance; } } AI写代码

===========================2023.3.8=============================== 转换成map类型，实体类接受前端传入json兼容性更好

package com.wisdomcity.laian.monitor.mapper.typehandler;

import com.alibaba.fastjson.JSONObject; import com.fasterxml.jackson.databind.ObjectMapper; import com.fhs.common.spring.SpringContextUtil; import com.kingbase8.util.KBobject; import com.wisdomcity.laian.monitor.utils.GisUtil; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.TypeHandler; import org.geotools.geojson.geom.GeometryJSON; import org.geotools.geometry.jts.JTS; import org.geotools.geometry.jts.WKBReader; import org.geotools.referencing.CRS; import org.locationtech.jts.geom.Geometry; import org.locationtech.jts.io.ByteOrderValues; import org.locationtech.jts.io.ParseException; import org.locationtech.jts.io.WKBWriter; import org.opengis.referencing.crs.CoordinateReferenceSystem; import org.opengis.referencing.operation.MathTransform;

import java.io.IOException; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Map;

/**

• @version 1.0

• @description

• @Author yaoct

• @create 2021/12/22 16:19 */ @Slf4j public class WKB2GeoJsonTypeHandler implements TypeHandler {

  /**

  • 插入数据，转换,geoJson2EWKB

  • @param ps

  • @param i

  • @param parameter

  • @param jdbcType

  • @throws SQLException */ @Override public void setParameter(PreparedStatement ps, int i, Map parameter, JdbcType jdbcType) throws SQLException { //EWKB是pg数据库自定义的类型，所以需要用数据库函数,插入数据库一般次数较少，这样并不会对数据库造成过大负担 // CommonCodeMapper mapper = SpringContextUtil.getBean(CommonCodeMapper.class); // String EWKB = mapper.geo2EWKB(parameter); // PGgeometry ewkb = new PGgeometry(EWKB); // ps.setObject(i,ewkb);

    //通过geoTool转换WKB GeometryJSON geometryJson = new GeometryJSON(7); Geometry geometry = null; try { geometry = geometryJson.read(JSONObject.toJSONString(parameter)); } catch (IOException e) { e.printStackTrace(); } if(geometry==null) return; // System.out.println(read.toString()); geometry.setSRID(4326); // GeometryJSON geometryJson = new GeometryJSON(7);

    //方式1.基于net.postgis WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true); byte[] write = wkbWriter.write(geometry); String s1 = WKBWriter.toHex(write); // PGgeometry ewkb = new PGgeometry(s1);//EWKB比WKB多了SRID头字符串，PGgeometry.toString()会自动添加SRID // ps.setObject(i,ewkb);

    //方式2.设置ewkb,Postgresql驱动会先去数据库 pg_catalog.pg_type 查出geometry的类型id，这里的geometry字符串对应数据库pg_catalog.pg_type表中的geometry // 以下方式不用导入net.postgis包 // PGobject pGobject = new PGobject(); // pGobject.setType("geometry"); // pGobject.setValue(s1); // ps.setObject(i,pGobject);

    //方式3.设置拼接ewkt //以下方式不用导入net.postgis包，ewkb太长会超出sql限制，以下转换为ewkt // PGobject pGobject = new PGobject(); // pGobject.setType("geometry"); // String pre="SRID="+4326+";"; // pGobject.setValue(pre+GisUtil.Geometry2WKT(geometry)); // ps.setObject(i,pGobject); KBobject pGobject = new KBobject(); pGobject.setType("geometry"); String pre="SRID="+4326+";"; pGobject.setValue(pre+ GisUtil.Geometry2WKT(geometry)); ps.setObject(i,pGobject); }

  /**

  • 取出数据转换,WKB->Geojson
  • @param rs
  • @param columnName
  • @return
  • @throws SQLException */ @Override public Map getResult(ResultSet rs, String columnName) throws SQLException { String WKB = rs.getString(columnName); if(WKB==null){ return null; } WKBReader reader = new WKBReader( ); Geometry geometry = null; try { geometry = reader.read(WKBReader.hexToBytes(WKB)); } catch (ParseException e) { //转换失败 return null; } //转换成4326 try { int srid = geometry.getSRID(); // if(srid==0)srid=4549;//默认4549 int targetSrid=4326;//默认4326 // Environment environment = SpringContextUtil.getBean(Environment.class); // if(environment!=null){ // String sridTarget = environment.getProperty("sridTarget"); // if(sridTarget!=null){ // try { // targetSrid=Integer.valueOf(sridTarget); // } catch (Exception e){ // //转换失败 // } // } // } if(srid!=targetSrid&&srid!=0){ CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true); CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:"+targetSrid,true); MathTransform transform = CRS.findMathTransform(sourceCRS, targetCRS,true); geometry = JTS.transform(geometry, transform); geometry.setSRID(targetSrid); } } catch (Exception e) { e.printStackTrace(); } // 设置保留6位小数，否则GeometryJSON默认保留4位小数 GeometryJSON geometryJson = new GeometryJSON(7); String json = geometryJson.toString(geometry); // JSONObject jsonObject = JSONObject.parseObject(json); // jsonObject.put("srid",geometry.getSRID()); // return jsonObject.toJSONString(); return JSONObject.parseObject(json); }

  @Override public Map getResult(ResultSet rs, int columnIndex) throws SQLException { return null; }

  @Override public Map getResult(CallableStatement cs, int columnIndex) throws SQLException { return null; }

} AI写代码

实体类字段改为 ：

@ApiModelProperty(value = "二进制WKB数据")
@TableField(value = "location",typeHandler = WKB2GeoJsonTypeHandler.class)
private Map location;

AI写代码 xml中使用

AI写代码 ===================================================================

2025.12.01更新，入库前先检测多边形是否自相交 package com.wisdomcity.yingkou.fmewcs_service.mapper.typehandler;

import com.alibaba.fastjson.JSONObject; import com.wisdomcity.yingkou.fmewcs_service.application.linkagetracinganalysis.util.GisUtil; import com.wisdomcity.yingkou.fmewcs_service.core.utils.SpringContextUtils; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.TypeHandler; import org.geotools.api.referencing.crs.CoordinateReferenceSystem; import org.geotools.api.referencing.operation.MathTransform; import org.geotools.geojson.geom.GeometryJSON; import org.geotools.geometry.jts.JTS; import org.geotools.geometry.jts.WKBReader; import org.geotools.referencing.CRS; import org.locationtech.jts.geom.Geometry; import org.locationtech.jts.geom.MultiPolygon; import org.locationtech.jts.geom.Polygon; import org.locationtech.jts.io.ByteOrderValues; import org.locationtech.jts.io.ParseException; import org.locationtech.jts.io.WKBWriter; import org.postgresql.util.PGobject;

import java.io.IOException; import java.sql.*; import java.util.Map;

/**

• @version 1.0

• @description

• @Author yaoct

• @create 2021/12/22 16:19

• postgresql输入可以是EWKB二进制格式，或者EWKB和EWKT文本格式。输出格式为二进制EWKB或文本格式HEXEWKB

• mysql输入输出都是WKB二进制格式 */ @Slf4j @Mapper public class WKB2GeoJsonTypeHandler implements TypeHandler {

  /**

  • 插入数据，转换,geoJson2EWKB

  • @param ps

  • @param i

  • @param parameter

  • @param jdbcType

  • @throws SQLException */ @Override public void setParameter(PreparedStatement ps, int i, Map parameter, JdbcType jdbcType) throws SQLException { //通过geoTool转换WKB GeometryJSON geometryJson = new GeometryJSON(7); Geometry geometry = null; try { geometry = geometryJson.read(JSONObject.toJSONString(parameter)); if(geometry==null) { ps.setObject(i,null); return; } } catch (IOException e) { e.printStackTrace(); } // System.out.println(read.toString()); geometry.setSRID(4490); // GeometryJSON geometryJson = new GeometryJSON(7);

    //二进制表示 //POINT(115.79504 33.867779) //{"type":"Point","coordinates":[115.79504,33.867779]} //postgresql BIG_ENDIAN、LITTLE_ENDIAN都支持 //0101000020e6100000c2c073efe1f25c40ea93dc6113ef4040 postgis ByteOrderValues.LITTLE_ENDIAN pg数据库存储方式 //E61000000101000000C2C073EFE1F25C40EA93DC6113EF4040 mysql ByteOrderValues.LITTLE_ENDIAN mysql数据库存储方式 //0020000001000010E6405CF2E1EF73C0C24040EF1361DC93EA ByteOrderValues.BIG_ENDIAN 0x10E6 4326 WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true); byte[] write = wkbWriter.write(geometry);

    //方式1.基于net.postgis // String s1 = WKBWriter.toHex(write); // PGgeometry ewkb = new PGgeometry(s1);//EWKB比WKB多了SRID头字符串，PGgeometry.toString()会自动添加SRID // ps.setObject(i,ewkb);

    //方式2.设置ewkb,Postgresql驱动会先去数据库 pg_catalog.pg_type 查出geometry的类型id，这里的geometry字符串对应数据库pg_catalog.pg_type表中的geometry // 以下方式不用导入net.postgis包 // PGobject pGobject = new PGobject(); // pGobject.setType("geometry"); // pGobject.setValue(s1); // ps.setObject(i,pGobject);

    //方式3.设置拼接ewkt //以下方式不用导入net.postgis包，ewkb太长会超出sql限制，以下转换为ewkt // PGobject pGobject = new PGobject(); // pGobject.setType("geometry"); // String pre="SRID="+4326+";"; // pGobject.setValue(pre+GisUtil.Geometry2WKT(geometry)); // ps.setObject(i,pGobject); Connection connection = ps.getConnection(); connection.getSchema(); DatabaseMetaData metaData = connection.getMetaData(); // Connection connection1 = metaData.getConnection(); String url = metaData.getURL(); String pre="SRID="+4490+";"; String wkt = GisUtil.Geometry2WKT(geometry); String ewkt = pre+ wkt; if(geometry instanceof Polygon|| geometry instanceof MultiPolygon){ //如果是多边形，借助数据库st_makevalid多边形有效性修复 TODO if(!geometry.isValid()){ //一般是多变形的自相交问题，自相交JTS1.20.0的修复方法不如postgis函数st_makevalid WKB2GeoJsonTypeMapper mapper = SpringContextUtils.getBean(WKB2GeoJsonTypeMapper.class); String wktNew=mapper.makevalid(wkt); Geometry geometryNew = GisUtil.WKT2Geometry(wktNew); log.info("多边形自相交修复前：{},自相交修复后：{}，修复后是否有效{}",wkt,wktNew,geometryNew.isValid()); ewkt = pre+wktNew; } } //适配kingbsbse8 postgresql if(url.startsWith("jdbc:kingbase")){ // KBobject pGobject = new KBobject(); // pGobject.setType("geometry"); // String pre="SRID="+4326+";"; // pGobject.setValue(pre+ GisUtil.Geometry2WKT(geometry)); // ps.setObject(i,pGobject); }else if(url.startsWith("jdbc:postgresql")){ PGobject pGobject = new PGobject(); pGobject.setType("geometry"); pGobject.setValue(ewkt); ps.setObject(i,pGobject); } }

  /**

  • 取出数据转换,WKB->Geojson
  • @param rs
  • @param columnName
  • @return
  • @throws SQLException */ @Override public Map getResult(ResultSet rs, String columnName) throws SQLException { String WKB = rs.getString(columnName); if(WKB==null){ return null; } WKBReader reader = new WKBReader(); Geometry geometry = null; try { geometry = reader.read(WKBReader.hexToBytes(WKB)); } catch (ParseException e) { //转换失败 return null; } //转换成4326 try { int srid = geometry.getSRID(); // if(srid==0)srid=4549;//默认4549 int targetSrid=4326;//默认4326 // Environment environment = SpringContextUtil.getBean(Environment.class); // if(environment!=null){ // String sridTarget = environment.getProperty("sridTarget"); // if(sridTarget!=null){ // try { // targetSrid=Integer.valueOf(sridTarget); // } catch (Exception e){ // //转换失败 // } // } // } if(srid!=targetSrid&&srid!=0){ CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true); CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:"+targetSrid,true); MathTransform transform = CRS.findMathTransform(sourceCRS, targetCRS,true); geometry = JTS.transform(geometry, transform); geometry.setSRID(targetSrid); } } catch (Exception e) { e.printStackTrace(); } // 设置保留6位小数，否则GeometryJSON默认保留4位小数 GeometryJSON geometryJson = new GeometryJSON(7); String json = geometryJson.toString(geometry); // JSONObject jsonObject = JSONObject.parseObject(json); // jsonObject.put("srid",geometry.getSRID()); // return jsonObject.toJSONString(); return JSONObject.parseObject(json); }

  @Override public Map getResult(ResultSet rs, int columnIndex) throws SQLException { return null; }

  @Override public Map getResult(CallableStatement cs, int columnIndex) throws SQLException { return null; }

} AI写代码

package com.wisdomcity.yingkou.fmewcs_service.mapper.typehandler;

import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Select;

/**

• @author yaoct

• @date 2025/12/1 13:43

• @description: */ @Mapper public interface WKB2GeoJsonTypeMapper {

  /**

  • 多边形自相交修复
  • @param ewkt
  • @return */ @Select("select st_asewkt(st_makevalid(#{ewkt}::geometry))") String makevalid(String ewkt); } AI写代码

====================================================================

参考：

Mybatis-plus读取和保存Postgis geometry数据 - 简书

MyBatis Plus 自动类型转换之TypeHandler - 周世元ISO8859-1 - 博客园

GeoTools The Open Source Java GIS Toolkit — GeoTools

Geotools中Geometry对象与GeoJson的相互转换_mathyrs的博客-CSDN博客_geojson转geometry

mybatis 自定义TypeHandler映射Geometry空间几何数据 PGPoint (java +mybatis+ pgsql) - 灰信网（软件开发博客聚合）

Mybatis常用注解

原文链接：blog.csdn.net/yaoct/artic…