PG+POSTGIS地图空间位置网格聚合算法

「爱情、让人受尽委屈。」 2023-07-12 03:55 22阅读 0赞

**目录**

PG核心分包函数WIDTH\_BUCKET

聚合点的归类过程演示

空间聚合效果展示

大规模空间数据方案

--------------------

PG，Postgresql数据库的简称，POSGIS是空间函数扩展支持插件集成了很多数据库级别的空间查询和空间图形数据处理能力。

## PG核心分包函数WIDTH\_BUCKET ##

WIDTH\_BUCKET的原理是将对应的经度、纬度范围值划分为多少份，二这个参数就是地图四至。比如高德地图，它的可见范围，从西至东（经度范围），从南至北（纬度范围），正确的四至是一个数组表示的值如，\[minX,minY,maxX,MaxY\]。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70][]

这里我们的四至（extent）假设为：\[30.431278,117.057442,32.614617，119.235188\]

如果我们要将四至分为x,y轴都分为20等分，那么就如下面的SQL语句：

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

如何测试请看我开源中国上的文章：[Postgesql 空间点聚合查询实验][Postgesql]

## 聚合点的归类过程演示 ##

**规则分布过程，取网格中心点：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 1][]

**不规则过程，取包含点的中心位置：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 2][]

**网格聚合，综合全图展示效果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 3][]

## 空间聚合效果展示 ##

期望效果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 4][]

真实效果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 5][]

## 大规模空间数据方案 ##

云数据库HybridDB for PostgreSQL（ApsaraDB HybridDB for PostgreSQL）是一种在线MPP大规模并行处理数据仓库服务。云数据库HybridDB for PostgreSQL基于Greenplum Database开源数据库项目，并由阿里云深度扩展，支持OSS外部表、JSON数据类型、HyperLogLog预估分析等功能特性。通过符合SQL2008标准查询语法及OLAP分析聚合函数，提供灵活的混合分析能力。支持行存储和列存储混合模式，提高分析性能，同时支持数据压缩技术，降低存储成本。并提供在线扩容、性能监测等服务，用户无需进行复杂的大规模MPP集群运维管理，让DBA、开发人员及数据分析师专注于如何通过SQL提高企业的生产力，创造核心价值。

产品详情：https://www.aliyun.com/product/gpdb

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200306230808331.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ==,size_16,color_FFFFFF,t_70
[Postgesql]: https://my.oschina.net/boonya/blog/3165013
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200306231754668.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200306231846816.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200306231945646.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200306232333570.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200306232455253.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ==,size_16,color_FFFFFF,t_70