1
votes

I am working in php.

i want to find out the places which are with in a given distance from a geographical latitude and longitude.

i have one table in a mysql database called places, in which there are three columns placeId, latitude and longitude.

user provide latitude, longitude of a place and a distance, then by using the following formula:

d=2*asin(sqrt((sin((lat1-lat2)/2))^2 + cos(lat1)cos(lat2)(sin((lon1-lon2)/2))^2));

i can check whether these place are with in the distance given by the user or not.

I want to know that how can i write a query and function to implement this functionality.

2

2 Answers

6
votes

I have created below function in MYSQL

DELIMITER $$    
DROP FUNCTION IF EXISTS `great_circle_distance`$$
    CREATE DEFINER=`root`@`localhost` FUNCTION `great_circle_distance`(
    lat1 DOUBLE(10,6),
    lon1 DOUBLE(10,6),
    lat2 DOUBLE(10,6),
    lon2 DOUBLE(10,6)
    ) RETURNS double(10,2)
        DETERMINISTIC
    BEGIN
                    DECLARE delta_lat DOUBLE(10,6);
                    DECLARE delta_lon DOUBLE(10,6);
                    DECLARE temp1 DOUBLE(10,6);
                    DECLARE EARTH_RADIUS DOUBLE(10,2);
                    DECLARE distance DOUBLE(10,2);
                    SET lat1 = RADIANS(lat1);
                    SET lon1 = RADIANS(lon1);
                    SET lat2 = RADIANS(lat2);
                    SET lon2 = RADIANS(lon2);
                    SET delta_lat = lat2 - lat1;
                    SET delta_lon = lon2 - lon1;

                    SET temp1 = pow(sin(delta_lat/2.0),2) + cos(lat1) * cos(lat2) * pow(sin(delta_lon/2.0),2);
                    SET EARTH_RADIUS = 3956.0;
                    SET distance = EARTH_RADIUS * 2 * atan2(sqrt(temp1),sqrt(1-temp1));
                    RETURN distance;
        END$$

    DELIMITER ;

Using it as

Select great_circle_distance(z.lat,z.log, 32.2342,-72.42342) AS Distance from tbl_abc AS z;
0
votes

I have created a simple php function to use MySQL queries.

Any query can be executed in 1 simple function.

In case of select query, We can get the selected arguments as variable name contains the selected argument value.

For ex :

<?php

q("select user_name,email_id from users where user_id=48");



   echo $user_name;   echo "<br>";
   echo $email_id;
?>

or you can set your own variable name by putting " as "

<?php

q("select user_name as uname, email_id as email from users where user_id=48");



   echo $uname;    echo "<br>";
   echo $email;
?>

result output will be :

  someuser
  someemail

If more number of rows has been selected, the variable name will be created as an array for ex :

<?php

      q("select user_name,user_id from users");

      for($n=0;$n<count($user_name);$n++)
      {

            if(count($user_name)==1)  // if single row is selected
            {

                $username_val=$user_name;
                $user_ids=$user_id;


            }else{
                $username_val=$user_name[$n]; // for multiple rows selected

               $user_ids=$user_id[$n];
            }

             echo $username;

      }

?>

or you can set your own variable name by putting " as "

<?php

      q("select user_name as un,user_id as uid from users");

      for($n=0;$n<count($user_name);$n++)
      {

            if(count($user_name)==1)  // if single row is selected
            {

                $username_val=$un;
                $user_ids=$uid;


            }else{
                $username_val=$un[$n]; // for multiple rows selected
                 $user_ids=$uid[$n];
            }

             echo $username_val; echo " "; 
             echo $user_ids; echo "<br>";

      }

?>

Result output will be : (If the user table has three rows )

User1 4043
User2 4048
User3 4056

Create mysql Connection file ex : mysql_connect_file.php

<?php

$dbc=new mysqli('localhost', 'my_user', 'my_password', 'my_db');

?>

The php function is below

<?php

   require_once './mysql_connect_file.php';
function q($q)
       {

    $main_q=$q;
    $q=  strtolower($q);
      global $dbc;

              $temp=$q;
              $temp=str_replace(" ", "", $temp);
              $temp=  strtolower($temp);
         $temp=".$temp";
              if(strpos($temp, "update")==1 || strpos($temp, "insert")==1 || strpos($temp, "delete")==1 || strpos($temp, "alter")==1 || strpos($temp, "create")==1)
              {
                  $rd2=  mysqli_query($dbc,$main_q);
                  if($rd2)
                  {
                      return TRUE;
                  }
                  else{


       $mysql_err=  mysqli_error($dbc);

              $err=  debug_backtrace();
              $err_line=$err[0]['line'];
              $err_file=$err[0]['file'];
        echo  "<font color='black'>Error at <b>$err_file on line $err_line  </b>query --></font><font color='maroon'>$main_q</font> (<font color='red'> $mysql_err </font> )";

        return FALSE;

                  }

              }elseif(strpos($temp, "select")==1){


     $qn=  str_replace("select ", "", $q);

     $qn=substr($qn,0,  strpos($qn, " from"));
     $qn="$qn,";

       $selc=  str_replace("`","", $qn);
       $qn=  str_replace("`","", $qn);
       $my_var=array();

      $my_nm=array();
       for($m=1;$m<=substr_count($selc, ',');$m++)
       {
              $my_nm[$m]=substr($qn,0,  strpos($qn, ","));

              $qn=substr($qn,strpos($qn, ",")+1, strlen($qn));
              if(strpos($my_nm[$m]," as ")>0)
              {
      $my_var[$m]=  str_replace(" as ", "~", $my_nm[$m]);
      $my_var[$m]=  str_replace(" ", "", $my_var[$m]);


      $my_var[$m]=substr($my_var[$m],strpos($my_var[$m],"~")+1,strlen($my_var[$m]));
              }else
              {
  $my_var[$m]=substr($my_nm[$m],0,  strlen($my_nm[$m]));  
  $my_var[$m]=  str_replace(" ","", $my_var[$m]);
              }

       }

       $rn=mysqli_query($dbc, $main_q);

       if($rn)
      {

              if(mysqli_num_rows($rn)>0)
              {       

               for($t=1;$t<=count($my_var);$t++)
             {

          $$my_var[$t]=array();


             }


    while($row=mysqli_fetch_array($rn,MYSQLI_ASSOC))
    {

           if(mysqli_num_rows($rn)>1)
           {


              for($t=1;$t<=count($my_var);$t++)
             {

             ${$my_var[$t]}[]=$row[$my_var[$t]];
    }

     }else{

             for($t=1;$t<=count($my_var);$t++)
             {
    $$my_var[$t]=$row[$my_var[$t]];

             }


           }
    }

  if(mysqli_num_rows($rn)>1)
  {
     for($t=1;$t<=count($my_var);$t++)
             {
     $GLOBALS[$my_var[$t]]= sel_mr($my_var,$$my_var[$t]);


             }   


             for($t=1;$t<=count($my_var);$t++)
             {
     return $$my_var[$t];


             }
  }
  if(mysqli_num_rows($rn)==1)
  {

              for($t=1;$t<=count($my_var);$t++)
             {
     $GLOBALS[$my_var[$t]]=$$my_var[$t];

             }
             for($t=1;$t<=count($my_var);$t++)
             {
     return $$my_var[$t];

             }

  }



              }else
              {

       for($t=1;$t<=count($my_var);$t++)
             {
     $GLOBALS[$my_var[$t]]=NULL;

             }



             for($t=1;$t<=count($my_var);$t++)
             {
     return $my_var[$t];


             }

              }

      }else
      {

             for($t=1;$t<=count($my_var);$t++)
             {
     $my=  mysqli_error($dbc);
     if($t==1)
     {
            $err=  debug_backtrace();
            $err_line=$err[0]['line'];
            $err_file=$err[0]['file'];
      echo  "<font color='#ef0000'>Error at <b>$err_file on line $err_line  </b>query --></font><font color='maroon'>$q</font> (<font color='red'> $my </font> )";

     }


             }



             for($t=1;$t<=count($my_var);$t++)
             {
     for($p=0;$p<count($$my_var[$t]);$p++)
     {
            $a=$$my_var[$t];
            return $a;    
     }


             }

      }
              }


       }
     function sel_mr($a,$ab)
     {
            for($t=1;$t<=count($a);$t++)
            {
      foreach ($ab as $my)
      {

             ${$a[$t]}[]=$my;

      }
            }

            for($t=1;$t<=count($a);$t++)
            { 
      return $$a[$t];
            }


     } 

?>

Notes :

You can save this code into a file then you can call this function by including that file name

for ex : if your file name is q.php ( --> contains q function ) then you can use the code for another files by including

<?php

include 'q.php';

  q("select user_name from users where user_id=4048");
   echo $user_name 
?>