DB account must have “execute” permission!
Stored procedure example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DELIMITER $$ CREATE PROCEDURE `pc_nearby_full`(IN `my_lat` FLOAT, IN `my_lng` FLOAT, IN `my_dist` DECIMAL(10,2)) READS SQL DATA BEGIN select *, acos(cos(my_lat * (PI()/180)) * cos(my_lng * (PI()/180)) * cos(dest.latitude * (PI()/180)) * cos(dest.longitude * (PI()/180)) + cos(my_lat * (PI()/180)) * sin(my_lng * (PI()/180)) * cos(dest.latitude * (PI()/180)) * sin(dest.longitude * (PI()/180)) + sin(my_lat * (PI()/180)) * sin(dest.latitude * (PI()/180)) ) * 3959 as Dist from place as dest having Dist < my_dist; END$$ DELIMITER ; |
PHP example: (Codeigniter)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
function nearby($lat, $lng, $radius=10){ $sql = "CALL `pc_nearby`(?, ?, ?)"; $values = array( $lat, $lng, $radius ); $query = $this->db->query($sql, $values); //logg($this->db->last_query()); if($query == false){ return false; }else{ return $query->result(); } } |
Reference: