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

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'.

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

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?

There is no such thing as a precise value of PI. Just one is
a little bit more precise than the other.

Michael

>
> Thanks.
>
> Ravi
>
>
>

"Jonathan Leffler" <jleffler@earthlink.net> 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'.

Thanks.

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. 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.

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

Ravi

Use 4 - it's close enough :-))

Michael Krzepkowski 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?
>
> There is no such thing as a precise value of PI. Just one is
> a little bit more precise than the other.
>
> Michael
>
> >
> > Thanks.
> >
> > Ravi
> >
> >
> >

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #

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/

Paul Watson wrote:

> Use 4 - it's close enough :-))

3 is closer, though.

> Michael Krzepkowski 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?
>>
>> There is no such thing as a precise value of PI. Just one is
>> a little bit more precise than the other.

--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

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 ....

Cheers


Obnoxio The Clown wrote:
>
> Paul Watson wrote:
>
> > Use 4 - it's close enough :-))
>
> 3 is closer, though.
>
> > Michael Krzepkowski 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?
> >>
> >> There is no such thing as a precise value of PI. Just one is
> >> a little bit more precise than the other.
>
> --
> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
> - Coluche

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #

"Jonathan Leffler" <jleffler@earthlink.net> wrote

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

Actually I apologise for this stupid thread. A complete code check
revealed that I made a typo in the earth radius, a static figure.
Once corrected, the results of SQLSever and Informix match.

Ravi.

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...!

> Obnoxio The Clown wrote:
>>
>> Paul Watson wrote:
>>
>> > Use 4 - it's close enough :-))
>>
>> 3 is closer, though.
>>
>> > Michael Krzepkowski 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?
>> >>
>> >> There is no such thing as a precise value of PI. Just one is
>> >> a little bit more precise than the other.
>>
>> --
>> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
>> - Coluche
>

--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche