CODEWITHSUNDEEP
mysqlsql
user1801605
user1801605

Reputation: 441

How to query computed distance from coordinates with multiple tables?

My app needs to search the nearby users where the users' photos are in other table. Below is my query, but it produces syntax error.

TABLE 1 (students)
student_id | name |  latitude  | longitude
----------------------------------------
1          | A    | 34.xxxxxx  | -118.xxxxxx
2          | B    | 34.xxxxxx  | -118.xxxxxx
3          | C    | 34.xxxxxx  | -118.xxxxxx


TABLE 2 (photos)
photo_id   | student_id | photo             | main 
---------------------------------------------------
1          | 1          | apple.jpg         |   1
2          | 1          | orange.jpg        |   0 
3          | 2          | cantaloupe.jpg    |   1
4          | 1          | lemon.jpg         |   0


SET @lat = 34.080318;
SET @lng = -118.239095;
SELECT s.student_id,s.name,s.latitude,s.longitude,p.photo,
    (   ACOS( COS( RADIANS( @lat  ) ) 
            * COS( RADIANS( s.latitude ) )
            * COS( RADIANS( s.longitude ) - RADIANS( @lng ) )
            + SIN( RADIANS( @lat  ) )
            * SIN( RADIANS( s.latitude ) )
        )
    * 3959
    ) AS distance
    FROM students s INNER JOIN photos p ON s.student_id = p.student_id
    HAVING distance < 10
    WHERE p.main=1 AND s.student_id=1
    ORDER BY distance ASC;

Upvotes: 1

Views: 163

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The WHERE and HAVING are in the wrong order:

SELECT s.student_id,s.name,s.latitude,s.longitude,p.photo,
    (   ACOS( COS( RADIANS( @lat  ) ) 
            * COS( RADIANS( s.latitude ) )
            * COS( RADIANS( s.longitude ) - RADIANS( @lng ) )
            + SIN( RADIANS( @lat  ) )
            * SIN( RADIANS( s.latitude ) )
        )
    * 3959
    ) AS distance
    FROM students s INNER JOIN photos p ON s.student_id = p.student_id
    WHERE p.main=1 AND s.student_id=1
    HAVING distance < 10
    ORDER BY distance ASC;

By the way, this uses a MySQL extension regarding the HAVING clause. In most databases, you would use a subquery.

Upvotes: 1

Related Questions