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

Question

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?

Answer 1

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:

• Order by distance