Spatial query in sql database

Question

hi I am currently developing a geo location based app which updates the user's location and displays the shops around him, I am using ordinary sql queries in sql database .My php code to select the shops around the user is :

 /*
    * Following code will list all the products
  */

 // array for JSON response
 $response = array();

// include db connect class
 require_once __DIR__ . '/db_connect.php';

 // connecting to db
  $db = new DB_CONNECT();
  if (isset($_POST['code']) && isset($_POST['lat']) && isset($_POST['lng'])) {
 $code = $_POST['code'];
   $lng = $_POST['lng'];
  $lat = $_POST['lat'];

// get all data from table this is my code to fetch data where i need change

         $result = mysql_query("SELECT *, ( 3959 * acos( cos( radians($lat) ) * cos(   radians(   lat ) ) * cos( radians(lng) - radians($lng) ) + sin( radians($lat) ) * sin(     radians(lat))))        AS distance 
  FROM    maintable 
  HAVING distance < 25 
 ORDER BY distance
  ") or die(mysql_error());

// check for empty result

     if (mysql_num_rows($result) > 0) {
    // looping through all results
    // products node
   $response["people"] = array();

while ($row = mysql_fetch_array($result)) {
    // temp user array
    $people = array();
    $product["_id"] = $row["_id"];
    $product["name"] = $row["name"];
    $product["distance"] = $row["distance"];
    $product["lat"] = $row["lat"];
    $product["lng"] = $row["lng"];
    $product["image_bit"] = $row["image_bit"];
    $product["security"] = $row["security"];
    $product["status"] = $row["status"];
    $product["code"] = $row["country_code"];
    $product["phone"] = $row["phone"];


    // push single product into final response array
    array_push($response["people"], $people);
  }
// success
    $response["success"] = 1;

   // echoing JSON response
   echo json_encode($response);
   } else {
  // no products found
  $response["success"] = 0;
  $response["message"] = "No people found";

// echo no users JSON
  echo json_encode($response);
   }
 }
      else{
   //required field is missing
   $response["success"] = 0;
   $response["message"] = "Required field missing";
   // echoing JSON response
   echo json_encode($response);
    }

?>

i store the user's latitude and longitude values as lat and lng respectively. But i recently heard about spatial datatype in sql,which will is more efficient, what i need to know is if i need to use spatial queries what change should be done in my lat and lng columns in sql table and can please anyone modify mysql query as a spatial query .any help would be appreciable

Answer 1

You can try a quadkey. Translate the points to a binary and interleave it. Treat it as base-4 number. The quadkey is similar to a quadtree but also it has interesting properties. It is often use for tiling.