AI 助理
文档备案控制台
开发者社区 数据库 关系型数据库 文章 正文

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

2017-05-08 6069
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL DuckDB 分析主实例，基础系列 4核8GB
RDS MySQL DuckDB 分析主实例，集群系列 4核8GB
RDS AI 助手，专业版
简介： 标签 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

文章标签：
索引
定位技术
关系型数据库
SQL
德哥
目录
相关文章
碳基沙盒
|
1月前
|
人工智能 JavaScript 编译器
AI工具的“超级外挂”：从零手把手教你搭建私人 MCP 服务器
本文手把手教你用Node.js从零搭建私人MCP（模型上下文协议）服务器，解决AI无法直接访问本地文件、数据库等痛点。含环境配置、TypeScript编译避坑、Hello World工具开发及Inspector调试全流程，助你赋予AI真实行动力！
碳基沙盒
555 0
AI工具的“超级外挂”：从零手把手教你搭建私人 MCP 服务器
北京木奇移动技术
|
2月前
|
人工智能 数据可视化 Java
AI智能体的开发方法
本文系统梳理国内AI智能体开发全景：从“感知-决策-行动-记忆”认知闭环架构出发，对比Dify、Coze等低代码平台与LangGraph、AgentScope、Eino、Spring AI Alibaba等编程级框架；解析MCP协议、RAG技术栈等基础设施；并按MVP、企业级、极客定制三类场景给出选型建议。（239字）
北京木奇移动技术
1061 5
Matlab科研工作室
|
23天前
|
机器学习/深度学习 传感器 算法
基于融合floyd算法的改进A星算法路径规划Matlab程序
🌿 往期回顾可以关注主页，点击搜索 智能优化算法 神经网络预测 雷达通信 无线传感器 电力系统 信号处理 图像处理 路径规划 元胞自动机 无人机 物理应用 机器学习系列 车间调度系列 滤波跟踪系列 数据分析系列 图像处理系列 ✅作者简介：热爱科研的Matlab仿真开发者，擅长毕业设计辅导、数学建模、数据处理、建模仿真、程序设计、完整代码获取、论文复现及科研仿真。 🍎 往期回顾关注个人主页：
Matlab科研工作室
103 11
YueGuan
|
3月前
|
人工智能 缓存 API
Dify：面向企业级LLM应用开发的现代化全栈框架深度技术解析
当 AI 应用从 Demo 走向生产，问题已不再只是模型效果，而是工程化与系统能力。本文从架构与实现机制出发，深入解析 Dify 作为 LLM 应用平台的设计思路、核心能力与边界，并探讨其在企业级场景中的真实价值与演进方向。
YueGuan
399 2
游客wsxnwr5p22s5w
|
4月前
|
自然语言处理
主流大模型结构
本文详解主流大模型架构：Encoder-Decoder（如T5）、Decoder-Only（如GPT）、Encoder-Only（如BERT）和Prefix-Decoder（如GLM），涵盖代表模型、特点与应用场景；梳理GPT系列演进、LLaMA发展及中文大模型现状，并提供模型对比与面试重点解析。
游客wsxnwr5p22s5w
227 0
阿里云计算巢-存坤
|
12月前
|
人工智能 运维 开发工具
产品动态丨阿里云计算巢月刊-2025年第3期
让优秀的企业软件生于云、长于云
阿里云计算巢-存坤
617 149
卓上
|
资源调度 JavaScript 前端开发
CentOS 7.2 下安装配置Node.js和Yarn
centos下node.js的安装配置管理，npm以及yarn包管理工具的安装。
卓上
7181 0
技术小胖子
|
领域建模 uml Android开发
EMF：Ecore模型
技术小胖子
2896 0
蓝多多的小仓库
|
开发工具
pip install第三方包 Could not fetch URL https://pypi.tuna.tsinghua.edu.cn/simple/pipenv/:【解决方案】
pip install第三方包 Could not fetch URL https://pypi.tuna.tsinghua.edu.cn/simple/pipenv/:【解决方案】
蓝多多的小仓库
3127 0
pip install第三方包 Could not fetch URL https://pypi.tuna.tsinghua.edu.cn/simple/pipenv/:【解决方案】
决策智能Lab_运营小编003
|
算法 API Python
约束冲突怎么办？没关系，MindOpt会出手的！
在对实际应用中的优化问题进行建模求解的过程中，往往会遇到问题不可行的情况。而不可行问题必然是由某些约束互相之间冲突导致的，如何分析问题的不可行性并识别出导致冲突的关键约束成为求解器应用的重要一环。这类导致问题不可行的最小约束子集被称为不可约不可行系统 (IIS, irreduciable infeasible system)。
决策智能Lab_运营小编003
783 0
约束冲突怎么办？没关系，MindOpt会出手的！
数据库

关系型数据库

热门文章

最新文章

  • 1
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 2
    什么场景应该用 MongoDB ？
  • 3
    PostgreSQL 如何实现批量更新、删除、插入
  • 4
    在Docker上玩转PostgreSQL -- Mac篇
  • 5
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 6
    Linux 性能诊断 perf使用指南
  • 7
    PostgreSQL 9种索引的原理和应用场景
  • 8
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 9
    Jedis常见异常汇总
  • 10
    PostgreSQL 9.5+ 高效分区表实现 - pg_pathman
  • 1
    OpenClaw-Observability：基于 DuckDB 构建 OpenClaw 的全链路可观测体系
    72
  • 2
    每天一个RDSClaw使用小技巧 — 合集【本文章会持续更新】
    82
  • 3
    阿里云RDSClaw：给OpenClaw装上超级记忆和超级大脑，会怎样？
    104
  • 4
    PolarClaw实战训练营，手把手带你免费部署自己的龙虾
    75
  • 5
    告别“鱼的记忆”：PolarDB Mem0 赋予 AI Agent “长期记忆”
    318
  • 6
    告别“鱼的记忆”：PolarDB Mem0 赋予 AI Agent “长期记忆”
    256
  • 7
    阿里云云数据库RDS功能、优势、应用场景及收费标准与最新活动参考
    698
  • 8
    Linux 安装 mysql 【基于 tar.gz | tar.xz安装包-离线安装】
    550
  • 9
    「RDS DuckDB 实训营」火热进行中！3月3日正式开营，参营即享多重好礼！
    184
  • 10
    【赵渝强老师】PostgreSQL中表的碎片
    129

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    PHP：将本地文件上传到阿里云OSS存储