Re: nearest match - Mailing list pgsql-sql

From Ryan
Subject Re: nearest match
Date
Msg-id 11625.65.102.128.233.1047578163.squirrel@fordparts.com
Whole thread Raw
In response to nearest match  ("Ryan" <pgsql-sql@seahat.com>)
List pgsql-sql
> 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





pgsql-sql by date:

Previous
From: "Jordan S. Jones"
Date:
Subject: Retrieving Definition for Composite Type
Next
From: Tilo Schwarz
Date:
Subject: ...