前言
web应用中很多查找某点范围内的poi功能，可以通过PostGIS实现。
安装postgis
通过docker安装postgis，官方镜像已经包含了postgresql
docker run --name some-postgis -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgis/postgis
建表，添加geometry字段
select AddGeometryColumn('表名','字段名',4326, 'POINT', 2);
--4326: WGS84,地理坐标系
maven
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<!--postgis类型和java映射-->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-spatial</artifactId>
<version>5.2.10.Final</version>
</dependency>
application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/db_test?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=postgres
spring.datasource.password=mysecretpassword
#pg方言，需要配置，否则会出现org.hibernate.type.SerializationException: could not deserialize
spring.jpa.database-platform: org.hibernate.spatial.dialect.postgis.PostgisPG9Dialect
代码
Entity:
@Data
@Entity
@Table(name = "tb_poi")
public class PoiEntity implements Serializable {
@Id
@Column(name = "id", length = 64, unique = true, nullable = false)
private Long id;
@Column(name = "name")
private String name;
//com.vividsolutions.jts.geom.Point
@Column(name = "location", columnDefinition = "geometry(Point,4326)")
private Point location;
}
Repository
使用postgis函数ST_DWithin查找范围内的poi:
boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);
对于geometry第三个距离参数单位和数据的坐标系有关，如果数据使用的是地理坐标系，则距离单位是度。
可以通过ST_Transform函数将地理坐标系转成平面坐标系。
public interface PoiRepository extends CrudRepository<PoiEntity, Long> {
//4326(wgs84)这个坐标系以度为单位.要想转成米为单位的话得做一下转换
//GEOCS代表的是地理坐标系，也就是以经纬度表示的坐标系统,例如4326
//PROJCS代表的投影坐标系，它是通过一种算法把球面坐标系转成平面坐标系，以便计算，一般是以米为单位表示, 例如26986
@Query(value = "SELECT d.* FROM tb_poi AS d WHERE ST_DWithin(ST_Transform(:point,26986)," +
"ST_Transform(d.location,26986), :distance)", nativeQuery = true)
List<ChargeSiteEntity> findWithin(@Param("point") Point point, @Param("distance") Integer distance);
}
Test
@RunWith(SpringRunner.class)
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class GisDemoApplicationTests {
@Autowired
PoiRepository poiRepository ;
@Test
void add(){
PoiEntity poi= new PoiEntity ();
poi.setId(1L);
poi.setName("test");
GeometryFactory geometryFactory = new GeometryFactory();
Point point = geometryFactory.createPoint(new Coordinate(120.153576, 30.287459));
point.setSRID(4326);
poi.setLocation(point);
poiRepository.save(poi);
}
@Test
void findOne(){
Optional<PoiEntity> byId =poiRepository.findById(1L);
if(byId.isPresent()){
PoiEntity poi = byId.get();
Point location = poi.getLocation();
if(location!=null){
System.out.println(location.getX()+"--"+location.getY());
}
}
}
@Test
void findWithIn(){
GeometryFactory geometryFactory = new GeometryFactory();
Point point = geometryFactory.createPoint(new Coordinate(120.153576, 30.287459));
point.setSRID(4326);
//查找当前1000m范围内的poi
List<PoiEntity> within = poiRepository.findWithin(point, 1000);
System.out.println(within.size());
}
}
