时间戳

首页 > 解决方案 > Postgres - 使用 postgis 计算距离

sql - Postgres - 使用 postgis 计算距离

问题描述

在寻找了几天之后，尝试了我找到的所有内容，我在这里询问如何使用 PostGis 计算 Postgres 上两点之间的距离。我有一张叫做位置的桌子。该表有一个类型为点的“坐标”列。当用户在应用程序上插入一个值时，我需要获取按近距离排序的位置。我知道我需要使用 ST_Distance，但每次我尝试投射坐标点时我都不能。我需要以公里为单位的结果。

我尝试：

SELECT ST_Distance('POINT(0.0 0.0)', ST_GeomFromText(location.coordenate)) FROM app.location as location;

标签： sqlpostgresqlpostgis

解决方案

要获得以米/公里为单位的距离，您需要将坐标转换为以米为单位的 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 typePostgreSQL。为了使用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)

进一步阅读：

推荐阅读