前言

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());
    }
}