From bill.lawrence@cox.net Sat Apr 9 01:48:00 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1C11153CD5 for ; Sat, 9 Apr 2005 02:48:00 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02882-09 for ; Sat, 9 Apr 2005 01:47:58 +0000 (GMT) Received: from fed1rmmtao09.cox.net (fed1rmmtao09.cox.net [68.230.241.30]) by svr1.postgresql.org (Postfix) with ESMTP id C94B753CB2 for ; Sat, 9 Apr 2005 02:47:57 +0100 (BST) Received: from KARENSLAPTOP ([70.176.123.3]) by fed1rmmtao09.cox.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with SMTP id <20050409014801.XJIH19936.fed1rmmtao09.cox.net@KARENSLAPTOP> for ; Fri, 8 Apr 2005 21:48:01 -0400 From: "Bill Lawrence" To: Subject: Getting the output of a function used in a where clause Date: Fri, 8 Apr 2005 18:43:39 -0700 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0030_01C53C6A.E15CEA50" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200504/144 X-Sequence-Number: 21036 This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C53C6A.E15CEA50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit 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: $sql = “SELECT zipcode from zipcodes where zipdist($lat1d,$lon1d,lat,long) <= $dist;”; 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 ------=_NextPart_000_0030_01C53C6A.E15CEA50 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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:

 

$sql =3D “SELECT =
zipcode from zipcodes where zipdist($lat1d,$lon1d,lat,long) <=3D =
$dist;”;
 
<=
font
size=3D2 color=3Dblack face=3DArial>What I want to write is =
something like:
 
<=
span
class=3DEmailStyle15>$sql =3D “SELECT zipcode, distance from =
zipcodes where distance <=3D $dist =
order by distance;”;
 
<=
font
size=3D2 color=3Dblack face=3DArial>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?
 
<=
font
size=3D2 color=3Dblack face=3DArial>Thanks in =
advance!
 
<=
font
size=3D2 color=3Dblack face=3DArial>Bill

 

 

 

