devbox
秋刀鱼在做梦
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：秋刀鱼在做梦 | 2024-07-23 14:42:14

赞

踩

mybatis geometry

在入库和查询中需要自动的让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引入：

  1. <!-- geotools的远程库 -->
  2.     <properties>
  3.         <geotools.version>27.0</geotools.version>
  4.     </properties>
  5.     <repositories>
  6.         <repository>
  7.             <id>osgeo</id>
  8.             <name>OSGeo Release Repository</name>
  9.             <url>https://repo.osgeo.org/repository/release/</url>
  10.             <snapshots>
  11.                 <enabled>false</enabled>
  12.             </snapshots>
  13.             <releases>
  14.                 <enabled>true</enabled>
  15.             </releases>
  16.         </repository>
  17.     </repositories>
  18.     <dependencies>
  19.         <!--geotool https://docs.geotools.org/latest/userguide/tutorial/quickstart/maven.html-->
  20.         <dependency>
  21.             <groupId>org.geotools</groupId>
  22.             <artifactId>gt-geojson</artifactId>
  23.             <version>${geotools.version}</version>
  24.         </dependency>
  25.         <dependency>
  26.             <groupId>org.geotools</groupId>
  27.             <artifactId>gt-main</artifactId>
  28.             <version>${geotools.version}</version>
  29.         </dependency>
  30.         <dependency>
  31.             <groupId>org.geotools</groupId>
  32.             <artifactId>gt-opengis</artifactId>
  33.             <version>${geotools.version}</version>
  34.         </dependency>
  35.         <dependency>
  36.             <groupId>org.geotools</groupId>
  37.             <artifactId>gt-referencing</artifactId>
  38.             <version>${geotools.version}</version>
  39.         </dependency>
  40.         <dependency>
  41.             <groupId>org.geotools</groupId>
  42.             <artifactId>gt-metadata</artifactId>
  43.             <version>${geotools.version}</version>
  44.         </dependency>
  45.         <dependency>
  46.             <groupId>org.geotools</groupId>
  47.             <artifactId>gt-epsg-hsql</artifactId>
  48.             <version>${geotools.version}</version>
  49.         </dependency>
  50.     </dependencies>

Geotools工具类转换WKB和Geojson

  1. WKBReader reader = new WKBReader( );
  2.         Geometry geometry = reader.read(WKBReader.hexToBytes("0101000020E61000002C39382229FD5D4085716007088C3E40"));
  3.         // 设置保留6位小数，否则GeometryJSON默认保留4位小数
  4.         GeometryJSON geometryJson = new GeometryJSON(7);
  5.         String s = geometryJson.toString(geometry);
  6.         System.out.println(s);
  7.  
  8.         //{"type":"Point","coordinates":[119.9556356,30.5469975]}
  9.         //EWKB->转geojson丢失信息
  10.         Geometry read = geometryJson.read("{\"type\":\"Point\",\"coordinates\":[119.9556356,30.5469975]}");
  11.         System.out.println(read.toString());
  12.         WKBWriter wkbWriter = new WKBWriter();
  13.         byte[] write = wkbWriter.write(geometry);
  14.         String s1 = WKBWriter.toHex(write);
  15.         System.out.println(s1);

2.mybatis-plus类型转换器

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

MyBatis Plus 自动类型转换之TypeHandler

  1.  
  2. @Data
  3. @ToString
  4. @TableName (value = "test_table",autoResultMap = true)
  5. @ApiModel ("test")
  6. public class TestTable  implements Serializable {
  7.  
  8. 	private static final long serialVersionUID =  8881418345724766899L;
  9.  
  10. 	@TableId(value = "id")
  11. 	private Integer id;
  12.  
  13. 	@ApiModelProperty(value = "二进制WKB数据")
  14.    	@TableField(value = "position",typeHandler = WKB2GeoJsonTypeHandler.class)
  15.     @JsonRawValue
  16. 	private String position;
  17.  
  18.    	@TableField("name")
  19. 	private String name;
  20.  
  21. }

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

  1. mybatis-plus:
  2.   type-handlers-package: com.zjzy.mapper.typehandler
  3.   configuration:
  4.     map-underscore-to-camel-case: true
  5.     auto-mapping-behavior: full
  6.     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  7.   mapper-locations: classpath*:mapper/**/*Mapper.xml

