X-Original-To: pgsql-sql@postgresql.org Received: from t-9.net (unknown [64.246.36.43]) by postgresql.org (Postfix) with SMTP id 9737D476357 for ; Thu, 13 Mar 2003 14:21:19 -0500 (EST) Received: (qmail 31714 invoked by uid 0); 13 Mar 2003 17:22:21 -0000 Received: from localhost (HELO seahat.com) (127.0.0.1) by localhost with SMTP; 13 Mar 2003 17:22:21 -0000 Received: from 65.102.128.233 (SquirrelMail authenticated user snubber@seahat.com) by fordparts.com with HTTP; Thu, 13 Mar 2003 11:22:21 -0600 (CST) Message-ID: <10193.65.102.128.233.1047576141.squirrel@fordparts.com> Date: Thu, 13 Mar 2003 11:22:21 -0600 (CST) Subject: Re: nearest match From: "Ryan Orth" To: In-Reply-To: <3E709AF4.9050407@propertykey.com> References: <10112.65.102.128.233.1047504752.squirrel@fordparts.com> <3E709AF4.9050407@propertykey.com> X-Priority: 3 Importance: Normal Cc: X-Mailer: SquirrelMail (version 1.2.8) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200303/351 X-Sequence-Number: 12390 > Ryan wrote: >> I'm doing some work with part diagrams and server-side image maps. I >> want to store single point coordinates (x,y) for reference numbers in >> a table looking like: >> >> reference_number text, >> x int, >> y int >> >> My question is: How can I find the *nearest* match of some clicked on >> coordinates without specifying some arbitrary distance from the stored >> point? > > How about something like this? > > select reference_number, '(50,50)'::point <-> point(x,y) as distance > from my_table order by distance limit 1; > > With a reasonably small table, it should perform fairly well. Hot damn! thats exactly what I needed. I imagine I would only be comparing the distance of 50 points at any given time (about the max number of reference numbers on any given image) so its mighty quick. (184 points takes 1.80 msec to check) I didn't even know about those geometric operators (I find new stuff every day I swear). Are they SQL standard or postgres specific? Ryan