PostGIS 点面叠加视觉判断输出
背景
在新零售、快递等行业,有大量的点数据(例如包裹位置、快递员位置、仓库位置等),同时有大量的面数据(如小区,商圈,写字楼等)。
如何判断实时的正在配送的包裹落在哪个面呢?并且将之联系起来。
这个从视觉角度来思考,非常简单。
例如有一个地图,将其划分为若干个面(例如前面提到的小区)。
然后有一些小点,这些是POINT数据。
我们从图上一眼就能看出每个点落在哪个小区(面)里面。
在数据库中,这可能是两份数据(一份为点,一份为面)。输出的实际上是点+面(ID)的数据。
怎么做到高效的输出呢?
DEMO
搜索某些订单,当前处于哪个面。这是非常典型的点面判断需求。
接下来的例子,有25万个面,查询若干笔订单属于哪个面。
1、创建、生成静态的面数据(通常面的数据是静态的,例如小区,商圈,大楼,仓库覆盖范围等)
postgres=# create table t2(id int, pos box);
CREATE TABLE
-- 在(0,0)到(500,500)的平面上,划分成251001个正方形的小面。
postgres=# do language plpgsql $$
declare
x int;
y int;
begin
for x in 0..500 loop
for y in 0..500 loop
insert into t2 values (x+y, box(point(x,y),point(x+1,y+1)));
end loop;
end loop;
end;
$$;
DO
postgres=# select count(*) from t2;
count
--------
251001
(1 row)
创建空间索引
postgres=# create index idx_t2 on t2 using gist(pos);
CREATE INDEX
2、创建、生成点的数据。
postgres=# create table t1(id int, pos point);
CREATE TABLE
-- 在(0,0),(500,500)的平面上,生成10000个随机的点
postgres=# insert into t1 select id, point(random()*500, random()*500) from generate_series(1,10000) t(id);
INSERT 0 10000
3、查询每个点,属于哪个面。
方法1,JOIN
postgres=# explain analyze select t1.*,t2.* from t1 join t2 on (t2.pos @> box(t1.pos, t1.pos));
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.29..73322.20 rows=2510010 width=56) (actual time=0.094..1191.076 rows=10000 loops=1)
-> Seq Scan on t1 (cost=0.00..116.00 rows=10000 width=20) (actual time=0.020..1.047 rows=10000 loops=1)
-> Index Scan using idx_t2 on t2 (cost=0.29..4.81 rows=251 width=36) (actual time=0.039..0.118 rows=1 loops=10000)
Index Cond: (pos @> box(t1.pos, t1.pos))
Planning time: 0.102 ms
Execution time: 1191.619 ms
(6 rows)
方法2,SUBQUERY
postgres=# explain analyze select t1.*, (select t2 from t2 where t2.pos @> box(t1.pos,t1.pos) limit 1) from t1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..13706.74 rows=10000 width=52) (actual time=0.077..427.466 rows=10000 loops=1)
SubPlan 1
-> Limit (cost=0.29..1.36 rows=1 width=60) (actual time=0.042..0.042 rows=1 loops=10000)
-> Index Scan using idx_t2 on t2 (cost=0.29..269.88 rows=251 width=60) (actual time=0.042..0.042 rows=1 loops=10000)
Index Cond: (pos @> box(t1.pos, t1.pos))
Planning time: 0.080 ms
Execution time: 427.942 ms
(7 rows)
如果是1000笔订单,返回差不多40毫秒
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.*, (select t2 from t2 where t2.pos @> box(t1.pos,t1.pos) limit 1) from t1 limit 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1370.67 rows=1000 width=52) (actual time=0.069..39.754 rows=1000 loops=1)
Output: t1.id, t1.pos, ((SubPlan 1))
Buffers: shared hit=3002
-> Seq Scan on public.t1 (cost=0.00..13706.74 rows=10000 width=52) (actual time=0.069..39.658 rows=1000 loops=1)
Output: t1.id, t1.pos, (SubPlan 1)
Buffers: shared hit=3002
SubPlan 1
-> Limit (cost=0.29..1.36 rows=1 width=60) (actual time=0.039..0.039 rows=1 loops=1000)
Output: t2.*
Buffers: shared hit=3000
-> Index Scan using idx_t2 on public.t2 (cost=0.29..269.88 rows=251 width=60) (actual time=0.039..0.039 rows=1 loops=1000)
Output: t2.*
Index Cond: (t2.pos @> box(t1.pos, t1.pos))
Buffers: shared hit=3000
Planning time: 0.066 ms
Execution time: 39.830 ms
(16 rows)
因为@>
暂时不支持hash join,因此subquery更优一些。
本文没有用到PostGIS空间数据库插件,而是使用了内置的平面几何类型,用于演示。
真实场景请使用PostGIS。
http://postgis.net/
例如
select t1.*, (select t2 from t2 where ST_Within(t1.geom, t2.geom) limit 1) from t1;
小结
点面判断在GIS信息崛起的今天,在越来越多的企业中成为了非常常见的需求,比如文中提到的。
PostgreSQL在空间数据库领域有非常丰富的应用,从科研、军工、商业到民用,无处不在。
结合空间索引,BRIN索引实现空间数据的高效率检索是很轻松的事情。