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.