Greenplum 空间(GIS)数据检索 B-Tree & GiST 索引实践 - 阿里云HybridDB for PostgreSQL最佳实践...

点击查看全文

 

标签

PostgreSQL , GIS , PostGIS , Greenplum , 空间检索 , GiST , B-Tree , geohash

背景

气象数据、地震数据、室内定位、室外定位、手机、车联网、还有我们最喜欢的“左划不喜欢、右划喜欢”，越来越多的位置属性的数据。将来会越来越多。

基于GIS的数据分析、OLTP业务也越来越受到决策者的青睐，例如商场的选址决策，O2O的广告营销等。有很多基于多边形、时间、用户对象属性过滤的需求。

阿里云HybridDB for PostgreSQL是一个支持GIS数据类型处理的MPP分布式数据库，支持海量的GIS数据的存储和分析处理。

支持三种索引接口：

bitmap

btree

gist

三种索引的原理请参考

《PostgreSQL 9种索引的原理和应用场景》

在Greenplum中，GiST索引比较重（是R-Tree结构的空间索引），但是它支持几乎所有的空间搜索，但是overhead相比btree也更大一些：

1、平面、三维、多维对象 几何相交、不相交、相邻。

2、平面、三维、多维对象的方位判断（相交或严格在左边、右边、上边、下边），类似数值的大于、小于、大于等于、小于等于。

3、平面、三维、多维对象 包含 另一个对象

4、平面、三维、多维对象 等于 另一个对象

5、平面、三维、多维对象 与另一个对象的（边、最近、中心点的）距离，按距离排序输出满足条件的行，输出距离在XX以内的行。

PostgreSQL 比Greenplum支持的索引接口更多，比如BRIN是一个很好的块级索引，对于基于多边形的群体分析非常有效。但是Greenplum中没有BRIN。

《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践》

那么Greenplum中能否有类似BRIN块级索引的功能的，在阿里云HybridDB for PostgreSQL中是有这个功能的：

《解密上帝之手 - 阿里云HDB for PostgreSQL数据库metascan特性(存储级、块级、batch级过滤与数据编排)》

但是我们先来说说社区版本的Greenplum，能否用b-tree替代overhead更大的GiST？

答案必须是肯定的。

精度不是那么高的GEOHASH

《geohash vs PostGIS》

geohash的精度随着它的位数变化，例如4位时，它是一个20公里大的BOX。（也就是说一个点，被模糊化为1个方圆20公里的BOX。够粗糙的，但是我们就要用这个粗糙来过滤数据。）

GEOHASH B-Tree一重过滤

使用geohash一重过滤，得到一个大范围的BOX内的数据。

由于GEOHASH是text类型，支持b-tree索引。

当POINT在某个范围(box)内时，它的prefix会和这个box的prefix重叠。

查询某些纵横20公里内的点。  
  
select * from table where st_geohash(pos,15) ~ '^abcd';  

st_distancespheroid二重过滤

一重过滤会得到一个较大范围，例如我们要查询5公里内的人群，实际上返回的是20公里内的人群。

二重过滤，使用距离函数，计算真实的距离，过滤不符合条件的记录。

Name  
  
ST_DistanceSpheroid — Returns the minimum distance between two lon/lat geometries given a particular spheroid. PostGIS versions prior to 1.5 only support points.  
  
Synopsis  
  
float ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid);  

性能评测

 

点击查看全文

转载于:https://my.oschina.net/u/3627103/blog/1524977