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;

Kufic

when you give id bigint NOT NULL DEFAULT nextval('location_id_seq'::regclass), better rewrite it to id bigserial - save time – Pueblo

All of these answers are slow and needlessly complex. Please see my answer for how to do this with PostGIS. In the future, you should ask these questions on Database Administrators – Structuralism

Answer 1

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;

Pueblo

do you recommend adding indexes on lat and lng columns? – Efflux

In what unit is the distance measured? Miles? Kilometeres? – Intransigent

IS the distance in kilometer ?. – Artisan

This one is in miles, to change it to km change the value from 3,959 to 6,371. Earth radius: 6,371 kilometres (3,959 mi). – Elswick

Answer 2

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)

Structuralism

wow, I did not expect a reply @Evan :) Thanks a bunch - I am new to PostGis and your answer to this question is on the money. I disapprove of the marked correct answer :) – Justinejustinian

@Justinejustinian if you have any questions check out Database Administrators – Structuralism

Answer 3

You can use PostgreSQL's cube and earthdistance extensions.

Enable them like this:

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

Let's say your current location is 35.697933, 139.707318. Then your query will be something like this:

SELECT *, point(35.697933, 139.707318) <@>  (point(longitude, latitude)::point) as distance
FROM station_location
-- WHERE (point(35.697933, 139.707318) <@> point(longitude, latitude)) < 3
ORDER BY distance;

Please note that the distance is in miles (by default).

Scarface

Fantastic! Thank you! – Pyrostat

I confirm, it works like a charm! – Che

Answer 4

See this gist, you will find how to declare a DOMAIN on the point type and how to override the distance operator to return the orthodromic distance.

Declare a latlong type inherited from point:

CREATE DOMAIN latlong AS point CHECK (VALUE[0] BETWEEN -90.0 AND 90.0 AND VALUE[1] BETWEEN -180 AND 180);

The orthodromic distance in kilometers (distance on a sphere with the earth radius):

CREATE OR REPLACE FUNCTION orthodromic_distance(latlong, latlong) RETURNS float AS $_$
     SELECT acos(
              sin(radians($1[0])) 
            * 
              sin(radians($2[0]))
            + 
              cos(radians($1[0])) 
            * 
              cos(radians($2[0]))
            * 
              cos(radians($2[1]) 
            - 
              radians($1[1]))
            ) * 6370.0;
$_$ LANGUAGE sql IMMUTABLE;

Override the distance operator <-> using this function when used with latlongs:

CREATE OPERATOR <-> ( PROCEDURE = orthodromic_distance
, LEFTARG = latlong, RIGHTARG = latlong
);

Now in your SQL queries, to find the nearest entities:

WITH
  station_distance AS (
    SELECT
      id AS station_id,
      point(lat, long)::latlong <-> point(6.414478, 12.466646)::latlong AS distance
    FROM station_location
    WHERE NOT is_deleted
  )
  SELECT
    sl.state_name,
    sl.country_name,
    sl.locality,
    point(sl.lat, sl.long)::latlong AS coordinates,
    sd.distance
  FROM
    station_location sl
    JOIN station_distance sd
      ON sd.station_id = sl.id
  ORDER BY
    distance ASC
  LIMIT 10

You probably want to store the position lat and long in the same field using the latlong type.

Mcclintock

Answer 5

The manual clarifies :

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

Housum