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: [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. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
