patternsqlMinor

Why earthdistance <@> operator returns different value than earth_distance function?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

whyoperatorearth_distanceearthdistancethanfunctionvaluedifferentreturns

Problem

Seeing a different value with ` and earth_distance`

with points as (
  select 
    '(1,1)'::point as p1
    ,'(2,2)'::point as p2
)
select 
  ((p1  p2) * 1609.34::double precision) as d1
  ,(earth_distance(ll_to_earth(p1[1], p1[0]), ll_to_earth(p2[1], p2[0]))) as d2
from points

Result is:

157224.717583288 157402.326073052

Almost 200m difference - why is that?

Solution

They do different things,

earth_distance Returns the great circle distance between two points on the surface of the Earth.

` Gives the distance in statute miles between two points on the Earth's surface.




So this is the difference between the average of the two (assuming neither is correct) is given as,

SELECT ((d2-d1) / ((d2+d1)/2)) * 100 AS distance_diff FROM results;
   distance_diff   
-------------------
 0.112652419817465
(1 row)


Which is 0.11%. Per the Great-circle distance on wikipedia

  
The Earth is nearly spherical (see Earth radius) so great-circle distance formulas give the distance between points on the surface of the Earth (as the crow flies) correct to within 0.5% or so.

So which one is more accurate?

, clearly. But, we can do better as the docs say,

  
In this module, the Earth is assumed to be perfectly spherical. (If that's too inaccurate for you, you might want to look at the PostGIS project.)

SELECT ST_Distance(
  ST_MakePoint(1,1)::geography,
  ST_MakePoint(2,2)::geography
);
   st_distance   
-----------------
 156876.14940189
(1 row)


But if your coordinators are SRID 4326, the correct-most distance is

156876.14940189`.

Code Snippets

SELECT ((d2-d1) / ((d2+d1)/2)) * 100 AS distance_diff FROM results;
   distance_diff   
-------------------
 0.112652419817465
(1 row)

SELECT ST_Distance(
  ST_MakePoint(1,1)::geography,
  ST_MakePoint(2,2)::geography
);
   st_distance   
-----------------
 156876.14940189
(1 row)

Context

StackExchange Database Administrators Q#156167, answer score: 4

Revisions (0)

No revisions yet.