3.mybatis类型转换器转换geometry格式

 因为缺少JDBC映射，数据库开始报错：‘类型为 geometry, 但表达式的类型为 character varying’

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

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

添加maven包：

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

类型转换器代码：

  1. package com.zjzy.mapper.typehandler;
  2.  
  3. import com.zjzy.mapper.CommonCodeMapper;
  4. import com.zjzy.util.SpringContextUtil;
  5. import lombok.extern.slf4j.Slf4j;
  6. import org.apache.ibatis.type.JdbcType;
  7. import org.apache.ibatis.type.MappedJdbcTypes;
  8. import org.apache.ibatis.type.MappedTypes;
  9. import org.apache.ibatis.type.TypeHandler;
  10. import org.geotools.geojson.geom.GeometryJSON;
  11. import org.geotools.geometry.jts.WKBReader;
  12. import org.locationtech.jts.geom.Geometry;
  13. import org.locationtech.jts.io.ParseException;
  14. import org.postgis.PGgeometry;
  15.  
  16. import java.sql.CallableStatement;
  17. import java.sql.PreparedStatement;
  18. import java.sql.ResultSet;
  19. import java.sql.SQLException;
  20. import java.util.Set;
  21.  
  22. /**
  23.  * @version 2.0
  24.  * @description
  25.  * @Author yaoct
  26.  * @create 2021/12/22 16:19
  27.  */
  28. @Slf4j
  29. public class WKB2GeoJsonTypeHandler implements TypeHandler<String> {
  30.  
  31.     /**
  32.      * 插入数据，转换,geoJson2EWKB
  33.      * @param ps
  34.      * @param i
  35.      * @param parameter
  36.      * @param jdbcType
  37.      * @throws SQLException
  38.      */
  39.     @Override
  40.     public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
  41. //EWKB是pg数据库自定义的类型，所以需要用数据库函数,插入数据库一般次数较少，这样并不会对数据库造成过大负担
  42. //        CommonCodeMapper mapper = SpringContextUtil.getBean(CommonCodeMapper.class);
  43. //        String EWKB = mapper.geo2EWKB(parameter);
  44. //        PGgeometry ewkb = new PGgeometry(EWKB);
  45. //        ps.setObject(i,ewkb);
  46.         
  47.         //通过geoTool转换WKB
  48.         GeometryJSON geometryJson = new GeometryJSON(7);
  49.         Geometry read = null;
  50.         try {
  51.             read = geometryJson.read(parameter);
  52.         } catch (IOException e) {
  53.             e.printStackTrace();
  54.         }
  55. //        System.out.println(read.toString());
  56.         read.setSRID(4326);
  57. //        GeometryJSON geometryJson = new GeometryJSON(7);
  58.         WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true);
  59.         byte[] write = wkbWriter.write(read);
  60.         String s1 = WKBWriter.toHex(write);
  61.         PGgeometry ewkb = new PGgeometry(s1);
  62.         ps.setObject(i,ewkb);
  63.     }
  64.  
  65.     /**
  66.      * 取出数据转换,WKB->Geojson
  67.      * @param rs
  68.      * @param columnName
  69.      * @return
  70.      * @throws SQLException
  71.      */
  72.     @Override
  73.     public String getResult(ResultSet rs, String columnName) throws SQLException {
  74.         String WKB = rs.getString(columnName);
  75.         if(WKB==null){
  76.             return null;
  77.         }
  78.         WKBReader reader = new WKBReader( );
  79.         Geometry geometry = null;
  80.         try {
  81.             geometry = reader.read(WKBReader.hexToBytes(WKB));
  82.         } catch (ParseException e) {
  83.             //转换失败
  84.             return null;
  85.         }
  86.         //转换成4326
  87.         try {
  88.             int srid = geometry.getSRID();
  89.             CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true);
  90.             CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:4326",true);
  91.             MathTransform transform = CRS.findMathTransform(sourceCRS, targetCRS,true);
  92.             geometry = JTS.transform(geometry, transform);
  93.         } catch (Exception e) {
  94.             e.printStackTrace();
  95.         }
  96.         // 设置保留6位小数，否则GeometryJSON默认保留4位小数
  97.         GeometryJSON geometryJson = new GeometryJSON(7);
  98.         return geometryJson.toString(geometry);
  99.     }
  100.  
  101.     @Override
  102.     public String getResult(ResultSet rs, int columnIndex) throws SQLException {
  103.         return null;
  104.     }
  105.  
  106.     @Override
  107.     public String getResult(CallableStatement cs, int columnIndex) throws SQLException {
  108.         return null;
  109.     }
  110. }
  1. @Component
  2. public class SpringContextUtil implements ApplicationContextAware {
  3.  
  4.     private static ApplicationContext applicationContext;
  5.  
  6.     @Override
  7.     public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
  8.         SpringContextUtil.applicationContext = applicationContext;
  9.     }
  10.  
  11.     //获取applicationContext
  12.     public static ApplicationContext getApplicationContext() {
  13.         return applicationContext;
  14.     }
  15.  
  16.     //通过name获取 Bean.
  17.     public static Object getBean(String name) {
  18.         return getApplicationContext().getBean(name);
  19.     }
  20.  
  21.     //通过class获取Bean.
  22.     public static <T> T getBean(Class<T> clazz) {
  23.         return getApplicationContext().getBean(clazz);
  24.     }
  25.  
  26.     //通过name,以及Clazz返回指定的Bean
  27.     public static <T> T getBean(String name, Class<T> clazz) {
  28.         return getApplicationContext().getBean(name, clazz);
  29.     }
  30.  
  31.     //关闭springboot
  32.     public static void close(){
  33.         ((ConfigurableApplicationContext)(getApplicationContext())).close();
  34.     }
  35.  
  36.     //得到当前线程的HttpServletResponse,可在静态方法中得到
  37.     public static HttpServletResponse getHttpServletResponse(){
  38.         return ((ServletRequestAttributes) RequestContextHolder.currentRequestAttributes()).getResponse();
  39.     }
  40.  
  41.     //得到当前线程的HttpServletRequest,可在静态方法中得到
  42.     public static HttpServletRequest getHttpServletRequest(){
  43.         return ((ServletRequestAttributes) RequestContextHolder.currentRequestAttributes()).getRequest();
  44.     }
  45.  
  46. }

  1. @Repository
  2. public interface CommonCodeMapper extends BaseMapper<CommonCode> {
  3.  
  4.     /**
  5.      * EWKB是pg数据库自定义的类型，所以需要用数据库函数
  6.      * @param geoJson
  7.      * @return
  8.      */
  9.     @Select("select ST_GeomFromGeoJSON(#{geoJson})")
  10.     String geo2EWKB(String geoJson);
  11.  
  12.  
  13. }

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

