作者
digoal
日期
2017-03-28
标签
PostgreSQL , PostGIS , 多点几何 , 空
背景
某个业务中存储了一批用户的访问轨迹数据，（每个店铺访问的次数，包括店铺ID，访问次数：1:1, 2:1即1号店访问了1次，2号店访问了1次）。
业务方有一个需求，根据店铺的访问次数圈出一部分人群，比如A店铺访问超出多少次的，或者B店铺访问超过多少次的等。
如果让你来处理，你会使用什么技术呢？
数据结构：店铺ID上亿、用户数上亿、访问次数不定。
如果裸算，会耗费大量的CPU。
PostGIS是一个空间数据库，如果将这些数据转换为空间数据，则可以使用空间函数来实现圈人的目的，比如圈人可以表示为与某条线段相交。这个操作可以使用PostGIS的空间索引来完成。
(目前release的postgis版本不支持这个QUERY, 解决办法参考我的下一篇文档)
于是用户把数据转换为几何类型来实现这个业务需求。
这两个函数，可以将multipoint构造为几何类型
http://postgis.net/docs/manual-2.3/ST_MPointFromText.html
http://postgis.net/docs/manual-2.3/ST_GeomFromText.html
```
ST_MPointFromText
ST_GeomFromText
```
但是他们遇到了一个问题
```
select ST_MPointFromText('MULTIPOINT('||replace(replace(feeds,':', ' '),';',',')||')') from test limit 2; -- 正常
select ST_MPointFromText('MULTIPOINT('||replace(replace(feeds,':', ' '),';',',')||')') from test limit 3; -- 报错
ERROR: parse error - invalid geometry (lwgeom_pg.c:96) (seg16 slice1 ..... pid=15695) (cdbdisp.c:1326)
```
这是GPDB报的错。
原因查找
一开始，我以为是第三条数据有误，所以就这样试验，结果发现还是有问题
select ST_MPointFromText('MULTIPOINT('||replace(replace(feeds,':', ' '),';',',')||')') from test offset 3 limit 1; -- 报错
直接查询test.feeds，发现里面有一些空数据，这些空的数据，导致了postgis在转换时报错.
测试如下
```
create table test(feeds text);
insert into test values ('');
insert into test values ('1:1;100:2');
test=> select ST_MPointFromText('MULTIPOINT('||replace(replace(feeds,':', ' '),';',',')||')') from test;
ERROR: parse error - invalid geometry
HINT: "MULTIPOINT()" <-- parse error at position 13 within geometry
CONTEXT: SQL function "st_mpointfromtext" statement 1
```
解决方法
将''空字符串，转换为0坐标，或者过滤这些数据即可。
select ST_MPointFromText('MULTIPOINT('||replace(replace(feeds,':', ' '),';',',')||')') from test where feeds<>'';
或者
select case when feeds='' then ST_MPointFromText('MULTIPOINT(0 0)') else ST_MPointFromText('MULTIPOINT('||replace(replace(feeds,':', ' '),';',',')||')') end from test;
空间函数索引
建立几何类型的空间函数索引
test=> create index idx_test_feeds on test using gist ((case when feeds='' then ST_MPointFromText('MULTIPOINT(0 0)') else ST_MPointFromText('MULTIPOINT('||replace(replace(feeds,':', ' '),';',',')||')') end));
CREATE INDEX
test=> \d test
Table "public.test"
Column | Type | Modifiers
--------+------+-----------
feeds | text |
Indexes:
"idx_test_feeds" gist ((
CASE
WHEN feeds = ''::text THEN st_mpointfromtext('MULTIPOINT(0 0)'::text)
ELSE st_mpointfromtext(('MULTIPOINT('::text || replace(replace(feeds, ':'::text, ' '::text), ';'::text, ','::text)) || ')'::text)
END))
插入两条测试数据，表示访问1,2号店铺的次数分别是1次，100次。
test=> insert into test values ('1:1');
INSERT 0 1
test=> insert into test values ('1:100');
INSERT 0 1
test=> insert into test values ('2:1');
INSERT 0 1
test=> insert into test values ('2:100');
INSERT 0 1
圈人应用举例
参考我的下一篇文章
参考
http://postgis.net/docs/manual-2.3/ST_MPointFromText.html
http://postgis.net/docs/manual-2.3/ST_GeomFromText.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
