数据库表设计

MySQL支持多种空间数据类型，这里我们主要用到：

• POINT：存储点位置
• LINESTRING：存储线段
• POLYGON：存储多边形区域

建表语句：

-- 创建点数据表
CREATE TABLE spatial_points (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    location POINT NOT NULL,
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    SPATIAL INDEX(location) 
) ENGINE=InnoDB;

-- 创建线数据表
CREATE TABLE spatial_lines (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    path LINESTRING NOT NULL,
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    SPATIAL INDEX(path)
) ENGINE=InnoDB;

-- 创建面数据表
CREATE TABLE spatial_polygons (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    area POLYGON NOT NULL,
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    SPATIAL INDEX(area)
) ENGINE=InnoDB;

数据操作示例

插入数据

MySQL使用WKT（Well-Known Text）格式来表示空间数据。下面是一些插入数据的例子：

-- 插入一个点（比如上海某个地标的位置）
INSERT INTO spatial_points (name, location) 
VALUES ('Point A', ST_GeomFromText('POINT(121.47 31.23)'));

-- 插入一条线（比如两点之间的路线）
INSERT INTO spatial_lines (name, path)
VALUES ('Line 1', ST_GeomFromText('LINESTRING(121.47 31.23, 121.48 31.24)'));

-- 插入一个多边形（比如某个商圈的范围）
INSERT INTO spatial_polygons (name, area)
VALUES ('Polygon 1', ST_GeomFromText('POLYGON((121.47 31.23, 121.48 31.23, 121.48 31.24, 121.47 31.24, 121.47 31.23))'));

常用空间查询

-- 查找在指定多边形内的所有点
SELECT p.id, p.name
FROM spatial_points p, spatial_polygons poly
WHERE poly.id = 1 
AND ST_Contains(poly.area, p.location);

-- 查找距离某个点1公里以内的所有点
SELECT p2.id, p2.name,
    ST_Distance_Sphere(p1.location, p2.location) as distance
FROM spatial_points p1, spatial_points p2
WHERE p1.id = 1
AND p1.id != p2.id
AND ST_Distance_Sphere(p1.location, p2.location) <= 1000
ORDER BY distance;

-- 计算多边形面积
SELECT id, name, ST_Area(area) * POW(111195, 2) as area_in_sqm
FROM spatial_polygons;

Spring Boot整合

实体类定义

@Data
@TableName("spatial_points")
public class SpatialPoint {
    @TableId(type = IdType.AUTO)
    private Long id;
    
    private String name;
    
    // 注意：这里用String类型存储WKT格式的点位置
    // 需要配合TypeHandler来处理数据转换
    @TableField(typeHandler = PointTypeHandler.class)
    private String location;
    
    private LocalDateTime createdTime;
}

自定义TypeHandler

为了处理空间数据类型和String之间的转换，我们需要实现一个TypeHandler：

@MappedTypes(String.class)
@MappedJdbcTypes(JdbcType.OTHER)
public class PointTypeHandler extends BaseTypeHandler<String> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) 
            throws SQLException {
        ps.setString(i, String.format("ST_GeomFromText('%s')", parameter));
    }

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

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

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

Mapper

@Mapper
public interface SpatialPointMapper extends BaseMapper<SpatialPoint> {
}

Service

下面是一些空间关系查询的示例：

public interface ISpatialPointService extends IService<SpatialPoint> {
    List<SpatialPoint> findPointsInPolygon(String polygonWkt);
    // ...
}

@Service
@Transactional
public class SpatialPointServiceImpl extends ServiceImpl<SpatialPointMapper, SpatialPoint> 
    implements ISpatialPointService {

    @Override
    public List<SpatialPoint> getByNameLike(String name) {
        return lambdaQuery()
                .like(SpatialPoint::getName, name)
                .list();
    }
    
    @Override
    public List<SpatialPoint> getByTimeRange(LocalDateTime start, LocalDateTime end) {
        return lambdaQuery()
                .between(SpatialPoint::getCreatedTime, start, end)
                .list();
    }
    
    // 查询在多边形内的所有点
    @Override
    public List<SpatialPoint> findPointsInPolygon(String polygonWkt) {
        return lambdaQuery()
                .apply("ST_Contains(ST_GeomFromText({0}), location)", polygonWkt)
                .list();
    }
    
    @Override
    public List<SpatialPoint> findPointsInPolygon(Long polygonId) {
        // 1. 先通过id获取多边形
        SpatialPolygon polygon = polygonMapper.selectById(polygonId);
        if (polygon == null) {
            return Collections.emptyList();
        }

        // 2. 查找在这个多边形内的所有点
        return lambdaQuery()
                .apply("ST_Contains((SELECT area FROM spatial_polygons WHERE id = {0}), location)", polygonId)
                .list();
    }

    // 或者使用连接查询的方式
    @Override
    public List<SpatialPoint> findPointsInPolygon(Long polygonId) {
        return lambdaQuery()
                .apply("EXISTS (SELECT 1 FROM spatial_polygons p WHERE p.id = {0} AND ST_Contains(p.area, location))", polygonId)
                .list();
    }
}