PostGIS 多点几何类型 空字符构造异常CASE - parse error - invalid geometry (lwgeom_pg.c:96)

背景

某个业务中存储了一批用户的访问轨迹数据，（每个店铺访问的次数，包括店铺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

digoal’s 大量PostgreSQL文章入口