From: "Bill Lawrence" <bill.lawrence@cox.net>
To: "PFC" <lists@boutiquenumerique.com>, <pgsql-sql@postgresql.org>
Subject: Re: Getting the output of a function used in a where clause
Date: Mon, 11 Apr 2005 18:25:10 -0700
Message-ID: <NEBBJBFOALCOMIDOAMHCIEMGDAAA.bill.lawrence@cox.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Importance: Normal
In-Reply-To: <op.so1772t7th1vuj@localhost>

Boy I sure thought that would work... I received the following from =
postgres:

ERROR:  Attribute "distance" not found.

Started looking into gist.... Looks complex.

Any other ideas?


-----Original Message-----
From: PFC [mailto:lists@boutiquenumerique.com]
Sent: Monday, April 11, 2005 1:51 AM
To: Bill Lawrence; pgsql-sql@postgresql.org
Subject: Re: [SQL] Getting the output of a function used in a where =
clause


try:

SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from =
zipcodes=20
where distance <=3D $dist;";

OR you could use a gist index with a geometric datatype to get it a lot=20
faster.


On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence =
<bill.lawrence@cox.net>=20
wrote:

> HI,
>
> I'm a newbie so please bear with me. I have a function defined (got it =

> from
> one of your threads... thanks Joe Conway) which calculates the =
distance
> between 2 zip code centeroids (in lat,long). This thing works great.
> However, I want to sort my results by distance without incurring the
> additional burden of executing the function twice. A simplified =
version=20
> of
> my current SQL (written in a perl cgi)  that returns a set of zip =
codes
> within a given radius is:
>
>
> What I want to write is something like:
>
> $sql =3D "SELECT zipcode, distance from zipcodes where distance <=3D =
$dist=20
> order
> by distance;";
>
> But I don't the magic SQL phrase to populate the distance variable =
using=20
> my
> nifty function. Do I need to create an output type for distance?
>
> Thanks in advance!
>
> Bill
>
>
>