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;
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;
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.
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: