CODEWITHSUNDEEP
sqlpostgresqlspatial
boycod3
boycod3

Reputation: 5317

find the nearest location by latitude and longitude in postgresql

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;

Upvotes: 29

Views: 31256

Answers (5)

Evan Carroll
Evan Carroll

Reputation: 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)

Upvotes: 72

nesimtunc
nesimtunc

Reputation: 869

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

Upvotes: 20

Deehan
Deehan

Reputation: 1

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.

Upvotes: 0

greg
greg

Reputation: 3495

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.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51466

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;

Upvotes: 18

Related Questions