Postgres - Calculate distance with postgis

Question

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;

Answer 1

To get distances in metres/kilometres you need to either transform your coordinates to a SRS that has metre as unit or, if possible, use geography instead of geometry, as ST_Distance returns the distance of two geography parameters in metres (1km = 1000m), e.g.

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

Casting geometry / text to geography

Demo: 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)

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

Casting point to geography

The data type point is not a PostGIS data type, but a geometric data type from PostgreSQL. In order to use ST_Distance you have to cast the points to either geometry or geography.

Demo: 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)

Further reading:

• ST_MakePoint
• ST_SetSRID
• ST_Distance