Re: Doubt about join clause - Mailing list pgsql-general

From Sam Mason
Subject Re: Doubt about join clause
Date
Msg-id 20090421011358.GH12225@frubble.xen.chris-lamb.co.uk
Whole thread Raw
In response to Re: Doubt about join clause  (David Wilson <david.t.wilson@gmail.com>)
List pgsql-general
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.michaca@paasel.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/

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: round behavior differs between 8.1.5 and 8.3.7
Next
From: John DeSoi
Date:
Subject: Re: converting from bytea to integers