前言

web應(yīng)用中很多查找某點(diǎn)范圍內(nèi)的poi功能，可以通過PostGIS實(shí)現(xiàn)。

安裝postgis

通過docker安裝postgis，官方鏡像已經(jīng)包含了postgresql

docker run --name  some-postgis  -e POSTGRES_PASSWORD=mysecretpassword   -d  -p 5432:5432 postgis/postgis

建表，添加geometry字段

select AddGeometryColumn('表名','字段名',4326, 'POINT', 2);
--4326: WGS84,地理坐標(biāo)系

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方言，需要配置，否則會(huì)出現(xiàn)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函數(shù)ST_DWithin查找范圍內(nèi)的poi:
boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);
對(duì)于geometry第三個(gè)距離參數(shù)單位和數(shù)據(jù)的坐標(biāo)系有關(guān)，如果數(shù)據(jù)使用的是地理坐標(biāo)系，則距離單位是度。
可以通過ST_Transform函數(shù)將地理坐標(biāo)系轉(zhuǎn)成平面坐標(biāo)系。

public interface PoiRepository  extends CrudRepository<PoiEntity, Long> {

    //4326(wgs84)這個(gè)坐標(biāo)系以度為單位.要想轉(zhuǎn)成米為單位的話得做一下轉(zhuǎn)換
    //GEOCS代表的是地理坐標(biāo)系，也就是以經(jīng)緯度表示的坐標(biāo)系統(tǒng),例如4326
    //PROJCS代表的投影坐標(biāo)系，它是通過一種算法把球面坐標(biāo)系轉(zhuǎn)成平面坐標(biāo)系，以便計(jì)算，一般是以米為單位表示, 例如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);
        //查找當(dāng)前1000m范圍內(nèi)的poi
        List<PoiEntity> within = poiRepository.findWithin(point, 1000);
        System.out.println(within.size());
    }
}