public function testUpdateTicketClusters()
{
$ticket = new Ticket($this->testTicketId);
GeoCluster::updateTicketClusters($ticket);
$zend_db = Database::getConnection();
$result = $zend_db->query('select * from ticket_geodata where ticket_id=?')->execute([$this->testTicketId]);
$row = $result->current();
for ($i = 0; $i <= 6; $i++) {
$this->assertGreaterThan(0, $row["cluster_id_{$i}"]);
}
}
public function save()
{
$this->setLastModified(date(DATE_FORMAT));
parent::save();
if ($this->needToUpdateClusters) {
GeoCluster::updateTicketClusters($this);
}
$this->updateSearchIndex();
}
/**
* Finds or creates a cluster for the given Ticket
*
* If there is already a cluster within a certain distance,
* this will return the ID for that cluster.
* If it doesn't find a nearby cluster, this will add a
* new cluster to the database and return the new ID.
*
* @param Ticket $ticket
* @param int $level
*/
public static function assignClusterIdForLevel(Ticket $ticket, $level)
{
$lat = $ticket->getLatitude();
$lng = $ticket->getLongitude();
$dist = 0.01 * pow(2, $level * 2);
$minX = $lng - $dist;
$maxX = $lng + $dist;
$minY = $lat - $dist;
$maxY = $lat + $dist;
// Geocluster center points are in the database, so we can just look
// them up. However, MySQL spatial functions only allow bounding box
// queries, not points inside a circle, which is what we want.
// So, here, we're still calculating the haversine distance
$sql = "\n\t\tSELECT id,x(center) as longitude, y(center) as latitude,\n\t\t\t( SELECT\n\t\t\t\t(ACOS(SIN(RADIANS(y(center))) * SIN(RADIANS({$lat}))\n\t\t\t\t + COS(RADIANS(y(center))) * COS(RADIANS({$lat}))\n\t\t\t\t * COS(RADIANS(x(center) - {$lng}))) * 6371.0)\n\t\t\t) as distance\n\t\tFROM geoclusters\n\t\tWHERE level=?\n\t\t and (ACOS(SIN(RADIANS(y(center))) * SIN(RADIANS({$lat}))\n\t\t\t + COS(RADIANS(y(center))) * COS(RADIANS({$lat}))\n\t\t\t * COS(RADIANS(x(center) - {$lng}))) * 6371.0) < {$dist}\n\t\torder by distance\n\t\tLIMIT 1\n\t\t";
$zend_db = Database::getConnection();
$result = $zend_db->query($sql)->execute([$level]);
if (count($result)) {
$row = $result->current();
return $row['id'];
} else {
$cluster = new GeoCluster();
$cluster->setLevel($level);
$cluster->setLatitude($lat);
$cluster->setLongitude($lng);
$cluster->save();
return $cluster->getId();
}
}
