postgresql-postGIS 计算距离

如何安装postGIS

启用postGIS

如何用SQL 访问计算距离

java中如何使用。

兼容性问题  当前安装什么版本

    http://postgis.net/documentation/
    https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

1. 如何安装postGIS 

postgis31_11.x86_64

https://yum.postgresql.org/

https://yum.postgresql.org/howto/

安装 postgresql   yum仓库信息

 yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

 

 

安装 postGIS  

 yum search postgis


 yum -y install postgis31_11.x86_64 postgis31_11-utils.x86_64

 
 启用postGIS

 
CREATE EXTENSION postgis;    
CREATE EXTENSION postgis_raster;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch; --needed for postgis_tiger_geocoder
--optional used by postgis_tiger_geocoder, or can be used standalone
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;


 select   postgis_lib_version()
 

 
 
 
 
     @Query(value = "select f from bc_contact f where f.data_end='2' and " +
            " ST_Distance(ST_SetSRID(f.point,4326)\\:\\:geography," +
            " ST_SetSRID(ST_MakePoint(:lon,:lat),4326)\\:\\:geography) < :distance ",nativeQuery = true)
    List<BcContact> nearbyList(@Param("lon") double lon,@Param("lat") double lat,@Param("distance") double distance) ;
    
    
    
    
  计算距离

 
select
ST_Distance(
    ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography,
    ST_SetSRID(ST_MakePoint(106.00231199774656,29.719258550486572),4326)::geography
),
ST_Length(
    ST_MakeLine(
        ST_MakePoint(115.97166453999147,28.716493914230423),
        ST_MakePoint(106.00231199774656,29.719258550486572)
    )::geography
)

备注：

ST_GeomFromText('LINESTRING(115.97166453999147 28.716493914230423,106.00231199774656 29.719258550486572)')与
ST_MakeLine(
    ST_MakePoint(115.97166453999147,28.716493914230423),
    ST_MakePoint(106.00231199774656,29.719258550486572)
)等价

ST_GeomFromText('POINT(115.97166453999147 28.716493914230423)',4326)与
ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)等价

ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography与
Geography(ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326))、
ST_GeographyFromText('SRID=4326;POINT(115.97166453999147 28.716493914230423)')等价
(::geography是postgis中的转换类型语法,把geometry转成geography)