postgis常用知识

2023-04-04 本文已影响0人何亮hook_8285

空间类型

对比项	geometry	geography
名称	几何对象	地理对象
坐标系	支持平面坐标系和球面坐标系	仅支持球面坐标系
对象类型	支持 POINT、MULTIPOINT、LINESTRING、LINEARRING、MULTILINESTRING、POLYGON、MULTIPOLYGON、POLYHEDRALSURFACE、TRIANGLE、TIN、GEOMETRYCOLLECTION等简单对象，还支持CIRCULARSTRING、COMPOUNDCURVE、CURVEPOLYGON、MULTICURVE、MULTISURFACE	仅支持POINT、LINESTRING、POLYGON、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION
函数限制	类型较丰富	支持类型较少，仅支持类型转换、长度面积距离计算、交并差运算函数
索引局限性	几何索引不能正确处理极地地区查询	地理索引可以正确处理覆盖极点或国际日期变更线的查询
元数据	geometry_columns：提供了数据库中所有空间数据表的描述信息，分别是数据库名、模式名、空间数据表名，属性列名称，几何图形维度，空间参考标识符，几何图形类型	无定义的元数据，需自定义

根据geometry与geography的特点，在使用时可根据以下情况选择：

geometry	geography
如果数据在地理范围上是紧凑的（包含在州、县或市内），推荐使用基于笛卡尔坐标的geometry类型	如果需要测量在地理范围上是分散的数据集（覆盖世界大部分地区）距离，推荐使用geography类型。
当做数据存储时，推荐使用geometry	由于地理坐标较为精确，因此在进行距离、面积等量算时，建议使用geography
如果用户较为了解投影信息知识，推荐使用geometry	geography不需要了解专业的投影知识只需要知道经纬度就可以进行计算，因此使用门槛较低
当场景需要运用大量复杂函数时，推荐使用geometry	geography的支持函数较少，且计算复杂，因此应用时需要占用较多计算资源。

1.jpg

插入数据


--创建地理测试表
create table sz_test(
  name varchar(255),
    geom geometry
);

--点
insert into sz_test(name,geom) values('test1','point(94.656608 40.140608)');

--线
insert into sz_test(name,geom) values('test2','linestring(0 0,0 1,0 2,0 0)');

--面
insert into sz_test(name,geom) values('test3','polygon((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2,1 2,1 1))');


--多点
insert into sz_test(name,geom) values('test4','MULTIPOINT(94.656608 40.140608,0 1)');

--多线
insert into sz_test(name,geom) values('test5','MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))');


--多面
insert into sz_test(name,geom) values('test6','MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))) ');

--几何集合
insert into sz_test(name,geom) values('test7','GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0,1 1,0 1, 0 0)))');

查询数据

-- 将geometry类型查询出来的数据转换字符串,ST_AsText函数用于转换字符串
select ST_AsText(geom) from sz_test;

-- 获取geomerty类型的x左边和y坐标,并且过滤出点的数据，ST_GeometryType函数获取数据类型
select ST_GeometryType(geom),st_x(geom),st_y(geom) from sz_test where ST_GeometryType(geom)='ST_Point';

-- 返回几何图形的维数
select ST_NDims(geom) from sz_test;

-- 查询几何图形的空间参考标识码，例如4326
select ST_SRID(geom) from sz_test;

-- 返回两个几何或地理值之间的距离，st_distance 计算是弧度，不是米
select st_distance(geo,'srid=4326;point(100.107425 39.165438)')  from sync_camera;

-- 查询在1000米范围，
-- ST_Transform 将几何对象转化到指定空间，2415是平面坐标  4326是椭球体坐标
-- ST_distance  换算两个geometry的距离
-- ST_dwithin   判断两个geometry对象是不是，是不是在范围内
select  name,ST_distance(geo,ST_GeomFromText('POINT(100.107425 39.165438)', 4326)) as distance,ST_AsText(geo) from sync_camera
where ST_dwithin(ST_Transform(geo,2415), ST_Transform(ST_GeomFromText('POINT(100.107425 39.165438)', 4326),2415), 1000)

