Postgres - Calculate distance with postgis-CodePudding

After looking for days, and try all what I find, I'm here to ask how to calculate the distance beetwen two points on Postgres with PostGis. I got a table called location. This table got a column "coordenate" of type point. When the user inserts a value on the application I need to get the locations ordered by the closes distance. I know I need to use ST_Distance, but everytime I try to cast the POINT of coordenate I can't. I need the result in Km.

I try:

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

CodePudding user response：

To get the distance in metre/kilometer you might wanna transform your coordinates to a SRS that has metre as unit or if possible use geography instead of geometry, e.g.

SELECT 
  ST_Distance('POINT(0.0 0.0)'::geography, coordenate::geography) 
FROM app.location;

Demo: db<>fiddle

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

SELECT *,ST_Distance(ST_MakePoint(0.0,0.0)::geography, coordenate::geography)  
FROM location
ORDER BY coordenate::geography <-> ST_MakePoint(0.0,0.0)::geography;

 gid |   coordenate   |   st_distance    
----- ---------------- ------------------
   2 | POINT(0.1 0.1) |   15690.34328966
   1 | POINT(10 10)   | 1565109.09921789
(2 rows)

The operator <-> means distance, so using it on the ORDER BY clause you order the result set by distance.

EDIT: The data type point in not a PostGIS data type, but a geometric data type from PostgreSQL. In order to use ST_Disance you have to cast the points geometry or geography:

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

Demo: db<>fiddle

CodePudding user response：

I try what you said, but I got the next error

SELECT ST_Distance('POINT(0.0 0.0)'::geography,coordenate::geography) FROM app.location;
ERROR:  cannot cast type point to geography
LINE 1: ...T_Distance('POINT(0.0 0.0)'::geography, coordenate::geograph...

the fields of my table are:

ID => Integer
NAME => varchar(255)
COUNTRY => varchar(255)
COORDENATE => POINT