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) - 灰信网（软件开发博客聚合）
