Thread: Doubt about join queries

Doubt about join queries

From

jc_mich

Date:

20 April 2009, 20:07:09

Hello I have a table with clients and other with stores, I want to calculate minimum distances between stores and clients, the client name and its closer store. At this moment I can only get clients ids and minimum distances grouping by client id, but when I try to join their respective store id, postgres requires me to add store id in group clause and it throws as many rows as the product of number clients and stores. This result is wrong, I only expect the minimum distance for every client. My code looks like this: SELECT distances.client_id, min(distances.distance) FROM( SELECT stores.id AS store_id, clients.id AS client_id, sqrt(power(store.x)+power(store.y)) AS distance FROM stores, clients WHERE 1=1 ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id; Also I've tried this: SELECT clients.id, MIN(distances.distance) FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id, sqrt(power(stores.x)+power(stores.y)) AS distance FROM stores, clients WHERE 1=1) distances ON distances.client_id = clients.id GROUP BY clients.id Thanks in advance!

View this message in context: Doubt about join clause
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Doubt about join clause

From

Bill Moran

Date:

20 April 2009, 20:28:41

In response to jc_mich <juan.michaca@paasel.com>:
>
> Hello
>
> I have a table with clients and other with stores, I want to calculate
> minimum distances between stores and clients, the client name and its closer
> store.
>
> At this moment I can only get clients ids and minimum distances grouping by
> client id, but when I try to join their respective store id, postgres
> requires me to add store id in group clause and it throws as many rows as
> the product of number clients and stores. This result is wrong, I only
> expect the minimum distance for every client.
>
> My code looks like this:
>
> SELECT distances.client_id, min(distances.distance) FROM(
> SELECT stores.id AS store_id, clients.id AS client_id,
> sqrt(power(store.x)+power(store.y)) AS distance
> FROM stores, clients
> WHERE 1=1
> ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
>
> Also I've tried this:
> SELECT clients.id, MIN(distances.distance)
> FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
> sqrt(power(stores.x)+power(stores.y)) AS distance
> FROM stores, clients
> WHERE 1=1) distances
> ON distances.client_id = clients.id GROUP BY clients.id
>
> Thanks in advance!

Something like this should work, (assuming I understand your tables):

SELECT clients.id, stores.id,
  min(sqrt(power(clients.x-stores.x)+power(clients.y-stores.y))
 FROM clients, stores
 GROUP BY clients.id, stores.id;

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: Doubt about join clause

From

jc_mich

Date:

20 April 2009, 23:52:29

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

Thanks a lot


Bill Moran wrote:
>
> In response to jc_mich <juan.michaca@paasel.com>:
>>
>> Hello
>>
>> I have a table with clients and other with stores, I want to calculate
>> minimum distances between stores and clients, the client name and its
>> closer
>> store.
>>
>> At this moment I can only get clients ids and minimum distances grouping
>> by
>> client id, but when I try to join their respective store id, postgres
>> requires me to add store id in group clause and it throws as many rows as
>> the product of number clients and stores. This result is wrong, I only
>> expect the minimum distance for every client.
>>
>> My code looks like this:
>>
>> SELECT distances.client_id, min(distances.distance) FROM(
>> SELECT stores.id AS store_id, clients.id AS client_id,
>> sqrt(power(store.x)+power(store.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1
>> ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
>>
>> Also I've tried this:
>> SELECT clients.id, MIN(distances.distance)
>> FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
>> sqrt(power(stores.x)+power(stores.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1) distances
>> ON distances.client_id = clients.id GROUP BY clients.id
>>
>> Thanks in advance!
>
> Something like this should work, (assuming I understand your tables):
>
> SELECT clients.id, stores.id,
>   min(sqrt(power(clients.x-stores.x)+power(clients.y-stores.y))
>  FROM clients, stores
>  GROUP BY clients.id, stores.id;
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

--
View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23146909.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Doubt about join clause

From

David Wilson

Date:

21 April 2009, 00:03:01

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.

--
- David T. Wilson
david.t.wilson@gmail.com

Re: Doubt about join clause

From

Sam Mason

Date:

21 April 2009, 01:14:07

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/

Re: Doubt about join queries

From

Filip Rembiałkowski

Date:

21 April 2009, 15:05:12

2009/4/20 jc_mich <juan.michaca@paasel.com>

Hello

I have a table with clients and other with stores, I want to calculate
minimum distances between stores and clients, the client name and its closer
store.

At this moment I can only get clients ids and minimum distances grouping by
client id, but when I try to join their respective store id, postgres
requires me to add store id in group clause and it throws as many rows as
the product of number clients and stores. This result is wrong, I only
expect the minimum distance for every client.

My code looks like this:

SELECT distances.client_id, min(distances.distance) FROM(
SELECT stores.id AS store_id, clients.id AS client_id,
sqrt(power(store.x)+power(store.y)) AS distance
FROM stores, clients
WHERE 1=1
ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;

Also I've tried this:
SELECT clients.id, MIN(distances.distance)
FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
sqrt(power(stores.x)+power(stores.y)) AS distance
FROM stores, clients
WHERE 1=1) distances
ON distances.client_id = clients.id GROUP BY clients.id

It would be much easier if you show actual database schema.

It is not clear what is the meaning of stores.x and stores.y variables - what do they measure. If they are just coordinates, then where are client coordinates stored?

--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

Re: Doubt about join queries

From

jc_mich

Date:

21 April 2009, 16:25:26

The problem is not the formula, the problem is the join. I've summarized it
because is a large formula and I've got right results

Here is the complete code code, stores and clients have a longitude and
latitude, and ponderator is a demand variable:

SELECT distances.client_id, min(distances.dist) FROM(
    SELECT stores.id AS store_id, clients.id AS client_id,


6378*stores.ponderator*acos(cos((90-clients.lat)*(pi()/180))*cos((90-stores.lat)*(pi()/180))+sin((90-clients.lat)*(pi()/180))*sin((90-stores.lat)*(pi()/180))*cos((clients.lon-stores.lon)*(pi()/180)))
AS dist
 FROM stores, clients
 WHERE stores.ponderator IS NOT NULL) AS distances GROUP BY
distances.client_id;

And if I use DISTINCT ON on the column client_id, it only takes one id:
client                store            mindist
151220001004-8        1        45.6380309516689
151220001005-2        1        46.0792813322767
151220001006-7        1        46.5147573468595
151220001007-1        1        47.2687415561174
151220001008-6        1        45.8824501966662
151220001009-0        1        44.4038586264495
151220001012-2        1        42.8882442536237

I only want to assign to each client a store in function of the minimum
distance between them.

Thanks

Best regards


Filip Rembiałkowski-3 wrote:
>
> 2009/4/20 jc_mich <juan.michaca@paasel.com>
>
>>
>> Hello
>>
>> I have a table with clients and other with stores, I want to calculate
>> minimum distances between stores and clients, the client name and its
>> closer
>> store.
>>
>> At this moment I can only get clients ids and minimum distances grouping
>> by
>> client id, but when I try to join their respective store id, postgres
>> requires me to add store id in group clause and it throws as many rows as
>> the product of number clients and stores. This result is wrong, I only
>> expect the minimum distance for every client.
>>
>> My code looks like this:
>>
>> SELECT distances.client_id, min(distances.distance) FROM(
>> SELECT stores.id AS store_id, clients.id AS client_id,
>> sqrt(power(store.x)+power(store.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1
>> ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
>>
>> Also I've tried this:
>> SELECT clients.id, MIN(distances.distance)
>> FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
>> sqrt(power(stores.x)+power(stores.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1) distances
>> ON distances.client_id = clients.id GROUP BY clients.id
>>
>
>
> It would be much easier if you show actual database schema.
>
> It is not clear what is the meaning of stores.x and stores.y variables -
> what do they measure. If they are just coordinates, then where are client
> coordinates stored?
>
>
>
>
> --
> Filip Rembiałkowski
> JID,mailto:filip.rembialkowski@gmail.com
> http://filip.rembialkowski.net/
>
>

--
View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23159394.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Doubt about join queries

From

jc_mich

Date:

21 April 2009, 16:26:37

The problem is not the formula, the problem is the join. I've summarized it
because is a large formula and I've got right results

Here is the complete code code, stores and clients have a longitude and
latitude, and ponderator is a demand variable:

SELECT distances.client_id, min(distances.dist) FROM(
    SELECT stores.id AS store_id, clients.id AS client_id,


6378*stores.ponderator*acos(cos((90-clients.lat)*(pi()/180))*cos((90-stores.lat)*(pi()/180))+sin((90-clients.lat)*(pi()/180))*sin((90-stores.lat)*(pi()/180))*cos((clients.lon-stores.lon)*(pi()/180)))
AS dist
 FROM stores, clients
 WHERE stores.ponderator IS NOT NULL) AS distances GROUP BY
distances.client_id;

And if I use DISTINCT ON on the column client_id, it only takes one id:
client                store            mindist
151220001004-8        1        45.6380309516689
151220001005-2        1        46.0792813322767
151220001006-7        1        46.5147573468595
151220001007-1        1        47.2687415561174
151220001008-6        1        45.8824501966662
151220001009-0        1        44.4038586264495
151220001012-2        1        42.8882442536237

I only want to assign to each client a store in function of the minimum
distance between them.

Thanks

Best regards


Filip Rembiałkowski-3 wrote:
>
> 2009/4/20 jc_mich <juan.michaca@paasel.com>
>
>>
>> Hello
>>
>> I have a table with clients and other with stores, I want to calculate
>> minimum distances between stores and clients, the client name and its
>> closer
>> store.
>>
>> At this moment I can only get clients ids and minimum distances grouping
>> by
>> client id, but when I try to join their respective store id, postgres
>> requires me to add store id in group clause and it throws as many rows as
>> the product of number clients and stores. This result is wrong, I only
>> expect the minimum distance for every client.
>>
>> My code looks like this:
>>
>> SELECT distances.client_id, min(distances.distance) FROM(
>> SELECT stores.id AS store_id, clients.id AS client_id,
>> sqrt(power(store.x)+power(store.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1
>> ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
>>
>> Also I've tried this:
>> SELECT clients.id, MIN(distances.distance)
>> FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
>> sqrt(power(stores.x)+power(stores.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1) distances
>> ON distances.client_id = clients.id GROUP BY clients.id
>>
>
>
> It would be much easier if you show actual database schema.
>
> It is not clear what is the meaning of stores.x and stores.y variables -
> what do they measure. If they are just coordinates, then where are client
> coordinates stored?
>
>
>
>
> --
> Filip Rembiałkowski
> JID,mailto:filip.rembialkowski@gmail.com
> http://filip.rembialkowski.net/
>
>

--
View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23159394.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Doubt about join queries

From

jc_mich

Date:

21 April 2009, 16:34:11

The problem is not the formula, the problem is the join. I've summarized it
because is a large formula and I've got right results

Here is the complete code code, stores and clients have a longitude and
latitude, and ponderator is a demand variable:

SELECT distances.client_id, min(distances.dist) FROM(
    SELECT stores.id AS store_id, clients.id AS client_id,


6378*stores.ponderator*acos(cos((90-clients.lat)*(pi()/180))*cos((90-stores.lat)*(pi()/180))+sin((90-clients.lat)*(pi()/180))*sin((90-stores.lat)*(pi()/180))*cos((clients.lon-stores.lon)*(pi()/180)))
AS dist
 FROM stores, clients
 WHERE stores.ponderator IS NOT NULL) AS distances GROUP BY
distances.client_id;

And if I use DISTINCT ON on the column client_id, it only takes one id:
client                store            mindist
151220001004-8        1        45.6380309516689
151220001005-2        1        46.0792813322767
151220001006-7        1        46.5147573468595
151220001007-1        1        47.2687415561174
151220001008-6        1        45.8824501966662
151220001009-0        1        44.4038586264495
151220001012-2        1        42.8882442536237

I only want to assign to each client a store in function of the minimum
distance between them.

Thanks

Best regards


Filip Rembiałkowski-3 wrote:
>
> 2009/4/20 jc_mich <juan.michaca@paasel.com>
>
>>
>> Hello
>>
>> I have a table with clients and other with stores, I want to calculate
>> minimum distances between stores and clients, the client name and its
>> closer
>> store.
>>
>> At this moment I can only get clients ids and minimum distances grouping
>> by
>> client id, but when I try to join their respective store id, postgres
>> requires me to add store id in group clause and it throws as many rows as
>> the product of number clients and stores. This result is wrong, I only
>> expect the minimum distance for every client.
>>
>> My code looks like this:
>>
>> SELECT distances.client_id, min(distances.distance) FROM(
>> SELECT stores.id AS store_id, clients.id AS client_id,
>> sqrt(power(store.x)+power(store.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1
>> ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
>>
>> Also I've tried this:
>> SELECT clients.id, MIN(distances.distance)
>> FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
>> sqrt(power(stores.x)+power(stores.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1) distances
>> ON distances.client_id = clients.id GROUP BY clients.id
>>
>
>
> It would be much easier if you show actual database schema.
>
> It is not clear what is the meaning of stores.x and stores.y variables -
> what do they measure. If they are just coordinates, then where are client
> coordinates stored?
>
>
>
>
> --
> Filip Rembiałkowski
> JID,mailto:filip.rembialkowski@gmail.com
> http://filip.rembialkowski.net/
>
>

--
View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23159394.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Doubt about join queries

From

Michael Nolan

Date:

21 April 2009, 16:42:00

On Tue, Apr 21, 2009 at 12:04 PM, jc_mich <juan.michaca@paasel.com> wrote:

The problem is not the formula, the problem is the join. I've summarized it
because is a large formula and I've got right results

You're quite correct that the problem is the join, because neither of the examples you gave have any code to limit the number of store records returned.

You may need to include something like the following code fragment. (I'm using a 'distance' formula as a proxy for the latitude/longitude formula.)

where store_no = (select store_no from store order by distance(store,customer) limit 1)
--
Mike Nolan

Re: Doubt about join queries

From

jc_mich

Date:

21 April 2009, 17:15:19

Thanks a lot! It works as good as I wish

Best regards.


Michael Nolan wrote:
>
> On Tue, Apr 21, 2009 at 12:04 PM, jc_mich <juan.michaca@paasel.com> wrote:
>
>>
>> The problem is not the formula, the problem is the join. I've summarized
>> it
>> because is a large formula and I've got right results
>>
>>
> You're quite correct that the problem is the join, because neither of the
> examples you gave have any code to limit the number of store records
> returned.
>
> You may need to include something like the following code fragment.  (I'm
> using a 'distance' formula as a proxy for the latitude/longitude formula.)
>
> where store_no = (select store_no from store order by
> distance(store,customer) limit 1)
> --
> Mike Nolan
>
>

--
View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23160612.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.