-- 获取线的最小值和最大值
select st_xmin(geo),st_ymin(geo),st_xmax(geo),st_ymax(geo),ST_AsText(geo) from sync_camera;

-- 查看线的长度
select st_length(ST_GeomFromText('linestring(100.108183 39.166803,100.111778 39.162805,100.108183 39.166803)'));

-- 查询面的面积
select st_area(st_transform(
ST_SetSRID(ST_GeomFromText(
'POLYGON ((115.440261 33.8547281, 115.4400647 33.8548702, 
115.4403265 33.8549768, 115.4404674 33.8549267, 115.4404397 33.8547365, 
115.440261 33.8547281))'),4326),4527))

-- 圆的中心点
SELECT ST_AsText(ST_centroid(g))
FROM  ST_GeomFromText('CIRCULARSTRING(0 2, -1 1,0 0, 0.5 0, 1 0, 2 1, 1 2, 0.5 2, 0 2)')  AS g ;

-- 查询聚合 ，将四至分为x,y轴都分为20等分
SELECT 
width_bucket(st_x(geo), 103.823557 ,119.235188 ,20) grid_x, 
width_bucket(st_y(geo), 36.058039 , 37.614617, 20) grid_y,  
count(*),
st_centroid(st_collect(geo)) geom,  
array_agg(id) gids
from sync_camera
where 
st_x(geo) between 103.823557 and 119.235188
and 
st_y(geo) between 36.058039   and 37.614617
GROUP BY grid_x,grid_y 

-- 点转线
select st_astext(st_makeline(array_agg(geo))) from sync_camera;

-- 查询多边形内的点
SELECT count(1),st_x(geo),st_y(geo),geo,name
FROM sync_camera
WHERE ST_Contains(ST_GeomFromText('polygon((105.63903808593751 34.60269355405186,105.70770263671876 34.474863669009004,105.73928833007812 34.56538299699511,105.63903808593751 34.60269355405186))',4326), geo);

-- 查询直线距离50米范围的设备
select p.id,p.name,p.geo from sync_camera p where ST_DWithin( 
    ST_Transform(ST_GeomFromText('linestring(105.68148136138917 34.56085936708384,105.68369150161744 34.56442881428687)',4326), 2415), ST_Transform( p.geo,2415), 50)
    
    
-- 中心点
select st_centroid(ST_GeomFromText('polygon((105.63903808593751 34.60269355405186,105.70770263671876 34.474863669009004,105.73928833007812 34.56538299699511,105.63903808593751 34.60269355405186))',4326)),ST_GeomFromText('polygon((105.63903808593751 34.60269355405186,105.70770263671876 34.474863669009004,105.73928833007812 34.56538299699511,105.63903808593751 34.60269355405186))',4326);

-- 将数据转geojson
select ST_AsGeoJSON(ST_GeomFromText('polygon((105.63903808593751 34.60269355405186,105.70770263671876 34.474863669009004,105.73928833007812 34.56538299699511,105.63903808593751 34.60269355405186))',4326));


-- 查询一千米范围内
select id, name, ST_AsText(geo),ST_Distance(ST_GeomFromText('POINT(105.63903808593751 34.60269355405186)',4326), geo) from sync_camera where ST_DWithin(geo::geography, ST_GeographyFromText('POINT(105.63903808593751 34.60269355405186)'), 1000.0);


-- 聚合器，按不同方式聚合，可以输出点和多边形
SELECT kmean, count(*), ST_SetSRID(ST_Extent(geo), 4326) as bbox 
FROM
(
    SELECT ST_ClusterKMeans(geo, 10) OVER() AS kmean, ST_Centroid(geo) as geo
    FROM sync_camera sc 
) tsub
GROUP BY kmean;

-- 聚合点
SELECT kmean, count(*), st_centroid(st_collect(geo)) as bbox 
FROM
(
    SELECT ST_ClusterKMeans(geo, 20) OVER() AS kmean, ST_Centroid(geo) as geo
    FROM sync_camera sc 
) tsub
GROUP BY kmean;

-- 网格聚合点
SELECT
    array_agg(id) AS ids,
    COUNT( geo ) AS count,
   ST_Centroid(ST_Collect(geo))  AS center
FROM sync_camera sc 
GROUP BY
    ST_SnapToGrid( ST_SetSRID(geo, 4326), 22.25, 11.125)
ORDER BY
    count DESC
;

更新数据

-- 将x列和y列更新到geometry类型列中，ST_GeomFromText函数是将字符串转geometry类型
update sync_camera set geo=ST_GeomFromText(concat('POINT(',x,' ',y,')'), 4326);

-- 将geo列字段更新，更新面
update building set geo = ST_GeomFromText('POLYGON((121.415703 31.172893,121.415805 31.172664,121.416127 31.172751,121.41603 31.172976,121.415703 31.172893))',4632) where id = 123

-- 更新geom字段的空间坐标系
SELECT UpdateGeometrySRID('sz_test','geom',4326);

常用函数

ST_GeometryType(geometry) —— 返回几何图形的类型

ST_NDims(geometry) —— 返回几何图形的维数

ST_SRID(geometry) —— 返回几何图形的空间参考标识码

点（Points）

ST_X(geometry) —— 返回X坐标

ST_Y(geometry) —— 返回Y坐标

线串（Linestring）

ST_Length(geometry) —— 返回线串的长度

ST_StartPoint(geometry) —— 将线串的第一个坐标作为点返回

ST_EndPoint(geometry） —— 将线串的最后一个坐标作为点返回

ST_NPoints(geometry) —— 返回线串的坐标数量

多边形（Polygon）

ST_Area(geometry) —— 返回多边形的面积

ST_NRings(geometry) —— 返回多边形中环的数量（通常为1个，其他是孔）

ST_ExteriorRing(geometry) —— 以线串的形式返回多边形最外面的环

ST_InteriorRingN(geometry, n) —— 以线串形式返回指定的内部环

ST_Perimeter(geometry) —— 返回所有环的长度

集合（Collection）

ST_NumGeometries(geometry) —— 返回集合中的组成部分的数量

ST_GeometryN(geometry, n) —— 返回集合中指定的组成部分

ST_Area(geometry) —— 返回集合中所有多边形组成部分的总面积

ST_Length(geometry) —— 返回所有线段组成部分的总长度

几何图形输入和输出

①Well-known text（WKT）

ST_GeomFromText(text, srid) —— 返回geometry
ST_AsText(geometry) —— 返回text
ST_AsEWKT(geometry) —— 返回text

②Well-known binary（WKB）
ST_GeomFromWKB(bytea) —— 返回geometry
ST_AsBinary(geometry) —— 返回bytea
ST_AsEWKB(geometry) —— 返回bytea

③Geographic Mark-up Language（GML）
ST_GeomFromGML(text) —— 返回geometry
ST_ASGML(geometry) —— 返回text

④Keyhole Mark-up Language（KML）
ST_GeomFromKML(text) —— 返回geometry
ST_ASKML(geometry) —— 返回text

⑤GeoJson
ST_AsGeoJSON(geometry) —— 返回text

⑥Scalable Vector Graphics(SVG）
ST_AsSVG(geometry) —— 返回text

Spring Jpa Data使用PostGis

pom.xml

        <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>${hibernate.version}</version>
        </dependency>

application.properties

spring.datasource.url=jdbc:postgresql://192.168.207.120:15432/test?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.minimum-idle=20
spring.datasource.hikari.idle-timeout=180000
spring.datasource.hikari.maximum-pool-size=1000
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.pool-name=HeartHikariCP
spring.jpa.database=postgresql
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#设置postgis方言
spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.postgis.PostgisPG95Dialect
spring.jpa.show-sql=true

Entity

package com.postgistest.entity;

import lombok.Data;
import org.locationtech.jts.geom.Point;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

/**
 * @author heliang
 * 摄像头实体
 */
@Data
@Entity
@Table(name = "sync_camera")
public class Camera implements Serializable {

    @Id
    @Column(name = "id")
    private String id;

    @Column(name = "device_id")
    private String deviceId;

    @Column(name = "name")
    private String name;

    private Double x;

    private Double y;

    @Column(name = "vx_external_id")
    private String vxExternalId;

    @Column(name = "area_id")
    private String areaId;

    @Column(name = "vs_device_id")
    private String vsDeviceId;

    private String source;

    @Column(name = "sync_time")
    private Long syncTime;

    private String type;

    private String keywords;

    private String description;

    //geometry类型
    @Column(columnDefinition = "geometry(Point,4326)")
    private Point geo;
}

Repository

package com.postgistest.dao;

import com.postgistest.entity.Camera;
import org.locationtech.jts.geom.Point;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;


/**
 * 摄像头持久化
 * @author heliang
 */
public interface CameraDao extends PagingAndSortingRepository<Camera,String>, JpaSpecificationExecutor<Camera> {

    //范围查询
    @Query(value="SELECT d.* FROM sync_camera AS d WHERE ST_DWithin(ST_Transform(:point,26986),ST_Transform(geo,26986), :distance)",nativeQuery=true)
    public List<Camera> findWithin(@Param("point") Point point, @Param("distance")int distance);
}

service

package com.postgistest.service;

import com.postgistest.dao.CameraDao;
import com.postgistest.entity.Camera;
import org.hibernate.query.NativeQuery;
import org.hibernate.spatial.JTSGeometryType;
import org.hibernate.spatial.dialect.postgis.PGGeometryTypeDescriptor;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.locationtech.jts.geom.Point;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;

/**
 * 摄像头业务
 * @author heliang
 */
@Service
public class CameraService {


    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private CameraDao cameraDao;

    //聚合
    public List<Map> findCluster(Double minLng, Double maxLng, Double minLat, Double maxLat)
    {

        StringBuffer sql=new StringBuffer();
        sql.append("SELECT ");
        sql.append("   width_bucket(st_x(geo),"+minLng+" ,"+maxLng+" ,20) grid_x, ");
        sql.append("   width_bucket(st_y(geo), "+minLat+" ,"+maxLat+", 20) grid_y, ");
        sql.append("  count(*) total,   ");
        sql.append("  st_centroid(st_collect(geo)) centerpoint   ");
        sql.append("from sync_camera ");
        sql.append("where");
        sql.append("  st_x(geo) between "+minLng+" and "+maxLng+" ");
        sql.append(" and ");
        sql.append( " st_y(geo) between  "+minLat+"   and "+maxLat+" ");
        sql.append(" GROUP BY grid_x,grid_y ");


        //addScalar 方法指定返回值的类型
        Query nativeQuery = entityManager.createNativeQuery(sql.toString());
        List<Map> resultList = nativeQuery.unwrap(NativeQuery.class)
                .addScalar("centerpoint", new JTSGeometryType(PGGeometryTypeDescriptor.INSTANCE_WKB_1)) //指定返回列的类型
                .addScalar("grid_x", StandardBasicTypes.DOUBLE)
                .addScalar("grid_y",StandardBasicTypes.DOUBLE)
                .addScalar("total",StandardBasicTypes.LONG)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP) //设置每条数据映射成map对象
                .getResultList();

        return resultList;
    }


    //分页获取
    public List<Camera> findTop10()
    {
        PageRequest pageable= PageRequest.of(0, 10);
        Page<Camera> page=cameraDao.findAll(pageable);
        return  page.getContent();
    }


    //点的范围
    public List<Camera> findWithin(@Param("point") Point point, @Param("distance")int distance)
    {
            return cameraDao.findWithin(point,distance);
    }


}

Test

@RunWith(SpringRunner.class)
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class GisDemoApplicationTests {

    @Autowired
    CameraService  cameraService;

    @Test
    void testDao(){
       //获取10条数据
        List<Camera> cameraServiceTop10 = cameraService.findTop10();

        //查询1000米范围内的摄像头
        Point point = new GeometryFactory().createPoint(new Coordinate(100.107425d,39.165438d));
        point.setSRID(4326);
        List<Camera> cameraServiceWithin = cameraService.findWithin(point, 1000);

        //查询聚合点位
        List<Map> cameraServiceCluster = cameraService.findCluster(minLng, maxLng, minLat, maxLat);
    }

}