title: PHP and MySQL get the n closest points using the SPATIAL extention
body: <p>I realize that this question has been asked multiple times, but each answer I've seen seems very localized to what the OP wants to achieve. Which is why I am looking for a general answer to simply using a latitude value between -90 and 90, as well as using a longitude between -180 and 180.</p>
  
  <p>So here is the simple premise: There are two get variables received by the php file that contain the lat and lng. Find the <em>n</em> closest points, order by closest to furthest.</p>
  
  <p>Let us setup a simple table that has a indexed point called location</p>
  
  <pre><code>CREATE TABLE `trees` 
  (
    `TREEID` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `location` point NOT NULL,
    PRIMARY KEY (`TREEID`),
    SPATIAL KEY `location` (`location`)
  ) ENGINE=MyISAM;
  </code></pre>
  
  <p>Now we want to work with php</p>
  
  <pre><code>&lt;?php
  
  //Setup variables
  $lat = trim(mysql_prep($_GET['lat'])); 
  $lng = trim(mysql_prep($_GET['lng'])); 
  $n   = 100;
  
  //Make sure they are valid lat and lng
  if (!is_numeric($lat) || floatval($lat) &lt; -90  || floatval($lat) &gt; 90 || 
      !is_numeric($lng) || floatval($lng) &lt; -180 || floatval($lng) &gt; 180  )
  {
          die("Invalid lng and lat coordinates.");
  }
  else
  {
          //Lets find the n closest points to our $lat, $lng
          $get_trees = "Select * FROM trees WHERE _____?______ Order by _?_ ASC";
          $result = mysql_query($get_trees , SQL_Connection());   
  }   
  
  
  ?&gt;
  </code></pre>
  
  <p>Now, it is from my understanding that with the spatial mysql indexing of the locations, queries should be relatively fast. However, there is some debate between answers as to what to do. Some users setup functions that compute haversine distances. Some do haversine right in the query and it looks a hell of a lot messy. Some cut corners depending on how accurate you want the answer. Most haversine answers seem to assume the developer stores the lat/lng as doubles and I'm not sure it would be best put this math to use with the point index. I've looked through the docs for the spatial extension and haven't been able to piece it together. So that question is, what to do. It would be helpful to state the query and the level of accuracy it has, as well as show any sql functions you have predefined. This question assumes we looking for the closest points to a particular lat lng rather than searching for points within a particular distance(e.g within 20km). I am in particular looking for an answer that gives near 100% correctness, for a <em>n</em> between 100 and 500.</p>