Re: 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: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23142980.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

In response to jc_mich <juan(dot)michaca(at)paasel(dot)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(dot)michaca(at)paasel(dot)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(at)postgresql(dot)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(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.

--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)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(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/

2009/4/20 jc_mich <juan(dot)michaca(at)paasel(dot)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(dot)rembialkowski(at)gmail(dot)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 1=1
ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;

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

Thanks

Best regards

Filip Rembiałkowski-3 wrote:
>
> 2009/4/20 jc_mich <juan(dot)michaca(at)paasel(dot)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(dot)rembialkowski(at)gmail(dot)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(dot)michaca(at)paasel(dot)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(dot)michaca(at)paasel(dot)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.