From: "Bill Lawrence" <bill.lawrence@cox.net>
To: "Scott Marlowe" <smarlowe@g2switchworks.com>
Cc: "PFC" <lists@boutiquenumerique.com>, <pgsql-sql@postgresql.org>
Subject: Re: Getting the output of a function used in a where clause
Date: Tue, 12 Apr 2005 23:08:05 -0700
Message-ID: <NEBBJBFOALCOMIDOAMHCAENBDAAA.bill.lawrence@cox.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In-Reply-To: <1113314123.20921.117.camel@state.g2switchworks.com>
Importance: Normal

Thanks,

Unfortunately, I think that solution requires the distance calculation to be
executed twice for each record in the table. There are ~70K records in the
table. Is the postgres query optimizer smart enough to only perform the
calculation once?

Bill


-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Tuesday, April 12, 2005 6:55 AM
To: Bill Lawrence
Cc: PFC; pgsql-sql@postgresql.org
Subject: Re: [SQL] Getting the output of a function used in a where clause

Why not just do:

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


On Mon, 2005-04-11 at 20:25, Bill Lawrence wrote:
> 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
> where distance <= $dist;";
>
> OR you could use a gist index with a geometric datatype to get it a lot
> faster.
>
>
> On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <bill.lawrence@cox.net>
> 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
> > 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 = "SELECT zipcode, distance from zipcodes where distance <= $dist
> > order
> > by distance;";
> >
> > But I don't the magic SQL phrase to populate the distance variable using
> > my
> > nifty function. Do I need to create an output type for distance?
> >
> > Thanks in advance!
> >
> > Bill
> >
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings