在postgresql中找到纬度和经度的最近位置

发布时间：2020-12-13 16:03:11 所属栏目：百科来源：网络整理

导读：嗨我正在尝试在 postgresql数据库中找到纬度和经度最近的位置.但是当我运行以下查询时,显示列距离不存在. ERROR: column "distance" does not existLINE 1: ... ) ) ) AS distance FROM station_location HAVING distance ... ^********** Error **********E

嗨我正在尝试在 postgresql数据库中找到纬度和经度最近的位置.但是当我运行以下查询时,显示列距离不存在.

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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!