在PostgreSQL中通过纬度和经度查找最近的位置

Question

您好，我正在尝试在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;

- boycod3

---

当你给出 id bigint NOT NULL DEFAULT nextval('location_id_seq'::regclass), 时，最好重写为 id bigserial - 节省时间。 - Vao Tsun

所有这些答案都很慢且不必要地复杂。请查看我的答案，了解如何使用PostGIS完成此操作。将来，您应该在[dba.se]上提出这些问题。 - Evan Carroll

Answer 1

PostGIS

不要像那样在一个表格上存储经度和纬度。相反，使用 PostGIS 几何类型或地理类型。

CREATE EXTENSION postgis;

CREATE TABLE foo (
  geog geography;
);

CREATE INDEX ON foo USING gist(geog);

INSERT INTO foo (geog)
  VALUES (ST_MakePoint(x,y));

现在当你需要查询它时，你可以使用 KNN (<->)，它将在索引上实际执行此操作。

SELECT *
FROM foo
ORDER BY foo.geog <-> ST_MakePoint(x,y)::geography;

在你的查询中，你明确使用了HAVING distance < 5。你也可以在索引上进行这样的操作。

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;

如果所有点都在distance_in_meters之外，这将确保不返回任何内容。

此外，x和y是十进制数：ST_MakePoint(46.06, 14.505)

- Evan Carroll

---

哇，我没想到会收到 @Evan 的回复 :) 非常感谢 - 我是 PostGis 的新手，你对这个问题的回答非常准确。我不赞成被标记为“正确”的答案 :) - JohnJ

@JohnJ 如果你有任何问题，请查看[dba.se]。 - Evan Carroll

Answer 2

你可以使用PostgreSQL的cube和earthdistance扩展。

像这样启用它们：

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

假设您当前的位置是35.697933, 139.707318。那么您的查询将类似于这样：

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;

请注意，默认情况下distance是以英里为单位。

- nesimtunc

---

太棒了！谢谢！ - funder7

我确认，它像魔法一样运行良好！ - Geoffrey R.

Answer 3

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;

- Vao Tsun

---

你建议在 lat 和 lng 列上添加索引吗？ - jasenkoh

距离是用什么单位来衡量的？英里？千米？ - Przemek

这是以公里为单位的距离吗？ - stacktesting

这个是以英里为单位的，如果要改成公里，请将数值从3,959改为6,371。 地球半径：6,371公里（3,959英里）。 - ALabrosk

Answer 4

请看this gist，你会发现如何在point类型上声明一个DOMAIN以及如何重写distance运算符以返回正弦距离。

声明一个从point继承的latlong类型：

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

正位角距离（以地球半径为参考的球面距离）：

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;

使用此函数重写与经纬度一起使用时的距离运算符<->：

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

现在，在您的SQL查询中，要查找最近的实体：

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

你可能想使用经纬度类型(latlong)将位置的lat和long存储在同一字段中。

- greg

Answer 5

手册澄清：

输出列的名称可用于在ORDER BY和GROUP BY子句中引用该列的值，但不能在WHERE或HAVING子句中使用；在那里，您必须写出表达式。

- Deehan