Re: nearest match - Mailing list pgsql-sql
From | Jason Earl |
---|---|
Subject | Re: nearest match |
Date | |
Msg-id | 873clr5ax3.fsf@npa01zz001.simplot.com Whole thread Raw |
In response to | nearest match ("Ryan" <pgsql-sql@seahat.com>) |
List | 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.142135623731003 | 90.5538513813742004 | 90.5538513813742002 | 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.142135623731002 | (100,100)| 127.279220613579003 | (0,100) | 90.5538513813742004 | (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