如何查询坐标5英里范围内的所有行？ [英] How do I query all rows within a 5-mile radius of my coordinates?

问题描述

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

Here's a sample of my PostgreSQL in CSV format.

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

我在全球范围内拥有数千个像这些跨越坐标的行。

I have thousands more rows like these spanning coordinates across the world.

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

I want to query the table only for coordinates within a certain radius. How do I do this with PostGIS and PostgreSQL?

推荐答案

我对Erwin和Patrick的答案进行了组合。

I did a combo of Erwin's and Patrick's answers.

-- Add geography column
ALTER TABLE googleplaces ADD COLUMN gps geography;
UPDATE googleplaces SET gps = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX googleplaces_gps ON googleplaces USING gist(gps);

SELECT *
FROM my_table
WHERE ST_DWithin(gps, ST_SetSRID(ST_MakePoint(-72.657, 42.0657), 4326), 5 * 1609);

这篇关于如何查询坐标5英里范围内的所有行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！