1: <?php
  2: /**
  3:  * Mysql implementation for storing/searching geo location data for events.
  4:  * Makes use of the GIS extensions available in mySQL 4.1 and later.
  5:  *
  6:  * Copyright 2009-2012 Horde LLC (http://www.horde.org/)
  7:  *
  8:  * See the enclosed file COPYING for license information (GPL). If you
  9:  * did not receive this file, see http://www.horde.org/licenses/gpl.
 10:  *
 11:  * @author  Michael J. Rubinsky <mrubinsk@horde.org>
 12:  * @package Kronolith
 13:  */
 14: class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql
 15: {
 16:     /**
 17:      * Conversion factor needed by search functions
 18:      *  Roughly 69 miles per distance unit
 19:      *
 20:      * @var integer
 21:      */
 22:     private $_conversionFactor = 69;
 23: 
 24:     /**
 25:      * Set the location of the specified event _id
 26:      *
 27:      * @see Kronolith_Geo_Base#setLocation()
 28:      * @throws Kronolith_Exception
 29:      */
 30:     public function setLocation($event_id, $point)
 31:     {
 32:         /* First make sure it doesn't already exist */
 33:         $sql = 'SELECT COUNT(*) FROM kronolith_events_mysqlgeo WHERE event_id = ?';
 34: 
 35:         try {
 36:             $count = $this->_db->selectValue($sql, array($event_id));
 37:         } catch (Horde_Db_Exception $e) {
 38:             throw new Kronolith_Exception($e);
 39:         }
 40: 
 41:         /* Do we actually have data? */
 42:         if ((empty($point['lat']) || empty($point['lon'])) && $count) {
 43:             // Delete the record.
 44:             $this->deleteLocation($event_id);
 45:             return;
 46:         } elseif (empty($point['lat']) || empty($point['lon'])) {
 47:             return;
 48:         }
 49: 
 50:         if (empty($point['zoom'])) {
 51:             $point['zoom'] = 0;
 52:         }
 53: 
 54:         /* INSERT or UPDATE */
 55:         if ($count) {
 56:             $sql = 'UPDATE kronolith_events_mysqlgeo SET event_coordinates = GeomFromText(\'POINT(%F %F)\'), event_zoom = ? WHERE event_id = ?';
 57:         } else {
 58:             $sql = 'INSERT into kronolith_events_mysqlgeo (event_coordinates, event_zoom, event_id) VALUES(GeomFromText(\'POINT(%F %F)\'), ?, ?)';
 59:         }
 60:         $sql = sprintf($sql, $point['lat'], $point['lon']);
 61:         $values = array($point['zoom'], $event_id);
 62: 
 63:         try {
 64:             $this->_db->execute($sql, $values);
 65:         } catch (Horde_Db_Error $e) {
 66:             throw new Kronolith_Exception($e);
 67:         }
 68:     }
 69: 
 70:     /**
 71:      * Get the location of the provided event_id.
 72:      *
 73:      * @see kronolith/lib/Driver/Kronolith_Driver_Geo#getLocation($event_id)
 74:      * @throws Kronolith_Exception
 75:      */
 76:     public function getLocation($event_id)
 77:     {
 78:         $sql = 'SELECT x(event_coordinates) as lat, y(event_coordinates) as lon, event_zoom as zoom FROM kronolith_events_mysqlgeo WHERE event_id = ?';
 79:         try {
 80:             return $this->_db->selectOne($sql, array($event_id));
 81:         } catch (Horde_Db_Exception $e) {
 82:             throw new Kronolith_Exception($e);
 83:         }
 84:     }
 85: 
 86:     /**
 87:      * Search for events "close to" a given point.
 88:      *
 89:      * TODO: If all we really use the geodata for is distance, it really doesn't
 90:      *       make sense to use the GIS extensions since the distance calculations
 91:      *       are done with Euclidian geometry ONLY ... and therefore will give
 92:      *       incorrect results when done on a geocentric coordinate system...
 93:      *       They might be useful if we eventually want to do searches on
 94:      *       MBRs
 95:      *
 96:      * @see kronolith/lib/Driver/Kronolith_Driver_Geo#search($criteria)
 97:      * @throws Kronolith_Exception
 98:      */
 99:     public function search($criteria)
100:     {
101:         $point = $criteria['point'];
102:         $limit = empty($criteria['limit']) ? 10 : $criteria['limit'];
103:         $radius = empty($criteria['radius']) ? 10 : $criteria['radius'];
104: 
105:         /* Allow overriding the default conversion factor */
106:         $factor = empty($criteria['factor']) ? $this->_conversionFactor : $criteria['factor'];
107: 
108:         $params = array($factor, $radius, $limit);
109:         $sql = "SELECT event_id, "
110:                . "GLength(LINESTRINGFromWKB(LineString(event_coordinates, GeomFromText('POINT(" . (float)$point['lat'] . " " . (float)$point['lon'] . ")')))) * ? as distance, "
111:                . "x(event_coordinates) as lat, y(event_coordinates) as lon FROM kronolith_events_mysqlgeo HAVING distance < ?  ORDER BY distance ASC LIMIT ?";
112: 
113:         try {
114:             $results = $this->_db->selectAll($sql, $params);
115:         } catch (Horde_Db_Exception $e) {
116:             throw new Kronolith_Exception($e);
117:         }
118: 
119:         return $results;
120:     }
121: 
122: }
123: