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

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 <[email protected]>
>
>>
>> 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:[email protected]
> 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.