大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说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);
/***插入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秒***/代码100分
aggregate_search
代码100分/***聚合查询效率测试，查询最大量***/
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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/10004.html
