X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8B56A534A7 for ; Sun, 27 Mar 2005 17:20:27 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52197-06 for ; Sun, 27 Mar 2005 16:20:21 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 542E153687 for ; Sun, 27 Mar 2005 17:20:20 +0100 (BST) Received: (qmail 11045 invoked by uid 500); 27 Mar 2005 16:36:53 -0000 Date: Sun, 27 Mar 2005 10:36:53 -0600 From: Bruno Wolff III To: Christopher Kings-Lynne Cc: Virgile Beddok , pgsql-novice@postgresql.org, pgsql-advocacy@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [pgsql-advocacy] Function which gives back the nearest neighbours Message-ID: <20050327163653.GH9280@wolff.to> Mail-Followup-To: Bruno Wolff III , Christopher Kings-Lynne , Virgile Beddok , pgsql-novice@postgresql.org, pgsql-advocacy@postgresql.org, pgsql-admin@postgresql.org References: <2657.146.140.8.144.1111775776.squirrel@webmail.igd.fraunhofer.de> <42464392.9040903@familyhealth.com.au> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42464392.9040903@familyhealth.com.au> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.012 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/447 X-Sequence-Number: 16945 On Sun, Mar 27, 2005 at 13:24:34 +0800, Christopher Kings-Lynne 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.