Postgis long lat geometry distance search tuning using gist knn function

简介:
空间数据中对临近点的检索使用非常常见, 例如以经纬度为坐标点, 检索离这个点1公里范围内的其他点的信息.
最近有网友问到这样的问题.
本文将以Postgis为例, 举一个简单的例子, 利用gist 索引加速检索.
测试表 :
create table cust_jw
(
 dz varchar(300),
 jwd geometry
);

测试数据, 取自经纬度信息网站.
insert into cust_jw values ('杭州', ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163));
insert into cust_jw values ('北京', ST_Transform(ST_GeomFromText('POINT(116.46 39.92)', 4326), 2163));
insert into cust_jw values ('南京', ST_Transform(ST_GeomFromText('POINT(118.78 32.04)', 4326), 2163));
insert into cust_jw values ('南宁', ST_Transform(ST_GeomFromText('POINT(108.33 22.84)', 4326), 2163));
insert into cust_jw values ('贵阳', ST_Transform(ST_GeomFromText('POINT(106.71 26.57)', 4326), 2163));
insert into cust_jw values ('南昌', ST_Transform(ST_GeomFromText('POINT(115.89 28.68)', 4326), 2163));
insert into cust_jw values ('余杭', ST_Transform(ST_GeomFromText('POINT(120.3 30.43)', 4326), 2163));

创建gist索引 : 
create index idx_cust_jw_1 on cust_jw using gist(jwd);

这个索引方法支持包含<->两个几何类型的距离排序和&&两个几何类型相交.
详见pg_amop , pg_am, pg_operator, pg_opfamily等系统表.

以下SQL查出北京到杭州的直线距离, 单位米 :
SELECT ST_Distance(
ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163),
ST_Transform(ST_GeomFromText('POINT(116.46 39.92)', 4326), 2163)
);
   st_distance    
------------------
 1256521.71432098
(1 row)

以下SQL 查出表中距离 ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)这个点20公里的坐标.
函数使用方法参考postgis手册.
digoal=# select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) from cust_jw where jwd && ST_Buffer(ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163), 20000, 10);
  dz  |                        jwd                         |   st_distance    
------+----------------------------------------------------+------------------
 杭州 | 0101000020730800004C94087D5D4F54C173AA7759E8FB5D41 |                0
 余杭 | 0101000020730800000E6E5A20494854C121FC688DA9EF5D41 | 14483.9823187612
(2 rows)
Time: 1.335 ms

前面已经说了, 这个索引访问方法支持&&操作符, <->操作符.
digoal=# explain select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) from cust_jw where jwd && ST_Buffer(ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163), 20000, 10);                                                                                                         
                          QUERY PLAN                                                                                                
                                                                                                                                    
                                                                                                                                    
                                                                                                                                    
                                                                                                                                    
                                                               
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
 Index Scan using idx_cust_jw_1 on cust_jw  (cost=0.14..3.41 rows=1 width=548)
   Index Cond: (jwd && '01030000207308000001000000290000004C94087DD53B54C173AA7759E8FB5D411122F50B133C54C154295A2DDAF85D41D751B134CA
3C54C1F4F2B643DFF55D41B6BBAE74F63D54C10FB6A0650AF35D41CDDC4767903F54C1D331586C6DF05D4124855AF48D4154C14B9BC9D018EE5D41AC1BE98FE24354
C1F4F2B6431BEC5D41E89F31897F4654C1DDD11D5181EA5D41CDDC4767544954C1FE67201155E95D412D13EB504F4C54C1383864E89DE85D414C94087D5D4F54C173
AA775960E85D416B1526A96B5254C1383864E89DE85D41CB4BC992665554C1FE67201155E95D41B088DF703B5854C1DDD11D5181EA5D41EC0C286AD85A54C1F4F2B6
431BEC5D4174A3B6052D5D54C14B9BC9D018EE5D41CB4BC9922A5F54C1D331586C6DF05D41E26C6285C46054C10FB6A0650AF35D41C1D65FC5F06154C1F4F2B643DF
F55D4187061CEEA76254C154295A2DDAF85D414C94087DE56254C173AA7759E8FB5D4187061CEEA76254C1922B9585F6FE5D41C1D65FC5F06154C1F261386FF1015E
41E26C6285C46054C1D79E4E4DC6045E41CB4BC9922A5F54C11323974663075E4174A3B6052D5D54C19BB925E2B7095E41EC0C286AD85A54C1F261386FB50B5E41B0
88DF703B5854C10983D1614F0D5E41CB4BC992665554C1E8ECCEA17B0E5E416B1526A96B5254C1AE1C8BCA320F5E414C94087D5D4F54C173AA7759700F5E412D13EB
504F4C54C1AE1C8BCA320F5E41CDDC4767544954C1E8ECCEA17B0E5E41E89F31897F4654C10983D1614F0D5E41AC1BE98FE24354C1F261386FB50B5E4124855AF48D
4154C19BB925E2B7095E41CDDC4767903F54C11323974663075E41B6BBAE74F63D54C1D79E4E4DC6045E41D751B134CA3C54C1F261386FF1015E411122F50B133C54
C1922B9585F6FE5D414C94087DD53B54C173AA7759E8FB5D41'::geometry)
(2 rows)
Time: 1.218 ms


以下SQL 按距离排序.
digoal=# select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) from cust_jw order by jwd <-> ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163);
  dz  |                        jwd                         |   st_distance    
------+----------------------------------------------------+------------------
 杭州 | 0101000020730800004C94087D5D4F54C173AA7759E8FB5D41 |                0
 余杭 | 0101000020730800000E6E5A20494854C121FC688DA9EF5D41 | 14483.9823187612
 南京 | 0101000020730800000FFE5AD1D62653C16F4F972A10635E41 | 321491.591341196
 南昌 | 010100002073080000B2744BA1FE5253C10975D1494AA25F41 | 503843.306221247
 北京 | 0101000020730800006EBBB0F1AB0E4FC17207C71D44525E41 | 1256521.71432098
 南宁 | 01010000207308000030806B3882F451C18E3F38DCBB686141 |  1409624.7420143
 贵阳 | 01010000207308000082EA89026EE14FC1D6A3AD6E9E786141 | 1732521.31784296
(7 rows)
Time: 0.598 ms

通过以下方法强制排序走索引 :
digoal=# set enable_seqscan=off;
SET
Time: 0.109 ms
digoal=# explain select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) from cust_jw order by jwd <-> ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163);
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Scan using idx_cust_jw_1 on cust_jw  (cost=0.14..54.44 rows=140 width=548)
   Order By: (jwd <-> '0101000020730800004C94087D5D4F54C173AA7759E8FB5D41'::geometry)
(2 rows)


以下为进一步的优化, 如果点比较密集的话, 这种方法比较好. 
digoal=# select * from (select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) AS dist from cust_jw order by jwd <-> ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163) limit 1000) t where dist<15000;
  dz  |                        jwd                         |       dist       
------+----------------------------------------------------+------------------
 杭州 | 0101000020730800004C94087D5D4F54C173AA7759E8FB5D41 |                0
 余杭 | 0101000020730800000E6E5A20494854C121FC688DA9EF5D41 | 14483.9823187612
(2 rows)
Time: 0.634 ms


进一步优化, 使用游标, 可以将数据扫描降到极限. (前提是for循环中的sql order by使用了索引)
digoal=# do language plpgsql $$
declare
  v_rec record;
  v_limit int := 1000;
begin
  set enable_seqscan=off;  -- 强制索引, 因为扫描行数够就退出.
  for v_rec in select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) AS dist from cust_jw order by jwd <-> ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163) loop
    if v_limit <=0 then 
      raise notice '已经取足数据';
      return;
    end if;
    if v_rec.dist > 20000 then 
      raise notice '满足条件的点已输出完毕';
      return;
    else
      raise notice 'do someting, v_rec:%', v_rec;
    end if;
    v_limit := v_limit -1;
  end loop;
