title: SQL Query For Total Points Within Radius of a Location
body: <p>I have a database table of all zipcodes in the US that includes city,state,latitude &amp; longitude for each zipcode. I also have a database table of points that each have a latitude &amp; longitude associated with them. I'd like to be able to use 1 MySQL query to provide me with a list of all unique city/state combinations from the zipcodes table with the total number of points within a given radius of that city/state. I can get the unique city/state list using the following query:</p>
  
  <pre><code>select city,state,latitude,longitude
  from zipcodes 
  group by city,state order by state,city;
  </code></pre>
  
  <p>I can get the number of points within a 100 mile radius of a specific city with latitude '$lat' and longitude '$lon' using the following query:</p>
  
  <pre><code>select count(*) 
  from points 
  where (3959 * acos(cos(radians($lat)) * cos(radians(latitude)) * cos(radians(longitude) - radians($lon)) + sin(radians($lat)) * sin(radians(latitude)))) &lt; 100;
  </code></pre>
  
  <p>What I haven't been able to do is figure out how to combine these queries in a way that doesn't kill my database. Here is one of my sad attempts:</p>
  
  <pre><code>select city,state,latitude,longitude,
      (select count(*) from points
       where status="A" AND 
            (3959 * acos(cos(radians(zipcodes.latitude)) * cos(radians(latitude)) * cos(radians(longitude) - radians(zipcodes.longitude)) + sin(radians(zipcodes.latitude)) * sin(radians(latitude)))) &lt; 100) as 'points' 
  from zipcodes 
  group by city,state order by state,city;
  </code></pre>
  
  <p>The tables currently have the following indexes:</p>
  
  <pre><code>Zipcodes - `zip` (zip)
  Zipcodes - `location` (state,city)
  Points - `status_length_location` (status,length,longitude,latitude)
  </code></pre>
  
  <p>When I run explain before the previous MySQL query here is the output:</p>
  
  <pre><code>+----+--------------------+----------+------+------------------------+------------------------+---------+-------+-------+---------------------------------+
  | id | select_type        | table    | type | possible_keys          | key                    | key_len | ref   | rows  | Extra                           |
  +----+--------------------+----------+------+------------------------+------------------------+---------+-------+-------+---------------------------------+
  |  1 | PRIMARY            | zipcodes | ALL  | NULL                   | NULL                   | NULL    | NULL  | 43187 | Using temporary; Using filesort | 
  |  2 | DEPENDENT SUBQUERY | points   | ref  | status_length_location | status_length_location | 2       | const | 16473 | Using where; Using index        | 
  +----+--------------------+----------+------+------------------------+------------------------+---------+-------+-------+---------------------------------+
  </code></pre>
  
  <p>I know I could loop through all the zipcodes and calculate the number of matching points within a given radius but the points table will be growing all the time and I'd rather not have stale point totals in the zipcodes database. I'm hoping a MySQL guru out there can show me the error of my ways. Thanks in advance for your help!</p>