Mybatis-plus读取(GeoJson)和保存Postgis geography数据
在入库和查询中需要自动的让geography的类型和实体类中的类型映射。
实体类中接收参数是String类型(geoJson)
PostGis中的geography类型是十六进制的扩展WKB类型(EWKB),
虽然Postgis数据库中提供类类型转换函数,能转换各种类型:postgis常用命令_yaoct的博客-CSDN博客
但是基于mybatis框架查询时,就需要用java代码来转换。初步方案时mybatis中的类型转换器。
先引入java处理GIS的库,这里用的是Geotools库。
1、geotools的maven引入
<properties>
<geotools.version>20.0</geotools.version>
</properties>
<dependencies>
<!-- geotools相关jar包 -->
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-geojson</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-swing</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-jdbc</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools.jdbc</groupId>
<artifactId>gt-jdbc-postgis</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-epsg-hsql</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-shapefile</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-api</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-data</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-referencing</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>net.postgis</groupId>
<artifactId>postgis-jdbc</artifactId>
<version>2.5.0</version>
</dependency>
</dependencies>
<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>
<repository>
<id>osgeo-snapshot</id>
<name>OSGeo Snapshot Repository</name>
<url>https://repo.osgeo.org/repository/snapshot/</url>
<snapshots><enabled>true</enabled></snapshots>
<releases><enabled>false</enabled></releases>
</repository>
</repositories>
2、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);
3、mybatis-plus类型转换器
package org.jeecg.oyz.modules.ost.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.jeecg.oyz.common.config.MyGeographyTypeHandler;
import java.io.Serializable;
//一标三识_单体化表
@TableName(value = "oyz_ost_monomer",autoResultMap = true)
@Data
public class Monomer implements Serializable {
@TableId(value = "id",type = IdType.AUTO)
@ApiModelProperty(value = "主键ID")
private Integer id;
@ApiModelProperty(value = "名称")
private String name;
@ApiModelProperty(value = "酒店(OST_HOTEL)、住宅房屋(OST_HOME)、党政机关(OST_PARTY)、小区楼宇(OST_BUILDING)、店铺(OST_SHOP)")
private String type;
//楼层高
@ApiModelProperty(value = "楼层高")
private Double floorHeig;
@ApiModelProperty(value = "基础高")
private Double baseHeigh;
@ApiModelProperty(value = "顶高")
private Double topHeight;
@ApiModelProperty(value = "地理数据")
@TableField(exist = false)
private String geometry;
@ApiModelProperty(value = "地理数据")
@TableField(typeHandler = MyGeographyTypeHandler.class)
private String geog;
@ApiModelProperty(value = "颜色")
private String color;
@ApiModelProperty(value = "小区名称")
private String communityName;
@ApiModelProperty(value = "楼号")
private String buildingNo;
@ApiModelProperty(value = "单元")
private String unit;
@ApiModelProperty(value = "小区名称")
private String doorNo;
@ApiModelProperty(value = "楼层")
private String floorName;
@ApiModelProperty(value = "相机坐标")
private String viewingAngle;
}
自定义GeoJson数据对象:
package org.jeecg.oyz.common.entity;
import com.alibaba.fastjson.JSONObject;
import java.util.List;
/**
* 自定义geojson对象
*/
public class MyGeoJson {
private String type;
private List features;
private JSONObject crs;
public MyGeoJson() {
this.type = "FeatureCollection";
this.crs = new JSONObject();
this.crs.put("type","name");
JSONObject properties = new JSONObject();
properties.put("name","EPSG:4326");
this.crs.put("properties",properties);
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public List getFeatures() {
return features;
}
public void setFeatures(List features) {
this.features = features;
}
public JSONObject getCrs() {
return crs;
}
public void setCrs(JSONObject crs) {
this.crs = crs;
}
//内部类:Feature对象
public static class Feature{
private String id;
private String type;
private JSONObject properties;
private JSONObject geometry;
public Feature() {
this.type = "Feature";
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public JSONObject getProperties() {
return properties;
}
public void setProperties(JSONObject properties) {
this.properties = properties;
}
public JSONObject getGeometry() {
return geometry;
}
public void setGeometry(JSONObject geometry) {
this.geometry = geometry;
}
}
}
自定义类型转换器代码:
package org.jeecg.oyz.common.config;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.geotools.data.postgis.WKBReader;
import org.geotools.geojson.geom.GeometryJSON;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.ParseException;
import org.postgis.PGgeography;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@MappedTypes({String.class})
public class MyGeographyTypeHandler extends BaseTypeHandler<String> {
//插入数据,转换,geoJson2EWKB
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
PGgeography pGgeography = new PGgeography(parameter);
ps.setObject(i, pGgeography);
}
@Override
public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
PGgeography pGgeography = new PGgeography(rs.getString(columnName));
if (pGgeography == null) {
return null;
}
return pGgeography.toString();
}
@Override
public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
PGgeography pGgeography = new PGgeography(rs.getString(columnIndex));
if (pGgeography == null) {
return null;
}
return pGgeography.toString();
}
@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
PGgeography pGgeography = new PGgeography(cs.getString(columnIndex));
if (pGgeography == null) {
return null;
}
return pGgeography.toString();
}
//取出数据转换,WKB->Geojson
@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;
}
// 设置保留15位小数,否则GeometryJSON默认保留4位小数
GeometryJSON geometryJson = new GeometryJSON(16);
return geometryJson.toString(geometry);
}
}
4、GeoJson测试数据
{
"type":"FeatureCollection",
"features":[
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.9738866671596,
38.24802650187823
],
[
101.97516074815859,
38.24760957596093
],
[
101.97525587066582,
38.24777783863527
],
[
101.97402119122222,
38.248244352806516
],
[
101.9738866671596,
38.24802650187823
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"21",
"base_height":"1959.71",
"top_height":"1980.71",
"address":"1",
"color":"#0099ff",
"id":1
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97516774566134,
38.24756192366929
],
[
101.97581658589463,
38.24734488944303
],
[
101.97586713946525,
38.247495261468295
],
[
101.97524052942822,
38.24769933767308
],
[
101.97516774566134,
38.24756192366929
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"20",
"base_height":"1959.21",
"top_height":"1979.21",
"address":"2",
"color":"#0099ff",
"id":2
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97408539624891,
38.24834258113536
],
[
101.97521113714267,
38.247963713081
],
[
101.97528940606377,
38.24810578159661
],
[
101.97417503681662,
38.248524362321845
],
[
101.97408539624891,
38.24834258113536
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"21.7",
"base_height":"1959.01",
"top_height":"1980.71",
"address":"3",
"color":"#0099ff",
"id":3
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97539956572204,
38.24794040388107
],
[
101.97598913456741,
38.24774703817632
],
[
101.97605022583218,
38.24787105919719
],
[
101.97548750933808,
38.248070046946246
],
[
101.97539956572204,
38.24794040388107
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"20.5",
"base_height":"1959.71",
"top_height":"1979.21",
"address":"4",
"color":"#0099ff",
"id":4
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97425025075555,
38.24863509426437
],
[
101.97535153527221,
38.2482423711667
],
[
101.97545665917151,
38.24840732335468
],
[
101.97433326065388,
38.248782588524065
],
[
101.97425025075555,
38.24863509426437
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"21.6",
"base_height":"1958.36",
"top_height":"1979.96",
"address":"5",
"color":"#0099ff",
"id":5
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97544662494822,
38.24830645258619
],
[
101.97616465300517,
38.24805769780499
],
[
101.97622897240959,
38.248178394776055
],
[
101.97548890345051,
38.24842273026219
],
[
101.97544662494822,
38.24830645258619
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"20.4",
"base_height":"1958.22",
"top_height":"1978.62",
"address":"6",
"color":"#0099ff",
"id":6
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97441138941603,
38.24895536097051
],
[
101.97555609614969,
38.2485602404651
],
[
101.9756472596321,
38.24873700704262
],
[
101.97448111772378,
38.249116048333235
],
[
101.97441138941603,
38.24895536097051
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"21.3",
"base_height":"1958.1",
"top_height":"1979.4",
"address":"7",
"color":"#0099ff",
"id":7
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97572508249701,
38.24862761373713
],
[
101.9763547169892,
38.24839146934206
],
[
101.97644057061181,
38.248555443150345
],
[
101.97580343185976,
38.24876455328588
],
[
101.97572508249701,
38.24862761373713
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"14.62",
"base_height":"1957.27",
"top_height":"1971.89",
"address":"8",
"color":"#0099ff",
"id":8
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97455020602396,
38.249271551722785
],
[
101.9752201501758,
38.24906667800479
],
[
101.97527212285912,
38.24918518811116
],
[
101.97462631248919,
38.249403013582885
],
[
101.97455020602396,
38.249271551722785
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"18.70",
"base_height":"1956.80",
"top_height":"1975.5",
"address":"9",
"color":"#0099ff",
"id":9
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97560756178844,
38.248907870439005
],
[
101.9764727439547,
38.24865343359926
],
[
101.97658723270561,
38.248871828833636
],
[
101.97574405645503,
38.24917948010292
],
[
101.97560756178844,
38.248907870439005
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"21.99",
"base_height":"1957.11",
"top_height":"1979.1",
"address":"10",
"color":"#0099ff",
"id":10
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97473448369715,
38.24953195068419
],
[
101.9754729952202,
38.249301055722896
],
[
101.97556936660517,
38.24945981465984
],
[
101.97481863748091,
38.249699636853514
],
[
101.97473448369715,
38.24953195068419
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"21.85",
"base_height":"1956.71",
"top_height":"1978.65",
"address":"11",
"color":"#0099ff",
"id":11
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.9755984423815,
38.24930334091215
],
[
101.97632040509546,
38.249065853713354
],
[
101.97640637757502,
38.24921825795419
],
[
101.97569336941301,
38.249441080080416
],
[
101.9755984423815,
38.24930334091215
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"20.7",
"base_height":"1957.2",
"top_height":"1977.9",
"address":"12",
"color":"#0099ff",
"id":12
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97489010202722,
38.24982900024954
],
[
101.97567489909527,
38.2495956955634
],
[
101.97576412774175,
38.24975163803929
],
[
101.97495345974949,
38.25002715229073
],
[
101.97489010202722,
38.24982900024954
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"22.80",
"base_height":"1955.11",
"top_height":"1979.61",
"address":"13",
"color":"#0099ff",
"id":13
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97580034565847,
38.249586902965234
],
[
101.97682340861867,
38.2492490796096
],
[
101.97693253298186,
38.24943374227494
],
[
101.9758976581547,
38.249761603608505
],
[
101.97580034565847,
38.249586902965234
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"20.75",
"base_height":"1957",
"top_height":"1975.75",
"address":"14",
"color":"#0099ff",
"id":14
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97501744258008,
38.2501171409867
],
[
101.97592532481521,
38.24982145008936
],
[
101.97606024676887,
38.25006283431469
],
[
101.97514914788083,
38.25034308087054
],
[
101.97501744258008,
38.2501171409867
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"23.4",
"base_height":"1955.83",
"top_height":"1979.23",
"address":"15",
"color":"#0099ff",
"id":15
}
},
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
101.97595220485555,
38.24981832023942
],
[
101.9769584425255,
38.24950409057583
],
[
101.977090638593,
38.249708662267096
],
[
101.97607942964663,
38.25005079494555
],
[
101.97595220485555,
38.24981832023942
]
]
]
]
},
"geometry_name":"the_geom",
"properties":{
"floor_height":"23.4",
"base_height":"1955.83",
"top_height":"1979.23",
"address":"16",
"color":"#0099ff",
"id":16
}
}
],
"totalFeatures":12,
"numberMatched":12,
"numberReturned":12,
"timeStamp":"2021-07-06T01:51:52.710Z",
"crs":{
"type":"name",
"properties":{
"name":"urn:ogc:def:crs:EPSG::404000"
}
}
}
5、读取数据库数据,返回前端geojson数据
{
"code": 200,
"message": "请求成功",
"data": {
"type": "FeatureCollection",
"features": [
{
"id": "1049",
"type": "Feature",
"properties": {
"topHeight": 1979.1,
"color": "#0099ff",
"geog": "{\"type\":\"MultiPolygon\",\"coordinates\":[[[[101.97560756178844,38.248907870439005],[101.9764727439547,38.24865343359926],[101.97658723270561,38.248871828833636],[101.97574405645503,38.24917948010292],[101.97560756178844,38.248907870439005]]]]}",
"baseHeigh": 1957.11,
"type": null,
"floorHeig": 21.99,
"unit": "3单元",
"buildingNo": "1号楼",
"name": null,
"communityName": "清河小区",
"geometry": null,
"viewingAngle": null,
"doorNo": null,
"floorName": null,
"id": 1049
},
"geometry": {
"coordinates": [
[
[
[
101.97560756178844,
38.248907870439005
],
[
101.9764727439547,
38.24865343359926
],
[
101.97658723270561,
38.248871828833636
],
[
101.97574405645503,
38.24917948010292
],
[
101.97560756178844,
38.248907870439005
]
]
]
],
"type": "MultiPolygon"
}
},
{
"id": "1048",
"type": "Feature",
"properties": {
"topHeight": 1975.5,
"color": "#0099ff",
"geog": "{\"type\":\"MultiPolygon\",\"coordinates\":[[[[101.97455020602396,38.249271551722785],[101.9752201501758,38.24906667800479],[101.97527212285912,38.24918518811116],[101.97462631248919,38.249403013582885],[101.97455020602396,38.249271551722785]]]]}",
"baseHeigh": 1956.8,
"type": null,
"floorHeig": 18.7,
"unit": "3单元",
"buildingNo": "1号楼",
"name": null,
"communityName": "清河小区",
"geometry": null,
"viewingAngle": null,
"doorNo": null,
"floorName": null,
"id": 1048
},
"geometry": {
"coordinates": [
[
[
[
101.97455020602396,
38.249271551722785
],
[
101.9752201501758,
38.24906667800479
],
[
101.97527212285912,
38.24918518811116
],
[
101.97462631248919,
38.249403013582885
],
[
101.97455020602396,
38.249271551722785
]
]
]
],
"type": "MultiPolygon"
}
},
{
"id": "1047",
"type": "Feature",
"properties": {
"topHeight": 1971.89,
"color": "#0099ff",
"geog": "{\"type\":\"MultiPolygon\",\"coordinates\":[[[[101.97572508249701,38.24862761373713],[101.9763547169892,38.24839146934206],[101.97644057061181,38.248555443150345],[101.97580343185976,38.24876455328588],[101.97572508249701,38.24862761373713]]]]}",
"baseHeigh": 1957.27,
"type": null,
"floorHeig": 14.62,
"unit": "3单元",
"buildingNo": "1号楼",
"name": null,
"communityName": "清河小区",
"geometry": null,
"viewingAngle": null,
"doorNo": null,
"floorName": null,
"id": 1047
},
"geometry": {
"coordinates": [
[
[
[
101.97572508249701,
38.24862761373713
],
[
101.9763547169892,
38.24839146934206
],
[
101.97644057061181,
38.248555443150345
],
[
101.97580343185976,
38.24876455328588
],
[
101.97572508249701,
38.24862761373713
]
]
]
],
"type": "MultiPolygon"
}
}
],
"crs": {
"type": "name",
"properties": {
"name": "EPSG:4326"
}
}
},
"success": true
}
参考:
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) - 灰信网(软件开发博客聚合)