Re: Getting the output of a function used in a where clause

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.