2020-08-11 postgis dbscan聚合

    前几天写好的sql忘记保存，这几天又忘得一干二净，晚上同事过来说要出9号的数据，本来想着很快完事，找了半天没找到，真的该好好做笔记了，研究了好久的东西，脑子再好使也会忘记，不如停下来好好记一记，某天想起来，翻出来也会容易一些。

    1.需求：

    根据弱覆盖数据，聚合三个以上挨在一起的栅格，形成区域，并把区域信息和输入数据信息一起入到新的表里。借鉴官方文档和公瑾大佬的文章，写了下面的sql。

首先:弱覆盖表加空间字段

alter table md_cover_grid_cavity add geom geometry(polygon,4326);

然后：更新这个字段，形成栅格边界。

update md_cover_grid_cavity set geom=st_setsrid(ST_GeomFromEWKT('SRID=4326;POLYGON((' || lt_longitude || ' ' || lt_latitude || ',' || lb_longitude || ' ' || lb_latitude || ','

|| rb_longitude || ' ' || rb_latitude || ',' || rt_longitude || ' ' || rt_latitude || ','  || lt_longitude || ' '  ||  lt_latitude  || '))'),4326)::geometry;

create index on md_cover_grid_cavity using gist(geom);

根据ST_ClusterDBSCAN进行聚类，形成聚类cid和没有聚类的cid(null)，eps单位是度，注意一下

SELECT ST_ClusterDBSCAN(geom, eps:= 0.00005, minpoints := 3) over () AS cid from md_cover_grid_cavity;

create table md_cover_grid_cavity_dbscan as SELECT *, ST_ClusterDBSCAN(geom, eps:= 0.00005, minpoints := 3) over () AS cid from md_cover_grid_cavity;

目标表添加空间字段和扩展字段，注意是Multipolygon类型。

alter table md_coverage_assessment add ids_in_cluster text;

alter table md_coverage_assessment add geom geometry(MultiPolygon,4326);

数据插入目标表

insert into md_coverage_assessment (id,start_time,city,weak_cover_areas_id,centre_longitude,centre_latitude,area,mr_avg_rsrp,sampling_point_num,grid_count,geom,ids_in_cluster)

SELECT cid,start_time, city,cid,round(cast (st_x(ST_Centroid(ST_Collect(geom))) as numeric),8),round(cast(st_y(ST_Centroid(ST_Collect(geom))) as numeric),8)

,2500*count(grid_id)

,round(avg(cast(mr_avg_rsrp as numeric)),2)

,sum(cast(sampling_point_num as numeric)) as sampling_point_num,count(grid_id) as grid_count, ST_Collect(geom) AS  cluster_geom

,array_agg(grid_id) AS ids_in_cluster FROM md_cover_grid_cavity_dbscan where cid is not null

GROUP BY cid,start_time, city;

create index on md_coverage_assessment using gist(geom);

手写的第一篇文章，给自己鼓励一下吧，oracle和pg数据同步还没研究，kettle有空得看一下，手动入睡觉。

发现聚合后生成的是数组，不是单个的面，所以改了下sql：如下：

insert into md_coverage_assessment (id,start_time,city,weak_cover_areas_id,centre_longitude,centre_latitude,area,mr_avg_rsrp,sampling_point_num,grid_count,geom,ids_in_cluster)

SELECT cid,start_time, city,cid,round(cast (st_x(ST_Centroid(ST_Collect(geom))) as numeric),8),round(cast(st_y(ST_Centroid(ST_Collect(geom))) as numeric),8)

,2500*count(grid_id)

,round(avg(cast(mr_avg_rsrp as numeric)),2)

,sum(cast(sampling_point_num as numeric)) as sampling_point_num,count(grid_id) as grid_count, st_Union(ST_Accum(ST_SnapToGrid(geom,0.000001))) AS  cluster_geom

,array_agg(grid_id) AS ids_in_cluster FROM md_cover_grid_cavity_dbscan where cid is not null

GROUP BY cid,start_time, city;

create index on md_coverage_assessment using gist(geom);

  insert into  md_coverage_assessment (id,start_time,city,weak_cover_areas_id,centre_longitude,centre_latitude,area,mr_avg_rsrp,sampling_point_num,grid_count,geom,ids_in_cluster) 

SELECT cid,start_time, city,cid,round(cast (st_x(ST_Centroid(ST_Collect(geom))) as numeric),8),round(cast(st_y(ST_Centroid(ST_Collect(geom))) as numeric),8)

,2500*count(grid_id)

,round(avg(cast(mr_avg_rsrp as numeric)),2)

,sum(cast(sampling_point_num as numeric)) as sampling_point_num,count(grid_id) as grid_count, st_Union(geom) AS  cluster_geom

,array_agg(grid_id) AS ids_in_cluster FROM md_cover_grid_cavity_dbscan where cid is not null

GROUP BY cid,start_time, city;

create index on md_coverage_assessment using gist(geom);