sql - Postgres - 使用 postgis 计算距离

Question

在寻找了几天并尝试了所有我找到的东西之后，我在这里询问如何使用 PostGis 计算 Postgres 上两点之间的距离。我有一张名为 location 的表。该表有一个点类型的列“coordenate”。当用户在应用程序中插入一个值时，我需要获取按关闭距离排序的位置。我知道我需要使用 ST_Distance，但每次我尝试转换坐标点时我都做不到。我需要以公里为单位的结果。

我尝试:

SELECT ST_Distance('POINT(0.0 0.0)', ST_GeomFromText(location.coordenate)) FROM app.location 作为位置；

Answer 1

要获得以米/公里为单位的距离，您需要将坐标转换为以米为单位的 SRS，或者如果可能，使用 geography而不是 geometry , 作为 ST_Distance返回两个 geography 的距离以米为单位的参数(1km = 1000m)，例如

SELECT 
  ST_Distance(ST_MakePoint(0.0,0.0)::geography, coordenate::geography)/1000 
FROM app.location;

类型转换 geometry/text至 geography

演示: db<>fiddle

CREATE TABLE location (gid int, coordenate geometry(point,4326));
INSERT INTO location VALUES
(1,'SRID=4326;POINT(10 10)'),(2,'SRID=4326;POINT(0.1 0.1)');

SELECT 
  gid, ST_AsText(coordenate),
  ST_Distance(ST_MakePoint(0.0,0.0)::geography, coordenate::geography)/1000  
FROM location
ORDER BY coordenate::geography <-> ST_MakePoint(0.0,0.0)::geography;

gid |   st_astext    |      ?column?      
-----+----------------+--------------------
   2 | POINT(0.1 0.1) | 15.690343289660001
   1 | POINT(10 10)   | 1565.1090992178902
(2 rows)

运营商<->表示距离，所以在 ORDER BY 上使用它子句，您可以按距离对结果集进行排序。

类型转换 point至 geography

数据类型point 不是 PostGIS 数据类型，而是 geometric data type 来自 PostgreSQL。为了使用ST_Distance您必须将这些点转换为几何或地理。

演示: db<>fiddle

CREATE TABLE location (gid int, coordenate point);
INSERT INTO location VALUES
(1,point(10,10)),(2,point(0.1,0.1));

SELECT *,
  ST_Distance(
    ST_MakePoint(0.0,0.0)::geography, 
    ST_MakePoint(coordenate[0],coordenate[1])::geography)/1000 
FROM location
ORDER BY ST_MakePoint(coordenate[0],coordenate[1])::geography <-> ST_MakePoint(0.0,0.0)::geography;

 gid | coordenate |      ?column?      
-----+------------+--------------------
   2 | (0.1,0.1)  | 15.690343289660001
   1 | (10,10)    | 1565.1090992178902
(2 rows)

进一步阅读:

• ST_MakePoint
• ST_SetSRID
• ST_Distance