基于成都市出租车轨迹的PostgreSQL空间检索性能测试

背景

为了测试PostgreSQL和PostGIS在大数据量下，对轨迹检索的性能，做了此次测试。

滴滴的轨迹数据已经不提供了下载连接，所以无法下载到。从网上搜索后，发现能下载到成都市2014年8月份的出租车轨迹数据，此数据集比较大，共12亿条左右，压缩后为6.7 GB左右。此次仅测试在1亿数据量的条件下，检索轨迹的性能，所以只使用了其中2天的数据，数据量为1.079亿条：

• 20140803_train.txt
• 20140804_train.txt

每一天的数据，单独存放在一个txt里面，以日期命名。

摘取其中的几条数据：

1,30.624806,104.136604,1,2014/8/3 21:18:46
1,30.624809,104.136612,1,2014/8/3 21:18:15
1,30.624811,104.136587,1,2014/8/3 21:20:17
1,30.624811,104.136596,1,2014/8/3 21:19:16
1,30.624811,104.136619,1,2014/8/3 21:17:44

其中数据每一列的含义为：

• 出租车的唯一编号
• 纬度
• 经度
• 是否载客，1：载客，0：空车
• 时间，注意此处的时间并不是严格递增的，所以使用的时候需要排一下序。

轨迹数据百度网盘下载地址：

链接：https://pan.baidu.com/s/1ulNjBhHA7rYeAomq2smH8Q 
提取码：6666

分别对如下3张表进行了检索测试：

• 实时表：存储实时的经纬度点。
• 轨迹表（原始）：从实时表中抽取出来的轨迹线。
• 轨迹表（ST_Simplify抽稀）：将轨迹表（原始）进行抽稀后的轨迹线，轨迹线条目数和上一张表相同，不过对轨迹进行了抽稀压缩。

实时表

数据准备

一共2张表：

1. 临时表：chengdu2，用来中转数据，用完删除即可。
2. 实时表：chengdu，经纬度点表。

因为需要将经纬度点，存储成geometry(Point,4326)类型，但是txt文件中，是以逗号分隔的经纬度点，无法对应上。所以先创建了一个临时表chengdu2，列和txt中的列完全一一对应，把数据导入到临时表，再从临时表中，把数据抽取成geometry(Point,4326)类型，插入到chengdu表中。

创建表

• 临时表chengdu2

  此处需要注意的是，txt文件中，纬度在前，经度在后，所以我们建立临时表的时候，要和txt中的列对应。

  create table chengdu2(
      id int,
      wd numeric,
      jd numeric,
      status int,
      sj timestamp
  );

• 实时表

  create table chengdu(
      id int,
      jwd geometry(Point, 4326),
      status int,
      sj timestamp
  );

导入数据

先把txt数据传到数据库所在服务器的磁盘上，然后将数据导入到临时表中，使用psql的copy命令

copy chengdu2 from '/data/postgres/data/20140803_train.txt' with csv;
copy chengdu2 from '/data/postgres/data/20140804_train.txt' with csv;

然后从临时表提取数据，填入到经纬度点表中去。

insert into chengdu(id, jwd, status, sj) 
select t.id, ST_GeomFromText('Point(' || t.jd || ' ' || t.wd || ')',4326), t.status, t.sj from chengdu2 t;

确保数据全部导入成功后，删除临时表

drop table chengdu2;

建立索引

create index idx_chengdu_id on chengdu(id);
create index idx_chengdu_jwd on chengdu using gist(jwd);
create index idx_chengdu_sj on chengdu(sj);

检索测试

因为成都市中心为环路，依次向外扩大，所以查询条件设置为：以成都市天府广场为中心，依次向外扩大的圆形。

• 成都市中心经纬度：104.066737,30.657399
• 每增长0.001度，粗略估算为增长100 m。
• 每增长0.01度，粗略估算为增长1 km。

检索语句为：

select * from chengdu as t where
st_contains(ST_Buffer(ST_GeomFromText('Point(104.066737 30.657399)', 4326), 0.001),
t.jwd);

检索结果为：

轨迹表（原始）

上述测试，仅仅是测试了轨迹点的查询，再来测试一下实际轨迹线的查询性能。

将同一天，同一辆车的经纬度点，按照时间的顺序，连接成一条线，称作为一条轨迹。

数据准备

创建表

create table chengdu_guiji(
    id int,
    gj geometry(LineString, 4326),
    sj date
);

抽取数据

因为使用了2天的数据，所以使用以下两条sql语句，将轨迹抽取出来。

此处需要注意的是，因为数据中的时间并不是严格递增的，所以将经纬度点抽取成轨迹的时候，需要按时间递增排一下序，否则轨迹是错误的。

抽取的总轨迹条数为27247条。

insert into chengdu_guiji
(select t.id, ST_MakeLine(t.jwd order by t.sj), '2014-08-03' from chengdu t where date(t.sj) = '2014-08-03' group by t.id);

insert into chengdu_guiji
(select t.id, ST_MakeLine(t.jwd order by t.sj), '2014-08-04' from chengdu t where date(t.sj) = '2014-08-04' group by t.id);

建立索引

create index idx_chengdu_guiji_gj on chengdu_guiji using gist(gj);
create index idx_chengdu_guiji_sj on chengdu_guiji(sj);

抽取出来的所有轨迹，效果如下图所示。

检索测试

此处仅仅检测和指定矩形区域ST_MakeEnvelope()相交的线，并没有截取区域内的线段。

检索语句为：

