mybatis优雅操作Geomerty
2024-09-13 13:24:03 # gis #gis #mybatis #postgis

小背景

在postgis中存在一些空间先关的函数,比如ST_AsEWKT,ST_AsGeoJSON 函数可以把空间数据转为我们常用的wkt,geojson格式等,但是这种方式都需要修改xml

比如这样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<!-- 更新项目的 geom 字段 -->  
<update id="updateProjectGeom" prameterType="com.xs.model.Project"> 
        UPDATE project  
        SET geom = ST_GeomFromEWKT(#{geom}),  
            name = #{name}  
        WHERE id = #{id}  
    </update>  
    
     <select id="selectProjectById" parameterType="int" resultType="com.xs.model.Project">  
        SELECT   
            id,  
            name,  
            ST_AsEWKT(geom) AS geom   
        FROM   
            project  
        WHERE   
            id = #{id}  
    </select>

这样就回到传统的ssm结构,比如查询的时候就需要去写一个查询,通用一点的在更新或查询的时候使用动态的表名,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

<update id="updateProjectGeom" prameterType="com.xs.model.Project"> 
        UPDATE ${tableName}  
        SET geom = ST_GeomFromEWKT(#{geom}) 
        WHERE id = #{id}  
    </update> 

 <select id="selectProjectById" parameterType="int" resultType="java.lang.String">  
        SELECT    
            ST_AsEWKT(geom) AS geom   
        FROM   
            ${tableName}   
        WHERE   
            id = #{id}  
    </select>

上边的方法也不是很优雅,pg数据支持使用PGGeometry的Handler,可以解决查询的问题,但是有一定的问题,java后端需要使用geotools,需要转为jts的对象才方面我们的操作,所以我们可以通过handle准转为我们需要的jts的Geometry类
下边展示

  1. 准备转换工具类,由于pg支持PgGeometry,可以直接转wkt格式,那么我可以基于这个转geojson,保存的时候数据库存储的是十六进制的字节数组,也需要wkb的转换,准备工具类
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109

    import org.geotools.geojson.geom.GeometryJSON;
    import org.locationtech.jts.geom.Geometry;
    import org.locationtech.jts.io.*;

    import java.io.IOException;
    import java.io.Reader;
    import java.io.StringReader;
    import java.io.UnsupportedEncodingException;
    import java.nio.charset.StandardCharsets;

    public class GeometryDataCoverUtils {

        /**
         * geojson转geometry
         *
         * @param data geometry 的json格式
         * @return
         * @throws ParseException
         * @throws IOException
         */
        public static Geometry geoJsonToGeometry(String data) throws IOException {
            Reader reader = new StringReader(data);
            GeometryJSON gjson = new GeometryJSON();
            return gjson.read(reader);
        }

        /**
         * geometry转geojson
         *
         * @param geometry geometry
         * @return 返回json的数据格式
         * @throws UnsupportedEncodingException
         */
        public static String geometryToGeoJson(Geometry geometry) {
            GeometryJSON g = new GeometryJSON(15);
            return g.toString(geometry);
        }

        public static  Geometry wktToGeometry(String data) throws ParseException {
            WKTReader reader = new WKTReader();
            return reader.read(data);
        }

        public static String geometryToWkt(Geometry geometry) {
            WKTWriter writer = new WKTWriter();
            return writer.write(geometry);
        }

        /**
         * wkt转geojson
         *
         * @param data wkt
         * @return
         * @throws ParseException
         */
        public static String wktToGeoJson(String data) throws ParseException {
            return geometryToGeoJson(wktToGeometry(data));
        }

        /**
         * geojson转wkt
         *
         * @param data geojson
         * @return
         * @throws IOException
         */
        public static String geoJsonToWkt(String data) throws IOException {
            return geometryToWkt(geoJsonToGeometry(data));
        }



        /**
         * wkb转geometry
         *
         * @param data wkb
         * @
         */
        public static Geometry wkbToGeometry(String data) throws ParseException {
            byte[] bytes = WKBReader.hexToBytes(data);
            org.locationtech.jts.io.WKBReader reader = new org.locationtech.jts.io.WKBReader();
            return reader.read(bytes);
        }

        /**
         * geometry转wkb
         * @param geometry
         * @return
         * @throws UnsupportedEncodingException
         */
        public static String geometryToWkb(Geometry geometry) throws UnsupportedEncodingException {
            WKBWriter writer = new WKBWriter();
            byte[] write = writer.write(geometry);
            return WKBWriter.toHex(write);
        }
        /**
         * geometry转wkb数组
         * @param geometry
         * @return
         * @throws UnsupportedEncodingException
         */
        public static byte[]  geometryToWkbArr(Geometry geometry) {
            WKBWriter writer = new WKBWriter();
            return writer.write(geometry);
        }

    }

  2. 先实现WKt格式的转换
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes({String.class})
public class WktTypeHandler extends BaseTypeHandler<String> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {

        WKTReader wktReader = new WKTReader();
        // 将 Geometry 对象转换为 PGgeometry 并设置 SRID 为 4326
        PGgeometry pGgeometry = null;
        try {
            pGgeometry = new PGgeometry(wktReader.read(parameter).toText());
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        pGgeometry.getGeometry().setSrid(4326);
        ps.setObject(i, pGgeometry);

    }



    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String string = rs.getString(columnName);
        return getResult(string);
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String string = rs.getString(columnIndex);
        return getResult(string);
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String string = cs.getString(columnIndex);
        return getResult(string);
    }


    //wkb
    private String getResult(String string) throws SQLException {
        return getWkt(string);
    }

    private String getWkt(String string) throws SQLException {
        if (StrUtil.isBlank(string)) {
            return null;
        }
        PGgeometry pGgeometry= new PGgeometry(string);
        String s = pGgeometry.toString();
        return s.replace("SRID=4326;", "");//这里我的数据全是4326,如果不是使用正则替换
    }
}

还有geojson格式的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
@MappedTypes({String.class})
@MappedJdbcTypes({JdbcType.OTHER})
@Slf4j
public class GeoJsonHandler extends BaseTypeHandler<String> {


    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        try {
            String wkt = GeometryDataCoverUtils.geoJsonToWkt(parameter);
            WKTReader wktReader = new WKTReader();
            PGgeometry pGgeometry = new PGgeometry(wktReader.read(wkt).toText());
            pGgeometry.getGeometry().setSrid(4326);
            ps.setObject(i, pGgeometry);
        } catch (IOException | ParseException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String string = rs.getString(columnName);
        return getResult(string);
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String string = rs.getString(columnIndex);
        return getResult(string);
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String string = cs.getString(columnIndex);
        return getResult(string);
    }


    //wkb
    private String getResult(String string) throws SQLException {
        return getWkt(string);
    }

    private String getWkt(String string) throws SQLException {
        if (StrUtil.isBlank(string)) {
            return null;
        }
        PGgeometry pGgeometry= new PGgeometry(string);
        String s = pGgeometry.toString();
        String replace = s.replace("SRID=4326;", "");
        try {
            return GeometryDataCoverUtils.wktToGeoJson(replace);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }
}
  1. 如果我们需要转为Jts的Geometry
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes({Geometry.class})
public class WktGeometryTypeHandler extends BaseTypeHandler<Geometry> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Geometry parameter, JdbcType jdbcType) throws SQLException {

        byte[] bytes = GeometryDataCoverUtils.geometryToWkbArr(parameter);
        ps.setBytes(i, bytes);

    }

    @Override
    public Geometry getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String string = rs.getString(columnName);
        return getGgeometry(string);
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String string = rs.getString(columnIndex);
        return getGgeometry(string);
    }

    @Override
    public Geometry getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String string = cs.getString(columnIndex);
        return getGgeometry(string);
    }

    private Geometry getGgeometry(String string) {
        if (StrUtil.isBlank(string)) {
            return null;
        }
        try {
            return GeometryDataCoverUtils.wkbToGeometry(string);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }
}
  1. 在实体上做修改,
1
2
3
4
5
6
7
8
9
10
11
@Data
public class Project {  
    private Integer id;  
    private String name;  
   	@TableField(typeHandler = WktTypeHandler.class, value = "geom")
   	//@TableField(typeHandler = WktTypeHandler.class, value = "geom")
    private String geom; 
    //或者
    //@TableField(typeHandler = WktGeometryTypeHandler.class, value = "geom")
    //private Geomerty geom; 
}

  1. 修改mybatis的配置

    1
    mybatis-plus.type-handlers-package=com.xs.handler #修改包名

    就可以实现查询,保存的时候自动转换,不用在写xml了

    总结

    1. mybatis配置需要注入handler
上一页
2024-09-13 13:24:03 # gis #gis #mybatis #postgis
下一页