`
骑猪逛街666
  • 浏览: 141802 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

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

开发 
阅读更多

摘要： 标签 PostgreSQL , PostGIS , KNN , order by 距离 sort 优化 背景 空间数据中对临近点的检索使用非常常见, 例如以经纬度为坐标点, 检索离这个点1公里范围内的其他点的信息. 最近有网友问到这样的问题，如何优化呢. ps 现在的版本可以直接支持，不需要使用子查询来支持了。

标签

 

PostgreSQL , PostGIS , KNN , order sort 优化

背景

 

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

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

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

《PostgreSQL 百亿地理位置数据 近邻查询性能》

正文

 

本文将以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 local 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行.

函数化

select * from 
(
select *,
  ST_Distance (ST_Transform ($1, 26986), ST_Transform (jwd, 26986) ) as dist 
  from cust_jw 
  where dz='杭州'
  order by ST_Transform (pos, 26986) <-> ST_Transform ($1, 26986) limit 200
) t
where
distance2Center <= 2000.0;


create or replace function ff(geometry, float8, int) returns setof record as $$                                                        
declare
  v_rec record;
  v_limit int := $3;
begin
  set local enable_seqscan=off;   -- 强制索引, 扫描行数够就退出.
  for v_rec in 
    select *,
    ST_Distance ( ST_Transform ($1, 26986), ST_Transform (jwd, 26986) ) as dist 
    from cust_jw 
    order by ST_Transform (jwd, 26986) <-> ST_Transform ($1, 26986)
  loop
    if v_limit <=0 then
      raise notice '已经取足数据';
      return;
    end if;
    if v_rec.dz='杭州' and v_rec.distance2Center > $2 then
      raise notice '满足条件的点已输出完毕';
      return;
    elsif v_rec.dz='杭州' then
      raise notice 'do someting, v_rec:%', v_rec;
      return next v_rec;
    else
      NULL;
    end if;
    v_limit := v_limit -1;
  end loop;
end;
$$ language plpgsql strict volatile;
  
select * from ff(ST_GeomFromText ('POINT(114.111618652344 28.332331814237)', 4326),2000.0,1) as t(dz varchar,jwd geometry,dist float8);

SRID

 

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

参考

 

1. http://www.ximizi.com/jingweidu.php

2. http://postgis.net/docs/manual-2.0/ST_Distance_Sphere.html

3. http://postgis.net/docs/manual-2.0/ST_Buffer.html

4. http://postgis.net/docs/manual-2.0/ST_Transform.html

5. http://postgis.net/docs/manual-2.0/ST_GeomFromText.html

6. http://postgis.net/docs/manual-2.0/geometry_distance_centroid.html

本文为云栖社区原创内容，未经允许不得转载，如需转载请发送邮件至yqeditor@list.alibaba-inc.com
原文链接
分享到：
| PostgreSQL 聚合表达式 FILTER , order , ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    gis.rar_gis_gis 方案

    5. 系统部署：在服务器上安装系统，进行性能优化。 6. 培训与维护：对用户进行操作培训，持续监控系统运行，提供技术支持。 六、预期效果 上海师范大学教学管理GIS系统的实施，将使教学资源的管理更为高效，教学...

    基于vc++的GIS二次开发

    7. **性能优化**：由于GIS处理的数据量可能很大，因此需要考虑性能优化，比如合理使用多线程、内存管理和数据缓存策略。 8. **错误处理和调试**：编写良好的异常处理代码，确保程序的健壮性。使用Visual Studio的...

    ehotgis开发嵌入式gis

    8. **性能优化**：对于嵌入式GIS，性能优化至关重要。ehotgis可能有优化策略，如分块加载地图、缓存常用数据、离线地图支持等，以确保在有限的硬件资源下流畅运行。 9. **事件监听与回调**：为了响应用户的操作，...

    基于构件化的GIS工作流中间件整体解决方案

    1. **构件设计与管理**：如何设计和管理GIS构件，保证其可复用性、互操作性和性能，以及如何在系统中方便地查找和部署构件。 2. **工作流建模与执行**：介绍工作流建模语言和工具，如何定义和执行工作流，以及支持...

    ArcGIS Server分布式技术实现与优化.pdf

    此外，文章还关注了ArcGIS Server的性能优化。优化的目标包括最小化服务器资源占用率、最小化数据传输量、选择最适合的图像格式以及提高代码执行效率。这些优化措施旨在提升整个系统的性能，确保服务的高效稳定运行...

    GIS解决方案

    - **高级三维分析工具**：如最近设施查找、热点分析、地形可视域分析、地下岩体剖切面分析等，支持复杂问题的解决。 - **空间处理框架**：允许二维和三维分析的组合，适应多样化的业务需求。 - **Python环境集成**：...

    基于GIS的房产测绘信息管理系统研究.doc

    通过GIS，用户可以进行复杂的空间查询，比如查找某一产权人名下的所有房产、查询某个区域内的房产分布等。同时，GIS还可以进行空间统计分析，如计算特定区域内房产的平均面积、评估房产价值分布等。 3.3 地图可视化...

    大数据存储及分层实践-6-5 PostGIS 在气象业务系统中的实践.zip

    结合其灵活性、高性能和丰富的GIS功能，PostGIS成为构建高效、智能化气象业务系统不可或缺的工具。通过深入理解和应用PostGIS，我们可以更好地应对气象大数据的挑战，提升气象服务的质量和效率。

    地图浏览（ArcEngine）

    9. **性能优化**：在处理大量数据时，性能优化至关重要。这可能涉及到数据预处理、缓存策略、服务端渲染等技术，以确保应用程序的流畅运行。 10. **数据安全与权限管理**：对于包含敏感信息的地图，需要实施数据...

    基于Oracle Spatial技术的地理信息管理与优化.pdf

    通过一体化存储空间数据和属性数据，以及使用空间邻近分析，它显著提高了GIS应用的性能和准确性。在实际业务场景，如EAM系统中，Oracle Spatial能够有效解决传统GIS技术存在的问题，提升数据管理和决策的效率。

    李崇贵组件开发AE课程代码

    10. 性能优化：探讨如何优化GIS应用的性能，例如减少内存消耗、提高渲染速度等。 11. 错误处理与调试：学习如何有效地处理错误和异常，以及使用调试工具来查找和修复问题。 12. 示例程序：压缩包中的“演示程序”...

    Android房地产业务移动办公系统

    9. **性能优化**：考虑到移动设备的资源限制，系统需优化数据加载、减少内存占用，确保在各种网络环境下的流畅运行。 10. **持续更新与维护**：随着业务需求和技术的发展，系统需要定期更新，添加新功能，修复问题...

    最新2022年贵州省道路矢量图层shp格式数据下载

    4. ".shx"文件：形状索引文件，加快了对shp文件中的几何对象进行查找和访问的速度，优化了数据的读取性能。 这些文件共同构成了一个完整的GIS数据集，使用者可以通过GIS软件（如ArcGIS、QGIS等）导入这些数据，进行...

    地理信息系统原理与应用

    在GIS的产生与发展章节中，提到了传统地图的局限性，如信息承载量小、量算统计精度低、查找分类困难等，这些问题在GIS的发展过程中逐渐被克服。GIS自1950年代末开始萌芽，最初应用于土地资源分析、交通研究、计算机...

    mapgis开发 API实列.zip

    这些示例代码不仅展示了API的基本用法，还可能包含了异常处理、性能优化等方面的实践，对于初学者和有经验的开发者都具有很高的参考价值。在实际项目中，开发者可以参考这些实例，快速解决遇到的问题，提高开发效率...

    ArcGISEngine2.rar_arcgis_arcgis engine_engine arcgis_training ar

    10. **性能优化与调试**：了解提高ArcGIS Engine应用性能的方法，以及如何进行有效的错误调试和程序优化。 11. **案例研究**：通过实例分析，加深对ArcGIS Engine开发的理解，例如，构建一个简单的地图浏览和查询...

    宏华水利小程序-网页设计-微信小程序

    为了保证小程序的稳定性和用户体验，开发者需要关注性能优化，如减少网络请求、合理缓存数据、优化图片资源等。同时，定期更新维护，修复可能出现的bug，根据用户反馈改进功能，是保持小程序活力的关键。 总的来说...

    三维管线加载

    3. **渲染优化**：通过LOD（Level of Detail）技术降低复杂度，只在必要的时候渲染细节，提高性能。 4. **光照与材质**：模拟真实世界的光照效果，使管道看起来更逼真，同时可以设置不同的材质属性，如金属光泽、...

    摩拜杯 算法挑战赛 第三名 解决方案

    4. **性能优化**：可能会有对原算法的改进和优化，如使用数据结构优化查找效率，或者通过并行计算提升运行速度。 5. **测试与验证**：可能会有测试用例和结果分析，展示算法在不同情况下的表现和效果。 6. **问题...

    C#开发的AE开发的门牌定位系统

    10. **性能优化**：为了处理大量地理数据和提供流畅的用户体验，系统需要进行性能优化。这可能涉及数据缓存、并发处理、多线程编程以及数据库查询优化等方面。 总之，C#开发的AE门牌定位系统结合了软件工程、GIS...

Magicbox
Global site tag (gtag.js) - Google Analytics