Wednesday, January 2, 2013

How to find distances relative to points of data in the same database

If you have a scenario where you need to calculate the distance in miles, between various point given as longitude and latitude, and these points are in the same database, then simply use the Haver-sine formula to calculate the distances:
    `origin`.`id` AS `o_id`,
    `destination`.`id` AS `d_id`,
    cast((3959 * 
        * cos(radians(`destination`.`latitude`)))
        * cos((radians(`destination`.`longitude`)
        - radians(`origin`.`longitude`))))
        + (sin(radians(`origin`.`latitude`))
        * sin(radians(`destination`.`latitude`))))))
   as decimal (4 , 1 )) AS `miles_apart`
from `point_of_origin` `origin` join `destinations` `destination`;

mysqldump doesn't backup views as views

mysqldump does not dump views as views, but instead, dumps them as tables. Even then, the tables columns all come out as TINYINT(4). If you're not fortunate enough to have Percona's slick backup tools then you'll need another solution.

Here is an over simplified PHP script to get the job done. Simply pipe the output to a .sql file to get it into a neat SQL script file.

// Configuration for the DB Connection
$_db_name = "test";
$_db_host = "";
$_db_user = "root";
$_db_pass = "";
$_db_port = '3306';
// ---- end configuration section
$mysqli = new mysqli($_db_host, $_db_user, $_db_pass, $_db_name, $_db_port);
if(!$mysqli) die("Connection error. Aborting\n");
if (!$result) die("The 1st query failed to return results. Aborting.\n");
$row = $result->fetch_all();
foreach($row as $a => $b)
    echo "CREATE VIEW `".$b[0]."` AS ".$b[1].";\n\n";