请教各位老哥， PostgreSQL、PostGIS 基于地理空间的查询如何优化速度？
liuguangxuan · 2022-05-14 11:52:16 +08:00 · 3329 次点击这是一个创建于 942 天前的主题，其中的信息可能已经有所发展或是发生改变。
场景：
可以理解为：记录飞机飞行的轨迹，把经、纬度点，高度、速度、航向存入PostgreSQL数据库，并在经纬度列建立 gist 索引。
数据量大概在 1 亿条左右。
想实现查询指定区域范围（圆形、矩形、多边形）内的轨迹。
测试：
随机在经度（-180°，180°），纬度（-90°，90°）的范围内生成 1 亿个坐标点，保留小数点后 5 位小数，并存入数据库，测试在指定的范围内的查询速度。
查询矩形区域32°*32°的范围，查询出来的记录数约为 150 万条，用时 15 分钟左右。
问题：
- 想请问各位老哥，如何把查询的时间给优化下去，现在耗时 15 分钟有点儿太长了。
- 如果不关注实时的点，只关注整体的轨迹线，如何把轨迹线抽取出来？做压缩？这样查询速度会不会快一些。
- 如果抽取轨迹线的话，如何保留速度、航向、高度等特征值。想以后做分析用，比如突然转向、突然减速、突然高度骤降等。
第 1 条附言 · 2022-05-14 12:42:43 +08:00
- 经纬度列的类型为
geometry(Point)，在该列上建立的gist索引。 - 建立索引语句为
create index idx_gistable_jwd on gistable using gist(jwd);，而且用\d gistable查看表的描述的时候，能看到该索引。 - 查询语句为
select count(*) from gistable as t where st_contains('POLYGON((0 0, 32 0, 32 32, 0 32, 0 0))', t.jwd); - 检索速度慢一方面和我使用的测试环境有关系，我是用的是腾讯云vps，2核8G内存，
PostgreSQL的配置保持安装时的设置。
36 条回复 • 2022-08-31 19:22:02 +08:00
 |
1
nuistzhou 2022-05-14 12:14:57 +08:00 via iPhone
你的 Gist 是不是有问题呀？一亿条数据也不应该这么久啊。另外，单看返回条数呢？是不是大部分时间花在返回数据本身上了？
|
 |
2
beginor 2022-05-14 12:34:16 +08:00 via Android
经纬度保存成空间数据类型 Geometry 然后再加索引试试，这样可以用上空间索引
|
 |
3
liuguangxuan OP |
|
4
liuguangxuan OP @nuistzhou #1 如果单看返回条数的话，使用 select count(*)，时间也差不太多。
|
 |
5
iseki 2022-05-14 13:12:05 +08:00  1
explain (analyze on, timing on)看看慢在哪呗
|
|
6
beginor 2022-05-14 13:12:54 +08:00 via Android 1
@liuguangxuan 坐标字段声明坐标系，空间数据类型建议使用 SP-GiST 索引类型， 查询时的空间参数也使用相同的坐标系， 空间函数 st_contains 可以改为 st_intersect 或者 && 算符
|
 |
7
a90120411 2022-05-14 13:29:23 +08:00
别查 Point ，查 Line 。
用点来生成线，在线对象数据中同时保存与点集合的业务数据关联。 |
 |
8
wd 2022-05-14 14:20:09 +08:00 via iPhone
st_contains 走索引吗？好像不走？
|
 |
9
v2eb 2022-05-14 14:45:34 +08:00 via Android
分析突然减速的这种场景，好像和坐标没有必然的联系吧，还是没有速度这个数据条目？
|
|
10
nuistzhou 2022-05-14 15:49:26 +08:00 via iPhone
1. explain 看看吧，前面的老哥提到了，看看是不是 hit 太多了
2. 试试 st_geohash 吧，可以把点聚集起来，然后建个空间索引，hit 应该会降低不少 3. 试试 @ 这个 operator |
|
11
liuguangxuan OP @beginor #6 老哥，我大概按你的方法测试了一下，分别测试了 st_contains 、st_intersects 、&&在不同索引（ gist,sp-gist ）下的查询情况，并且换了一台服务器。每次测试均重启了服务器。
总体而言，gist 索引性能好于 sp-gist 索引，首次查询 st_intersects 性能比较好，第二次查询&&性能比较好。 测试结果和老哥说的有点儿出入，能不能帮忙解答一下原因，还是我测试的方式不太对？  |
|
12
beginor 2022-05-17 09:37:44 +08:00 via Android
SP-GiST 是带分区的 GiST ，至于谁比谁更好，要看具体场景和数据类型，实际上也差不了多少。
|
 |
