pg几何字段的使用

ゞ浴缸里的玫瑰 2022-10-17 10:54 501阅读 0赞

对于集合类型的数据  
1，pg建表的时候用 CREATE EXTENSION postgis;  使用几何功能  
2，对于这种字段保存（insert）的之前要设置类型转化，将wkt转化成 PGgeometry保存  
注意是insert运行之前，不是set这个属性值的时候将wkt转化成PGgeometry  
3，对于这种字段在查询返回的时候要转回wkt ，pg也有函数ST\_GeomFromText将wkt转化成PGgeometry  
4，作为查询条件，传入wkt用ST\_GeomFromText函数转化成PGgeometry匹配

查询示例：

String startTime = packageDto.getStartTime();  
        String startData = DateUtil.timeStamp2Date(startTime, DatePattern.NORM\_MONTH\_PATTERN);  
        Point point = new Point();  
        point.setSrid(4326);  
        point.setX(packageDto.getLongitude());  
        point.setY(packageDto.getLatitude());  
        String geom = point.toString();  
        //查询当前数据所属的省份信息  
        RmsRegion region = regionMapper.queryRegionByPoint(geom, regionLevel);

RmsRegion queryRegionByPoint(@Param("point") String point, @Param("regionLevel") Integer regionLevel);  
  <select id="queryRegionByPoint" resultType="com.ecarx.collect.entity.RmsRegion">  
        SELECT \* FROM rms\_region WHERE region\_level = \#\{regionLevel\} and ST\_Contains(geom, ST\_GeomFromText(\#\{point\}))  
    </select>

@Data  
@Builder  
@NoArgsConstructor  
@AllArgsConstructor  
@TableName(value = "r\_region", autoResultMap = true)  
public class R implements Serializable \{  
    private static final long serialVersionUID = -2178894779068406929L;

@TableId(value = "id", type = IdType.AUTO)  
    private Integer id;

private String regionCode;

private String name;

private String localName;

private String lastLevelName;

private Integer regionLevel;

//也可在resultmap中设置转化  
    @ApiModelProperty("空间坐标")  
    @TableField(value = "geom", typeHandler = GeometryTypeHandler.class)  
    private String geom;  
\}

package com.x.collect.handle;

import com.x.collect.enums.ResultCode;  
import com.x.collect.exception.BizException;  
import org.apache.ibatis.type.BaseTypeHandler;  
import org.apache.ibatis.type.JdbcType;  
import org.apache.ibatis.type.MappedJdbcTypes;  
import org.apache.ibatis.type.MappedTypes;  
import org.postgis.LineString;  
import org.postgis.MultiPolygon;  
import org.postgis.PGgeometry;  
import org.postgis.Polygon;

import java.sql.CallableStatement;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;

/\*\*  
 \* @author wangGang  
 \* @create 2020-11-03 18:31  
 \*/  
