PostGIS点数据处理实例

空间数据库PostGIS开发实例教程 

第2讲 点数据处理实例 

视频讲解见：PostGIS开发实例第2讲点数据处理_哔哩哔哩_bilibili

实例需求

1. 在SRID=3857的（12571000，3269000）和（12572000，3270000）围成的矩形范围内随机生成200个点，要求矩形存为boundary表（id 自增主码, geom几何图形字段），点集存为points表（id 自增主码, geom几何图形字段）。

SQL语句：

DROP TABLE IF EXISTS points;

CREATE TABLE points

AS
  SELECT "row_number"() over() as id,

    ST_SetSRID(

        ST_MakePoint(12571000+random()*1000,3269000+random()*1000)

      ,3857) as geom

FROM 

     generate_series(1, 200)

;

-- 将两点围成的矩形存入boundary

DROP TABLE IF EXISTS boundary;

CREATE TABLE boundary

AS

   SELECT st_setsrid(      

       st_makebox2d(

           st_makepoint(12571000,3269000),

           st_makepoint(12572000,3270000))

        , 3857) as geom

;

2. 按照点生成的顺序，将所有点连接成一条linsestring，并存入line表（id 自增主码, geom几何图形字段）。

SQL语句：

DROP TABLE IF EXISTS line;

CREATE TABLE line

AS

   SELECT st_makeline(geom) as geom

   FROM

   (

       SELECT ST_UNION(geom) geom

        FROM points

    ) a  

;

3. 按id顺序将相邻两点连接成一条线，并存lines数据表（id 自增主码, geom几何图形字段）;

SQL语句：

DROP TABLE IF EXISTS lines;

CREATE TABLE lines

as

  select

       st_makeline(a.geom ,b.geom) as geom

  FROM

       points a

  left join

       points b

  on

        a.id=b.id-1

;

4. 计算所有点集中距离最大的两个点，显示两点id和距离。

SQL语句：

with foo

AS

(

   select

     a.id id1,b.id id2,st_distance(a.geom,b.geom) as dist

   from

       points a

   left join

       points b

   on

     a.id<>b.id

)

select

    id1,id2,dist

from 

   foo

where 

    dist=(select max(dist) from foo)

5. 生成点集包围凸包，并存为hull表（id 自增主码, geom几何图形字段）。

SQL语句：

drop table if exists hull;

create table hull

as

   select 

         st_convexhull(st_union(geom)) as geom

   from 

         points;

6. 搜索点集几何中心最近的点， 显示该点id号。

SQL语句：

select 

  id

from 

   points

where 

    st_equals(geom,

            (select 

                 st_closestpoint(st_union(a.geom), b.geom)

              from 

                  points a,

                  (select st_centroid(st_union(geom)) as geom from points) b

               group by  b.geom

            )

     );

7. 创建函数fn_knn(pid int,num int)，根据输入的pid和num参数，查找点pid的最近num个点，即KNN搜索。函数返回使用逗号分隔的所有近邻点id字符串。

SQL语句：

select

    id,

    (select string_agg(cast(id as text), ',')

 from

    (select 

        id

     from 

         points

     where 

          id<>a.id 

      order by 

           st_distance(geom,a.geom) 

      limit 5

     ) as foo

   ) as knn

from 

     points a

8. 以长为50m的滑动窗口，计算每个点的局部密度（以窗口中点数量代替密度）。

SQL:

with foo

AS

(

   select 

      id,

      st_intersection(    

          st_setsrid(

             st_makebox2d(

                 st_makepoint(12571000,3269000),

                 st_makepoint(12572000,3270000))

          ,3857),

        st_setsrid(

           st_makebox2d(

              st_makepoint(st_x(geom)-25,st_y(geom)-25),

              st_makepoint(st_x(geom)+25,st_y(geom)+25)

          ),3857)

      ) as grid

  from 

      points

)

select 

     id,

   ( select count(0) 

      from   points 

      where   st_within(geom,foo.grid)

   ) as num

from foo;

9. 使用k-means聚类方法，将点集分成10个类（簇），并将类（簇）号存入points表的cluster_id（int)字段。

SQL：

alter table points add column if not exists cluster_id int;

update points a

set cluster_id = b.cid

from

(

  select id,st_clusterkmeans(geom,10) over() as cid

   from points

) as b

where a.id = b.id;

10. 使用dbscan聚类方法，使用eps=10m和minpts=10参数，并点集分成若干类（簇），并将类（簇）号存入points表的cluster_id（int)字段。

SQL：

update points a

set cluster_id = b.cid

from

(

  select id, st_clusterdbscan(geom,80,5) over() as cid

  from points

) as b

where a.id = b.id;

11. 使用三角网法，为聚类后的每个类簇生成包围多边形。

SQL：

drop table if exists polygons;

create table polygons

as

  select cluster_id,st_union(geom) as geom

  from

  (

      select

           cluster_id,st_delaunaytriangles(st_union(geom)) as geom

      from points

      group by cluster_id

  ) as foo

  group by cluster_id