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(at)boutiquenumerique(dot)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.