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:

 select 
    `origin`.`id` AS `o_id`,
    `destination`.`id` AS `d_id`,
    cast((3959 * 
        acos((((cos(radians(`origin`.`latitude`))
        * 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.

<?php
// Configuration for the DB Connection
$_db_name = "test";
$_db_host = "127.0.0.1";
$_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");
$result = $mysqli->query("SELECT v.TABLE_NAME, v.VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS `v` WHERE v.TABLE_SCHEMA='test';");
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";
}

Find the nearest location by latitude and longitude in MySQL using the Haversine formula

If you need to find the nearest location of any point of interest by latitude and longitude in MySQL, then you can use the Haversine formula. Following here is and example that assumes you have a table named "destinations" which contains amongst other things, the latitude and longitude of those 'destinations'. An example of valid destinations in a real-world application would be a table listing hotels and their lat/long coordinates which would enable one to write a query to return the nearest hotels from that table based on your lat/long coordinates. (The distance provided is in miles. To get the distance in kilometers, exchange 3539 with 6371)

SET @my_lat = 35.229205 ; -- the source latitude
SET @my_lon = -114.26811 ;-- the source longitude

SELECT *, ( 3959 * acos( cos( radians(
-- latitude
@my_lat) ) * cos( radians( 
destination.latitude ) ) * cos( radians( 
destination.longitude ) - radians(
-- longitude
@my_lon) ) + sin( radians(
-- latitude
@my_lat) ) * sin( radians( 
destination.latitude ) ) ) ) AS distance 
-- table containing targets to compare distance
FROM destination
ORDER BY distance LIMIT 1 ;

Full example at SQL Fiddle ---> http://sqlfiddle.com/#!2/abba1/4/0 ( PLEASE DONATE )

I recently needed to use this to find the official timezone for a city closest to a person based on their latitude, longitude coordinates in MySQL, so as not to have to depend on look-ups to a web service. For anyone interested, city-timezone data can be downloaded from the geonames.org website and loaded as the 'destinations' table.

Reference:
http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula

Load timezone data by city and country into MySQL

If ever you needed to look-up timezone data in your MySQL database you know that you need to find a nice source for that data and load it into your database.

This is how I did it:

1. Download timezone information by cities from this site: http://citytimezones.info/cms/pending_requests.htm (this example uses the CSV format - download that file)
2. Unzip the file and note the location (full path) of the "cities.txt" file.
3. Use the following SQL to create a table to hold the data:

   CREATE TABLE `timezone_cities` (
     `city` varchar(100) NOT NULL,
     `timezone` varchar(250) NOT NULL,
     `country` varchar(45) NOT NULL,
     `latitude` double NOT NULL,
     `longitude` double NOT NULL,
     `timezone_name` varchar(200) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

4. Adapt and run the following SQL statement in MySQL to load the data:

   LOAD DATA LOCAL INFILE '/Users/nerd/Downloads/cities_csv/cities.txt'
   INTO TABLE timezone_cities
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   LINES TERMINATED BY '\r\n';
   

Make sure that when you run the LOAD DATA query, that there are no errors or warnings.
Note that I did not optimize the design of the table (field sizes) to match the data ( DIY )

Auto-Constructing DDL for a MySQL federated table from and existing table

For anyone looking for a tool to automatically generate a DDL statement for a federated table from the DDL statement of an existing table, here is the PHP script (note: this was only tested with MySQL 5.5)

Please see http://forums.mysql.com/read.php?10,512901,513020#msg-513020

 <?php  
 parse_str(implode('&', array_slice($argv, 1)), $_GET);  
 $mysqli = new mysqli('target_host', 'login_id', 'password', 'db_name', '3306' );  
 if(!$mysqli) die("Connection error. Aborting\n");  
 // this is the name of the server used in the CREATE SERVER statement  
 $fedlink_server = $_GET['fedlink_server'] = "my_preconfigured_fedlink";  
 $source_table = $_GET['source_table']   ;//= "accounts";  
 $target_table = $_GET['target_table']   ;//= "fedlinked_accounts";  
 if ((strlen($source_table) ==0) OR (strlen($target_table) ==0))  
   die("Required values: source_table=?, target_table=?\n\n");  
 echo "source_table=$source_table\n";  
 echo "target_table=$target_table\n";  
 $sql = "SHOW CREATE TABLE `$source_table`;";  
 $mysqli_result = $mysqli->query($sql);  
 if (!$mysqli_result) die("The query failed to return results\n");  
 $row = $mysqli_result->fetch_all();  
 $ddl = $row[0][1];  
 $ddl = str_replace("''", "'", $ddl);  
 $ddl_arr = preg_split("/\n/", $ddl);  
 $ddl_end = $ddl_arr[sizeof($ddl_arr) -1];  
 $ddl_arr[0] = "CREATE TABLE `$target_table` (";  
 $charset= (substr_count($ddl_end, "DEFAULT CHARSET=utf8") == 1)  
     ? $charset = " CHARSET=utf8"  
     : "";  
 $ddl_arr[sizeof($ddl_arr) -1]  
   = ") ENGINE=FEDERATED $charset CONNECTION='$fedlink_server/$source_table';";  
 echo join("\n", $ddl_arr);  
 ?>  

Simply execute the script as follows (for example):

php -f the-script-in-a-file.php source_table=accounts target_table=linkedtable_account

NOTE: This script was written to suite MY needs and won't necessarily work for every situation.

The Rise of Postgres

I have been advocating PostgreSQL close to a decade now with little success. People just didn't seem to get it. It seems that it's finally catching on now. Various apps I use are now being deployed with pro-PostgresSQL. Even ones which in the past were pro-MySQL (Atlassian Confluence for example). The tide is finally changing. Read the full article here, and another one here.

All I can say on this is that it's nice to be ahead of the curve, even when everyone thinks you're nuts - Unfortunately that doesn't help you get hired, in a place like Las Vegas.

TIP: How to save on web page hosting fees

As many of you know web hosting companies charge a fee for hosting websites. Most small businesses just need an online-brochure for their website. Nothing fancy, just a simple template to put images and text into. Such solutions usually cost about $5 per month. If a simple online-brochure is all you need your website to be then there's really no reason to spend that money. Did you know that blogger.com can host your site at your own "www" URL and you wont have to pay for the hosting?
All you need to do after purchasing your domain, is:

1. create a blogger.com account for your business
2. go to the settings for your blogger.com account
3. click "Add a custom domain", and follow the instructions

Probably the most complicated part for most people, is that you will need to update your DNS settings in your domain so that the web page requests to your "www" site point to your blogger site. Don't worry about adds. You don't have to publish Google AddSense adds on your SMB site. I would imagine that many SMBs that use GoDaddy for hosting, might be looking to have their site hosted elsewhere, after GoDaddy's service outage on Sept 10th.