sql - 如何查询坐标 5 英里半径范围内的所有行？

Question

这是 CSV 格式的我的 PostgreSQL 示例。

row,latitude,longitude
1,42.082513,-72.621498
2,42.058588,-72.633386
3,42.061118,-72.631541
4,42.06035,-72.634145

我还有数千行像这些横跨世界的坐标。

我只想查询表中某个半径内的坐标。如何使用 PostGIS 和 PostgreSQL 执行此操作？

Answer 1

您想要“坐标半径 5 英里内的所有行”，所以这不恰好是 K-nearest-neighbour (KNN) problem .相关，但你的情况更简单。 “找到最接近我的坐标的 10 行” 将是一个 KNN 问题。

将您的坐标转换为地理值:

ST_SetSRID(ST_MakePoint(longitude, latitude),4326)::geography

或者，您可以使用更简单的 geometry 类型。考虑:
4.2.2. When to use Geography Data type over Geometry data type

然后我们有一个像这样的表:

CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY
, geog geography NOT NULL
);

您只需要 ST_DWithin() - 和一个空间索引以加快速度:

CREATE INDEX tbl_geog_gist ON tbl USING gist(geog);

查询:

SELECT *, ST_Distance(c.x, geog) AS distance  -- distance is optional
FROM   tbl t, (SELECT ST_GeographyFromText('SRID=4326;POINT(-72.63 42.06)')) AS c(x)
WHERE  ST_DWithin(c.x, geog, 8045)  -- distance in meter
ORDER  BY distance; -- order is optional, you did not ask for that

或者您可以使用原始列并创建功能索引...这个和 dba.SE 上这个密切相关的答案中的其他细节:

• Order by distance