Re: [SQL] Getting the output of a function used in a where clause

2005-04-22 Thread Bill Lawrence
Thanks Tom and Rod. There are indeed several additional conditions on the real query which prune the search space (I formulate a quick search box and filter on Lat/Lon's within the box). Since my user interface limits the search to a 30 mile radius, there are at most 81 results (in New York city,

Re: [SQL] Getting the output of a function used in a where clause

2005-04-19 Thread PFC
Thanks Tom and Rod. There are indeed several additional conditions on the real query which prune the search space (I formulate a quick search box and filter on Lat/Lon's within the box). Since my user interface limits the search to a 30 mile radius, there are at most 81 results (in New York

Re: [SQL] Getting the output of a function used in a where clause

2005-04-18 Thread Rod Taylor
On Tue, 2005-04-12 at 23:08 -0700, Bill Lawrence wrote: 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

Re: [SQL] Getting the output of a function used in a where clause

2005-04-18 Thread Bill Lawrence
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

Re: [SQL] Getting the output of a function used in a where clause

2005-04-18 Thread Tom Lane
Rod Taylor [EMAIL PROTECTED] writes: You can force it with a subselect though: SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes) AS tab where distance = $dist; The above will *not* stop zipdist from being run twice,

Re: [SQL] Getting the output of a function used in a where clause

2005-04-12 Thread Scott Marlowe
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

Re: [SQL] Getting the output of a function used in a where clause

2005-04-12 Thread Bill Lawrence
Thanks a bunch! Looks pretty step-by-step at the site for the link you sent. I'll give it a shot and see how it turns out. Thanks again for all your help! Bill -Original Message- From: PFC [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 1:03 AM To: Bill Lawrence Subject: Re:

Re: [SQL] Getting the output of a function used in a where clause

2005-04-11 Thread PFC
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 [EMAIL PROTECTED] wrote: HI, Im a newbie so please bear

[SQL] Getting the output of a function used in a where clause

2005-04-10 Thread Bill Lawrence
HI, Im 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