请教各位老哥， PostgreSQL、PostGIS 基于地理空间的查询如何优化速度？

[liuguangxuan]

场景：

可以理解为：记录飞机飞行的轨迹，把经、纬度点，高度、速度、航向存入PostgreSQL数据库，并在经纬度列建立 gist 索引。

数据量大概在 1 亿条左右。

想实现查询指定区域范围（圆形、矩形、多边形）内的轨迹。

测试：

随机在经度（-180°，180°），纬度（-90°，90°）的范围内生成 1 亿个坐标点，保留小数点后 5 位小数，并存入数据库，测试在指定的范围内的查询速度。

查询矩形区域32°*32°的范围，查询出来的记录数约为 150 万条，用时 15 分钟左右。

问题：

1. 想请问各位老哥，如何把查询的时间给优化下去，现在耗时 15 分钟有点儿太长了。
2. 如果不关注实时的点，只关注整体的轨迹线，如何把轨迹线抽取出来？做压缩？这样查询速度会不会快一些。
3. 如果抽取轨迹线的话，如何保留速度、航向、高度等特征值。想以后做分析用，比如突然转向、突然减速、突然高度骤降等。

1. 经纬度列的类型为geometry(Point)，在该列上建立的gist索引。
2. 建立索引语句为create index idx_gistable_jwd on gistable using gist(jwd);，而且用\d gistable查看表的描述的时候，能看到该索引。
3. 查询语句为select count(*) from gistable as t where st_contains('POLYGON((0 0, 32 0, 32 32, 0 32, 0 0))', t.jwd);
4. 检索速度慢一方面和我使用的测试环境有关系，我是用的是腾讯云vps，2核8G内存，PostgreSQL的配置保持安装时的设置。

[nuistzhou]

你的 Gist 是不是有问题呀？一亿条数据也不应该这么久啊。另外，单看返回条数呢？是不是大部分时间花在返回数据本身上了？

[beginor]

经纬度保存成空间数据类型 Geometry 然后再加索引试试，这样可以用上空间索引

[liuguangxuan]

@nuistzhou #1
@beginor #2
老哥，经纬度索引列，应该是没问题，类型为 geometry(Point)，我用\d tablename ，能查到索引。只是建立索引的时候没有添加编码 4326 ，请问这个影响大吗？

另外可能和我测试环境的配置有关系，我用的是腾讯云 2 核 8G 内存，PostgreSQL 的配置文件保持默认的设置。

提高服务器硬件资源配置是一方面，老哥可否指点一下其它提高查询性能的方法。

[liuguangxuan]

@nuistzhou #1 如果单看返回条数的话，使用 select count(*)，时间也差不太多。

[iseki]

explain (analyze on, timing on)看看慢在哪呗

[beginor]

@liuguangxuan 坐标字段声明坐标系，空间数据类型建议使用 SP-GiST 索引类型， 查询时的空间参数也使用相同的坐标系， 空间函数 st_contains 可以改为 st_intersect 或者 && 算符

[a90120411]

别查 Point ，查 Line 。
用点来生成线，在线对象数据中同时保存与点集合的业务数据关联。

[wd]

st_contains 走索引吗？好像不走？

[v2eb]

分析突然减速的这种场景，好像和坐标没有必然的联系吧，还是没有速度这个数据条目？

[nuistzhou]

1. explain 看看吧，前面的老哥提到了，看看是不是 hit 太多了
2. 试试 st_geohash 吧，可以把点聚集起来，然后建个空间索引，hit 应该会降低不少
3. 试试 @ 这个 operator

[liuguangxuan]

@beginor #6 老哥，我大概按你的方法测试了一下，分别测试了 st_contains 、st_intersects 、&&在不同索引（ gist,sp-gist ）下的查询情况，并且换了一台服务器。每次测试均重启了服务器。

总体而言，gist 索引性能好于 sp-gist 索引，首次查询 st_intersects 性能比较好，第二次查询&&性能比较好。

测试结果和老哥说的有点儿出入，能不能帮忙解答一下原因，还是我测试的方式不太对？

[beginor]

SP-GiST 是带分区的 GiST ，至于谁比谁更好，要看具体场景和数据类型，实际上也差不了多少。

[dzdh]

完整 sql 方便贴一下吗

explain(timing, analyze, buffers) 或者是 explain 结果

[liuguangxuan]

换了一台性能比较好的服务器，再加上二次查询的原因，可能内存中有缓存，所以现在比较快，大约 64°*64°的区域，在 6~7 秒左右，老哥还有没有优化的方法？

[liuguangxuan]

@a90120411 #7 老哥，我现在用点生成线了。

但是我如何查询在指定区域(Polygon)内的线(geometry(LineString))呢？即部分包含的线。

