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(); } }