3

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?

  • 2
    I imagine one is straight-line distance, the other is circular. I'm sure someone will be along to correct me shortly ;) – Philᵀᴹ Nov 23 '16 at 15:06
4

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.

  • So earth_distance measures distance on earth surface and <@> along straight line? Which is more precise? – user606521 Nov 23 '16 at 16:40
  • 1
    @user606521 updated. – Evan Carroll Nov 23 '16 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.