我使用 ST_contains 、st_intersects 函数都查不出来。

[a90120411]

@liuguangxuan 用 ST_Covers

[liuguangxuan]

@a90120411 #16
多谢老哥回复。

再请教一下老哥，如图所示，一个 Polygon 和一个 LineString：
1 、我想求相交区域（绿色部分）的线，应该用哪个函数啊？
2 、我想求非相交区域（红色部分）的线，应该用哪个函数啊？

[a90120411]

@a90120411 说错了，应该用 ST_Intersects

[a90120411]

@liuguangxuan 判断包含关系，用 ST_Intersects 。

1 、交集 ST_Intersection

2 、差集 ST_Difference

[liuguangxuan]

@a90120411 #19

1 、交集没问题，一切正常。

2 、但是差集的话，会把旁边的那条无关的线也返回。我想实现只返回有交集的线的差集。😂

[a90120411]

@liuguangxuan 这种情况应该是你的线 Geometry 是多线几何对象（ MULTILINE 一个对象里面包含了多个线），可以把线用 ST_AsText 打印出来看一下结构。如果是 MultiLine 最好是在数据层面处理成单一的 Line 几何。或者对结果再进行一次计算也可以，但是不推荐。

[liuguangxuan]

@a90120411 #21 表中存的不是 MULTILINE ，每个都是一个 LineString 。我是这么写的，其中 gj 字段是 LineString 类型。

```
select ST_Difference( t.gj, ST_PolygonFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)) from test1 t;
```

[liuguangxuan]

@a90120411 #21
最右侧那个直线，跟矩形区域没有任何相交，但是还是查出来了。

[a90120411]

@liuguangxuan
```
select ST_Difference( t.gj, ST_PolygonFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)) from test1 t;
```
我理解你这条 SQL 的意思是遍历了 test1 表中所有的线和多边形进行差集计算。
按照官方文档中对 geometry ST_Difference(geometry geomA, geometry geomB, float8 gridSize = -1)
函数的说明： http://postgis.net/docs/manual-3.2/ST_Difference.html

用 A 与 B 进行差集计算，返回一个几何图形，它表示几何图形 A 与几何图形 B 不相交的部分。
等效于 A - ST_Intersection(A,B)。如果 A 完全包含在 B 中，则返回适当类型的空原子几何。

这是输入顺序很重要的唯一叠加功能。ST_Difference(A, B) 总是返回 A 的一部分。
This is the only overlay function where input order matters. ST_Difference(A, B) always returns a portion of A.

综上所述，你计算得到的结果是正常的。右侧的直线与多边形没有交集，总是返回 A 的一部分。
根据 A - ST_Intersection(A,B) = A 。

根据你以往所述的内容，我对需求的理解是：

1 、飞机的 GPS 点转换为飞机轨迹（线）；
2 、在地图上进行范围查询；

实现步骤：

1 、ST_Intersects 从轨迹线中查询出与传入的范围（多边形）相交的线数据集；
2 、将步骤 1 查询出的线数据与范围几何求交集。

因此我没有理解你最上面那个 SQL 的意义。

此外我已经很多年都不做 Gis 了，手头也没有相关环境，只能是帮你查查官方文档，根据自己的理解提供一些建议，不一定正确，仅供参考。

[liuguangxuan]

@a90120411 #24
感谢老哥的回复。

我也看了官方的那个函数文档，您的分析是正确的。

可能我没有表述清楚，不好意思哈。老哥。

我想实现的有两个：
需求一、给定传入的范围（多边形），求多边形区域内的轨迹。就是与多边形相交，而且在多边形区域内的部分。
需求二、给定传入的范围（多边形），求与多边形相交的轨迹，但是不在多边形内的部分。

其中需求一比较好实现：
使用 ST_Intersection()，直接就能求出来；

需求二呢：
您的分析是正确的，我也看到了官方的文档，我目前也是按照您说的两步来实现的：
1 、先使用 ST_Crosses()，求出和传入范围（多边形）相交的线数据量集；
2 、再使用 ST_Difference()，得到与多边形相交的轨迹，但是不在多边形区域内的部分。

但是感觉实现需求二这种方法比较麻烦，PostGIS 有没有现成的函数一步完成这个操作啊，老哥？

[a90120411]

@liuguangxuan 我没太理解需求二麻烦的原因是什么？
内置函数中应该不会提供这种函数，违反设计原则（单一职责）。
但 PostgreSQL 支持用户自定义函数 FUNCTION 。

[liuguangxuan]

@a90120411 #26 明白啦，老哥。非常感谢指点。

[a90120411]

@liuguangxuan 客气啦，谈不上指点。只是略知一二，互相交流学习。

[liuguangxuan]

@a90120411 #28 老哥可否留个联系方式，以后有问题好向您请教。😂