How to create a JOIN query that finds the closest store for each user

Question

I've got two simple tables:

User:
id - int
name - string
lat - decimal
long - decimal

Store:
id - int
name - string
lat - decimal
long - decimal

I'd like to have a query that gets all the users with the closest shop. I don't care about the roundness of the earth, because each user will have a shop pretty close by. That's why I choose to use Pythagoras for finding the nearest distance:

SELECT 
   User.*, 
   Store.*, 
   Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
FROM User, Store

Unfortunately this gives me the Cartesian product, so that I get all the Users with the distance to every store. Is there a way to only get the closest store?

Thanks

So if I understand correctly you have a full list of users and their relative distance to all the stores in Cartesian space? If that's the case, you can always use `GROUP BY` to group the entries based on the Users's `id` and then `ORDER BY` to sort in descending order of distance. Then you can check out this [post](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) to see how the get the first row of each `GROUP BY` and tweak it to your needs to get back the results you want based on your current query. — Michael Platt, Jul 10 '19 at 13:40
Try using [Max](https://www.w3schools.com/sql/sql_min_max.asp) on the Sqr equation to get only one result — Prostagma, Jul 10 '19 at 13:41
Ok, that is exactly the part I'm struggling with. I'm using Access, should I try the top query? — bo-oz, Jul 10 '19 at 13:42

score 1 · Accepted Answer · answered Jul 10 '19 at 13:45

You can group by user and calculate Min(distance) and then link back to first query to figure out which store was that distance from user.

This is your query with some fields removed for clarity (and User.Id aliased)

SELECT 
    User.id as userid, 
    Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
FROM User, Store

This wraps the first query to calculate a minimum distance to a store for each user

select userid, min(distance) from (
    SELECT 
        User.id as userid, 
        Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
    FROM User, Store
 ) as x 
group by userid

Join that back to your original query to populate the user fields and figure out which store is that (minimum) distance from the user


select z.*
from (
    select userid, min(distance) as distance from (
        SELECT 
            User.id as userid, 
            Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
        FROM User, Store
     ) as x 
group by userid
) as y inner join
(
    SELECT 
        User.Id as UserId, ... (populate the remaining user fields), 
        Store.*, 
        Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
    FROM User, Store
) as z
on y.userid = z.userid and y.distance = z.distance

score 0 · Answer 2 · answered Jul 10 '19 at 14:20

0

All you need is cross join the tables and a WHERE clause in which you select the store with the minimum distance from each user like this:

select u.*, s.*
from user as u, store as s
where ((u.Lat - s.Lat)^2+(u.Long - s.Long)^2) = (
  select min((u.Lat - ss.Lat)^2+(u.Long - ss.Long)^2)
  from  store as ss
)

You don't need to apply Sqr() to the calculations.

answered Jul 10 '19 at 14:20

forpas

160,666
10
38
76

Sounds interesting, will test this tomorrow. Thanks. – bo-oz Jul 11 '19 at 05:28

How to create a JOIN query that finds the closest store for each user

2 Answers2