find the nearest location by latitude and longitude in postgresql
PostGIS
Don't store lat and long on a table like that. Instead use an PostGIS geometry or geography type.
CREATE EXTENSION postgis;
CREATE TABLE foo (
geog geography;
);
CREATE INDEX ON foo USING gist(geog);
INSERT INTO foo (geog)
VALUES (ST_MakePoint(x,y));
Now when you need to query it, you can use KNN (<->
) which will actually do this on an index.
SELECT *
FROM foo
ORDER BY foo.geog <-> ST_MakePoint(x,y)::geography;
In your query, you explicitly have HAVING distance < 5
. You can do that on the index too.
SELECT *
FROM foo
WHERE ST_DWithin(foo.geog, ST_MakePoint(x,y)::geography, distance_in_meters)
ORDER BY foo.geog <-> ST_MakePoint(x,y)::geography;
This ensure that nothing is returned if all points lie outside of distance_in_meters
.
Furthermore x and y are decimal numbers ST_MakePoint(46.06, 14.505)
select * from (
SELECT *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance
FROM station_location
) al
where distance < 5
ORDER BY distance
LIMIT 20;