------=_NextPart_000_0030_01C53C6A.E15CEA50-- From lists@boutiquenumerique.com Mon Apr 11 08:51:33 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BF65153CCC for ; Mon, 11 Apr 2005 05:51:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35938-03 for ; Mon, 11 Apr 2005 08:51:29 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 7AD4453CE1 for ; Mon, 11 Apr 2005 05:51:27 -0300 (ADT) Received: (qmail 24314 invoked from network); 11 Apr 2005 10:51:30 +0200 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 11 Apr 2005 10:51:30 +0200 To: "Bill Lawrence" , pgsql-sql@postgresql.org Subject: Re: Getting the output of a function used in a where clause References: Message-ID: Date: Mon, 11 Apr 2005 10:51:28 +0200 From: PFC Organization: =?utf-8?Q?La_Boutique_Num=C3=A9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/150 X-Sequence-Number: 21043 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 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 > > > From bill.lawrence@cox.net Tue Apr 12 01:29:46 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1B4EA534B2 for ; Mon, 11 Apr 2005 22:29:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43752-06 for ; Tue, 12 Apr 2005 01:29:36 +0000 (GMT) Received: from fed1rmmtao09.cox.net (fed1rmmtao09.cox.net [68.230.241.30]) by svr1.postgresql.org (Postfix) with ESMTP id 28004529E2 for ; Mon, 11 Apr 2005 22:29:35 -0300 (ADT) Received: from KARENSLAPTOP ([70.176.123.3]) by fed1rmmtao09.cox.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with SMTP id <20050412012940.JEQQ19936.fed1rmmtao09.cox.net@KARENSLAPTOP>; Mon, 11 Apr 2005 21:29:40 -0400 From: "Bill Lawrence" To: "PFC" , Subject: Re: Getting the output of a function used in a where clause Date: Mon, 11 Apr 2005 18:25:10 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/158 X-Sequence-Number: 21051 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 = =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 > > > From smarlowe@g2switchworks.com Tue Apr 12 13:55:25 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 419B0534C8 for ; Tue, 12 Apr 2005 10:55:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95896-09 for ; Tue, 12 Apr 2005 13:55:21 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 9E29B534A6 for ; Tue, 12 Apr 2005 10:55:20 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 12 Apr 2005 08:55:23 -0500 Subject: Re: Getting the output of a function used in a where clause From: Scott Marlowe To: Bill Lawrence Cc: PFC , pgsql-sql@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1113314123.20921.117.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 12 Apr 2005 08:55:23 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/163 X-Sequence-Number: 21056 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 > 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 From bill.lawrence@cox.net Wed Apr 13 03:50:48 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4E71053814 for ; Wed, 13 Apr 2005 00:50:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61740-03 for ; Wed, 13 Apr 2005 03:50:38 +0000 (GMT) Received: from fed1rmmtao06.cox.net (fed1rmmtao06.cox.net [68.230.241.33]) by svr1.postgresql.org (Postfix) with ESMTP id E6BD55294E for ; Wed, 13 Apr 2005 00:50:34 -0300 (ADT) Received: from KARENSLAPTOP ([70.176.123.3]) by fed1rmmtao06.cox.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with SMTP id <20050413035031.THBI1497.fed1rmmtao06.cox.net@KARENSLAPTOP>; Tue, 12 Apr 2005 23:50:31 -0400 From: "Bill Lawrence" To: "PFC" , Subject: Re: Getting the output of a function used in a where clause Date: Tue, 12 Apr 2005 20:46:00 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.104 tagged_above=0 required=5 tests=AWL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200504/178 X-Sequence-Number: 21071 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:lists@boutiquenumerique.com] Sent: Tuesday, April 12, 2005 1:03 AM To: Bill Lawrence Subject: Re: [SQL] Getting the output of a function used in a where clause > 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? Complex ? CREATE TABLE stuff ( ... coords BOX NOT NULL, ... ) WITHOUT OIDS; CREATE INDEX cities_coords_idx ON geo.cities USING GIST ( coords gist_box_ops ); For some reason you must use BOX instead ot POINT to use the index. CREATE OR REPLACE FUNCTION boxpoint(FLOAT,FLOAT) RETURNS BOX RETURNS NULL ON NULL INPUT LANGUAGE plpgsql AS $$ DECLARE p POINT; BEGIN p := point($1,$2); IF $1=0 AND $2=0 THEN RETURN NULL; END IF; RETURN box(p,p); END; $$; now use boxpoint(x,y) to select a box : INSERT INTO stuff (...,coords,...) VALUES (...,boxpoint(x,y),...) Now to get all the records whose coords are inside a box using the index : SELECT ... WHERE cords && '((xa,ya),(xb,yb))'::box for all the details look there : http://www.postgis.org/docs/ch04.html#id3530280 it's simple once you're into it. You'll need to install postgis. From bill.lawrence@cox.net Wed Apr 13 06:12:42 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C231753AC9 for ; Wed, 13 Apr 2005 03:12:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04318-05 for ; Wed, 13 Apr 2005 06:12:39 +0000 (GMT) Received: from fed1rmmtao03.cox.net (fed1rmmtao03.cox.net [68.230.241.36]) by svr1.postgresql.org (Postfix) with ESMTP id 1C59453ACE for ; Wed, 13 Apr 2005 03:12:38 -0300 (ADT) Received: from KARENSLAPTOP ([70.176.123.3]) by fed1rmmtao03.cox.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with SMTP id <20050413061239.JFOR1282.fed1rmmtao03.cox.net@KARENSLAPTOP>; Wed, 13 Apr 2005 02:12:39 -0400 From: "Bill Lawrence" To: "Scott Marlowe" Cc: "PFC" , Subject: Re: Getting the output of a function used in a where clause Date: Tue, 12 Apr 2005 23:08:05 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: <1113314123.20921.117.camel@state.g2switchworks.com> Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.017 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/266 X-Sequence-Number: 21159 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 > 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 From pg@rbt.ca Tue Apr 19 02:02:25 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A5D415331C for ; Mon, 18 Apr 2005 23:02:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85593-07 for ; Tue, 19 Apr 2005 02:02:16 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5A9B653972 for ; Mon, 18 Apr 2005 23:02:12 -0300 (ADT) Received: from dyn-70-66.tor.dsl.tht.net (dyn-70-66.tor.dsl.tht.net [134.22.70.66]) by tht.net (Postfix) with ESMTP id B426476A62; Mon, 18 Apr 2005 22:02:22 -0400 (EDT) Subject: Re: Getting the output of a function used in a where clause From: Rod Taylor To: Bill Lawrence Cc: Scott Marlowe , PFC , pgsql-sql@postgresql.org In-Reply-To: References: Content-Type: text/plain Date: Mon, 18 Apr 2005 22:00:09 -0400 Message-Id: <1113876009.41948.1.camel@home> Mime-Version: 1.0 X-Mailer: Evolution 2.2.2 FreeBSD GNOME Team Port Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/269 X-Sequence-Number: 21162 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 > calculation once? It is in some places, but possibly not in that one. 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; > 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 > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- From tgl@sss.pgh.pa.us Tue Apr 19 03:00:09 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5E7865359B for ; Tue, 19 Apr 2005 00:00:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02342-06 for ; Tue, 19 Apr 2005 02:59:59 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2064E52951 for ; Mon, 18 Apr 2005 23:59:58 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3J2xn4i013118; Mon, 18 Apr 2005 22:59:49 -0400 (EDT) To: Rod Taylor Cc: Bill Lawrence , Scott Marlowe , PFC , pgsql-sql@postgresql.org Subject: Re: Getting the output of a function used in a where clause In-reply-to: <1113876009.41948.1.camel@home> References: <1113876009.41948.1.camel@home> Comments: In-reply-to Rod Taylor message dated "Mon, 18 Apr 2005 22:00:09 -0400" Date: Mon, 18 Apr 2005 22:59:49 -0400 Message-ID: <13117.1113879589@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/272 X-Sequence-Number: 21165 Rod Taylor 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, because the planner will happily flatten the subquery into the outer query, resulting in the same situation of zipdist() being present twice in the text of the query. You could force the issue with SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist; since LIMIT/OFFSET clauses presently disable the flattening optimization. Keep in mind though that the OFFSET is an absolute optimization fence: it will result in the subquery being evaluated completely, even if there were outer conditions that might have avoided the need to look at some rows. For example, if the query is SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist AND some-other-conditions then not letting the some-other-conditions migrate down below the evaluation of zipdist could result in making the query be far slower, not faster, than you would get if you weren't trying to outsmart the planner. In general the system operates on the assumption that function calls are cheap relative to disk I/O. If that's not true for you, you're going to have some issues ... regards, tom lane From bill.lawrence@cox.net Tue Apr 19 04:13:17 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2FEFA52A4B for ; Tue, 19 Apr 2005 01:13:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28701-02 for ; Tue, 19 Apr 2005 04:13:14 +0000 (GMT) Received: from fed1rmmtao09.cox.net (fed1rmmtao09.cox.net [68.230.241.30]) by svr1.postgresql.org (Postfix) with ESMTP id 93C8253209 for ; Tue, 19 Apr 2005 01:13:11 -0300 (ADT) Received: from KARENSLAPTOP ([70.176.123.3]) by fed1rmmtao09.cox.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with SMTP id <20050419041310.PRWL19936.fed1rmmtao09.cox.net@KARENSLAPTOP>; Tue, 19 Apr 2005 00:13:10 -0400 From: "Bill Lawrence" To: "Tom Lane" , "Rod Taylor" Cc: "Scott Marlowe" , "PFC" , Subject: Re: Getting the output of a function used in a where clause Date: Mon, 18 Apr 2005 21:08:22 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: <13117.1113879589@sss.pgh.pa.us> Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/342 X-Sequence-Number: 21235 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, far fewer, for other regions of the US), so I've elected to post process the results in my script (calculating the distance for each returned record) and display only the closest 20 results (I intended to use a LIMIT clause combined with an ORDER BY in my SQL... LOL). Again, Thanks for all the great advice! -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, April 18, 2005 8:00 PM To: Rod Taylor Cc: Bill Lawrence; Scott Marlowe; PFC; pgsql-sql@postgresql.org Subject: Re: [SQL] Getting the output of a function used in a where clause Rod Taylor 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, because the planner will happily flatten the subquery into the outer query, resulting in the same situation of zipdist() being present twice in the text of the query. You could force the issue with SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist; since LIMIT/OFFSET clauses presently disable the flattening optimization. Keep in mind though that the OFFSET is an absolute optimization fence: it will result in the subquery being evaluated completely, even if there were outer conditions that might have avoided the need to look at some rows. For example, if the query is SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist AND some-other-conditions then not letting the some-other-conditions migrate down below the evaluation of zipdist could result in making the query be far slower, not faster, than you would get if you weren't trying to outsmart the planner. In general the system operates on the assumption that function calls are cheap relative to disk I/O. If that's not true for you, you're going to have some issues ... regards, tom lane From lists@boutiquenumerique.com Tue Apr 19 07:44:13 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0EA97534A9 for ; Tue, 19 Apr 2005 04:44:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85058-01 for ; Tue, 19 Apr 2005 07:44:04 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id D0F57533A9 for ; Tue, 19 Apr 2005 04:44:03 -0300 (ADT) Received: (qmail 6931 invoked from network); 19 Apr 2005 09:44:10 +0200 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 19 Apr 2005 09:44:10 +0200 To: "Bill Lawrence" , "Tom Lane" , "Rod Taylor" Cc: "Scott Marlowe" , pgsql-sql@postgresql.org Subject: Re: Getting the output of a function used in a where clause References: Message-ID: From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Date: Tue, 19 Apr 2005 09:44:01 +0200 In-Reply-To: User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/277 X-Sequence-Number: 21170 > 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, far fewer, > for Why don't you use a GiST index which will index this bounding box search ?