Re: Doubt about join clause

On Mon, Apr 20, 2009 at 08:02:49PM -0400, David Wilson wrote:
> On Mon, Apr 20, 2009 at 7:39 PM, jc_mich <juan(dot)michaca(at)paasel(dot)com> wrote:
> > You've understood very well my problem, but also this query works as worse
> > than everything I did before, it throws as many rows as rows are contained
> > my tables clients and stores. I only want to find for every client what
> > store is closer to him, I expect one client to one store and their distance
>
> select clients.id as client_id, (select stores.id from stores order by
> (power(clients.x-stores.x)+power(clients.y-stores.y)) asc limit 1) as
> store_id from clients;
>
> Should do the trick, or at least something very similar.

Another option would be to use DISTINCT ON and the geometric bits in PG,
something like:

SELECT DISTINCT ON (client_id) client_id, store_id, distance
FROM (
SELECT c.id AS client_id, s.id AS store_id, point(c.x,c.y) <-> point(s.x,s.y) AS distance
FROM clients c, stores s)
ORDER BY client_id, distance;

I'd also expect there to be some GiST magic that can be weaved to get
the above to work somewhat efficiently.

--
Sam http://samason.me.uk/