end;
$$;
NOTICE:  do someting, v_rec:(杭州,0101000020730800004C94087D5D4F54C173AA7759E8FB5D41,0)
NOTICE:  do someting, v_rec:(余杭,0101000020730800000E6E5A20494854C121FC688DA9EF5D41,14483.9823187612)
NOTICE:  满足条件的点已输出完毕
DO

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

[参考]
目录
相关文章
|
Dubbo Java 测试技术
分布式RPC框架性能大比拼 dubbo、motan、rpcx、gRPC、thrift的性能比较
Dubbo 是阿里巴巴公司开源的一个Java高性能优秀的服务框架,使得应用可通过高性能的 RPC 实现服务的输出和输入功能,可以和 Spring框架无缝集成。不过,略有遗憾的是,据说在淘宝内部,dubbo由于跟淘宝另一个类似的框架HSF(非开源)有竞争关系,导致dubbo团队已经解散(参见http://www.oschina.net/news/55059/druid-1-0-9 中的评论),反到是当当网的扩展版本仍在持续发展,墙内开花墙外香。
7427 0
|
移动开发 小程序 IDE
《钉钉应用开发者速成手册》之“开放平台介绍”
什么是钉钉开放平台? 钉钉开放平台是基于钉钉的基础产品和底座,为生态合作伙伴、企业客户及开发者提供所需要的开放能力的应用开发平台。通过钉钉开放平台可以帮助企业快速、低成本的实现高质量的移动微应用,实现生产、管理、协作、运营的业务在线化。
2054 1
《钉钉应用开发者速成手册》之“开放平台介绍”
|
9月前
|
NoSQL Linux MongoDB
MongoDB提供的这些工具
【6月更文挑战第8天】MongoDB提供的这些工具
169 6
|
10月前
|
安全 Linux Shell
Linux常用命令详解与实战应用
【5月更文挑战第7天】本文详述了Linux常用命令,包括文件与目录操作(ls, cd, pwd, cp, mv, rm)、文本处理(cat, grep, sed)及系统管理(top, df, du)命令。通过实例演示了如何使用这些命令,帮助读者理解和掌握Linux操作,提升系统管理效率。学习和熟练运用这些基础命令,是高效使用Linux的关键。
|
10月前
|
缓存
服务器的丢包率高怎么办
服务器的丢包率高怎么办
159 0
|
10月前
|
机器学习/深度学习 算法
R语言用随机森林模型的酒店收入和产量预测误差分析
R语言用随机森林模型的酒店收入和产量预测误差分析
|
10月前
yolo-world 源码解析(三)(4)
yolo-world 源码解析(三)
98 0
|
10月前
|
开发框架 前端开发 JavaScript
纪念基于JavaScript 实现的后台桌面 UI 设计
纪念基于JavaScript 实现的后台桌面 UI 设计
|
网络协议 虚拟化
虚拟机可以ping通主机,但是主机ping不通虚拟机解决办法
虚拟机可以ping通主机,但是主机ping不通虚拟机解决办法
4553 1
|
算法
带你读《2022技术人的百宝黑皮书》——倾向得分匹配(PSM)的原理以及应用(7)
带你读《2022技术人的百宝黑皮书》——倾向得分匹配(PSM)的原理以及应用(7)
264 0