@MappedJdbcTypes(JdbcType.OTHER)  
@MappedTypes(\{String.class\})  
public class GeometryTypeHandler extends BaseTypeHandler<String> \{  
    private final static String POLYGON = "POLYGON";  
    private final static String MULTIPOLYGON = "MULTIPOLYGON";  
    private final static String LINESTRING = "LINESTRING";

/\*\*  
     \*  
     \* @param ps  
     \* @param i  
     \* @param parameter  
     \* @param jdbcType  
     \* @throws SQLException  
     \* @return void  
     \*/  
    @Override  
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException \{  
        if (parameter.startsWith(POLYGON)) \{  
            PGgeometry pGgeometry = new PGgeometry();  
            Polygon polygon = new Polygon(parameter);  
            polygon.setSrid(4326);  
            pGgeometry.setGeometry(polygon);  
            ps.setObject(i, pGgeometry);  
        \} else if (parameter.startsWith(MULTIPOLYGON)) \{  
            PGgeometry pGgeometry = new PGgeometry();  
            MultiPolygon multiPolygon = new MultiPolygon(parameter);  
            multiPolygon.setSrid(4326);  
            pGgeometry.setGeometry(multiPolygon);  
            ps.setObject(i, pGgeometry);  
        \} else if (parameter.startsWith(LINESTRING)) \{  
            PGgeometry pGgeometry = new PGgeometry();  
            LineString lineString = new LineString(parameter);  
            lineString.setSrid(4326);  
            pGgeometry.setGeometry(lineString);  
            ps.setObject(i, pGgeometry);  
        \} else \{  
            throw new BizException(ResultCode.INVALID\_FORMAT\_ERROR);  
        \}  
    \}

/\*\*  
     \*  
     \* @param rs  
     \* @param columnName  
     \* @throws SQLException  
     \* @return String  
     \*/  
    @Override  
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException \{  
        String geo = rs.getString(columnName);  
        PGgeometry pGgeometry = new PGgeometry(geo);  
        if (pGgeometry.getGeometry() instanceof LineString) \{  
            LineString lineString = (LineString) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            lineString.outerWKT(sb);  
            return sb.toString();  
        \} else if (pGgeometry.getGeometry() instanceof Polygon) \{  
            Polygon polygon = (Polygon) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            polygon.outerWKT(sb);  
            return sb.toString();  
        \} else if (pGgeometry.getGeometry() instanceof MultiPolygon) \{  
            MultiPolygon multiPolygon = (MultiPolygon) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            multiPolygon.outerWKT(sb);  
            return sb.toString();  
        \} else \{  
            throw new BizException(ResultCode.INTERNAL\_ERROR);  
        \}  
    \}

/\*\*  
     \*  
     \* @param rs  
     \* @param columnIndex  
     \* @throws SQLException  
     \* @return String  
     \*/  
    @Override  
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException \{  
        String geo = rs.getString(columnIndex);  
        PGgeometry pGgeometry = new PGgeometry(geo);  
        if (pGgeometry.getGeometry() instanceof LineString) \{  
            LineString lineString = (LineString) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            lineString.outerWKT(sb);  
            return sb.toString();  
        \} else if (pGgeometry.getGeometry() instanceof Polygon) \{  
            Polygon polygon = (Polygon) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            polygon.outerWKT(sb);  
            return sb.toString();  
        \} else if (pGgeometry.getGeometry() instanceof MultiPolygon) \{  
            MultiPolygon multiPolygon = (MultiPolygon) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            multiPolygon.outerWKT(sb);  
            return sb.toString();  
        \} else \{  
            throw new BizException(ResultCode.INTERNAL\_ERROR);  
        \}  
    \}

/\*\*  
     \*  
     \* @param cs  
     \* @param columnIndex  
     \* @throws SQLException  
     \* @return String  
     \*/  
    @Override  
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException \{  
        String geo = cs.getString(columnIndex);  
        PGgeometry pGgeometry = new PGgeometry(geo);  
        if (pGgeometry.getGeometry() instanceof LineString) \{  
            LineString lineString = (LineString) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            lineString.outerWKT(sb);  
            return sb.toString();  
        \} else if (pGgeometry.getGeometry() instanceof Polygon) \{  
            Polygon polygon = (Polygon) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            polygon.outerWKT(sb);  
            return sb.toString();  
        \} else if (pGgeometry.getGeometry() instanceof MultiPolygon) \{  
            MultiPolygon multiPolygon = (MultiPolygon) pGgeometry.getGeometry();  
            StringBuffer sb = new StringBuffer();  
            multiPolygon.outerWKT(sb);  
            return sb.toString();  
        \} else \{  
            throw new BizException(ResultCode.INTERNAL\_ERROR);  
        \}  
    \}  
\}

转wkt方式

1，使用集合类型的tostring  （ewkt）

Point extends Geometry

