V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
liuguangxuan
V2EX  ›  PostgreSQL

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

  •  1
     
  •   liuguangxuan · 2022-05-14 11:52:16 +08:00 · 2987 次点击
    这是一个创建于 685 天前的主题,其中的信息可能已经有所发展或是发生改变。

    场景:

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

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

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

    测试:

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

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

    问题:

    1. 想请问各位老哥,如何把查询的时间给优化下去,现在耗时 15 分钟有点儿太长了。
    2. 如果不关注实时的点,只关注整体的轨迹线,如何把轨迹线抽取出来?做压缩?这样查询速度会不会快一些。
    3. 如果抽取轨迹线的话,如何保留速度、航向、高度等特征值。想以后做分析用,比如突然转向、突然减速、突然高度骤降等。
    第 1 条附言  ·  2022-05-14 12:42:43 +08:00
    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的配置保持安装时的设置。
    36 条回复    2022-08-31 19:22:02 +08:00
    nuistzhou
        1
    nuistzhou  
       2022-05-14 12:14:57 +08:00 via iPhone
    你的 Gist 是不是有问题呀?一亿条数据也不应该这么久啊。另外,单看返回条数呢?是不是大部分时间花在返回数据本身上了?
    beginor
        2
    beginor  
       2022-05-14 12:34:16 +08:00 via Android
    经纬度保存成空间数据类型 Geometry 然后再加索引试试,这样可以用上空间索引
    liuguangxuan
        3
    liuguangxuan  
    OP
       2022-05-14 12:47:03 +08:00
    @nuistzhou #1
    @beginor #2
    老哥,经纬度索引列,应该是没问题,类型为 geometry(Point),我用\d tablename ,能查到索引。只是建立索引的时候没有添加编码 4326 ,请问这个影响大吗?

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

    提高服务器硬件资源配置是一方面,老哥可否指点一下其它提高查询性能的方法。
    liuguangxuan
        4
    liuguangxuan  
    OP
       2022-05-14 12:48:45 +08:00
    @nuistzhou #1 如果单看返回条数的话,使用 select count(*),时间也差不太多。
    iseki
        5
    iseki  
       2022-05-14 13:12:05 +08:00   ❤️ 1
    explain (analyze on, timing on)看看慢在哪呗
    beginor
        6
    beginor  
       2022-05-14 13:12:54 +08:00 via Android   ❤️ 1
    @liuguangxuan 坐标字段声明坐标系,空间数据类型建议使用 SP-GiST 索引类型, 查询时的空间参数也使用相同的坐标系, 空间函数 st_contains 可以改为 st_intersect 或者 && 算符
    a90120411
        7
    a90120411  
       2022-05-14 13:29:23 +08:00
    别查 Point ,查 Line 。
    用点来生成线,在线对象数据中同时保存与点集合的业务数据关联。
    wd
        8
    wd  
       2022-05-14 14:20:09 +08:00 via iPhone
    st_contains 走索引吗?好像不走?
    v2eb
        9
    v2eb  
       2022-05-14 14:45:34 +08:00 via Android
    分析突然减速的这种场景,好像和坐标没有必然的联系吧,还是没有速度这个数据条目?
    nuistzhou
        10
    nuistzhou  
       2022-05-14 15:49:26 +08:00 via iPhone
    1. explain 看看吧,前面的老哥提到了,看看是不是 hit 太多了
    2. 试试 st_geohash 吧,可以把点聚集起来,然后建个空间索引,hit 应该会降低不少
    3. 试试 @ 这个 operator
    liuguangxuan
        11
    liuguangxuan  
    OP
       2022-05-16 22:02:09 +08:00
    @beginor #6 老哥,我大概按你的方法测试了一下,分别测试了 st_contains 、st_intersects 、&&在不同索引( gist,sp-gist )下的查询情况,并且换了一台服务器。每次测试均重启了服务器。

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

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

    beginor
        12
    beginor  
       2022-05-17 09:37:44 +08:00 via Android
    SP-GiST 是带分区的 GiST ,至于谁比谁更好,要看具体场景和数据类型,实际上也差不了多少。
    dzdh
        13
    dzdh  
       2022-05-17 15:33:16 +08:00
    完整 sql 方便贴一下吗

    explain(timing, analyze, buffers) 或者是 explain 结果
    liuguangxuan
        14
    liuguangxuan  
    OP
       2022-05-17 15:39:17 +08:00
    换了一台性能比较好的服务器,再加上二次查询的原因,可能内存中有缓存,所以现在比较快,大约 64°*64°的区域,在 6~7 秒左右,老哥还有没有优化的方法?

    liuguangxuan
        15
    liuguangxuan  
    OP
       2022-05-27 09:42:36 +08:00
    @a90120411 #7 老哥,我现在用点生成线了。

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

    我使用 ST_contains 、st_intersects 函数都查不出来。
    a90120411
        16
    a90120411  
       2022-05-27 10:41:54 +08:00
    @liuguangxuan 用 ST_Covers
    liuguangxuan
        17
    liuguangxuan  
    OP
       2022-05-27 10:48:24 +08:00
    @a90120411 #16
    多谢老哥回复。

    再请教一下老哥,如图所示,一个 Polygon 和一个 LineString:
    1 、我想求相交区域(绿色部分)的线,应该用哪个函数啊?
    2 、我想求非相交区域(红色部分)的线,应该用哪个函数啊?
    a90120411
        18
    a90120411  
       2022-05-27 10:49:12 +08:00
    @a90120411 说错了,应该用 ST_Intersects
    a90120411
        19
    a90120411  
       2022-05-27 11:04:12 +08:00
    @liuguangxuan 判断包含关系,用 ST_Intersects 。

    1 、交集 ST_Intersection

    2 、差集 ST_Difference
    liuguangxuan
        20
    liuguangxuan  
    OP
       2022-05-27 11:14:46 +08:00
    @a90120411 #19

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

    2 、但是差集的话,会把旁边的那条无关的线也返回。我想实现只返回有交集的线的差集。😂
    a90120411
        21
    a90120411  
       2022-05-27 11:52:55 +08:00
    @liuguangxuan 这种情况应该是你的线 Geometry 是多线几何对象( MULTILINE 一个对象里面包含了多个线),可以把线用 ST_AsText 打印出来看一下结构。如果是 MultiLine 最好是在数据层面处理成单一的 Line 几何。或者对结果再进行一次计算也可以,但是不推荐。
    liuguangxuan
        22
    liuguangxuan  
    OP
       2022-05-27 12:12:59 +08:00
    @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
        23
    liuguangxuan  
    OP
       2022-05-27 12:16:45 +08:00
    @a90120411 #21
    最右侧那个直线,跟矩形区域没有任何相交,但是还是查出来了。
    a90120411
        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 了,手头也没有相关环境,只能是帮你查查官方文档,根据自己的理解提供一些建议,不一定正确,仅供参考。
    liuguangxuan
        25
    liuguangxuan  
    OP
       2022-05-27 14:16:17 +08:00
    @a90120411 #24
    感谢老哥的回复。

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

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

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

    其中需求一比较好实现:
    使用 ST_Intersection(),直接就能求出来;

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

    但是感觉实现需求二这种方法比较麻烦,PostGIS 有没有现成的函数一步完成这个操作啊,老哥?
    a90120411
        26
    a90120411  
       2022-05-27 14:55:36 +08:00   ❤️ 1
    @liuguangxuan 我没太理解需求二麻烦的原因是什么?
    内置函数中应该不会提供这种函数,违反设计原则(单一职责)。
    但 PostgreSQL 支持用户自定义函数 FUNCTION 。
    liuguangxuan
        27
    liuguangxuan  
    OP
       2022-05-27 16:13:15 +08:00
    @a90120411 #26 明白啦,老哥。非常感谢指点。
    a90120411
        28
    a90120411  
       2022-05-27 17:04:32 +08:00
    @liuguangxuan 客气啦,谈不上指点。只是略知一二,互相交流学习。
    liuguangxuan
        29
    liuguangxuan  
    OP
       2022-05-27 19:19:24 +08:00
    @a90120411 #28 老哥可否留个联系方式,以后有问题好向您请教。😂
    liuguangxuan
        30
    liuguangxuan  
    OP
       2022-08-29 15:53:45 +08:00
    @a90120411 #7 老哥,之前您说过在线对象中保存与点集合的业务数据关联,请问这一步如何操作呢?

    我的疑问点是:
    我有一张轨迹点的表,里面存有经纬度点,还有速度、高度等业务数据,现在我新建了一张轨迹线表,把点表中的经纬度字段使用 ST_Simplify(ST_MakeLine(t.jwd))给抽取出来了,但是这两个表如何关联起来呢?关联到每个点的速度、高度字段。

    或者是使用 ST_Simplify 的时候,保留其他维度(速度、高度)的信息?


    =============================
    别查 Point ,查 Line 。
    用点来生成线,在线对象数据中同时保存与点集合的业务数据关联。
    a90120411
        31
    a90120411  
       2022-08-30 15:32:57 +08:00
    @liuguangxuan #30 这种情况解决方法挺多的,默认就是查线对象,拿到点的 XY 之后去点集合表中去查询对应的数据就可以,空间索引是生效的。如果速度不理想,你可以把生成线的那些点的 FID ,使用集合存到线对象的字段中(按照顺序),查询线的时候从这个集合里面取出来 FID ,然后去点集合表中去查。
    此外我以前常用的方式是把空间数据(只存放涉及地理查询分析的数据)与业务数据分离,通过 ID 进行一对一的关联。例如你把轨迹点的表,拆成两个表,空间表中的每条记录都有一个与之相关的业务数据记录 ID 。业务表中的每条记录也有一个空间记录的 FID 。或者建立一个中间表,里面可以建立业务数据记录与多个空间数据表的关联性,也就是一条业务记录可以关联多个空间几何要素表。在甚者你可以考虑增加冗余来换取速度,直接在线集合表中扔个 JSON 。
    再复杂的话,也可以用拓扑结构来存对应关系。上述的这些方法都要根据实际业务需求来进行设计开发,集合数据使用场景频次,针对性的优化。
    liuguangxuan
        32
    liuguangxuan  
    OP
       2022-08-31 09:29:33 +08:00
    @a90120411 #31 感谢老哥的耐心解答,看了您的回答,我有 3 点疑问:

    1 、假如 XY 经纬度点有重合怎么办?这种情况下就不能用经纬度点 XY 做为 key 去点集合表中查询对应的点记录了,因为查询出来的数据是多个。场景为:出租车原地掉了个头,此时采集的精度没有那么高,XY 值一致,但是航向却变化了 180 度,查询出来的数据为多条。

    2 、空间表和业务表的 FID 如何关联起来?因为抽稀使用的是 ST_Simplify 这个函数,假如从 1 万个点里面抽取了 200 个点,我不知道是哪 200 个点,上面提到的根据 XY 反差结果不靠谱,所以也无法关联这 200 个点的 FID 。

    3 、拓扑结构如何来存储对应的关系呢?
    a90120411
        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 开发了,回答的内容仅供你参考。
    liuguangxuan
        34
    liuguangxuan  
    OP
       2022-08-31 17:06:07 +08:00
    @a90120411 #33 感谢老哥的回复,明白你的意思了。
    请教一下您另外一个问题,假如我现在有很多的轨迹线了,飞机的或者出租车的都行,对这些轨迹线进行分析,我能做哪些有趣的事情呢?这方面老哥有无书籍推荐一些,轨迹分析相关的。
    a90120411
        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/
    liuguangxuan
        36
    liuguangxuan  
    OP
       2022-08-31 19:22:02 +08:00 via Android
    @a90120411 好嘞,多谢老哥,🐮。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5753 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 06:11 · PVG 14:11 · LAX 23:11 · JFK 02:11
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.