在mapper类中使用示例：https://www.jianshu.com/p/6465189984b0

  1. /**
  2.  * 使用ResultMap 
  3.  */
  4. @Results(id = "userMap", value = {
  5.             //可以使用这种方式来处理字段名和数据库表字段名不一致的情况
  6.             @Result(column = "username", property = "username", jdbcType=JdbcType.VARCHAR,typeHandler = WKB2GeoJsonTypeHandler.class),
  7.             @Result(column = "passwd", property = "passwd", jdbcType=JdbcType.VARCHAR),
  8.             @Result(column = "birth_day", property = "birthDay", jdbcType=JdbcType.VARCHAR)
  9.     })
  10. @Select("SELECT * FROM t_user WHERE id=#{id}")
  11. User loadByIdResultMap(Long id);
  12.  
  13. /**
  14.  * 引用其他的Result 
  15.  */
  16. @ResultMap("userMap")
  17. @Select("SELECT * FROM t_user WHERE id=#{id}")
  18. User loadByIdResultMapReference(Long id);

在xml中使用示例示例：

  1.     <resultMap id="locationResultMap" type="com.zjzy.model.po.ExaminationApplication">
  2.         <result typeHandler="com.zjzy.mapper.typehandler.WKB2GeoJsonTypeHandler" column="dig_position" javaType="String"
  3.                 property="digPosition"/>
  4.     </resultMap>
  5.  
  6.     <select id="selectExaminationApplicationByPage" resultMap="locationResultMap">
  7.     </select>

