gmradius.sql

# INITIAL QUERY
SELECT *, 
  (
    ( 
      ACOS(
        SIN($LAT * Pi() / 180)
        * SIN(latitude * Pi() / 180) 
        +
        COS($LAT * Pi() / 180) 
        * COS(latitude * Pi() / 180)
        * COS(($LNG - longitude ) * Pi() / 180)
      ) * 180 / Pi() 
    ) * 60 * 1.1515 
  ) AS distance 
FROM   school 
HAVING distance <= $RADIUS 

# QUERY WITH JOIN ON VOTES
SELECT s.*, COUNT(v.gmi_id),
  (
    ( 
      ACOS(
        SIN(42.460984 * Pi() / 180)
        * SIN(latitude * Pi() / 180) 
        +
        COS(42.460984 * Pi() / 180) 
        * COS(latitude * Pi() / 180)
        * COS((-83.90612 - longitude ) * Pi() / 180)
      ) * 180 / Pi() 
    ) * 60 * 1.1515 
  ) AS distance 
FROM school AS s
LEFT JOIN vote AS v ON v.gmi_id = s.gmi_id
WHERE (
    ( 
      ACOS(
        SIN(42.460984 * Pi() / 180)
        * SIN(latitude * Pi() / 180) 
        +
        COS(42.460984 * Pi() / 180) 
        * COS(latitude * Pi() / 180)
        * COS((-83.90612 - longitude ) * Pi() / 180)
      ) * 180 / Pi() 
    ) * 60 * 1.1515 
  ) <= 20
GROUP BY v.gmi_id