项目中需要存储地理范围类型(矩形区域),并能够按照区域范围进行检索,使用了Mysql数据库中的Geometry类型(Polygon)。
参考的几篇文章如下:
MySQL空间索引简单使用
mysql中的空间地理字段
让你的spring data jpa支持mysql的空间存储
springboot使用jpa绑定mysql空间数据geometry到实体类
总结几点:
1.实体类
@Data
@Entity
@Table(name = "cpxx_geoarea")
public class CpxxGeo {
@Id
@GenericGenerator(name = "product-uuid",strategy = "uuid")
@GeneratedValue(generator = "product-uuid")
@Column(name = "productId", columnDefinition = "varchar(100)")
private String productId; //产品标识
@Column(columnDefinition = "varchar(200)")
private String productName; //产品名称
@Column(columnDefinition = "Polygon COMMENT'矩形区域范围'")
private Polygon recArea;
@Transient
private Float topLeftLat; //左上纬度
@Transient
private Float topLeftLon; //左上经度
@Transient
private Float topRightLat; //右上纬度
@Transient
private Float topRightLon; //右上经度
@Transient
private Float lowerRightLat; //右下纬度
@Transient
private Float lowerRightLon; //右下经度
@Transient
private Float lowerLeftLat; //左下纬度
@Transient
private Float lowerLeftLon; //左下经度
/**
*
*/
public void setRecArea(){
String polygonStr = "Polygon((" +
this.getLowerLeftLon() + " " + this.getLowerLeftLat() + "," +
this.getLowerRightLon() + " " + this.getLowerRightLat() + "," +
this.getTopRightLon() + " " + this.getTopRightLat() + "," +
this.getTopLeftLon() + " " + this.getTopLeftLat() + "," +
this.getLowerLeftLon() + " " + this.getLowerLeftLat()
+"))";
this.setRecArea(polygonStr);
}
/**
*
* @param polygonStr 矩形区域字符串
*/
public void setRecArea(String polygonStr){
WKTReader reader = new WKTReader();
Geometry geom = null;
try {
geom = reader.read(polygonStr);
this.recArea = (com.vividsolutions.jts.geom.Polygon) geom;
this.recArea.setSRID(4326);
}catch (Exception e){
e.printStackTrace();
}
}
2. Repository
public interface CpxxGeoRep extends JpaRepository<CpyxxGeo, String>{
/**
* 区域范围检索
* @param polygon 矩形区域string:如Polygon((0 0, 10 0, 10 10, 0 10, 0 0));
* @return
*/
@Query(value = "select * from cpxx_geoarea where mbrcontains(?1, recArea)", nativeQuery = true)
public List<CpxxGeo> findByAreaPolygon(Polygon polygon);
}
3.application.properties配置文件
这里一定要注意,需要配置方言为mysql方言。如下所示,其中hibernate.dialect即为方言配置。
如果方言未设置,会提示异常:Cannot get geometry object from data you send to the GEOMETRY field
spring:
#jdbc config
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/gfg?serverTimezone=CTT&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
#jpa config
jpa:
show-sql: true
hibernate:
ddl-auto: update
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
properties:
hibernate.dialect: org.hibernate.spatial.dialect.mysql.MySQL56InnoDBSpatialDialect
4.插入Geometry类型的数据
Insert into table(geo) values(st_geomfromtext(‘Polygon((0 0, 10 0, 10 10, 0 10, 0 0))’, 4326));
5.查询区域是否包含
MBRContains(geom1, geom2) //判断geom1是否包含geom2
MBRWithin(geom1, geom2) //判断geom2是否包含geom1
6.在geometry列上建立索引
Create spatial index sp_index on table(geo);
需要注意的是,只建立索引是不够的,同时要指定SRID(mysql默认srid为0),如上面代码中的this.recArea.setSRID(4326)即为指定84坐标系。如果不设置SRID,无法命中索引。