To: "Ryan" <pgsql-sql@seahat.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: nearest match
References: <10112.65.102.128.233.1047504752.squirrel@fordparts.com>
From: Jason Earl <jason.earl@simplot.com>
Date: 13 Mar 2003 12:22:32 -0700
Message-ID: <873clr5ax3.fsf@npa01zz001.simplot.com>
Lines: 101
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


I have no idea if this is a *good* way to do this.  I do notice that
PostgreSQL has a pile of geometric functions which (to my mind at
least) would almost certainly be a better match than making this up
yourself.  This is especially true since one of the geometric
functions <-> gives you distance without having to remember the
Pythagorean theorem.

First of all let's solve the problem using the table that you
supplied.  First I created the table and filled it with values.

create table foo (reference_number text, x int, y int);

insert into foo (reference_number, x, y) values ('001', 0, 0);
insert into foo (reference_number, x, y) values ('002', 100, 100);
insert into foo (reference_number, x, y) values ('003', 0, 100);
insert into foo (reference_number, x, y) values ('004', 100, 0);

Then I used a little geometry to calculate the distance from each
point to the point (10, 10).

test=# select reference_number, sqrt(pow(abs(x - 10), 2) + pow(abs(y - 10), 2))
as distance from foo order by distance;
 reference_number |     distance
------------------+------------------
 001              |  14.142135623731
 003              | 90.5538513813742
 004              | 90.5538513813742
 002              | 127.279220613579

Pretty neat, huh?  Now by adding a limit statement we can get the
closest point.  Please note, if several points are equally close
PostgreSQL will simply pick one.

test=# select reference_number, sqrt(pow(abs(x - 10), 2) + pow(abs(y - 10), 2))
as distance from foo order by distance limit 1;
 reference_number |    distance
------------------+-----------------
 001              | 14.142135623731
(1 row)

Well, that was fun.  Now here's a similar example using the built in
geometric types.  First I create the table:

create table bar (reference_number text, location point);

insert into table bar (reference_number, location) values ('001', '(0,0)');
insert into table bar (reference_number, location) values ('002', '(100, 100)');
insert into table bar (reference_number, location) values ('003', '(0, 100)');
insert into table bar (reference_number, location) values ('004', '(100, 0)');

Now I query the table.  Notice how much easier the <-> operator is to
use than the other query.  It's probably faster too because some smart
hacker wrote the operator in C.

test=# select reference_number, location, point '(10, 10)' <-> location as distance from bar;
 reference_number | location  |     distance
------------------+-----------+------------------
 001              | (0,0)     |  14.142135623731
 002              | (100,100) | 127.279220613579
 003              | (0,100)   | 90.5538513813742
 004              | (100,0)   | 90.5538513813742
(4 rows)

Adding the limit clause to narrow our search gets us:

test=# select reference_number, location, point '(10, 10)' <-> location as distance from bar limit 1;
 reference_number | location |    distance
------------------+----------+-----------------
 001              | (0,0)    | 14.142135623731
(1 row)

I hope this is helpful,
Jason

"Ryan" <pgsql-sql@seahat.com> writes:

> 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?
> 
> The more I think about this the more I am realizing it is probally not
> that hard, I just can't seem to grasp the answer right now.
> 
> Thanks,
> Ryan
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org