关于 PostGIS，难道是我的打开方式不对？

V2EX = way to explore

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

For Existing Member Sign In

This topic created in 4123 days ago, the information mentioned may be changed or developed.

SQL

select
    *,
    st_distance_sphere(
                       geometry(poi),
                       geometry('POINT(116.41739 39.913164)')
                      ) distance
from
    test
order by
    distance asc
limit
    10

EXPLAIN结果：

Limit  (cost=14826.00..14826.02 rows=10 width=51)
  ->  Sort  (cost=14826.00..14950.85 rows=49939 width=51)
        Sort Key: (_st_distance(geography(geometry(poi)), '0101000020E61000005BEB8B84B61A5D4039F1D58EE2F44340'::geography, 0::double precision, false))
        ->  Seq Scan on test  (cost=0.00..13746.83 rows=49939 width=51)

已经给poi建立了gist索引了……为啥却没用呢？
才5w数据，检索却要0.2s

Supplement 1 · Jan 18, 2015

已解决。postgis果然没让人失望。

<->操作符可以解决。

No Comments Yet

width=51 geometry rows=49939