Postgesql空间点聚合查询实验

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

回到顶部