本文介绍了在postgresql中按纬度和经度找到最近的位置的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试在 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;
推荐答案
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;
这篇关于在postgresql中按纬度和经度找到最近的位置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!