注意：

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包

  1. package com.zjzy.mapper.typehandler;
  2.  
  3. import com.alibaba.fastjson.JSONObject;
  4. import com.zjzy.util.GisUtil;
  5. import com.zjzy.util.SpringContextUtil;
  6. import lombok.extern.slf4j.Slf4j;
  7. import org.apache.ibatis.type.JdbcType;
  8. import org.apache.ibatis.type.TypeHandler;
  9. import org.geotools.geojson.geom.GeometryJSON;
  10. import org.geotools.geometry.jts.JTS;
  11. import org.geotools.geometry.jts.WKBReader;
  12. import org.geotools.referencing.CRS;
  13. import org.locationtech.jts.geom.Geometry;
  14. import org.locationtech.jts.io.ByteOrderValues;
  15. import org.locationtech.jts.io.ParseException;
  16. import org.locationtech.jts.io.WKBWriter;
  17. import org.locationtech.jts.io.WKTWriter;
  18. import org.opengis.referencing.crs.CoordinateReferenceSystem;
  19. import org.opengis.referencing.operation.MathTransform;
  20. import org.postgis.PGbox2d;
  21. import org.postgis.PGgeometry;
  22. import org.postgresql.util.PGobject;
  23. import org.springframework.core.env.Environment;
  24.  
  25. import java.io.IOException;
  26. import java.sql.CallableStatement;
  27. import java.sql.PreparedStatement;
  28. import java.sql.ResultSet;
  29. import java.sql.SQLException;
  30.  
  31. /**
  32.  * @version 1.0
  33.  * @description
  34.  * @Author yaoct
  35.  * @create 2021/12/22 16:19
  36.  */
  37. @Slf4j
  38. public class WKB2GeoJsonTypeHandler implements TypeHandler<String> {
  39.  
  40.     /**
  41.      * 插入数据，转换,geoJson2EWKB
  42.      * @param ps
  43.      * @param i
  44.      * @param parameter
  45.      * @param jdbcType
  46.      * @throws SQLException
  47.      */
  48.     @Override
  49.     public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
  50.         //EWKB是pg数据库自定义的类型，所以需要用数据库函数,插入数据库一般次数较少，这样并不会对数据库造成过大负担
  51. //        CommonCodeMapper mapper = SpringContextUtil.getBean(CommonCodeMapper.class);
  52. //        String EWKB = mapper.geo2EWKB(parameter);
  53. //        PGgeometry ewkb = new PGgeometry(EWKB);
  54. //        ps.setObject(i,ewkb);
  55.  
  56.         //通过geoTool转换WKB
  57.         GeometryJSON geometryJson = new GeometryJSON(7);
  58.         Geometry geometry = null;
  59.         try {
  60.             geometry = geometryJson.read(parameter);
  61.         } catch (IOException e) {
  62.             e.printStackTrace();
  63.         }
  64. //        System.out.println(read.toString());
  65.         geometry.setSRID(4326);
  66. //        GeometryJSON geometryJson = new GeometryJSON(7);
  67.  
  68.         //方式1.基于net.postgis
  69. //        WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true);
  70. //        byte[] write = wkbWriter.write(geometry);
  71. //        String s1 = WKBWriter.toHex(write);
  72. //        PGgeometry ewkb = new PGgeometry(s1);//EWKB比WKB多了SRID头字符串，PGgeometry.toString()会自动添加SRID
  73. //        ps.setObject(i,ewkb);
  74.  
  75.         //方式2.设置ewkb,Postgresql驱动会先去数据库 pg_catalog.pg_type 查出geometry的类型id，这里的geometry字符串对应数据库pg_catalog.pg_type表中的geometry
  76.         //以下方式不用导入net.postgis包
  77. //        PGobject pGobject = new PGobject();
  78. //        pGobject.setType("geometry");
  79. //        pGobject.setValue(s1);
  80. //        ps.setObject(i,pGobject);
  81.  
  82.         //方式3.设置拼接ewkt
  83.         //以下方式不用导入net.postgis包，ewkb太长会超出sql限制，以下转换为ewkt
  84.         PGobject pGobject = new PGobject();
  85.         pGobject.setType("geometry");
  86.         String pre="SRID="+4326+";";
  87.         pGobject.setValue(pre+GisUtil.Geometry2WKT(geometry));
  88.         ps.setObject(i,pGobject);
  89.     }
  90.  
  91.  
  92.     /**
  93.      * 取出数据转换,WKB->Geojson
  94.      * @param rs
  95.      * @param columnName
  96.      * @return
  97.      * @throws SQLException
  98.      */
  99.     @Override
  100.     public String getResult(ResultSet rs, String columnName) throws SQLException {
  101.         String WKB = rs.getString(columnName);
  102.         if(WKB==null){
  103.             return null;
  104.         }
  105.         WKBReader reader = new WKBReader( );
  106.         Geometry geometry = null;
  107.         try {
  108.             geometry = reader.read(WKBReader.hexToBytes(WKB));
  109.         } catch (ParseException e) {
  110.             //转换失败
  111.             return null;
  112.         }
  113.         //转换成4326
  114.         try {
  115.             int srid = geometry.getSRID();
  116.             if(srid==0)srid=4549;//默认4549
  117.             int targetSrid=4326;//默认4326
  118.             Environment environment = SpringContextUtil.getBean(Environment.class);
  119.             if(environment!=null){
  120.                 String sridTarget = environment.getProperty("sridTarget");
  121.                 if(sridTarget!=null){
  122.                     try {
  123.                         targetSrid=Integer.valueOf(sridTarget);
  124.                     } catch (Exception e){
  125.                         //转换失败
  126.                     }
  127.                 }
  128.             }
  129.  
  130.             CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true);
  131.             CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:"+targetSrid,true);
  132.             MathTransform transform = CRS.findMathTransform(sourceCRS, targetCRS,true);
  133.             geometry = JTS.transform(geometry, transform);
  134.         } catch (Exception e) {
  135.             e.printStackTrace();
  136.         }
  137.         // 设置保留6位小数，否则GeometryJSON默认保留4位小数
  138.         GeometryJSON geometryJson = new GeometryJSON(7);
  139.         String json = geometryJson.toString(geometry);
  140.         JSONObject jsonObject = JSONObject.parseObject(json);
  141.         jsonObject.put("srid",geometry.getSRID());
  142.         return jsonObject.toJSONString();
  143.     }
  144.  
  145.     @Override
  146.     public String getResult(ResultSet rs, int columnIndex) throws SQLException {
  147.         return null;
  148.     }
  149.  
  150.     @Override
  151.     public String getResult(CallableStatement cs, int columnIndex) throws SQLException {
  152.         return null;
  153.     }
  154.  
  155. }
  1. package com.zjzy.util;
  2.  
  3. import org.geotools.geojson.geom.GeometryJSON;
  4. import org.geotools.geometry.jts.JTS;
  5. import org.geotools.geometry.jts.WKBReader;
  6. import org.geotools.referencing.CRS;
  7. import org.geotools.referencing.GeodeticCalculator;
  8. import org.geotools.referencing.crs.DefaultGeographicCRS;
  9. import org.locationtech.jts.geom.Geometry;
  10. import org.locationtech.jts.io.*;
  11. import org.opengis.referencing.FactoryException;
  12. import org.opengis.referencing.crs.CoordinateReferenceSystem;
  13. import org.opengis.referencing.operation.MathTransform;
  14. import org.opengis.referencing.operation.TransformException;
  15.  
  16. import java.io.IOException;
  17.  
  18. /**
  19.  * @Author yaoct
  20.  * @Date 2022/9/16 10:55
  21.  * @Version 1.0
  22.  * @description
  23.  */
  24. public class GisUtil {
  25.  
  26.     /**
  27.      * WKB字符串转Geometry格式
  28.      * @param WKB
  29.      * @return
  30.      */
  31.     public static Geometry WKB2Geometry(String WKB){
  32.         WKBReader reader = new WKBReader();
  33.         Geometry geometry = null;
  34.         try {
  35.             geometry = reader.read(WKBReader.hexToBytes(WKB));
  36.         } catch (ParseException e) {
  37.             //转换失败
  38.             throw new RuntimeException("转换失败",e);
  39.         }
  40.         return geometry;
  41.     }
  42.  
  43.     /**
  44.      * Geometry格式转WKB字符串
  45.      * @param geometry
  46.      * @return
  47.      */
  48.     public static String Geometry2WKB(Geometry geometry){
  49.         WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true);
  50.         byte[] write = wkbWriter.write(geometry);
  51.         String ret = WKBWriter.toHex(write);
  52.         return ret;
  53.     }
  54.  
  55.     /**
  56.      * WKT字符串转Geometry格式
  57.      * @param WKT
  58.      * @return
  59.      */
  60.     public static Geometry WKT2Geometry(String WKT){
  61.         WKTReader reader = new WKTReader();
  62.         Geometry geometry = null;
  63.         try {
  64.             geometry = reader.read(WKT);
  65.         } catch (ParseException e) {
  66.             //转换失败
  67.             throw new RuntimeException("转换失败",e);
  68.         }
  69.         return geometry;
  70.     }
  71.  
  72.     /**
  73.      * Geometry格式转WKT字符串
  74.      * @param geometry
  75.      * @return
  76.      */
  77.     public static String Geometry2WKT(Geometry geometry){
  78.         WKTWriter wktWriter = new WKTWriter();
  79.         String wkt= wktWriter.write(geometry);
  80.         return wkt;
  81.     }
  82.  
  83.     /**
  84.      * 转换Geometry坐标
  85.      * @param geometry
  86.      * @param targetSrid 新坐标
  87.      * @return
  88.      */
  89.     public static Geometry coordinateTransfer(Geometry geometry, int targetSrid){
  90.         int originSrid = geometry.getSRID();
  91.         CoordinateReferenceSystem sourceCRS = null;
  92.         CoordinateReferenceSystem targetCRS = null;
  93.         MathTransform transform=null;
  94.         try {
  95.             sourceCRS = CRS.decode("EPSG:"+originSrid,true);
  96.             targetCRS = CRS.decode("EPSG:"+targetSrid,true);
  97.             transform = CRS.findMathTransform(sourceCRS, targetCRS,true);
  98.         } catch (FactoryException e) {
  99.             throw new RuntimeException("坐标错误",e);
  100.         }
  101.         Geometry ret = null;
  102.         try {
  103.             ret = JTS.transform(geometry, transform);
  104.         } catch (TransformException e) {
  105.             throw new RuntimeException("坐标转换错误",e);
  106.         }
  107.         return ret;
  108.     }
  109.  
  110.     /**
  111.      * Geometry->geoJson
  112.      * @param geometry
  113.      * @return
  114.      */
  115.     public static String geometry2GeoJson(Geometry geometry){
  116.         // 设置保留6位小数，否则GeometryJSON默认保留4位小数
  117.         GeometryJSON geometryJson = new GeometryJSON(7);
  118.         String json = geometryJson.toString(geometry);
  119. //        JSONObject jsonObject = JSONObject.parseObject(json);
  120. //        jsonObject.put("srid",geometry.getSRID());
  121. //        return jsonObject.toJSONString();
  122.         return json;
  123.     }
  124.  
  125.     /**
  126.      * geoJson->Geometry
  127.      * @param geoJson
  128.      * @return
  129.      */
  130.     public static Geometry geoJson2Geometry(String geoJson){
  131.         GeometryJSON geometryJson = new GeometryJSON(7);
  132.         Geometry ret=null;
  133.         try {
  134.             Geometry read = geometryJson.read(geoJson);
  135.         } catch (IOException e) {
  136.             throw new RuntimeException("转换失败",e);
  137.         }
  138.         return ret;
  139.     }
  140.  
  141.     /**
  142.      * 计算两点间的距离
  143.      * @param lon1
  144.      * @param lat1
  145.      * @param lon2
  146.      * @param lat2
  147.      * @return
  148.      */
  149.     public static double calculateDistance(double lon1,double lat1,double lon2,double lat2){
  150.         //CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true);
  151.         GeodeticCalculator geodeticCalculator = new GeodeticCalculator(DefaultGeographicCRS.WGS84);
  152.         // 起点经纬度
  153.         geodeticCalculator.setStartingGeographicPoint(lon1,lat1);
  154.         // 末点经纬度
  155.         geodeticCalculator.setDestinationGeographicPoint(lon2,lat2);
  156.         // 计算距离，单位：米
  157.         double distance = geodeticCalculator.getOrthodromicDistance();
  158.         return distance;
  159.     }
  160. }

