GIS业务,附近查找性能优化

postgresql , postgis , knn , order sort 优化

空间数据中对临近点的检索使用非常常见, 例如以经纬度为坐标点, 检索离这个点1公里范围内的其他点的信息.

最近有网友问到这样的问题，如何优化呢.

ps 现在的版本可以直接支持，不需要使用子查询来支持了。

<a href="https://github.com/digoal/blog/blob/master/201601/20160119_01.md">《postgresql 百亿地理位置数据 近邻查询性能》</a>

本文将以postgis为例, 举一个简单的例子, 利用gist 索引加速检索.

测试表 :

测试数据, 取自经纬度信息网站.

创建gist索引 :

这个索引方法支持包含&lt;-&gt;两个几何类型的距离排序和&amp;&amp;两个几何类型相交.

详见pg_amop , pg_am, pg_operator, pg_opfamily等系统表.

以下sql查出北京到杭州的直线距离, 单位米 :

以下sql 查出表中距离st_transform(st_geomfromtext('point(120.19 30.26)', 4326), 2163)这个点20公里的坐标.

函数使用方法参考postgis手册.

前面已经说了, 这个索引访问方法支持&amp;&amp;操作符, &lt;-&gt;操作符.

以下sql 按距离排序.

通过以下方法强制排序走索引 :

以下为进一步的优化, 如果点比较密集的话, 这种方法比较好.

极致优化

进一步优化, 使用游标, 可以将数据扫描降到极限. (前提是for循环中的sql order by使用了索引)

使用这种方法最多扫描比需求结果多1行.

函数化

如果需要转换srid，那么请使用表达式索引，例如st_transform (pos, 26986).