pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting the output of a function used in a where clause
10+ messages / 5 participants
[nested] [flat]
* Getting the output of a function used in a where clause
@ 2005-04-09 01:43 Bill Lawrence <bill.lawrence@cox.net>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Lawrence @ 2005-04-09 01:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-11 08:51 PFC <lists@boutiquenumerique.com>
parent: Bill Lawrence <bill.lawrence@cox.net>
0 siblings, 1 reply; 10+ messages in thread
From: PFC @ 2005-04-11 08:51 UTC (permalink / raw)
To: Bill Lawrence <bill.lawrence@cox.net>; pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-12 01:25 Bill Lawrence <bill.lawrence@cox.net>
parent: PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Lawrence @ 2005-04-12 01:25 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-12 13:55 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Bill Lawrence <bill.lawrence@cox.net>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2005-04-12 13:55 UTC (permalink / raw)
To: Bill Lawrence <bill.lawrence@cox.net>; +Cc: PFC <lists@boutiquenumerique.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-13 03:46 Bill Lawrence <bill.lawrence@cox.net>
0 siblings, 0 replies; 10+ messages in thread
From: Bill Lawrence @ 2005-04-13 03:46 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-13 06:08 Bill Lawrence <bill.lawrence@cox.net>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Lawrence @ 2005-04-13 06:08 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: PFC <lists@boutiquenumerique.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-19 02:00 Rod Taylor <pg@rbt.ca>
parent: Bill Lawrence <bill.lawrence@cox.net>
0 siblings, 1 reply; 10+ messages in thread
From: Rod Taylor @ 2005-04-19 02:00 UTC (permalink / raw)
To: Bill Lawrence <bill.lawrence@cox.net>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; PFC <lists@boutiquenumerique.com>; pgsql-sql
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 <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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-19 02:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2005-04-19 02:59 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Bill Lawrence <bill.lawrence@cox.net>; Scott Marlowe <smarlowe@g2switchworks.com>; PFC <lists@boutiquenumerique.com>; pgsql-sql
Rod Taylor <pg@rbt.ca> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-19 04:08 Bill Lawrence <bill.lawrence@cox.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Lawrence @ 2005-04-19 04:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Rod Taylor <pg@rbt.ca>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; PFC <lists@boutiquenumerique.com>; pgsql-sql
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 <pg@rbt.ca> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Getting the output of a function used in a where clause
@ 2005-04-19 07:44 PFC <lists@boutiquenumerique.com>
parent: Bill Lawrence <bill.lawrence@cox.net>
0 siblings, 0 replies; 10+ messages in thread
From: PFC @ 2005-04-19 07:44 UTC (permalink / raw)
To: Bill Lawrence <bill.lawrence@cox.net>; Tom Lane <tgl@sss.pgh.pa.us>; Rod Taylor <pg@rbt.ca>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-sql
> 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 ?
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-04-19 07:44 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-09 01:43 Getting the output of a function used in a where clause Bill Lawrence <bill.lawrence@cox.net>
2005-04-11 08:51 ` PFC <lists@boutiquenumerique.com>
2005-04-12 01:25 ` Bill Lawrence <bill.lawrence@cox.net>
2005-04-12 13:55 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-04-13 06:08 ` Bill Lawrence <bill.lawrence@cox.net>
2005-04-19 02:00 ` Rod Taylor <pg@rbt.ca>
2005-04-19 02:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-19 04:08 ` Bill Lawrence <bill.lawrence@cox.net>
2005-04-19 07:44 ` PFC <lists@boutiquenumerique.com>
2005-04-13 03:46 Re: Getting the output of a function used in a where clause Bill Lawrence <bill.lawrence@cox.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox