Postgis long lat geometry distance search tuning using gist knn function

最新推荐文章于 2022-11-01 07:24:44 发布
最新推荐文章于 2022-11-01 07:24:44 发布
阅读量179 收藏
点赞数
文章标签： php
原文链接：https://yq.aliyun.com/articles/15010
版权
空间数据中对临近点的检索使用非常常见, 例如以经纬度为坐标点, 检索离这个点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行.

[参考]
weixin_34232363
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
三维GIS数据性能调优
11-29
该文档主要内容：数据承载力，性能指标参数，性能优化
ArcGIS性能优化建议
05-17
根据实际项目应用里部署的ArcGIS平台运行状态提出性能优化的技术方案，包括数据库资源优化、配图优化、ArcGIS Server出图优化等，按此方案优化后效果明显。
如何提高GIS应用系统的运行速度
赤荒野人的专栏
07-25 2025
    前几年写过GPS系统，也好久没接触这类代码了，前不久看了一个公司的GIS有感，所以写点东西。    不可否认的是现在的图越来越精确了，1：500 的图在我们的那个时候是不可想像的，但图的精确却带来运行速度的下降，可以看到的是现在的计算机速度是越来越快了，于是掩盖了运算速度的问题。比起我们做GIS时的公司提供的486是快的是没得比的。也许我们以前的速度太慢，所以我们不得不考虑更多的是速度
PostgreSQL 百亿地理位置数据 近邻查询性能
weixin_33709609的博客
01-19 974
背景 本文主要要展示的是PostgreSQL在位置信息近邻（KNN）查询方面的性能。 测试类型point，索引类型GiST。 （PostGIS同样支持KNN查询，性能和本文的测试差不多） 测试数据量大于100亿。 结果 64个并发，随机选点，单次KNN查询请求的平均响应时间为0.848毫秒。 测试环境和优化请参考 http://blog.163.c...
postgis 求离点最近的线
南山老沙
11-06 3488
With inputPoint AS (Select st_geomfromtext('POINT(114.19061431884767 22.543833325111848)', 4326) as pnt ) select buffer.* from ( select r.* from road as r,inputPoint as p where st_intersects(r.g...
postgis-geojson:用于PostGIS Geometry对象的GeoJSON Jackson序列化器和反序列化器
05-26
postgis-geojson 用于PostGIS Geometry对象的GeoJSON Jackson序列化器和反序列化器。GeoJSON支持该库支持对GeoJSON规范中定义的所有序列化/反序列化。 下面给出了GeoJSON几何对象与PostGIS对象之间的关系： GeoJSON ...
学说-后记：使用PostGIS和学说的空间和地理数据
02-05
教义的PostGIS扩展 该库允许您将Doctrine与PostGIS（PostgreSQL空间数据库扩展）一起使用。 PostGIS 1.5和2.x均受支持。 安装 使用安装最新版本。 composer require jsor/doctrine-postgis 查看以获取所有可用...
postgis/postgresql实验报告
03-08
【PostGIS PostgreSQL 实验报告】 PostGIS 是一个强大的开源扩展，它增强了 PostgreSQL 数据库系统，使其能够处理空间数据。在本实验中，我们将探讨如何在 PostgreSQL 中安装和使用 PostGIS，以及利用 PostGIS 进行...
Postgis_10.7_3.0.2安装包
05-15
PostGIS是基于开源数据库系统PostgreSQL的一个扩展，它为地理数据提供了全面的空间和几何对象支持。PostGIS_10.7_3.0.2安装包是针对PostgreSQL 10.7版本的一个升级，增加了对空间数据处理的增强功能和优化。在本文中...
centos操作系统安装postgresql+postgis部署配置
最新发布
03-03
利用rpm包进行简便的pg库部署安装，避免跟进源文件进行编译，需要根据postgresql版本问题存在的依赖查找，以及postgis插件与postgresql版本对应关系问题，减少了各个依赖版本间查找，以及各种依赖不正确，需要根据一...
postgresql找出时间最近的数据_不要让关系型数据库限制了你的监控规模
weixin_39622760的博客
11-27 634
多余的话就不说了，关系型数据库在大规模读写的缺点网上一大把，zabbix4.2时代已经可支持时序数据库了，在5.0版本时代方案更加成熟。TimescaleDB 介绍TimescaleDB 是基于 PostgreSQL 的时序数据库插件，完全继承了 PostgreSQL 的功能，对于复杂查询，各种类型(GIS,json,k-v,图像特征值,range,数组,复合类型,自定义类型,…..)的支持非常丰...
postgis 常用函数
cuisidong1997的博客
11-01 1060
St_difference(geom A,geom B)返回A与B不相交的部分几何。St_intersects(geom A,geom B)返回A是否和B有接触。St_intersection(geom A,geom B)返回A和B的交集。St_disjoint(geom A,geom B)返回A是否不在B中。St_union(geom A,geom B)返回A+B两个几何的合并。St_within(geom A,geom B)返回A是否处于B中。postgis常用操作手册。
mysql距离函数st_distance
热门推荐
十五楼亮哥
06-02 4万+
随着近几年各类移动终端的迅速普及，在手机移动定位app中，附近的人，附近的地点功能十分常见,基于地理位置的服务（LBS）和相关应用也越来越多，而支撑这些应用的最基础技术之一，就是基于地理位置信息的处理。st_distance 函数 st_distance 函数是从mysql5.6.1才加入的。 SET @g1 = POINT(1,1), @g2 = POINT(2,2); select st
利用mysql5.6 的st_distance 实现按照距离远近排序。
zhouzhiwengang的专栏
12-13 8967
一、用户需求          1、显示我附近的小区。          2、由近到远排序。          3、显示距离 二、数据库表结构数据： /* Navicat MySQL Data Transfer Source Server : rdshme0env6yf2n829ympublic.mysql.rds.aliyuncs.com Source
Postgresql 地理位置操作
hzh254052917的博客
12-12 6601
（longitude：经度 latitude：纬度 radius：范围/m） insert/update 地理位置： st_geomfromtext('point(longitude latitude)',4326); 例:(业务为：每10秒请求一次，上传车的位置，拼接到 driver_track表中的track字段，记录行车轨迹) update driver_track s
Geometry+PostgreSQL+PostGIS常用函数
xingsilong的博客
05-19 3992
一、PostGIS介绍 PostGIS是对象关系型数据库系统PostgreSQL的一个扩展，PostGIS提供如下空间信息服务功能:空间对象、空间索引、空间操作函数和空间操作符。 同时，PostGIS遵循OpenGIS的规范。 二、 PostGIS中的几何类型 PostGIS支持所有OGC(Open Geospatial Consortium) 规范的“Simple Features”类型，同时在此基础上扩展了对3DZ、3DM、4D坐标的支持。 1. OGC的WKB和WKT...
postgis创建的geometry字段，如何自动创建索引
05-26
PostGIS创建的geometry字段可以使用以下命令自动创建索引： ```sql CREATE INDEX index_name ON table_name USING GIST (geometry_field); ``` 其中，`index_name`是索引名称，`table_name`是表名，`geometry_field`是geometry字段名称。 使用GIST索引类型可以提高查询性能，因为它支持对几何对象的快速搜索和过滤。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值