Re: [SQL] abs value, power, sqrt

On Thu, 19 Nov 1998, Michael Olivier wrote:

:Hi folks,
:
:I previously posted this to pgsql-novice but haven't gotten an answer,
:so I thought I'd try here.
:
:I need a way to get abs value of a floating point number and (possibly
:separate function for) abs value of an integer in a select statement.
:Is there a function to do this? If not, how can I write a function to
:do it?
:
:Also how do I do squares and square roots? I saw some \|/ type notation
:for square root in an SQL book, but I didn't get postgres to take that.

On my RH installation I found:

  file:/usr/doc/postgresql-6.3.2/user/c05.htm

which I have attached.  It documents the operators and functions
available.  Examples of your specific operators are:

  absolute value: @-5
  square root   : |/ 25

Square would just be x * x or use the exponentiation operator:

  2.0 ^ 3.0

As an example, here is a snippet from a perl cgi script that finds all the
zip codes within a specified radius of a target location.  The target
location is specified by $lat and $lon (in degrees), the radius by
$Radius (in miles).

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#! /usr/bin/perl -w
# file: zips.by.location.m4

snip ...

# radius is in miles ...
$Radius = $input{'radius'};
# convert radius to degrees, circumference is pi * d
$Radius = $Radius * 360.0 / (7900.0 * atan2(1,1) * 4.0);
$RadiusSquared = $Radius * $Radius;

snip ...

# call psql to find the zip codes ...
$temp = qx{ psql -d lrcf -c "
  select
    city, state, zip5,
    population, latitude, longitude
  from
    zips
  where
    ( ( ((latitude - $lat) ^ 2.0) +
       ((longitude - $lon) ^ 2.0) )
      < $RadiusSquared );" };

# print results to html page
print "<pre>$temp</pre>";

snip ...

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Hope this helps,
======================================================================
Bob Smither, Ph.D.      281-331-2744; fax:-4616      Smither@C-C-I.Com
Windows - making simple things easy, and interesting things impossible
======================================================================