如何查询以我的坐标为半径 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屋！