本文共 6512 字，大约阅读时间需要 21 分钟。

Mysql Geometry数据处理之WKT方案

WKT（Well-Known Text）简介

WKT（Well-Known Text）是一种表达几何信息的字符串格式，广泛应用于存储和处理空间几何数据。例如，点可以用POINT (3 3)表示，线可以用LINESTRING (1 1, 2 2)表示。

Geometry类的使用

在Mysql 8.0及以上版本，几何数据类型得到了支持。我们可以通过org.locationtech.jts库中的Geometry类来构建和操作几何数据。

引入依赖

在项目中引入必要的依赖：

       
    
     org.locationtech.jts
        
    
     jts-core
        
    
     1.19.0
    
   

Geometry对象的创建

GeometryFactory geometryFactory = new GeometryFactory();Geometry point = geometryFactory.createPoint(new Coordinate(3, 3));

示例

// 创建点GeometryFactory geometryFactory = new GeometryFactory();Geometry point = geometryFactory.createPoint(new Coordinate(3, 3));// 创建线GeometryFactory geometryFactory = new GeometryFactory();LineString lineString = geometryFactory.createLineString(    new Coordinate[]{new Coordinate(1, 1), new Coordinate(2, 2)});// 创建线集合LineString line1 = geometryFactory.createLineString(new Coordinate[]{new Coordinate(1, 1), new Coordinate(2, 2)});LineString line2 = geometryFactory.createLineString(new Coordinate[]{new Coordinate(3, 3), new Coordinate(4, 4)});MultiLineString multiLineString = geometryFactory.createMultiLineString(new LineString[]{line1, line2});

自定义类型处理器

为了实现Mysql Geometry数据与WKT格式的转换，我们需要自定义一个BaseTypeHandler，用于处理Geometry对象。

public class GeometryTypeWKTHandler extends BaseTypeHandler
   
     {    private static final GeometryFactory factory = new GeometryFactory(new PrecisionModel(PrecisionModel.FLOATING), 4326);    @Override    public void setNonNullParameter(PreparedStatement ps, int i, Geometry parameter, JdbcType jdbcType) throws SQLException {        String wkt = serializeGeometry(parameter);        ps.setString(i, wkt);    }    @Override    public Geometry getNullableResult(ResultSet rs, String columnName) throws SQLException {        String wkt = rs.getString(columnName);        return deserializeGeometry(wkt);    }    @Override    public Geometry getNullableResult(ResultSet rs, int columnIndex) throws SQLException {        String wkt = rs.getString(columnIndex);        return deserializeGeometry(wkt);    }    @Override    public Geometry getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {        String wkt = cs.getString(columnIndex);        return deserializeGeometry(wkt);    }    private String serializeGeometry(Geometry geometry) {        WKTWriter writer = new WKTWriter(2);        return writer.write(geometry);    }    private Geometry deserializeGeometry(String wkt) throws ParseException {        return new WKTReader(factory).read(wkt);    }}
   

数据库表结构设计

创建存储WKT格式几何数据的表t_geo_wkt：

CREATE TABLE `t_geo_wkt` (    `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',    `del_flag` char(1) DEFAULT '0' COMMENT '删除标记，0未删除，1已删除',    `name` varchar(255) DEFAULT NULL COMMENT '名称',    `geo_type` varchar(255) DEFAULT NULL COMMENT 'geo类型',    `geo` geometry NOT NULL COMMENT '几何数据',    PRIMARY KEY (`id`),    SPATIAL KEY `idx_geo` (`geo`) COMMENT '空间数据索引') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '几何数据WKT表';

DO类定义

定义一个GeoWktDO类来封装几何数据：

@Table(name = "t_geo_wkt")public class GeoWktDO implements Serializable {    private static final long serialVersionUID = 1L;    @TableId(value = "id", type = IdType.AUTO)    private Long id;    @TableField("create_time")    private LocalDateTime createTime;    @TableField("update_time")    private LocalDateTime updateTime;    @TableField("del_flag")    private String delFlag;    @TableField("name")    private String name;    @TableField("geo_type")    private String geoType;    @TableField("geo")    private Geometry geo;}

Mapper XML配置

在Mapper XML中配置数据处理：

       
            
             
             
             
             
             
             
         
        
    
             id, create_time, update_time, del_flag, name, geo_type, ST_AsText(geo) AS geo_wkt    
        
            SELECT * FROM t_geo_wkt        
            SELECT * FROM t_geo_wkt WHERE id = #{id}        
    
             INSERT INTO t_geo_wkt(create_time, update_time, del_flag, name, geo_type, geo)        VALUES (now(), now(), #{delFlag}, #{name}, #{geoType}, ST_GeomFromText(#{geo, typeHandler=com.charge.ws.handler.jts.GeometryTypeWKTHandler, jdbcType=BLOB}))    
        
    
             INSERT INTO t_geo_wkt(id, create_time, update_time, del_flag, name, geo_type, geo)        VALUES (#{id}, now(), now(), #{delFlag}, #{name}, #{geoType}, ST_GeomFromText(#{geo, typeHandler=com.charge.ws.handler.jts.GeometryTypeWKTHandler, jdbcType=BLOB}))    
        
    
             INSERT INTO t_geo_wkt(id, create_time, update_time, del_flag, name, geo_type, geo)        VALUES (#{id}, now(), now(), #{delFlag}, #{name}, #{geoType}, ST_GeomCollFromText(#{geo, typeHandler=com.charge.ws.handler.jts.GeometryTypeWKTHandler, jdbcType=BLOB}))    
        
    
             UPDATE t_geo_wkt        SET geo = ST_GeomFromText(#{geo, typeHandler=com.charge.ws.handler.jts.GeometryTypeWKTHandler, jdbcType=BLOB}),            update_time = now()        WHERE id = #{id}    
    
   

Mapper接口定义

定义GeoWktMapper接口：

public interface GeoWktMapper extends BaseMapper
   
     {    List
    
      findAll();    GeoWktDO findById(@Param("id") Long id);    int insertOne(@Param("geoWktDO") GeoWktDO geoWktDO);    int insertList(@Param("geoWktDOList") List
     
       geoWktDOList);    int insertGeometryCollection(@Param("geoWktDO") GeoWktDO geoWktDO);    int updateOne(@Param("geoWktDO") GeoWktDO geoWktDO);}
     
    
   

单元测试示例

@Autowiredprivate GeoWktMapper geoWktMapper;@Testpublic void testFindAll() {    List
   
     doList = geoWktMapper.findAll();    System.out.println(doList);}@Testpublic void testGetById() {    GeoWktDO geoWktDO = geoWktMapper.findById(1L);    System.out.println(geoWktDO);}@Testpublic void testInsert1() {    GeometryFactory geometryFactory = new GeometryFactory();    Geometry point = geometryFactory.createPoint(new Coordinate(108.939645, 34.343205));    GeoWktDO saveDO = new GeoWktDO();    saveDO.setDelFlag(CommonConstants.DELETE_FLAG_NORMAL);    saveDO.setName("点");    saveDO.setGeoType("1");    saveDO.setGeo(point);    geoWktMapper.insert(saveDO);}// ... 其他测试用例
   

注意事项

通过以上配置和实现，可以在Mysql数据库中高效存储和操作各种几何数据。