Ravi Krishna wrote:
> "Jonathan Leffler" <jleffler@earthlink.net> wrote:
>> Ravi Krishna wrote:
>>>I am converting a SQL Server stored procedure to Informix.
>>>In SQL Server there is a function PI() which returns 3.1415926535897931
>>>and this can be used in a SQL Server FLOAT variable.
>>>When I try to create a datatype in Informix to store the same value,
>>>it seems the best I could do is to create FLOAT(16) and it can only
>>>take 3.141592653590. Is there a data type in Informix which can take
>>>the precise value of PI. Am I missing something?
>>
>>Well, first of all, the value of PI is infinitely big, so unless you
>>have an inifinite amount of disk space, you can't store it precisely :-)
>>
>>If you want a better approximation to PI, I recommend using
>>DECIMAL(n), where n could go as large as 32.
>>
>>Note that a FLOAT value in Informix is stored in a C double; a DECIMAL
>>is stored in the on-disk format of an ESQL/C 'struct decimal'.
>
> I changed the code to decimal(32), but there is no difference in the
> calculation.
>
> The function I am writing takes two inputs, the airport code of two
> cities and returns the distance between them.
>
> Besides PI, the function uses: ACOS, COS and SIN function.

Aargh! No-one ever tells the whole story first time... :-)

Well, the implementation of the SQL trigonometric functions in
Informix takes a short cut: the DECIMAL value is converted to FLOAT
(SQL FLOAT, C double), passed to the C math library function, and the
result returned - after conversion as necessary.

> I have noticed that the precision in these mathematical functions
> is different in informix and SQLserver, just like PI. As a result
> the distance between cities tends to be off. For example, SQLserver
> shows distance between DFW(Dallas) and ORD (Chicago) as 805 miles.
> Informix shows it as 802. SQLserver shows Dallas to Adelaide,
> Australia as 9294. Informix shows it as 9310.

Oh - well that's because SQL Server uses the SW corner of DFW, but
Informix uses the NE corner - and they're about 3 miles apart.

:-)

Have you done the calculation with a reliable alternative calculator?
Which is more nearly correct? Since the globe is about 40,000 km
around (when the metre was defined, the distance from pole to equator
was supposed to be 10,000 km), a difference of 3 miles (5 km) is about
1 in 8000 or say 1-2 digits in the 5th significant digit. (Or, 3
miles in 800 for 3 digits in the 3rd significant digit?) The
differences at the 15th or 16th digit are, or should be, immaterial.

> The code is exactly same. So I suspect it is due to precision level
> difference.

Very likely. The question is "whose transcendental maths functions
are more accurate"? Off-hand, I don't know. Even if you get a
calculator, or use 'bc -m', or ... will you know whether the
arithmetic is correct to 5, or 10, or 15, or 30 significant digits?
(Equivalently - how will you tell whose answer is more nearly correct?)

What are your coordinates for the DFW, ORD and ADL (Adelaide, S
Australia)? Are you using spherical trigonometry or taking the
spheroidal nature of the world into account? If a sphere, which
diameter; if a sphere, what spheroid (dimensions and alignment)?
Which formulae are you using to determine the distance?

Note that taking the difference of two big numbers is notorious for
reducing the relevant precision of the result.

This is partly a numerical analysis question (in case you hadn't
noticed), and partly a question of fidelity of SQL Server and Informix
to the theoretical limits of the precision. Given a calculation in C
doubles, I would not expect the difference in the answers to be as bad
as it seems to be, but I have not done the calculations to show that
(my spherical trig book is still in the UK, unfortunately). You may
need to generate tabular results for COS, SIN, ACOS on the two DBMS
and compare those with the best available alternative sources (note
the plural!). This may show that one or the other DBMS is
consistently wrong - or just less precise.

I'd worry about the results (in radians) of converting the latitude
and longitude of the airports (which is most likely stored in either
degrees and fractions of a degree or in degrees, minutes and seconds)
that are stored for the airport locations. A bug in that could
account for a lot of trouble.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/