查找距给定点一定半径内的点的最有效方法 [英] Most efficient way to find points within a certain radius from a given point

问题描述

我在这里已经阅读了有关此主题的几个问题，但我不明白找到一个带有圆圈的圆圈"中所有要点的常用方法(如果有一个...).以给定点为中心的特定半径.

I've read several questions + answers here on SO about this theme, but I can't understand which is the common way (if there is one...) to find all the points whithin a "circle" having a certain radius, centered on a given point.

我特别发现了两种最有说服力的方法:

In particular I found two ways that seem the most convincing:

select id, point 
from my_table 
where st_Distance(point, st_PointFromText('POINT(-116.768347 33.911404)', 4326)) < 10000;

和:

select id, point 
from my_table 
where st_Within(point, st_Buffer(st_PointFromText('POINT(-116.768347 33.911404)', 4326), 10000));

哪种是查询数据库的最有效方法?还有其他选择吗?

Which is the most efficient way to query my database? Is there some other option to consider?

推荐答案

创建缓冲区以查找点绝对是不行，因为(1)创建表示缓冲区的几何体的开销很大，并且(2)多边形点计算的效率比简单的距离计算要低得多.

Creating a buffer to find the points is a definite no-no because of (1) the overhead of creating the geometry that represents the buffer, and (2) the point-in-polygon calculation is much less efficient than a simple distance calculation.

您显然正在使用(经度，纬度)数据，因此应将其转换为适当的笛卡尔坐标系，该坐标系的度量单位与您的10,000的距离相同.如果该距离以米为单位，那么您也可以将表中的点转换为geography并直接在(经度和纬度)坐标上进行计算.由于您只想标识指定距离内的点，因此可以使用 ST_DWithin()函数计算球体上的附加速度(在高纬度或距离很长的情况下不要这样做):

You are obviously working with (longitude, latitude) data so you should convert that to an appropriate Cartesian coordinate system which has the same unit of measure as your distance of 10,000. If that distance is in meter, then you could also cast the point from the table to geography and calculate directly on the (long, lat) coordinates. Since you only want to identify the points that are within the specified distance, you could use the ST_DWithin() function with calculation on the sphere for added speed (don't do this when at very high latitudes or with very long distances):

SELECT id, point 
FROM my_table 
WHERE ST_DWithin(point::geography,
                 ST_GeogFromText('POINT(-116.768347 33.911404)'),
                 10000, false);

这篇关于查找距给定点一定半径内的点的最有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！