Обсуждение: Doubt about join queries
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.
View this message in context: Doubt about join clause
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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/
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.
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
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/
2009/4/20 jc_mich <juan.michaca@paasel.com>
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?
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/
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.
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.
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.
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
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.