Re: storing the value of PI
What worries me is that I actually understood the following :) Clifton PS. Merry Christmas, Everyone :) "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:fOPBb.7058$_r6.1008@newsread1.news.pas.earthlink.net... > 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/ >