PHP and MySQL get the n closest points using the SPATIAL extention

Question

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.

So here is the simple premise: There are two get variables received by the php file that contain the lat and lng. Find the n closest points, order by closest to furthest.

Let us setup a simple table that has a indexed point called location

CREATE TABLE `trees` 
(
  `TREEID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `location` point NOT NULL,
  PRIMARY KEY (`TREEID`),
  SPATIAL KEY `location` (`location`)
) ENGINE=MyISAM;

Now we want to work with php

<?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) < -90  || floatval($lat) > 90 || 
    !is_numeric($lng) || floatval($lng) < -180 || floatval($lng) > 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());   
}   


?>

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 n between 100 and 500.

Simpler alternative available at stackoverflow.com/questions/4995428/… — abhinav penmetsa

Satbir Kira Satbir Kira · Accepted Answer · 2014-01-19T20:23:58

Okay so I ended up using haversine. It's actually quite fast, and using it as a ajax request to get points is a good idea.

$get_trees = "Select TREEID,  X(location) as lat, Y(location) as lng,
(6371000 * 
        acos( 
          cos( radians($lat) )
        * cos( radians( X(location) ) ) 
        * cos( radians( Y(location) ) - radians($lng) )
        + sin( radians($lat) ) 
        * sin( radians( X(location) ) ) ) ) AS distance 


FROM trees ORDER BY distance ASC LIMIT 0 , $n;";

$result = mysql_query($get_trees , SQL_Connection());   

while ($record = mysql_fetch_assoc ($result))
{
    $tree_identity = $record['TREEID'];
    $distance_in_meters = $record['distance'];
    $tree_lat = $record['lat'];
    $tree_lng = $record['lng'];
}

Since the formula assumes the earth is a sphere(constant radius), here are the values for the radius you can use:

For Meters, use 6371000

For Miles, use 3959

For Kilometers, use 6371

I hope someone will find this helpful or at least it will give you a idea of how to approach the problem. If you want points to be within a certain distance, like 10 meters, add "HAVING distance < 10".

PHP and MySQL get the n closest points using the SPATIAL extention

1 Answers