mysql空间函数结合java

一、mysql空间函数使用

1. 使用场景(存储了坐标点和多边形)，查找相等的坐标点、查找几何图形是否相交
   mysql的数据类型

   create table `geom`(
   `id` int not null auto_increment,
   `center_point` point not null COMMENT '点类型坐标点',
    `polyline` polygon NOT NULL COMMENT '面类型坐标点',
   primary key(`id`)
   );
   

2. 空间函数的索引（空间函数索引字段要设置为非空字段）

   ALTER TABLE geom ADD SPATIAL INDEX(center_point);
   ALTER TABLE geom ADD SPATIAL INDEX(polyline);
   create SPATIAL INDEX index_point on cs_zipcode_geocoding_info(`point`);
   
   ALTER TABLE cs_zipcode_geocoding_info MODIFY column `point` POINT  NOT NULL;
   

create SPATIAL INDEX index_point on cs_zipcode_geocoding_info(point);

```

3. 使用到的空间函数
   ST_Intersects() 包括在边上
   ST_Within() 在范围内

   SELECT *
   FROM geom
   where ST_Within ( POLYGONFROMTEXT ('POINT(24.567000 23.345000)'), polyline )
   

   SELECT *
   FROM geom
   where ST_Within ( POLYGONFROMTEXT ('POLYGON((45.519612352449194 22.544369710807317, 
   45.51773140250114 22.525272105494178, 45.51216083650864 22.506908410443163, 
   45.51773140250114 22.563467316120455, 45.519612352449194 22.544369710807317))'), polyline ) 
   

注意：当是POLYGON类型的数据时，坐标点必须是闭合（第一个和最后一个坐标点要一样）的关系。

二、java中的使用

1、SpringBoot+mysql+mybatis
引入jar包

<dependency>
    <groupId>com.vividsolutions</groupId>
    <artifactId>jts</artifactId>
    <version>${jts.version}</version>
</dependency>

版本号是1.13

@Component
@MappedTypes({GeoPoint.class})
public class GeoPointTypeHandler extends BaseTypeHandler<GeoPoint> {

    private GeoPointConverter converter = new GeoPointConverter();

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, GeoPoint parameter, JdbcType jdbcType) throws SQLException {
        ps.setBytes(i, converter.to(parameter));
    }

    @Override
    public GeoPoint getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return converter.from(rs.getBytes(columnName));
    }

    @Override
    public GeoPoint getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return converter.from(rs.getBytes(columnIndex));
    }

    @Override
    public GeoPoint getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return converter.from(cs.getBytes(columnIndex));
    }
}

public class GeoPointConverter {


    /**
     * Little endian or Big endian
     */
    private int byteOrder = ByteOrderValues.LITTLE_ENDIAN;
    /**
     * Precision model
     */
    private PrecisionModel precisionModel = new PrecisionModel();
    /**
     * Coordinate sequence factory
     */
    private CoordinateSequenceFactory coordinateSequenceFactory = CoordinateArraySequenceFactory.instance();
    /**
     * Output dimension
     */
    private int outputDimension = 2;

    /**
     * Convert byte array containing SRID + WKB Geometry into Geometry object
     */
    public GeoPoint from(byte[] bytes) {
        if (bytes == null) {
            return null;
        }
        try (ByteArrayInputStream inputStream = new ByteArrayInputStream(bytes)) {
            // Read SRID
            byte[] sridBytes = new byte[4];
            inputStream.read(sridBytes);
            int srid = ByteOrderValues.getInt(sridBytes, byteOrder);

            // Prepare Geometry factory
            GeometryFactory geometryFactory = new GeometryFactory(precisionModel, srid, coordinateSequenceFactory);

            // Read Geometry
            WKBReader wkbReader = new WKBReader(geometryFactory);
            Geometry geometry = wkbReader.read(new InputStreamInStream(inputStream));
            Point point = (Point) geometry;
            // convert to GeoPoint double精度问题，及去除小数点后多余的0
            return new GeoPoint(new BigDecimal(point.getX()).setScale(16, BigDecimal.ROUND_HALF_UP).stripTrailingZeros(), new BigDecimal(point.getY()).setScale(16, BigDecimal.ROUND_HALF_UP).stripTrailingZeros());
        } catch (IOException | ParseException e) {
            throw new IllegalArgumentException(e);
        }
    }

    /**
     * Convert Geometry object into byte array containing SRID + WKB Geometry
     */
    public byte[] to(GeoPoint geoPoint) {
        if (geoPoint == null) {
            return null;
        }
        Coordinate coordinate = new Coordinate(geoPoint.getLng().doubleValue(), geoPoint.getLat().doubleValue());
        CoordinateArraySequence coordinateArraySequence = new CoordinateArraySequence(new Coordinate[]{coordinate}, 2);
        Point point = new Point(coordinateArraySequence, new GeometryFactory());
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            // Write SRID
            byte[] sridBytes = new byte[4];
            ByteOrderValues.putInt(point.getSRID(), sridBytes, byteOrder);
            outputStream.write(sridBytes);
            // Write Geometry
            WKBWriter wkbWriter = new WKBWriter(outputDimension, byteOrder);
            wkbWriter.write(point, new OutputStreamOutStream(outputStream));
            return outputStream.toByteArray();
        } catch (IOException ioe) {
            throw new IllegalArgumentException(ioe);
        }
    }
}

这是点的应用
2、面的应用展示是字符串处理
使用的AsText()

   <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-main</artifactId>
        <version>${geotools.version}</version>
    </dependency>
    <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-referencing</artifactId>
        <version>${geotools.version}</version>
    </dependency>
    <dependency>
        <groupId>org.geotools</groupId>
        <artifactId>gt-epsg-wkt</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-geojson</artifactId>
        <version>${geotools.version}</version>
    </dependency>


    <!--列出外部仓库，maven可以从中下载GeoTools和其他必需的jar-->
    <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>

版本是17.0 这个包是可以计算面积。

private double getArea(Geometry geometry) {
        double area = 8.20947332106365E-13;
        if (Objects.nonNull(geometry) && geometry.getArea() < area) {
            return geometry.getArea();
        }
        CoordinateReferenceSystem source = null;
        try {
            source = CRS.decode("CRS:84");
        } catch (FactoryException e) {
            e.printStackTrace();
        }
        CoordinateReferenceSystem target = null;
        try {
            target = CRS.decode("EPSG:3857");
        } catch (FactoryException e) {
            e.printStackTrace();
        }
        MathTransform transform = null;
        try {
            transform = CRS.findMathTransform(source, target, true);
        } catch (FactoryException e) {
            e.printStackTrace();
        }
        Geometry transform1 = null;
        try {
            transform1 = JTS.transform(geometry, transform);
        } catch (org.opengis.referencing.operation.TransformException e) {
            e.printStackTrace();
        }
        return transform1 == null ? 0 : transform1.getArea();
    }