pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednearest match
6+ messages / 5 participants
[nested] [flat]
* nearest match
@ 2003-03-12 21:32 Ryan <pgsql-sql@seahat.com>
0 siblings, 3 replies; 6+ messages in thread
From: Ryan @ 2003-03-12 21:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: nearest match
@ 2003-03-13 08:57 Tomasz Myrta <jasiek@klaster.net>
parent: Ryan <pgsql-sql@seahat.com>
2 siblings, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2003-03-13 08:57 UTC (permalink / raw)
To: Ryan <pgsql-sql@seahat.com>; +Cc: 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?
>
> 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.
Can you say it in math logic? How to find, that some points are nearer
then other without counting distance?
For image maps consider using geometric types like polygon. You need
only one row for one area when using them.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: nearest match
@ 2003-03-13 17:22 Ryan Orth <ryan@seahat.com>
parent: Ryan <pgsql-sql@seahat.com>
2 siblings, 1 reply; 6+ messages in thread
From: Ryan Orth @ 2003-03-13 17:22 UTC (permalink / raw)
To: jeff@propertykey.com; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: nearest match
@ 2003-03-13 17:56 Ryan <pgsql-sql@seahat.com>
0 siblings, 0 replies; 6+ messages in thread
From: Ryan @ 2003-03-13 17:56 UTC (permalink / raw)
To: jeff@propertykey.com; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: nearest match
@ 2003-03-13 19:22 Jason Earl <jason.earl@simplot.com>
parent: Ryan <pgsql-sql@seahat.com>
2 siblings, 0 replies; 6+ messages in thread
From: Jason Earl @ 2003-03-13 19:22 UTC (permalink / raw)
To: Ryan <pgsql-sql@seahat.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: nearest match
@ 2003-03-14 05:18 Larry Rosenman <ler@lerctr.org>
parent: Ryan Orth <ryan@seahat.com>
0 siblings, 0 replies; 6+ messages in thread
From: Larry Rosenman @ 2003-03-14 05:18 UTC (permalink / raw)
To: Ryan Orth <ryan@seahat.com>; jeff@propertykey.com; +Cc: pgsql-sql
--On Thursday, March 13, 2003 11:22:21 -0600 Ryan Orth <ryan@seahat.com>
wrote:
>> Ryan wrote:
> 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?
postgres specific. There are a WHOLE bunch of geometric operators and
functions
in PG.
LER
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-14 05:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-12 21:32 nearest match Ryan <pgsql-sql@seahat.com>
2003-03-13 08:57 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-13 17:22 ` Ryan Orth <ryan@seahat.com>
2003-03-14 05:18 ` Larry Rosenman <ler@lerctr.org>
2003-03-13 19:22 ` Jason Earl <jason.earl@simplot.com>
2003-03-13 17:56 Re: nearest match Ryan <pgsql-sql@seahat.com>
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