CODEWITHSUNDEEP
sqlpostgresqlindexingpostgis
Username
Username

Reputation: 3663

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?

Upvotes: 5

Views: 6559

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658342

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 much 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 the manual:

Then we have a table like:

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

Use ST_DWithin() and a spatial index to make the query 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
     , ST_GeographyFromText('SRID=4326;POINT(-72.63 42.06)') c(x)  -- you are here
WHERE  ST_DWithin(c.x, geog, 8047)  -- distance in meter
ORDER  BY distance;  -- optional

5 miles are =~ 8047 meter.

Or use your original columns and create an expression index ... See:

Upvotes: 13

Username
Username

Reputation: 3663

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);

Upvotes: 3

Patrick
Patrick

Reputation: 32326

You should first create a table from your CSV formatted file, using the COPY command (if the file is accessible to the PostgreSQL server) or the \copy command in psql if the file is not local to the server. See other Q+A on SO for examples if you have any trouble.

Once you have your data in a table, you should convert your longitude and latitude columns to the PostGIS geography type by adding a column to your table of type geography(POINT, 4326) and then populating that column (here called gps) with the appropriate values:

UPDATE my_table SET gps = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

Add an index on that column to allow for efficient searches:

CREATE INDEX my_table_gps ON my_table USING gist(gps);

You can now find the rows within 5 miles from a given location, e.g. (-72.657, 42.0657), as follows:

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

Note that ST_DWithin() on a geography column will work in meters, so you have to multiply your radius in miles with the 1,609 meters in a mile.

Upvotes: 4

Related Questions