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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 2194 天前的主题，其中的信息可能已经有所发展或是发生改变。

一个内网项目，需要获取到距离 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

2018-11-19 13:47:09 +08:00

先把 st_asgeojson(geom)去了试试

bobo9ok

2018-11-19 13:51:02 +08:00

可以把铁路之类无关道路信息过滤, 根据点经纬度设定一个查询范围(缓冲区)

reus

2018-11-19 14:20:53 +08:00

http://web.archive.org/web/20141104185651/http://geeohspatial.blogspot.com/2013/05/k-nearest-neighbor-search-in-postgis.html

hws8033856

2018-11-19 14:48:05 +08:00

大概思路是先设置一个查询缓冲区，将查询范围限制在一个区域内，可以参考下文：
https://www.jianshu.com/p/42e74122b9ac

luozic

2018-11-19 15:05:35 +08:00

https://github.com/digoal/blog

liuzhedash

2018-11-19 16:29:43 +08:00

谢谢大家的提示。

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

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

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

@luozic #5
惊了老哥，太全了

liuzhedash

2018-11-19 16:31:16 +08:00

@reus #3

谢谢，初步测试这个非常靠谱，postgis 中<->运算符+order by 可以有索引加成