问题描述:

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;

网友答案:
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;
网友答案:

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.

相关阅读:
Top