13
dzdh 2022-05-17 15:33:16 +08:00
完整 sql 方便贴一下吗
explain(timing, analyze, buffers) 或者是 explain 结果 |
|
14
liuguangxuan OP 换了一台性能比较好的服务器，再加上二次查询的原因，可能内存中有缓存，所以现在比较快，大约 64°*64°的区域，在 6~7 秒左右，老哥还有没有优化的方法？
 |
|
15
liuguangxuan OP @a90120411 #7 老哥，我现在用点生成线了。
但是我如何查询在指定区域(Polygon)内的线(geometry(LineString))呢？即部分包含的线。 我使用 ST_contains 、st_intersects 函数都查不出来。 |
|
16
a90120411 2022-05-27 10:41:54 +08:00
@liuguangxuan 用 ST_Covers
|
|
17
liuguangxuan OP @a90120411 #16
 多谢老哥回复。 再请教一下老哥，如图所示，一个 Polygon 和一个 LineString： 1 、我想求相交区域（绿色部分）的线，应该用哪个函数啊？ 2 、我想求非相交区域（红色部分）的线，应该用哪个函数啊？ |
|
19
a90120411 2022-05-27 11:04:12 +08:00
|
|
20
liuguangxuan OP |
|
21
a90120411 2022-05-27 11:52:55 +08:00
@liuguangxuan 这种情况应该是你的线 Geometry 是多线几何对象（ MULTILINE 一个对象里面包含了多个线），可以把线用 ST_AsText 打印出来看一下结构。如果是 MultiLine 最好是在数据层面处理成单一的 Line 几何。或者对结果再进行一次计算也可以，但是不推荐。
|
|
22
liuguangxuan OP @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; ``` |
|
23
liuguangxuan OP |
|
24
a90120411 2022-05-27 13:07:15 +08:00 1
@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 了，手头也没有相关环境，只能是帮你查查官方文档，根据自己的理解提供一些建议，不一定正确，仅供参考。 |
|
25
liuguangxuan OP @a90120411 #24
感谢老哥的回复。 我也看了官方的那个函数文档，您的分析是正确的。 可能我没有表述清楚，不好意思哈。老哥。 我想实现的有两个： 需求一、给定传入的范围（多边形），求多边形区域内的轨迹。就是与多边形相交，而且在多边形区域内的部分。 需求二、给定传入的范围（多边形），求与多边形相交的轨迹，但是不在多边形内的部分。 其中需求一比较好实现： 使用 ST_Intersection()，直接就能求出来； 需求二呢： 您的分析是正确的，我也看到了官方的文档，我目前也是按照您说的两步来实现的： 1 、先使用 ST_Crosses()，求出和传入范围（多边形）相交的线数据量集； 2 、再使用 ST_Difference()，得到与多边形相交的轨迹，但是不在多边形区域内的部分。 但是感觉实现需求二这种方法比较麻烦，PostGIS 有没有现成的函数一步完成这个操作啊，老哥？ |
|
26
a90120411 2022-05-27 14:55:36 +08:00 1
|
|
27
liuguangxuan OP @a90120411 #26 明白啦，老哥。非常感谢指点。
|
|
28
a90120411 2022-05-27 17:04:32 +08:00
@liuguangxuan 客气啦，谈不上指点。只是略知一二，互相交流学习。
|
|
29
liuguangxuan OP @a90120411 #28 老哥可否留个联系方式，以后有问题好向您请教。😂
|
|
30
liuguangxuan OP @a90120411 #7 老哥，之前您说过在线对象中保存与点集合的业务数据关联，请问这一步如何操作呢？
我的疑问点是： 我有一张轨迹点的表，里面存有经纬度点，还有速度、高度等业务数据，现在我新建了一张轨迹线表，把点表中的经纬度字段使用 ST_Simplify(ST_MakeLine(t.jwd))给抽取出来了，但是这两个表如何关联起来呢？关联到每个点的速度、高度字段。 或者是使用 ST_Simplify 的时候，保留其他维度（速度、高度）的信息？ ============================= 别查 Point ，查 Line 。 用点来生成线，在线对象数据中同时保存与点集合的业务数据关联。 |
|
31
a90120411 2022-08-30 15:32:57 +08:00
@liuguangxuan #30 这种情况解决方法挺多的，默认就是查线对象，拿到点的 XY 之后去点集合表中去查询对应的数据就可以，空间索引是生效的。如果速度不理想，你可以把生成线的那些点的 FID ，使用集合存到线对象的字段中（按照顺序），查询线的时候从这个集合里面取出来 FID ，然后去点集合表中去查。
此外我以前常用的方式是把空间数据（只存放涉及地理查询分析的数据）与业务数据分离，通过 ID 进行一对一的关联。例如你把轨迹点的表，拆成两个表，空间表中的每条记录都有一个与之相关的业务数据记录 ID 。业务表中的每条记录也有一个空间记录的 FID 。或者建立一个中间表，里面可以建立业务数据记录与多个空间数据表的关联性，也就是一条业务记录可以关联多个空间几何要素表。在甚者你可以考虑增加冗余来换取速度，直接在线集合表中扔个 JSON 。 再复杂的话，也可以用拓扑结构来存对应关系。上述的这些方法都要根据实际业务需求来进行设计开发，集合数据使用场景频次，针对性的优化。 |
|
32
liuguangxuan OP @a90120411 #31 感谢老哥的耐心解答，看了您的回答，我有 3 点疑问：
1 、假如 XY 经纬度点有重合怎么办？这种情况下就不能用经纬度点 XY 做为 key 去点集合表中查询对应的点记录了，因为查询出来的数据是多个。场景为：出租车原地掉了个头，此时采集的精度没有那么高，XY 值一致，但是航向却变化了 180 度，查询出来的数据为多条。 2 、空间表和业务表的 FID 如何关联起来？因为抽稀使用的是 ST_Simplify 这个函数，假如从 1 万个点里面抽取了 200 个点，我不知道是哪 200 个点，上面提到的根据 XY 反差结果不靠谱，所以也无法关联这 200 个点的 FID 。 3 、拓扑结构如何来存储对应的关系呢？ |
|
33
a90120411 2022-08-31 16:37:19 +08:00
@liuguangxuan
1 、这种情况我暂时没想什么好的解决方法。 2 、自带的不行，自己写一个 函数 （存储过程）吧。 https://en.wikipedia.org/wiki/Ramer%E2%80%93Douglas%E2%80%93Peucker_algorithm Java 版： https://github.com/locationtech/jts/blob/444903159c8eab21b5ee33e05297c64e17022e9b/modules/core/src/main/java/org/locationtech/jts/simplify/DouglasPeuckerSimplifier.java JS 版： https://github.com/Turfjs/turf/blob/84110709afda447a686ccdf55724af6ca755c1f8/packages/turf-simplify/lib/simplify.js 3 、建议你看一下官方文档，本质上是一种 graph 数据结构，常用于路径分析。 --------- 我觉得讨论的解决方法趋向复杂化了，感觉你的业务场景应该是比较简单的 GPS 轨迹回放。 ST_MakeLine 生成线，然后 ST_Simplify 抽稀的方法性能可能是瓶颈。 从入库阶段做好数据预处理是否是较好的解决方法呢？例如点数据入库之前，预先根据容差值，标记一个关键点数据出来（把抽稀的过程提前做了）。 我好久没做一线的 GIS 开发了，回答的内容仅供你参考。 |
|
34
liuguangxuan OP @a90120411 #33 感谢老哥的回复，明白你的意思了。
请教一下您另外一个问题，假如我现在有很多的轨迹线了，飞机的或者出租车的都行，对这些轨迹线进行分析，我能做哪些有趣的事情呢？这方面老哥有无书籍推荐一些，轨迹分析相关的。 |
|
35
a90120411 2022-08-31 18:03:06 +08:00
@liuguangxuan 可以看看国外的 航空协会或者交通部门的数据报告，里面应该有不同维度的分析案例。
书籍没有推荐的，网站可以推荐几个供你参考： 1 、https://doc.arcgis.com/en/arcgis-storymaps/gallery/ 2 、https://www.flightradar24.com/blog/ 3 、https://www.uber.com/nl/en/community/supporting-cities/data/ 4 、https://mapoftheweek.blogspot.com/search/label/traffic 5 、https://blog.mapbox.com/data-visualization/home 6 、https://cnr.ncsu.edu/geospatial/ |
|
36
liuguangxuan OP @a90120411 好嘞，多谢老哥，🐮。
|
Select Language