mybatis类型转换器处理MYSQL数据库geometry类型转换
首先分析mysql的geometry的存储格式和输入输出格式，再实现mybatis的转换器转换geometry格式。mybatis的配置见。
本文的解决方案是在mybatis类型转换器处理Postgis的方案基础上更改的，使用mysql5.7+版本。
mybatis类型转换器处理PostGis数据库geometry类型转换_mybatis geometry-CSDN博客
首先需要确定在mysql中geometry类型数据的存储格式，输入输出格式。在Postgis中的geometry存储格式，输入输出格式。
1.Postgis的geometry格式与mysql的geometry格式联系
1.1postgis的geometry格式
在Postgis官方文档说明了，OGC SFA specifications这套规范定义了WKB数据只支持2D数据，不支持3D数据，不支持坐标系数据。在OGC SFA specification 1.2.1 (which aligns with the ISO 19125 standard)这个标准中定义了3D数据，但是不支持坐标数据。
EWKB作为一种WKB格式的扩展，支持3D数据格式、测量坐标数据measured coordinates（XYZM）和SRID坐标数据（M坐标为监测值，可看成第4维）。（注意EWKB的3D格式扩展方式和OGC SFA specification 1.2.1中的3D格式扩展 OGC WKB不一样）。Postgis的几个类型底层存储的是EWKB格式。Postgis输入格式支持EWKB二进制格式，或者EWKB和EWKT文本格式。输出格式为二进制EWKB或文本格式HEXEWKB。
EWKB的扩展类型标志位代表的含义（BIG_ENDIAN），分别是Z,M,SRID的标志位。本文转换默认SRID标志位存在。Well-Known Binary (WKB) | GEOS
WKT数据{"type":"Point","coordinates":[115.79504,33.867779]}对应
WKB数据0101000000C2C073EFE1F25C40EA93DC6113EF4040
EWKB数据字节0101000020E6100000C2C073EFE1F25C40EA93DC6113EF4040
WKB第一个字节01代表了字节顺序，01代表LITTLE_ENDIAN；再后面4个字节代表了几何类型，这里01000000（LITTLE_ENDIAN）代表了类型1是Point类型。EWKB与WKB在这4个字节中的前3个字节含义一样，第4个字节通过字节or操作可代表4维数据XYZM（注意这里是（LITTLE_ENDIAN情况，BIG_ENDIAN情况相反）。
EWKB又在之后的插入4个字节代表SRID坐标。
在Postgis实现org.locationtech.jts.io.WKBWriter源码中也能找出对应解析方式。
1.2mysql的geometry格式
mysql官方文档说明了mysql的geometry类型是在WKB前面加了4个字节表明SRID坐标。
WKT数据{"type":"Point","coordinates":[115.79504,33.867779]}对应
WKB数据0101000000C2C073EFE1F25C40EA93DC6113EF4040
Mysql的WKB数据字节E61000000101000000C2C073EFE1F25C40EA93DC6113EF4040
通过抓包分析可知Mysql的输出格式是Mysql扩展的WKB格式。
1.3mysql的geometry格式与postgis的geometry格式转换
如果需要Geotools包来解析mysql的WKB格式，首先需要将mysql的WKB转换EWKB。
可以看出mysql的geometry格式和postgis的geometry格式都是在WKB基础上改造的。
postgis支持3D，mysql只支持2D，所以EWKB数据类型包括Mysql扩展的WKB数据类型。通过以上分析只需要将坐标位的顺序和类型位转换，就能将EWKB和Mysql扩展的WKB互相转换。
2.1代码实现
Mysql官方文档中提供许多操作geometry类型的函数，一个简单的思路可以是调用ST_GeomFromText转换函数，将WKT转换为Mysql扩展的WKB格式。
本文实现直接向Mysql发送二进制WKB数据。
通过查询mysql官方文档发送二进制数据需要添加introducer标识符，二进制的Introducers标识符是_binary，一般情况下sql语句只能发送字符流，在_binary后面可以输入二进制流（上图中为'abc'对应的ascii，但一般ascii只能通过程序编程输入）。
2.1 java实现
(1)将Mysql扩展WKB与Postgis的EWKB格式互相转换，再调用geotools工具解析EWKB。其余大致实现类似mybatis类型转换器处理PostGis数据库geometry类型转换_mybatis geometry-CSDN博客
(2)发送二进制方法需要调用mysql提供的JDBC驱动，其提供了一些操作二进制的方法。
2.1.1 MYSQL的JDBC驱动操作二进制部分源码分析
大致思路是
(1).调用com.mysql.cj.jdbc.ClientPreparedStatement#setBlob(int, java.io.InputStream)方法，底层调用com.mysql.cj.ClientPreparedQueryBindings#setBinaryStream(int, java.io.InputStream, int)方法。将二进制流设置到bindValues数组中，bindValues数组代表数据库的字段。
(2).在mabtis调用org.apache.ibatis.executor.statement.PreparedStatementHandler#update时，再调用JDBC语句执行时java.sql.PreparedStatement#execute，底层调用com.mysql.cj.AbstractPreparedQuery#fillSendPacket(com.mysql.cj.QueryBindings<?>)方法，拼接这个二进制字段，bindVlaues包括（1）中设置的二进制字段。
进入com.mysql.cj.AbstractPreparedQuery#streamToBytes中可以看到拼接的introducer标识符_binary。
注意：二进制的字段对应的值，平时手写sql是打不出来的，只能用编码时发送。
2.1.2 二进制语句抓包分析
上图的sql是插入一条包括geometry字段的数据。这里geometry字段对应的WKT为:POLYGON((115.676422 33.913164,115.681229 33.822512,115.811691 33.889227,115.676422 33.913164))，可以看到MYSQL的JDBC驱动发送的geom2字段值是二进制数据。
2.2mybatis转换类实现
package com.zjzy.emergencyservice.mapper.emergency.typehandler;
import com.alibaba.fastjson.JSONObject;
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.geom.GeometryFactory;
import org.locationtech.jts.geom.PrecisionModel;
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.ByteArrayInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Map;
/**
* @version 1.0
* @description mysql几何类型转换
* @Author yaoct
* @create 2024/01/24 16:19
* mysql输入输出都是WKB二进制格式+前4字节坐标，
* mysql不支持三维四维Unsupported number of coordinate dimensions in function st_geomfromgeojson: Found 3, expected 2
*/
@Slf4j
public class WKB2MysqlGeoJsonTypeHandler implements TypeHandler<Map> {
/**
* 插入数据，转换,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));
} catch (IOException e) {
e.printStackTrace();
}
if(geometry==null) return;
geometry.setSRID(4326);
//POINT(115.79504 33.867779)
//{"type":"Point","coordinates":[115.79504,33.867779]}
//0101000020e6100000c2c073efe1f25c40ea93dc6113ef4040 postgis ByteOrderValues.LITTLE_ENDIAN pg数据库存储方式
//E61000000101000000C2C073EFE1F25C40EA93DC6113EF4040 mysql ByteOrderValues.LITTLE_ENDIAN mysql数据库存储方式
//E61000000101000020C2C073EFE1F25C40EA93DC6113EF4040
//0020000001000010E6405CF2E1EF73C0C24040EF1361DC93EA ByteOrderValues.BIG_ENDIAN 0x10E6 4326
//二进制表示
// WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.BIG_ENDIAN,true);
WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.LITTLE_ENDIAN,true);
byte[] write = wkbWriter.write(geometry);
String s=bytes2HEXString(write);
write=ewkb2MysqlWKB(write);//转换为mysql wkb
String s1 = WKBWriter.toHex(write);
ps.setBlob(i, new ByteArrayInputStream(write));
}
/**
* 取出数据转换,WKB->Geojson
* @param rs
* @param columnName
* @return
* @throws SQLException
*/
@Override
public Map getResult(ResultSet rs, String columnName) throws SQLException {
// String string = rs.getString(columnName);
//https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html#gis-wkb-format
//mysql中的wkb前四位是坐标
Blob blob = rs.getBlob(columnName);
if(blob==null) return null;
byte[] bytes = blob.getBytes(1, (int) blob.length());
bytes=mysqlWKB2Ewkb(bytes);//转换为mysql wkb
// String s=bytes2HEXString(bytes);
// String str="0101000020e6100000c2c073efe1f25c40ea93dc6113ef4040";
// bytes=strHex2Byte(str);
WKBReader reader = new WKBReader();
Geometry geometry = null;
try {
geometry = reader.read(bytes);
} catch (ParseException e) {
//转换失败
return null;
}
try {
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){
// //转换失败
// }
// }
// }
int srid = geometry.getSRID();
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;
}
private String bytes2HEXString(byte[] bytes) {
StringBuilder sb=new StringBuilder();
for(byte b:bytes){
String s = Integer.toHexString(Byte.toUnsignedInt(b));
if(s.length()==1)s='0'+s;
sb.append(s.toUpperCase());
}
return sb.toString();
}
static byte[] strHex2Byte(String hex){
char[] chars = hex.toCharArray();
int len=chars.length/2;
byte[] ret=new byte[len];
for(int i=0;i<len;i++){
char h=chars[i*2];
char l=chars[i*2+1];
int hv=0;
int lv=0;
if(h<='9'&&h>='0'){
hv=(int)(h-'0');
}
if(h<='f'&&h>='a'){
hv=(int)(h-'a')+10;
}
if(h<='F'&&h>='A'){
hv=(int)(h-'A')+10;
}
if(l<='9'&&l>='0'){
lv=(int)(l-'0');
}
if(l<='f'&&l>='a'){
lv=(int)(l-'a')+10;
}
if(l<='F'&&l>='A'){
lv=(int)(l-'A')+10;
}
ret[i]=(byte)(hv*16+lv);
}
return ret;
}
/**
* pg数据库ewkb二进制格式转换为mysql扩展的wkb格式，因为框架能解析ewkb {@link org.geotools.geometry.jts.WKBReader}{@link org.locationtech.jts.io.WKBReader}
* mysql扩展的wkb格式 前四个字节是坐标，后面是wkb格式，第9位为0
* ewkb格式在wkb格式第5个字节后插入4位坐标信息，
*
* Table 11.2 WKB Components Example
* Component Size Value
* Byte order 1 byte 01
* WKB type 4 bytes 01000000
* X coordinate 8 bytes 000000000000F03F
* Y coordinate 8 bytes 000000000000F0BF
* @return
*/
byte[] ewkb2MysqlWKB(byte[] ewkb){
//0101000020e6100000c2c073efe1f25c40ea93dc6113ef4040 postgis ByteOrderValues.LITTLE_ENDIAN pg数据库存储方式
//E61000000101000000C2C073EFE1F25C40EA93DC6113EF4040 mysql ByteOrderValues.LITTLE_ENDIAN mysql数据库存储方式
//0020000001000010E6405CF2E1EF73C0C24040EF1361DC93EA ByteOrderValues.BIG_ENDIAN 0x10E6 4326
byte[] ret = Arrays.copyOf(ewkb, ewkb.length);
//srid
for(int i=0;i<4;i++){
ret[i]=ewkb[i+5];
}
//Byte order WKB type
for(int i=4;i<8;i++){
ret[i]=ewkb[i-4];
}
ret[8]=0;//ewkb标识三维四维坐标的字节，wkb为0
//https://libgeos.org/specifications/wkb/#extended-wkb
//wkbZ = 0x80000000 三维
//wkbM = 0x40000000 集合
//wkbSRID = 0x20000000 坐标
//mysql不支持三维四维
return ret;
}
/**
* pg数据库ewkb二进制格式转换为mysql扩展的wkb格式，因为框架能解析ewkb
* mysql扩展的wkb格式 前四个字节是坐标，后面是wkb格式
* ewkb格式在wkb格式第5个字节后插入4位坐标信息
*
* Table 11.2 WKB Components Example
* Component Size Value
* Byte order 1 byte 01
* WKB type 4 bytes 01000000
* X coordinate 8 bytes 000000000000F03F
* Y coordinate 8 bytes 000000000000F0BF
* @return
*/
byte[] mysqlWKB2Ewkb(byte[] mysqlWkb){
//0101000020e6100000c2c073efe1f25c40ea93dc6113ef4040 postgis ByteOrderValues.LITTLE_ENDIAN pg数据库存储方式
//E61000000101000000C2C073EFE1F25C40EA93DC6113EF4040 mysql ByteOrderValues.LITTLE_ENDIAN mysql数据库存储方式
//0020000001000010E6405CF2E1EF73C0C24040EF1361DC93EA ByteOrderValues.BIG_ENDIAN 0x10E6 4326
byte[] ret = Arrays.copyOf(mysqlWkb, mysqlWkb.length);
//Byte order,WKB type
for(int i=0;i<5;i++){
ret[i]=mysqlWkb[i+4];
}
//srid
for(int i=5;i<9;i++){
ret[i]=mysqlWkb[i-5];
}
ret[4]=0x20;//ewkb是否存在坐标标识
//https://libgeos.org/specifications/wkb/#extended-wkb
//wkbZ = 0x80000000 三维
//wkbM = 0x40000000 集合
//wkbSRID = 0x20000000 坐标
//mysql不支持三维四维
return ret;
}
}
3.mysql的局限
1.不支持3维4维
很多时候2维也是够用的。
2.提供的函数功能有限
mysql提供的地理空间函数比在PostGIS的少很多，而且函数的功能不能满足开发需求。
平时用的最多的就是求地理上几何的距离。在PostGIS中提供的相关函数能求任意几何类型的距离，PostGIS还提供了一种geography类型方便进行地理信息计算的操作。
PostGIS中geometry与geography的区别_postgre geography-CSDN博客
mysql提供的ST_Distance_Sphere()函数只支持求点到点的距离计算。相关的ST_Buffer()函数也不能按照地理上的距离产生圆。在8.0.26版本之后ST_Buffer函数才支持按地理上的单位产生圆。MySQL :: MySQL 8.0 Reference Manual :: 14.16.8 Spatial Operator Functions
所以如果一定要用mysql处理几何数据的话，建议8.0.26之后的版本。ST_Buffer和ST_Within()结合使用能在一定程度上替代ST_Distance_Sphere()函数。
当前mysql最新版本是8.0.36。
该版本的WKT坐标系轴与之前早期版本默认相反，需要指定参数如：
select ST_GeomFromText('POINT(115.79504 33.867779)',4326,'axis-order=long-lat')
MySQL :: MySQL 8.0 Reference Manual :: 14.16.6 Geometry Format Conversion Functions
8.0.36函数调用参考：
SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT(33.867779 115.79504)',4326), 10));
SELECT ST_AsGeoJSON(ST_Buffer(ST_GeomFromText('POINT(33.867779 115.79504)',4326), 10));
SELECT ST_AsGeoJSON(ST_Buffer(ST_SRID(Point(115.79504, 33.867779),4326), 10));
select ST_SRID(Point(115.79504, 33.867779),4326)
select ST_AsWKT(ST_SRID(Point(115.79504, 33.867779),4326))
select ST_GeomFromText('POINT(33.867779 115.79504)',4326)
select ST_GeomFromText('POINT(115.79504 33.867779)',4326,'axis-order=long-lat')如需在java代码中求WGS84坐标距离（单位米），需要用到geotools工具类。
有2个思路，
1.一个是先转换WGS84坐标系到其他单位为米的坐标系，再调用DistanceOp.distance()方法球距离
2.先调用DistanceOp.nearestPoints(g2, g1)求最近的点，再调用GeodeticCalculator.getOrthodromicDistance()求距离
java - Geotools: bounding box for a buffer in wgs84 - Stack Overflow
参考
https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary
http://cse.naro.affrc.go.jp/yellow/pgisman/ZMSgeoms.html
https://github.com/gravitystorm/postgis/blob/master/doc/ZMSgeoms.txt
https://libgeos.org/specifications/wkb/
https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
Mysql中JDBC的三种查询(普通、流式、游标)详解_Mysql_脚本之家
Java.sql 和 Javax.sql 之间的区别 - 极道
jdbc驱动加载过程_com.mysql.jdbc.driver的加载过程-CSDN博客
SPI(Service Provider Interface)详解-CSDN博客
Mysql中JDBC的三种查询(普通、流式、游标)详解_Mysql_脚本之家
Java.sql 和 Javax.sql 之间的区别 - 极道
MySQL :: MySQL 5.7 Reference Manual :: 10.3.8 Character Set Introducers
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
12
0- 0
已为社区贡献5条内容
所有评论(0)