On 16 October 2010 21:13, Vince Carney <vincecarney@xxxxxxxxx> wrote: > Is it possible to do an if/else statement inside acos() to account for a > 1 > or < -1. I can't seem to get if/else working in postgres? > Try to stay on-list, so that others can benefit from the discussion. Yes you could use CASE..WHEN, but that would add extra complexity to your code without solving the underlying problem with acos(). It won't give you accurate answers when the distance between the points is small, which is usually the most common case. <begin rant> It's a shame that so many people quote this formula when telling people how to compute great circle distances, because it really isn't a very good approach. If you're prepared to consider a little maths, then think about this: Suppose y = cos(x) Then for small values of x (absolute value much less than 1), this can be approximated by a power series expansion: y = 1 - x^2 / 2 + O(x^4) So y is approximately 1, and small changes to x have a little effect on y. Thus computing x = acos(y) when y is approximately 1 is numerically very inaccurate. For small distances, computing Haversine using acos() involves an intermediate value for y, which is very close to 1. Rounding errors might push it slightly over 1, but even if they didn't, computing acos(y) is going to amplify those rounding errors, giving an inaccurate answer. For this reason, it is much better to use the Haversine formula that uses asin(). The power series for sin() looks like this: y = sin(x) = x - x^3 / 6 + O(x^5) So if x is very small, so is y, and small changes to x are matched by small changes to y, and the full accuracy of the computation is preserved. There's also another variant of the formula that uses atan2(), which is good for both small and large distances, but is a little more complicated. Aside from being slightly simpler, the acos() formula really has nothing to recommend it. <end rant> Regards, Dean http://en.wikipedia.org/wiki/Great-circle_distance -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
