Postgesql空间点聚合查询实验

  • Z时代
  • 2024-01-10
  • 分类：综合

database

aggregate_build_data

/***创建测试空间点数据库***/
create table tpoint(  
  gid serial primary key,  
  geom geometry(Point,4326)  
);
/***创建索引***/
create index tpoint_geom_idx on tpoint using gist(geom);  
/***插入50万条范围内随机位置数据***/
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 
/***耗时0.5秒，去掉gid字段的查询耗时0.3秒***/
/***模拟数据在50万时，耗时0.9秒，去掉gid字段的查询耗时0.7秒***/

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 
/***耗时0.5秒，去掉gid字段的查询耗时0.3秒***/
/***模拟数据在50万时，耗时0.9秒，去掉gid字段的查询耗时0.7秒***/

width_bucket说明：

aggregate effects

参考阅读

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

以上是 Postgesql空间点聚合查询实验 的全部内容， 来源链接： utcz.com/z/532227.html

回到顶部