find the nearest location by latitude and longitude in postgresql

Question

Hi i'm trying find the nearest location by latitude and longitude in postgresql database.But when i run the below query it showing column distance does not exists.

ERROR:  column "distance" does not exist
LINE 1: ... ) ) ) AS distance FROM station_location   HAVING distance <...
                                                             ^
********** Error **********

ERROR: column "distance" does not exist
SQL state: 42703
Character: 218

CREATE TABLE station_location
(
  id bigint NOT NULL DEFAULT nextval('location_id_seq'::regclass),
  state_name character varying NOT NULL,
  country_name character varying NOT NULL,
  locality character varying NOT NULL,
  created_date timestamp without time zone NOT NULL,
  is_delete boolean NOT NULL DEFAULT false,
  lat double precision,
  lng double precision,
  CONSTRAINT location_pkey PRIMARY KEY (id)
)

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
HAVING distance < 5
ORDER BY distance
LIMIT 20;

Answer 1

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)