介绍
由于试用了JTS Java工具 的精度位置,可以读取数据二进制精度信息,但是生成的数据不能带SRID信息,默认是0,导致插入数据库报错
ERROR: Geometry SRID (0) does not match column SRID (4326)
现在换成 GeoLatte 工具来进行操作
项目依赖
<dependency>
<groupId>org.geolatte</groupId>
<artifactId>geolatte-geom</artifactId>
<version>1.8.2</version>
</dependency>
<dependency>
<groupId>org.geolatte</groupId>
<artifactId>geolatte-geojson</artifactId>
<version>1.8.2</version>
</dependency>
创建测试类
这个测试类,包含基本操作
package util;
import com.zht.redistopost.ProductDataApplication;
import org.geolatte.geom.codec.Wkb;
import org.geolatte.geom.codec.Wkt;
import org.geolatte.geom.crs.CoordinateReferenceSystem;
import org.geolatte.geom.crs.LinearUnit;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import org.geolatte.geom.*;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import static org.geolatte.geom.builder.DSL.*;
import static org.geolatte.geom.crs.CoordinateReferenceSystems.WGS84;
@ExtendWith(SpringExtension.class)
@SpringBootTest(classes = {ProductDataApplication.class})
public class GeolatteGeometryTest {
/**
* 日志
*/
private final static Logger LOGGER = LoggerFactory.getLogger(GeolatteGeometryTest.class);
/**
* 数据库源
*/
@Autowired
private DataSource dataSource;
/**
* 创建地理位置信息
*/
@Test
public void createGeometry2D() {
// 2D 点
Point<G2D> pnt2D = point(WGS84, g(4.33, 53.21));
// 2D 线
LineString<G2D> lstr2D = linestring(WGS84, g(4.43, 53.21), g(4.44, 53.20), g(4.45, 53.19));
// 2D 面
Polygon<G2D> pgn2D = polygon(WGS84, ring(g(4.43, 53.21), g(4.44, 53.22), g(4.43, 53.21)));
// 2D 多点
MultiPoint<G2D> multiPoint2D = multipoint(point(WGS84, g(4.33, 53.21)), point(WGS84, g(4.33, 53.21)), point(WGS84, g(4.33, 53.21)), point(WGS84, g(4.33, 53.21)));
// 2D 多线
MultiLineString<G2D> multiLineString2D = multilinestring(linestring(WGS84, g(4.43, 53.21), g(4.44, 53.20), g(4.45, 53.19)), linestring(WGS84, g(4.43, 53.21), g(4.44, 53.20), g(4.45, 53.19)), linestring(WGS84, g(4.43, 53.21), g(4.44, 53.20), g(4.45, 53.19)), linestring(WGS84, g(4.43, 53.21), g(4.44, 53.20), g(4.45, 53.19)));
// 2D 多面
MultiPolygon<G2D> multiPolygon2D = multipolygon(polygon(WGS84, ring(g(4.43, 53.21), g(4.44, 53.22), g(4.43, 53.21))), polygon(WGS84, ring(g(4.43, 53.21), g(4.44, 53.22), g(4.43, 53.21))), polygon(WGS84, ring(g(4.43, 53.21), g(4.44, 53.22), g(4.43, 53.21))), polygon(WGS84, ring(g(4.43, 53.21), g(4.44, 53.22), g(4.43, 53.21))), polygon(WGS84, ring(g(4.43, 53.21), g(4.44, 53.22), g(4.43, 53.21))), polygon(WGS84, ring(g(4.43, 53.21), g(4.44, 53.22), g(4.43, 53.21))));
}
/**
* 创建3D图形
*/
@Test
public void createGeometry3D() {
// 3D转换
CoordinateReferenceSystem<G3D> wgs84E = WGS84.addVerticalSystem(LinearUnit.METER, G3D.class);
// 3D 点
Point<G3D> point3D = point(wgs84E, g(4.33, 53.21, 350));
// 3D 线
LineString<G3D> lstr3D = linestring(wgs84E, g(4.43, 53.21, 53.11), g(4.44, 53.20, 53.11), g(4.45, 53.19, 53.11));
// 3D 面
Polygon<G3D> pgn3D = polygon(wgs84E, ring(g(4.43, 53.21, 53.11), g(4.44, 53.22, 53.11), g(4.43, 53.21, 53.11)));
// 3D 多点
MultiPoint<G3D> multiPoint3D = multipoint(point(wgs84E, g(4.33, 53.21, 350)), point(wgs84E, g(4.33, 53.21, 350)), point(wgs84E, g(4.33, 53.21, 350)), point(wgs84E, g(4.33, 53.21, 350)));
// 3D 多线
MultiLineString<G3D> multiLineString3D = multilinestring(linestring(wgs84E, g(4.43, 53.21, 53.11), g(4.44, 53.20, 53.11), g(4.45, 53.19, 53.11)), linestring(wgs84E, g(4.43, 53.21, 53.11), g(4.44, 53.20, 53.11), g(4.45, 53.19, 53.11)), linestring(wgs84E, g(4.43, 53.21, 53.11), g(4.44, 53.20, 53.11), g(4.45, 53.19, 53.11)), linestring(wgs84E, g(4.43, 53.21, 53.11), g(4.44, 53.20, 53.11), g(4.45, 53.19, 53.11)));
// 3D 多面
MultiPolygon<G3D> multiPolygon2D = multipolygon(polygon(wgs84E, ring(g(4.43, 53.21, 53.11), g(4.44, 53.22, 53.11), g(4.43, 53.21, 53.11))), polygon(wgs84E, ring(g(4.43, 53.21, 53.11), g(4.44, 53.22, 53.11), g(4.43, 53.21, 53.11))), polygon(wgs84E, ring(g(4.43, 53.21, 53.11), g(4.44, 53.22, 53.11), g(4.43, 53.21, 53.11))), polygon(wgs84E, ring(g(4.43, 53.21, 53.11), g(4.44, 53.22, 53.11), g(4.43, 53.21, 53.11))), polygon(wgs84E, ring(g(4.43, 53.21, 53.11), g(4.44, 53.22, 53.11), g(4.43, 53.21, 53.11))), polygon(wgs84E, ring(g(4.43, 53.21, 53.11), g(4.44, 53.22, 53.11), g(4.43, 53.21, 53.11))));
}
/**
* 生成WKT数据
*/
@Test
public void WKT() {
CoordinateReferenceSystem<G3D> wgs84E = WGS84.addVerticalSystem(LinearUnit.METER, G3D.class);
Point<G2D> pnt2D = point(WGS84, g(4.33, 53.21));
Point<G3D> point3D = point(wgs84E, g(4.33, 53.21, 350));
String wkt2D = Wkt.toWkt(pnt2D);
LOGGER.info("wkt2D: " + wkt2D);
// Wkt.Dialect 对应数据库的方言
String wkt3D = Wkt.toWkt(point3D, Wkt.Dialect.POSTGIS_EWKT_1);
LOGGER.info("wkt3D: " + wkt3D);
}
/**
* 生成WKB数据
*/
@Test
public void WKB() {
CoordinateReferenceSystem<G3D> wgs84E = WGS84.addVerticalSystem(LinearUnit.METER, G3D.class);
Point<G2D> pnt2D = point(WGS84, g(4.33, 53.21));
Point<G3D> point3D = point(wgs84E, g(4.33, 53.21, 350));
ByteBuffer byteBuffer2D = Wkb.toWkb(pnt2D, Wkb.Dialect.POSTGIS_EWKB_1);
LOGGER.info("byteBuffer2D: " + byteBuffer2D);
ByteBuffer byteBuffer3D = Wkb.toWkb(point3D, Wkb.Dialect.POSTGIS_EWKB_1);
LOGGER.info("byteBuffer3D: " + byteBuffer3D);
}
/**
* 通过JDBC 读取精度信息
*/
@Test
public void jdbcReadData() {
String sql = "select vehicle_location as vehicle_location from tb_vehicle_step where id = ?::uuid";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "5fbbffd7-9b3d-4acc-863e-efe8327068a0");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String vehicle_location = resultSet.getString("vehicle_location");
LOGGER.info("数据库取到的数据:" + vehicle_location);
// 转换数据成为对象
Geometry<?> geometry = Wkb.fromWkb(ByteBuffer.from(vehicle_location), Wkb.Dialect.POSTGIS_EWKB_1);
LOGGER.info("精度信息:" + Wkt.toWkt(geometry));
}
} catch (SQLException e) {
LOGGER.error("数据库连接对象生成异常", e);
}
}
/**
* 通过JDBC 写入精度信息
*/
@Test
public void jdbcWriteData() {
String sql = "insert into tb_vehicle_step( machno, c_h_eading, v_h_eading, vehicle_location, tm_collect, tm_receive ) values ( 'H348', 119.910, 119.910, ?, to_timestamp(1636037065200::double precision / 1000), to_timestamp(1636037073758::double precision / 1000));";
Connection connection = null;
PreparedStatement preparedStatement = null;
Point<G2D> pnt2D = point(WGS84, g(4.33, 53.21));
ByteBuffer byteBuffer = Wkb.toWkb(pnt2D, ByteOrder.XDR, Wkb.Dialect.POSTGIS_EWKB_1);
try {
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, byteBuffer.toString());
boolean bol = preparedStatement.execute();
if (bol) {
LOGGER.info("执行成功");
} else {
LOGGER.info("执行失败");
}
} catch (SQLException e) {
LOGGER.error("数据库连接对象生成异常", e);
} catch (Exception e) {
LOGGER.error("方法执行异常", e);
}
}
}
Mybatis 转换类
package com.zht.redistopost.typehandler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.geolatte.geom.ByteBuffer;
import org.geolatte.geom.ByteOrder;
import org.geolatte.geom.Geometry;
import org.geolatte.geom.codec.Wkb;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author Lihan
* @since 2021年11月15日 10:48:55
*/
@MappedTypes(value = {Geometry.class})
public class GeometryTypeHandler extends BaseTypeHandler<Geometry<?>> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Geometry geometry, JdbcType jdbcType) throws SQLException {
ByteBuffer byteBuffer = Wkb.toWkb(geometry, ByteOrder.XDR, Wkb.Dialect.POSTGIS_EWKB_1);
preparedStatement.setString(i, byteBuffer.toString());
}
@Override
public Geometry<?> getNullableResult(ResultSet resultSet, String s) throws SQLException {
String value = resultSet.getString(s);
return Wkb.fromWkb(ByteBuffer.from(value), Wkb.Dialect.POSTGIS_EWKB_1);
}
@Override
public Geometry<?> getNullableResult(ResultSet resultSet, int i) throws SQLException {
return null;
}
@Override
public Geometry<?> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
return null;
}
}
总结
GeoLatte
项目Git 地址: https://github.com/GeoLatte/