Title: Overcoming Over Query Limit Error with GeoCoder in php
Tags: php;google-maps;maps
Question: I have a database table with over 13000 tuples of data/addresses and I would like to use php and geocoder to be able to add the latitude and longitude of all the addresses. I have done so but it keeps giving return the over_query_limit status error. I have tried to search and most people proposed adding sleep(1) between the geocoder calls which I have done but to no avail. I wish to ask if there is a way by which I may be able to bypass this status and add the latitude and longitude to all my addresses? My code is below:
<?php
error_reporting(E_ALL);
set_time_limit(0);
class google_geocode
public $geocodeOverQueryLimit=false;
protected $PDO=false;
public function __construct ()
$host='151.154.11.224';
$database='forge';
$charset='utf8';
$username='root';
$password='';
$this-&gt;PDO = new PDO('mysql:host=' . $host . ';dbname=' . $database . ';charset=' . $charset, $username, $password);
public function dbQuote($text)
return $this-&gt;PDO-&gt;quote($text);
public function dbName($value)
return '`'. $value . '`';
public function dbQuery($cmd)
return $this-&gt;PDO-&gt;query($cmd);
public function dbExec($cmd)
return $this-&gt;PDO-&gt;exec($cmd);
public function dbFetch($res)
return $res-&gt;fetch(PDO75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0TCH_ASSOC);
public function getGoogleGeocode($address,$language='',$region='' )
$url = 'http://maps.googleapis.com/maps/api/geocode/json?address=' . urlencode($address) . '&amp;sensor=false&amp;output=json';
if ($region!='') $url .= '&amp;region=' . $region;
if ($language!='') $url .= '&amp;language=' .$language;
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER,0);
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows NT 6.0; rv:41.0) Gecko/20100101 Firefox/41.0');
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$data = curl_exec($ch);
curl_close($ch);
return $data;
protected function getGoogleGeocodeData ($address,$region='',$language='') {
$expired = 365; // in days
$query=$this-&gt;dbQuery("SELECT `id`,`lng`, `lat`, `data`, `parsed`, TIMESTAMPDIFF(SECOND,`timestamp`,CURRENT_TIMESTAMP()) as `diff` FROM google_geocode_data WHERE address=" . $this-&gt;dbQuote($address) . " AND region=" . $this-&gt;dbQuote($region) . " AND language=" . $this-&gt;dbQuote($language) );
if ($query&amp;&amp;($row=$this-&gt;dbFetch($query)))
{
$return = new stdClass;
$return-&gt;address = $address;
$return-&gt;lng = $row['lng'];
$return-&gt;lat = $row['lat'];
$return-&gt;data = json_decode($row['data']);
$return-&gt;expired = ($row['diff']&gt;$expired*24*60*60);
return $return;
}
return false;
protected function setGoogleGeocodeData ($address,$region,$data='',$lng='',$lat='',$language='') {
if (!($_data=json_decode($data)) || $_data-&gt;status!=='OK') return false;
if (!is_numeric($lng) || !is_numeric($lat))
{
$lng=$_data-&gt;results[0]-&gt;geometry-&gt;location-&gt;lng;
$lat=$_data-&gt;results[0]-&gt;geometry-&gt;location-&gt;lat;
}
$query=$this-&gt;dbQuery("SELECT `id` FROM google_geocode_data WHERE address=" . $this-&gt;dbQuote($address) . " AND region=" . $this-&gt;dbQuote($region) . " AND language=" . $this-&gt;dbQuote($language) );
if ($query&amp;&amp;($row=$this-&gt;dbFetch($query)))
{
$id=$row['id'];
return !!$this-&gt;dbQuery("UPDATE `google_geocode_data` SET `lng`=" . $this-&gt;dbQuote($lng)
. ",`lat`=" . $this-&gt;dbQuote($lat)
. ",`data`=" . $this-&gt;dbQuote($data)
. ",`timestamp`=now() where `id`=" . $id);
}
else
return !!$this-&gt;dbQuery("INSERT INTO `google_geocode_data` (`address`,`region`,`language`,`lng`,`lat`,`data`) VALUES ("
. $this-&gt;dbQuote($address) . ","
. $this-&gt;dbQuote($region) . ","
. $this-&gt;dbQuote($language) . ","
. $this-&gt;dbQuote($lng) . ","
. $this-&gt;dbQuote($lat) . ","
. $this-&gt;dbQuote($data) . ")");
public function getGeocode($address,$region='',$language='') {
if (false===($data=$this-&gt;getGoogleGeocodeData($address,$region,$language)) || $data-&gt;expired)
{
if (($data=$this-&gt;getGoogleGeocode($address,$language,$region))
&amp;&amp; ($geodata=json_decode($data)) &amp;&amp; $geodata-&gt;status=='OK')
{
if ($this-&gt;setGoogleGeocodeData($address,$region,$data,false,false,$language))
$data=$this-&gt;getGoogleGeocodeData($address,$region,$language);
else
{
$data=new stdClass();
$data-&gt;address=$address;
$data-&gt;lat=$geodata-&gt;results[0]-&gt;geometry-&gt;location-&gt;lat;
$data-&gt;lng=$geodata-&gt;results[0]-&gt;geometry-&gt;location-&gt;lng;
$data-&gt;data=json_decode($data);
$data-&gt;expired=false;
}
}
else $data=false;
if (isset($geodata)&amp;&amp;$geodata&amp;&amp;$geodata-&gt;status=='OVER_QUERY_LIMIT')
{
$this-&gt;geocodeOverQueryLimit=true;
}
}
return $data;
class listings
static $PDO;
static function dbQuote($text)
return stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$PDO-&gt;quote($text);
static function dbName($value)
return '`'. $value . '`';
static function dbQuery($cmd)
return stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$PDO-&gt;query($cmd);
static function dbExec($cmd)
return stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$PDO-&gt;exec($cmd);
static function dbFetch($res)
return $res-&gt;fetch(PDO75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0TCH_ASSOC);
static function dbQuery1Row($cmd)
if (($query=stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$PDO-&gt;query($cmd)))
return $query-&gt;fetch(PDO75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0TCH_ASSOC);
return false;
static function initialize ()
$host='151.154.11.224';
$database='forge';
$charset='utf8';
$username='root';
$password='';
stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0$PDO = new PDO('mysql:host=' . $host . ';dbname=' . $database . ';charset=' . $charset, $username, $password);
$geocode=new google_geocode();
$mlsid=0;
$sql="SELECT mlsid,apt_num,addr FROM commercials1 WHERE (`lat`='0' OR `lat` IS NULL OR `lng`='0' OR `lng` IS NULL) AND mlsid&gt;'{$mlsid}' LIMIT 0,1";
while (($row=stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0dbQuery1Row($sql)))
{
echo ($mlsid=$row['mlsid']) ."\n";
$address=trim($row['apt_num']);
if (($value=trim($row['addr']))!='')
{
if ($address!='') $address.=' ';
$address.=$value;
}
if ($address=='') continue;
if (($data=$geocode-&gt;getGeocode($address,'ca','en')))
{
echo $data-&gt;lat."-".$data-&gt;lng;
stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0dbQuery("UPDATE commercials1 SET `lat`=" . stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0dbQuote($data-&gt;lat) . ",`lng`=" . stati75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0dbQuote($data-&gt;lng) . " WHERE mlsid='{$mlsid}'");
}
sleep(3);
if ($geocode-&gt;geocodeOverQueryLimit)
{
echo "OverQueryLimit\n";
break;
}
//usleep (100);
}
listings75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0initialize ();
echo 'Done';


Here is another answer: "over_query_limit status error" means you have reached the quota for Geocoding. You've exceeded the 2,500 free requests limit.
The Google Maps Geocoding API has the following limits in place:
-Standard Usage Limits Users of the standard API:
-2,500 free requests per day, calculated as the sum of client-side and
server-side queries. 50 requests per second, calculated as the sum of
client-side and server-side queries.
-If you want to exceed the 2,500 quota, you have to be licensed as a
Premium Plan
customer.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2016:00:06/26/16', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<php><google-maps><maps>', 'Title': 'Overcoming Over Query Limit Error with GeoCoder in php', 'ViewCount': 487, 'id': 38034408, 'user_AboutMe': '', 'user_AccountId': 5517338.0, 'user_DisplayName': 'gastonche', 'user_Reputation': 453.0, 'user_Views': 44.0, 'user_id': 4381603.0}}