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

请教一个逆地理编码的 PostgresSQL 语句优化问题

  •  
  •   liuzhedash · 2018-11-19 11:32:30 +08:00 · 2433 次点击
    这是一个创建于 1956 天前的主题,其中的信息可能已经有所发展或是发生改变。

    一个内网项目,需要获取到距离 GPS 坐标最近的两条路,道路 polyline 保存在 PostgresSQL+PostGIS 数据库里。现在我的查询语句是这样的形式:

                SELECT
                name,
                st_distance (
                    ST_GeomFromText ('POINT(108.862531 34.288909)') :: geography,
                    geom :: geography
                ) as distance,
                id,
                st_asgeojson(geom)
                FROM
                    road1_polyline
                WHERE name is not null
                UNION
                
                SELECT
                name,
                st_distance (
                    ST_GeomFromText ('POINT(108.862531 34.288909)') :: geography,
                    geom :: geography
                ) as distance ,
                id,
                st_asgeojson(geom)
                FROM
                    road2_polyline
                WHERE name is not null
                
            ORDER BY distance ASC
            LIMIT 100
    

    总的数据记录数大概在 6w,执行时间 4-8s,explain 的输出如下:

    Limit  (cost=155093.26..155093.51 rows=100 width=358)
      ->  Sort  (cost=155093.26..155226.28 rows=53207 width=358)
            Sort Key: (_st_distance('0101000020E6100000513239B533375B40914259F8FA244140'::geography, (national_highway_polyline_clip.geom)::geography, '0'::double precision, true))
            ->  HashAggregate  (cost=152527.66..153059.73 rows=53207 width=358)
                  Group Key: national_highway_polyline_clip.name, (_st_distance('0101000020E6100000513239B533375B40914259F8FA244140'::geography, (national_highway_polyline_clip.geom)::geography, '0'::double precision, true)), national_highway_polyline_clip.id, (st_asgeojson(national_highway_polyline_clip.geom, 15, 0))
                  ->  Append  (cost=0.00..151995.59 rows=53207 width=358)
                        ->  Seq Scan on national_highway_polyline_clip  (cost=0.00..6681.75 rows=2381 width=54)
                              Filter: (name IS NOT NULL)
                        ->  Seq Scan on country_road_polyline_clip  (cost=0.00..18560.35 rows=6620 width=58)
                              Filter: (name IS NOT NULL)
                        ->  Seq Scan on city_fast_road_polyline_clip  (cost=0.00..508.69 rows=180 width=68)
                              Filter: (name IS NOT NULL)
                        ->  Seq Scan on highway_polyline_clip  (cost=0.00..7240.14 rows=2566 width=70)
                              Filter: (name IS NOT NULL)
                        ->  Seq Scan on level_nine_road_polyline_clip  (cost=0.00..6616.24 rows=2362 width=59)
                              Filter: (name IS NOT NULL)
                        ->  Seq Scan on other_road2_polyline_clip  (cost=0.00..16308.71 rows=5172 width=59)
                              Filter: (name IS NOT NULL)
                        ->  Seq Scan on provincial_highway_polyline_clip  (cost=0.00..13497.61 rows=4811 width=59)
                              Filter: (name IS NOT NULL)
                        ->  Seq Scan on sub_country_road2_polyline_clip  (cost=0.00..82050.03 rows=29115 width=59)
                              Filter: (name IS NOT NULL)
    
    

    求教如何优化?

    7 条回复    2018-11-19 16:31:16 +08:00
    shangfabao
        1
    shangfabao  
       2018-11-19 13:47:09 +08:00
    先把 st_asgeojson(geom)去了试试
    bobo9ok
        2
    bobo9ok  
       2018-11-19 13:51:02 +08:00
    可以把铁路之类无关道路信息过滤, 根据点经纬度设定一个查询范围(缓冲区)
    hws8033856
        4
    hws8033856  
       2018-11-19 14:48:05 +08:00
    大概思路是先设置一个查询缓冲区,将查询范围限制在一个区域内,可以参考下文:
    https://www.jianshu.com/p/42e74122b9ac
    luozic
        5
    luozic  
       2018-11-19 15:05:35 +08:00
    liuzhedash
        6
    liuzhedash  
    OP
       2018-11-19 16:29:43 +08:00
    谢谢大家的提示。

    @shangfabao #1
    有道理,确实不应该在这个阶段获取 geojson

    @bobo9ok #2
    过滤我也想了,但是没有合适的筛选条件

    @hws8033856 #4
    这是我最早的思路,但是这需要重新构造一个表结构,我还是希望尽可能简单地在查询层面提高效率

    @luozic #5
    惊了老哥,太全了
    liuzhedash
        7
    liuzhedash  
    OP
       2018-11-19 16:31:16 +08:00
    @reus #3

    谢谢,初步测试这个非常靠谱,postgis 中<->运算符+order by 可以有索引加成
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5213 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 01:23 · PVG 09:23 · LAX 18:23 · JFK 21:23
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.