How can I request all lines within a radius of 5 miles from my coordinates?

Question

Here is an example of my PostgreSQL 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 lines, such as coordinates around the world.

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

Answer 1

I made a combo of Erwin and Patrick 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);

Answer 2

You want "all lines within a radius of 5 miles of the coordinate", so this is not exactly a K-nearest neighbor (KNN) problem . Connected, but your case is simpler. "Finding 10 lines closest to my coordinates" will be a KNN problem.

geography:

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

geometry. :
4.2.2.

:

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

, , ST_DWithin() - . :

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

... dba.SE:

Answer 3

CSV, COPY ( PostgreSQL ) \copy psql, . Q + A SO , .

, , longitude latitude PostGIS geography, geography(POINT, 4326) ( gps) :

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

Add an index to this column to provide an efficient search:

CREATE INDEX my_table_gps ON my_table USING gist(gps);

Now you can find lines within 5 miles of a given location, for example. (-72.657, 42.0657)as below:

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

Note that ST_DWithin()the column geographywill work in meters, so you need to multiply the radius in miles by 1.609 meters per mile.