Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
348 views
in Technique[技术] by (71.8m points)

sql - How do I query all rows within a 5-mile radius of my coordinates?

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.

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You want "all rows within a 5-mile radius of a coordinate", so this is not exactly a K-nearest-neighbour (KNN) problem. Related, but your case is simpler. "Find the 10 rows closest to my coordinates" would be a KNN problem.

Convert your coordinates to geography values:

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

Alternatively you could use the simpler geometry type. Consider:
4.2.2. When to use Geography Data type over Geometry data type

Then we have a table like:

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

All you need is ST_DWithin() - and a spatial index to make it fast:

CREATE INDEX tbl_geog_gist ON tbl USING gist(geog);

Query:

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

Or you can use your original columns and create a functional index ... This and other details in this closely related answer on dba.SE:


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...