SELECT ST_NPoints(t.gj), t.gj FROM chengdu_guiji WHERE ST_Intersects(gj, ST_MakeEnvelope(104.046737, 30.457399, 104.086737, 30.857399, 4326)); 

检索结果为：

注：

• psql检索耗时：在数据库服务器上用Linux命令行终端查询，所以是仅仅检索的耗时，不包含网络传输的耗时。
• pgAdmin检索耗时：在Windows机器上，用pgAdmin客户端连接数据库服务器查询，所以包含检索、网络传输的耗时。

轨迹表（ST_Simplify抽稀）

geometry ST_Simplify(geometry geomA, float tolerance, boolean preserveCollapsed);

将chengdu_guiji表中的数据进行抽稀，采用PostGIS提供的ST_Simplify函数进行轨迹点的提取。函数声明如下所示，其中参数：

• geomA：需要进行抽稀的轨迹线；
• tolerance：容差，此处取值0.001。因为经过测试，在本应用场景中，取值为0.001时，既能最大限度的压缩轨迹点，又能比较完成的保留轨迹拓扑结构。具体容差值的选取，可以参考道格拉斯-普克算法，并结合实际不同经纬度点之间的差值选择合适的值。

数据准备

创建表

create table chengdu_guiji_0001(
    id int,
    gj geometry(LineString, 4326),
    sj date
);

表名的后缀0001代表抽稀函数中的参数tolerance值0.001，如果以后用其他值做为参数的话，可以用同样的命名方式另建一张表用以区分。

抽取数据

抽稀步骤如下：

insert into chengdu_guiji_0001
select t.id, ST_Simplify(t.gj, 0.001), t.sj from chengdu_guiji t;

建立索引

create index idx_chengdu_guiji_0001_gj on chengdu_guiji_0001 using gist(gj);
create index idx_chengdu_guiji_0001_sj on chengdu_guiji_0001(sj);

检索测试

检索过程同未压缩章节，只是检索的时间有些差异，实际测试情况如下表所述：

注：

• 上述表格中，检索耗时仅仅为检索与指定的矩形相交的轨迹线的时间，并不包含：

  • 裁剪出矩形内区域的时间。如果需要将相交的轨迹线做区域内和区域外的区分，还需要进一步进行交集裁剪，可使用ST_Intersection()函数。
  • 在界面上绘制显示的时间。后台服务将检索结果传输至web前端，web前端需要在界面上绘制，也需要部分时间呈现效果。

抽稀前、后结果对比

随便选取两条轨迹，分别从坐标点数、检索速度、拓扑结构三个维度做一下对比。

坐标点数

检索语句为：

select ST_NPoints(t.gj) from chengdu_guiji t where t.id = 269 and t.sj = '2014-08-03' limit 1;
select ST_NPoints(t.gj) from chengdu_guiji_0001 t where t.id = 269 and t.sj = '2014-08-03' limit 1;

select ST_NPoints(t.gj) from chengdu_guiji t where t.id = 428 and t.sj = '2014-08-03' limit 1;
select ST_NPoints(t.gj) from chengdu_guiji_0001 t where t.id = 428 and t.sj = '2014-08-03' limit 1;

检索速度

检索速度见上面的两个表格，优化后，使用psql检索相交的轨迹线，时间在1秒内，用pgAdmin检索时间在3秒内。

拓扑结构

经过该函数抽稀后，在此场景中，拓扑结构基本保持不变，但是轨迹点数大大降低。

随便抽取一些轨迹线做对比，左侧为原始轨迹线，右侧为抽稀后的轨迹线：

select ST_NPoints(t.gj), t.gj from chengdu_guiji t where t.id = 269 and t.sj = '2014-08-03' limit 1;
select ST_NPoints(t.gj), t.gj from chengdu_guiji_0001 t where t.id = 269 and t.sj = '2014-08-03' limit 1;

select ST_NPoints(t.gj), t.gj from chengdu_guiji t where t.id = 428 and t.sj = '2014-08-03' limit 1;
select ST_NPoints(t.gj), t.gj from chengdu_guiji_0001 t where t.id = 428 and t.sj = '2014-08-03' limit 1;

注：

抽稀后细节部分可能有稍微的差异。仔细查看上述检索速度测试的两个表格，会发现最后一列，检索出来条数不一致的现象，问题出在抽稀过程中，会对经纬度点进行抽取，轨迹线的细节部位可能稍微有些失真，具体可以看以下轨迹线：

• id：13919
• sj：2014-08-04

左侧为原始轨迹线，右侧为抽稀后的轨迹线。可以看到左侧和指定区域没有交集，右侧和指定区域有交集。当检索的区域正好和轨迹线距离很近的时候会出现此类问题。

结论

1. 当数据量较大时，网络传输耗时较长。

2. 直接用ST_Intersection()函数，查检索区域内的轨迹段的话，耗时比较长，原因为：PostgreSQL执行的是顺序扫描，没有走索引。

   • 如果有截取区域内的轨迹段的需求的话，通常做法是分两步筛选：

     • 先使用ST_Intersects()检索出来相交的轨迹，称为集合A。此时走索引，检索效率较高。
     • 从集合A中再次筛选出来相交的部分。此时不走索引，顺序遍历，检索效率和轨迹的条数成正比。
3. 原始轨迹点不经过压缩，查询时间较长。经过压缩后，查询速度较快，基本上在3秒之内能返回数据。

硬件配置

配置：
20核64G
cpu：Intel(R) Core(TM) i9-10900 CPU @ 2.80GHz
硬盘：500G NVME硬盘
数据库：
ssh终端登录，切换到postgres账户，用psql，可以直接连接数据库。
# psql
# \c gis