Brighton Web Design

Sometime it is necessary to list items which are close by geographically. If you have the longitude and latitude then this is a simple matter and can be done within one sql statement.

An example implementation is shown below. As companies very rarely change location, and this query will cause MySql to traverse each row in the table everytime it is run, the results here are cached using Zend Cache.

<?php

/*
* A class to list the nearby companies
*/

class NearbyCompanies
{

private $co;

public static function getTable($id)
{

$nc = new NearbyCompanies();
$temp = $nc->getNearbyTable($id);
return $temp;

}

public function __construct()
{

$this->co = new Companies(null);

}

private function getNearbyTable($id)
{

// see if the item is in the cache
Zend_Loader::loadClass('Zend_Cache');
$frontendOptions  =  array( 'lifetime' => 60 * 60 *24 * 60, // 60 days for nearby - companies rarely move!
'automatic_serialization' => true);

$backendOptions  = array(  'cache_dir' =>  ROOT_DIR . '/application/cache','file_name_prefix' => 'zend_cache_nearby','hashed_directory_level' => 2);

$query_cache = Zend_Cache::factory('Core','File',$frontendOptions, $backendOptions);

$cacheName = "nearby{$id}";

if (! ($result = $query_cache->load($cacheName)))
{

// do the query for result
$result = $this->getTableFromDatabase($id);
$query_cache->save($result, $cacheName);

}
return $result;

}

private function getTableFromDatabase($id)
{

$result = $this->getTableRowsFromDatabase($id);
//echo array_count($result);

if (8 < count($result))
{

$output[] = "<h3>Nearby:</h3>";
$output[] = "<table>";
$output[] = "<tr><th>Company</th><th>In Categories</th><th>Distance</th></tr>";
$output[] = implode("\n",$result);
$output[] = "</table>";
return implode("\n",$output);

}

}

private function getTableRowsFromDatabase($id)
{

$db = Zend_Registry::get('db');
// use the appropriate table for the site
$table  = Zend_Registry::get('data_table');

// get the longitude and latitude of the current record
$row = $db->fetchRow("SELECT longitude,latitude FROM {$table} WHERE id = {$id}");

$longitude = $row['longitude'];
$latitude = $row['latitude'];

$output = array();
// maybe some empty data - don't perform if values not valid
if (is_numeric($latitude) AND is_numeric($longitude))
{

$sql = "SELECT *, ((ACOS(SIN({$latitude} * PI() / 180) * SIN(latitude * PI() / 180) + COS({$latitude} * PI() / 180) * COS(latitude * PI() / 180) * COS(({$longitude} - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM {$table} WHERE id != {$id} AND latitude > 50 ORDER BY distance ASC LIMIT 10";

$result = $db->fetchAll($sql);

foreach ($result as $data_table_row)
{

$output[] = $this->formatRow($data_table_row);

}

}

return $output;
}

private function formatRow($row)
{

$title   = $row['title'];
$href   = "<a href=\"/companies/{$row['title']}\">{$title}</a>";
$categories     = $this->co->getCategoriesNoPrefix($row['id']);
$distance       = number_format($row['distance'],2);
return "<tr><td>{$href}</td><td>{$categories}</td><td>{$distance}</td></tr>";

}

}

?>