From: "Bill Lawrence" <bill.lawrence@cox.net>
To: "PFC" <lists@boutiquenumerique.com>, <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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
In-Reply-To: <op.so30m3zjth1vuj@localhost>
Importance: Normal

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.