PostgreSQL 生成空间热力图

标签

PostgreSQL , 热力图 , 空间切割 , 并行计算 , parallel safe

---

背景

结合空间数据，计算基于地理位置信息的热力图，在空间数据可视化场景中是一个非常常见的需求。

结合流计算，可以实现实时的热力图计算。

结合并行计算，可以高效率的对海量数据进行热力图计算。

计算热力图中bucket的方法

https://www.postgresql.org/docs/devel/static/functions-math.html

width_bucket(operand dp, b1 dp, b2 dp, count int)	  
int	  
return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2;   
returns 0 or count+1 for an input outside the range	  
width_bucket(5.35, 0.024, 10.06, 5)	  
3  
  
width_bucket(operand numeric, b1 numeric, b2 numeric, count int)	  
int	  
return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2;   
returns 0 or count+1 for an input outside the range	  
width_bucket(5.35, 0.024, 10.06, 5)	  
3  

例如

postgres=# select width_bucket(1,1,10,10);  
 width_bucket   
--------------  
            1  
(1 row)  
  
postgres=# select width_bucket(0,1,10,10);  
 width_bucket   
--------------  
            0  
(1 row)  
  
postgres=# select width_bucket(10,1,10,10);  
 width_bucket   
--------------  
           11  
(1 row)  
  
postgres=# select width_bucket(9.9,1,10,10);  
 width_bucket   
--------------  
           10  
(1 row)  

width_bucket(  
  p1 -- 输入值  
  p2 -- 边界值（最小，包含）  
  p3 -- 边界值（最大，不包含）  
  p4 -- 切割份数  
)  
  
当小于最小边界值时，返回0  
当大于等于最大边界值时，返回p4+1  

例如x轴的边界是1,10000，y轴的边界是1,10000。

x,y两个方向分别切割为50个bucket，一共2500个bucket，求一个点落在哪个bucket：

width_bucket(pos[0], 1, 10001, 50),  -- x轴落在哪列bucket  
width_bucket(pos[1], 1, 10001, 50),  -- y轴落在哪列bucket  

例子

1、建表

create table tbl_pos(  
  id int,    
  info text,   -- 信息  
  val float8,  -- 取值  
  pos point    -- 位置  
);  

2、写入1亿个点

vi test.sql  
insert into tbl_pos values ( random()*100000, md5(random()::text), random()*1000, point((random()*10000::int), (random()*10000::int)) );  
  
  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 2000000