Point point = new Point();
    point.setSrid(4326);
    point.setX(packageDto.getLongitude());
    point.setY(packageDto.getLatitude());
    String geom = point.toString();
    //查询当前数据所属的省份信息
    RmsRegion region = regionMapper.queryRegionByPoint(geom, regionLevel);

1.1 真正的wkt

Point lineString = (Point) pGgeometry.getGeometry();
    StringBuffer sb = new StringBuffer();
    lineString.outerWKT(sb);

2,用工具类

package com.x.collect.util;

import org.geotools.geometry.jts.JTSFactoryFinder;  
import org.locationtech.jts.geom.Coordinate;  
import org.locationtech.jts.geom.Geometry;  
import org.locationtech.jts.geom.GeometryFactory;  
import org.locationtech.jts.geom.LineSegment;  
import org.locationtech.jts.io.ParseException;  
import org.locationtech.jts.io.WKTReader;  
import org.locationtech.jts.io.WKTWriter;

/\*\*  
 \* 坐标计算工具类  
 \*  
 \* @author shichao.shan  
 \* @date 2020/10/19 15:37  
 \*/  
public class CoordinateUtils \{

private static final double DOUBLE\_MIN\_VALUE = 0.01;  
    public static final GeometryFactory GEOMETRY\_FACTORY = JTSFactoryFinder.getGeometryFactory();

/\*\*  
     \* 计算两个经纬点的距离  
     \*  
     \* @param co1 \[lon,lat,hei\]  
     \* @param co2 \[lon,lat,hei\]  
     \* @return 返回距离值  
     \*/  
    public static double calculateLonLatHeiDis(Coordinate co1, Coordinate co2) \{  
        Coordinate xyz1 = ConvertedUtils.transformLatLonHeiToXyz(co1);  
        Coordinate xyz2 = ConvertedUtils.transformLatLonHeiToXyz(co2);  
        return xyz1.distance3D(xyz2);  
    \}

/\*\*  
     \* 计算点到线段的内部垂足点，不考虑延长线  
     \*  
     \* @param p           点  
     \* @param coordinates 线段的坐标  
     \* @return 返回 垂足点坐标  
     \*/  
    public static Coordinate calculateInnerFoot(Coordinate p, Coordinate\[\] coordinates) \{  
        Coordinate start = coordinates\[0\];  
        Coordinate end = coordinates\[coordinates.length - 1\];

LineSegment lineSegment = new LineSegment(start, end);

Coordinate foot = new Coordinate();  
        double frag = lineSegment.projectionFactor(p);  
        if (frag > 1 || frag < 0) \{  
            return null;  
        \}  
        double x = start.x + frag \* (end.x - start.x);  
        double y = start.y + frag \* (end.y - start.y);  
        double z = start.getZ() + frag \* (end.getZ() - start.getZ());  
        foot.setX(x);  
        foot.setY(y);  
        foot.setZ(z);  
        return foot;  
    \}

/\*\*  
     \* geometry 转WKT串，指定维度，2/3维  
     \*  
     \* @param geo       形状对象  
     \* @param dimension 维度  
     \* @return 返回转换后的串  
     \*/  
    public static String geometryToWkt(Geometry geo, int dimension) \{  
        WKTWriter wktWriter = new WKTWriter(dimension);  
        String wkt = wktWriter.write(geo);  
        wkt = wkt.replace(" Z", "");  
        return wkt;  
    \}

/\*\*  
     \* wkt 字符串 转为 形状对象  
     \*  
     \* @param wkt wkt 字符串  
     \* @return 返回对应的形状对象  
     \*/  
    public static Geometry wktStringToGeometry(String wkt) \{  
        WKTReader reader = new WKTReader(GEOMETRY\_FACTORY);  
        try \{  
            return reader.read(wkt);  
        \} catch (ParseException e) \{  
            e.printStackTrace();  
            throw new IllegalArgumentException("Can not convert WKT String:" + wkt);  
        \}  
    \}