"Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message
news:brac46$15fr3$2@ID-64669.news.uni-berlin.de...
> Paul Watson wrote:
>
> > Some guy in the late 1800s use 4 'cos it's has a nice root. Then
> > he was surprised when the clocks where wrong, he got lost a lot ....
>
> Had a nice root, did he? Fnaar...!

Definition of an Australian's foreplay:
"Fancy a root, darlin' ?"

Are you doing calculations in SQL or are you using an ESQL/C program? If
the latter, look in your /usr/include directory for values.h (in Solaris
systems, for example) or in /usr/include/math.h on most Linux systems.
You'll find contants there that should be adaquate for anything you're
ever going to do:

/* Some useful constants. */
#if defined __USE_BSD || defined __USE_XOPEN
# define M_E 2.7182818284590452354 /* e */
# define M_LOG2E 1.4426950408889634074 /* log_2 e */
# define M_LOG10E 0.43429448190325182765 /* log_10 e */
# define M_LN2 0.69314718055994530942 /* log_e 2 */
# define M_LN10 2.30258509299404568402 /* log_e 10 */
# define M_PI 3.14159265358979323846 /* pi */
# define M_PI_2 1.57079632679489661923 /* pi/2 */
# define M_PI_4 0.78539816339744830962 /* pi/4 */
# define M_1_PI 0.31830988618379067154 /* 1/pi */
# define M_2_PI 0.63661977236758134308 /* 2/pi */
# define M_2_SQRTPI 1.12837916709551257390 /* 2/sqrt(pi) */
# define M_SQRT2 1.41421356237309504880 /* sqrt(2) */
# define M_SQRT1_2 0.70710678118654752440 /* 1/sqrt(2) */
#endif

/* The above constants are not adequate for computation using `long
double's.
Therefore we provide as an extension constants with similar names as a
GNU extension. Provide enough digits for the 128-bit IEEE quad. */
#ifdef __USE_GNU
# define M_El 2.7182818284590452353602874713526625L /* e */
# define M_LOG2El 1.4426950408889634073599246810018921L /* log_2 e */
# define M_LOG10El 0.4342944819032518276511289189166051L /* log_10
e */
# define M_LN2l 0.6931471805599453094172321214581766L /* log_e 2 */
# define M_LN10l 2.3025850929940456840179914546843642L /* log_e
10 */
# define M_PIl 3.1415926535897932384626433832795029L /* pi */
# define M_PI_2l 1.5707963267948966192313216916397514L /* pi/2 */
# define M_PI_4l 0.7853981633974483096156608458198757L /* pi/4 */
# define M_1_PIl 0.3183098861837906715377675267450287L /* 1/pi */
# define M_2_PIl 0.6366197723675813430755350534900574L /* 2/pi */
# define M_2_SQRTPIl 1.1283791670955125738961589031215452L /*
2/sqrt(pi) */
# define M_SQRT2l 1.4142135623730950488016887242096981L /* sqrt(2) */
# define M_SQRT1_2l 0.7071067811865475244008443621048490L /*
1/sqrt(2) */
#endif


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?
>
> Thanks.
>
> Ravi
>
>
>

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.earthl ink.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/
>