===========================2023.3.8===============================

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

  1. package com.wisdomcity.laian.monitor.mapper.typehandler;
  2.  
  3. import com.alibaba.fastjson.JSONObject;
  4. import com.fasterxml.jackson.databind.ObjectMapper;
  5. import com.fhs.common.spring.SpringContextUtil;
  6. import com.kingbase8.util.KBobject;
  7. import com.wisdomcity.laian.monitor.utils.GisUtil;
  8. import lombok.extern.slf4j.Slf4j;
  9. import org.apache.ibatis.type.JdbcType;
  10. import org.apache.ibatis.type.TypeHandler;
  11. import org.geotools.geojson.geom.GeometryJSON;
  12. import org.geotools.geometry.jts.JTS;
  13. import org.geotools.geometry.jts.WKBReader;
  14. import org.geotools.referencing.CRS;
  15. import org.locationtech.jts.geom.Geometry;
  16. import org.locationtech.jts.io.ByteOrderValues;
  17. import org.locationtech.jts.io.ParseException;
  18. import org.locationtech.jts.io.WKBWriter;
  19. import org.opengis.referencing.crs.CoordinateReferenceSystem;
  20. import org.opengis.referencing.operation.MathTransform;
  21.  
  22. import java.io.IOException;
  23. import java.sql.CallableStatement;
  24. import java.sql.PreparedStatement;
  25. import java.sql.ResultSet;
  26. import java.sql.SQLException;
  27. import java.util.Map;
  28.  
  29. /**
  30.  * @version 1.0
  31.  * @description
  32.  * @Author yaoct
  33.  * @create 2021/12/22 16:19
  34.  */
  35. @Slf4j
  36. public class WKB2GeoJsonTypeHandler implements TypeHandler<Map> {
  37.  
  38.     /**
  39.      * 插入数据，转换,geoJson2EWKB
  40.      * @param ps
  41.      * @param i
  42.      * @param parameter
  43.      * @param jdbcType
  44.      * @throws SQLException
  45.      */
  46.     @Override
  47.     public void setParameter(PreparedStatement ps, int i, Map parameter, JdbcType jdbcType) throws SQLException {
  48.         //EWKB是pg数据库自定义的类型，所以需要用数据库函数,插入数据库一般次数较少，这样并不会对数据库造成过大负担
  49. //        CommonCodeMapper mapper = SpringContextUtil.getBean(CommonCodeMapper.class);
  50. //        String EWKB = mapper.geo2EWKB(parameter);
  51. //        PGgeometry ewkb = new PGgeometry(EWKB);
  52. //        ps.setObject(i,ewkb);
  53.  
  54.         //通过geoTool转换WKB
  55.         GeometryJSON geometryJson = new GeometryJSON(7);
  56.         Geometry geometry = null;
  57.         try {
  58.             geometry = geometryJson.read(JSONObject.toJSONString(parameter));
  59.         } catch (IOException e) {
  60.             e.printStackTrace();
  61.         }
  62. //        System.out.println(read.toString());
  63.         geometry.setSRID(4326);
  64. //        GeometryJSON geometryJson = new GeometryJSON(7);
  65.  
  66.         //方式1.基于net.postgis
  67.         WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true);
  68.         byte[] write = wkbWriter.write(geometry);
  69.         String s1 = WKBWriter.toHex(write);
  70. //        PGgeometry ewkb = new PGgeometry(s1);//EWKB比WKB多了SRID头字符串，PGgeometry.toString()会自动添加SRID
  71. //        ps.setObject(i,ewkb);
  72.  
  73.         //方式2.设置ewkb,Postgresql驱动会先去数据库 pg_catalog.pg_type 查出geometry的类型id，这里的geometry字符串对应数据库pg_catalog.pg_type表中的geometry
  74. //        以下方式不用导入net.postgis包
  75. //        PGobject pGobject = new PGobject();
  76. //        pGobject.setType("geometry");
  77. //        pGobject.setValue(s1);
  78. //        ps.setObject(i,pGobject);
  79.  
  80.         //方式3.设置拼接ewkt
  81.         //以下方式不用导入net.postgis包，ewkb太长会超出sql限制，以下转换为ewkt
  82. //        PGobject pGobject = new PGobject();
  83. //        pGobject.setType("geometry");
  84. //        String pre="SRID="+4326+";";
  85. //        pGobject.setValue(pre+GisUtil.Geometry2WKT(geometry));
  86. //        ps.setObject(i,pGobject);
  87.         KBobject pGobject = new KBobject();
  88.         pGobject.setType("geometry");
  89.         String pre="SRID="+4326+";";
  90.         pGobject.setValue(pre+ GisUtil.Geometry2WKT(geometry));
  91.         ps.setObject(i,pGobject);
  92.     }
  93.  
  94.  
  95.     /**
  96.      * 取出数据转换,WKB->Geojson
  97.      * @param rs
  98.      * @param columnName
  99.      * @return
  100.      * @throws SQLException
  101.      */
  102.     @Override
  103.     public Map getResult(ResultSet rs, String columnName) throws SQLException {
  104.         String WKB = rs.getString(columnName);
  105.         if(WKB==null){
  106.             return null;
  107.         }
  108.         WKBReader reader = new WKBReader( );
  109.         Geometry geometry = null;
  110.         try {
  111.             geometry = reader.read(WKBReader.hexToBytes(WKB));
  112.         } catch (ParseException e) {
  113.             //转换失败
  114.             return null;
  115.         }
  116.         //转换成4326
  117.         try {
  118.             int srid = geometry.getSRID();
  119. //            if(srid==0)srid=4549;//默认4549
  120.             int targetSrid=4326;//默认4326
  121. //            Environment environment = SpringContextUtil.getBean(Environment.class);
  122. //            if(environment!=null){
  123. //                String sridTarget = environment.getProperty("sridTarget");
  124. //                if(sridTarget!=null){
  125. //                    try {
  126. //                        targetSrid=Integer.valueOf(sridTarget);
  127. //                    } catch (Exception e){
  128. //                        //转换失败
  129. //                    }
  130. //                }
  131. //            }
  132.             if(srid!=targetSrid&&srid!=0){
  133.                 CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:"+srid,true);
  134.                 CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:"+targetSrid,true);
  135.                 MathTransform transform = CRS.findMathTransform(sourceCRS, targetCRS,true);
  136.                 geometry = JTS.transform(geometry, transform);
  137.                 geometry.setSRID(targetSrid);
  138.             }
  139.         } catch (Exception e) {
  140.             e.printStackTrace();
  141.         }
  142.         // 设置保留6位小数，否则GeometryJSON默认保留4位小数
  143.         GeometryJSON geometryJson = new GeometryJSON(7);
  144.         String json = geometryJson.toString(geometry);
  145. //        JSONObject jsonObject = JSONObject.parseObject(json);
  146. //        jsonObject.put("srid",geometry.getSRID());
  147. //        return jsonObject.toJSONString();
  148.         return JSONObject.parseObject(json);
  149.     }
  150.  
  151.     @Override
  152.     public Map getResult(ResultSet rs, int columnIndex) throws SQLException {
  153.         return null;
  154.     }
  155.  
  156.     @Override
  157.     public Map getResult(CallableStatement cs, int columnIndex) throws SQLException {
  158.         return null;
  159.     }
  160.  
  161. }

实体类字段改为 ：

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

xml中使用

  1. <resultMap id="locationResultMap" type="com.wisdomcity.laian.monitor.model.supply.po.SupplyPipePoint">
  2.         <result typeHandler="com.wisdomcity.laian.monitor.mapper.typehandler.WKB2GeoJsonTypeHandler" column="geom" javaType="Map"
  3.                 property="geom"/>
  4.     </resultMap>

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

参考：

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常用注解

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/870363
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号