Skip site navigation (1) Skip section navigation (2)

Re: [NOVICE] [pgsql-advocacy] Function which gives back the nearest neighbours

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>,Virgile Beddok <virgile(dot)beddok(at)igd(dot)fraunhofer(dot)de>,pgsql-novice(at)postgresql(dot)org, pgsql-advocacy(at)postgresql(dot)org,pgsql-admin(at)postgresql(dot)org
Subject: Re: [NOVICE] [pgsql-advocacy] Function which gives back the nearest neighbours
Date: 2005-03-27 16:32:12
Message-ID: 5796.1111941132@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-advocacypgsql-novice
Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> On Sun, Mar 27, 2005 at 13:24:34 +0800,
>   Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> wrote:
>>> I'm looking for an existing function which allows me to search the nearest
>>> neighbours of the requested value.
>> 
>> Well you could try something like:
>> 
>> SELECT * FROM table ORDER BY ABS(val - 2) LIMIT 1;
>> 
>> That doesn't get you all the way there, but it's an idea...

> For multidimensional objects you can do the same thing with a distance
> metric function. It will be relatively slow since this won't be indexable
> and will require a sort of all of the values. If you have some bound on
> how far apart points can be, then you might be able to limit the set
> of candidate points using an indexable search.

I'd probably go with looking for the nearest "above" neighbor and
nearest "below" neighbor separately, eg

	select * from tab where val > 'target' order by val limit 1;
	select * from tab where val < 'target' order by val desc limit 1;

If there's an index on val, this should work really well.  Of course, if
"nearest" is being defined in multidimensional terms as Bruno is
imagining, it doesn't work at all...

BTW, why is this thread cross-posted to so many lists?  It seems
off-topic for at least two of 'em.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2005-03-27 16:36:53
Subject: Re: [pgsql-advocacy] Function which gives back the nearest neighbours
Previous:From: Tom LaneDate: 2005-03-27 16:06:03
Subject: Re: Time Conversion

pgsql-admin by date

Next:From: Bruno Wolff IIIDate: 2005-03-27 16:36:53
Subject: Re: [pgsql-advocacy] Function which gives back the nearest neighbours
Previous:From: Tom LaneDate: 2005-03-27 16:23:00
Subject: Re: Help: my table is corrupt!

pgsql-advocacy by date

Next:From: Bruno Wolff IIIDate: 2005-03-27 16:36:53
Subject: Re: [pgsql-advocacy] Function which gives back the nearest neighbours
Previous:From: Mitch PirtleDate: 2005-03-27 15:37:35
Subject: Re: postgresQL.org "Latest News" or "Latest Product"?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group