From: Jason Earl <jason.earl@simplot.com>
To: Ryan <pgsql-sql@seahat.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: nearest match
Date: 13 Mar 2003 12:22:32 -0700
Message-ID: <873clr5ax3.fsf@npa01zz001.simplot.com> (raw)
References: <10112.65.102.128.233.1047504752.squirrel@fordparts.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jason.earl@simplot.com, pgsql-sql@seahat.com
Subject: Re: nearest match
In-Reply-To: <873clr5ax3.fsf@npa01zz001.simplot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox