From: Bill Lawrence <bill.lawrence@cox.net>
To: PFC <lists@boutiquenumerique.com>
To: pgsql-sql@postgresql.org
Subject: Re: Getting the output of a function used in a where clause
Date: Tue, 12 Apr 2005 20:46:00 -0700
Message-ID: <NEBBJBFOALCOMIDOAMHCMEMODAAA.bill.lawrence@cox.net> (raw)
In-Reply-To: <op.so30m3zjth1vuj@localhost>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bill.lawrence@cox.net, lists@boutiquenumerique.com
Subject: Re: Getting the output of a function used in a where clause
In-Reply-To: <NEBBJBFOALCOMIDOAMHCMEMODAAA.bill.lawrence@cox.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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