I'm trying to select a set of users based on the latitude and longitude of another user for whom I have the ID. I need to do a distance calculation in the SELECT statement of the query and am not sure how to access the latitude and longitude of the user that I'm searching against.

Here is my query so far (which doesn't work)

SELECT 
    *, 
    (SELECT u1.latitude, u1.longitude 
     FROM user u1 
     WHERE u1.id = '77c3d4e0-37f6-4fae-b8ac-66cffad07179'), 
    (3959 * acos(cos(radians(u1.latitude))
          * cos(radians(latitude))
          * cos(radians(longitude) - radians(u1.longitude))
    + sin (radians(u1.latitude))
       * sin(radians(latitude)))) AS distance 
FROM 
    user u2

Basically I want to get all of the columns from user and a column called distance, which is the distance from the user with the ID

1 Answers

5
Gordon Linoff On Best Solutions

Move the subquery reference to the from clause:

SELECT u2.*, 
       (3959 * acos (
           cos ( radians(u1.latitude) )
           * cos( radians( u2.latitude ) )
           * cos( radians( u2.longitude ) - radians(u1.longitude) )
           + sin ( radians(u1.latitude) )
           * sin( radians( u2.latitude ) )
         )
       ) AS distance
FROM icebreaker_user u2 JOIN
     icebreaker_user u1
     ON u1.id = '77c3d4e0-37f6-4fae-b8ac-66cffad07179'