Postgesql 空间点聚合查询实验

aggregate_build_data

create table tpoint(  
  gid serial primary key,  
  geom geometry(Point,4326)  
);

create index tpoint_geom_idx on tpoint using gist(geom);  

insert into tpoint(geom) SELECT st_setsrid((ST_Dump(p_geom)).geom,4326)  
from (select ST_GeneratePoints(ST_GeomFromText("Polygon((117.357442 30.231278,119.235188 30.231278,119.235188 32.614617,117.357442 32.614617,117.357442 30.231278))"), 300000) as p_geom) as b  

SELECT width_bucket(st_x(geom), 117.057442 ,119.235188 ,20) grid_x,  width_bucket(st_y(geom), 30.431278 , 32.614617, 20) grid_y,  
  count(*),  st_centroid(st_collect(geom)) geom,  array_agg(gid) gids  
  from tpoint where st_x(geom) between 117.057442 and 119.235188 and st_y(geom) between 30.431278  and 32.614617 GROUP BY grid_x,grid_y 

aggregate_search

SELECT width_bucket(st_x(geom), 117.057442 ,119.235188 ,20) grid_x,  width_bucket(st_y(geom), 30.431278 , 32.614617, 20) grid_y,  
  count(*),  st_centroid(st_collect(geom)) geom,  array_agg(gid) gids  
  from tpoint where st_x(geom) between 117.057442 and 119.235188 and st_y(geom) between 30.431278  and 32.614617 GROUP BY grid_x,grid_y 

width_bucket说明：

aggregate effects

参考阅读

PostgreSQL 9.6.0 手册 http://www.postgres.cn/docs/9.6/functions-math.html