谷歌地图-克服超过查询限制错误与GeoCoder在php

Question

我有一个数据库表超过13000元组的数据/地址，我想使用php和地理编码器能够添加所有地址的纬度和经度。我已经这样做了，但它不断返回 over_query_limit 状态错误。我尝试过搜索，大多数人建议在我所做的地理编码器调用之间添加sleep(1)，但无济于事。我想问一下，是否有一种方法可以让我绕过这个状态，在我所有的地址中添加纬度和经度?我的代码如下:

    <?php
error_reporting(E_ALL);
set_time_limit(0);
class google_geocode
{
    public $geocodeOverQueryLimit=false;
    protected $PDO=false;
    public function __construct ()
    {
        $host='127.0.0.1';
        $database='forge';
        $charset='utf8';
        $username='root';
        $password='';
        $this->PDO = new PDO('mysql:host=' . $host . ';dbname=' . $database . ';charset=' . $charset, $username, $password);
    }
    public function dbQuote($text) 
    {
        return $this->PDO->quote($text);
    }
    public function dbName($value) 
    {
        return '`'. $value . '`';
    }
    public function dbQuery($cmd) 
    {
        return $this->PDO->query($cmd);
    }
    public function dbExec($cmd) 
    {
        return $this->PDO->exec($cmd);
    }
    public function dbFetch($res) 
    {
        return $res->fetch(PDO::FETCH_ASSOC);
    }
    public function getGoogleGeocode($address,$language='',$region='' )
    {
        $url = 'http://maps.googleapis.com/maps/api/geocode/json?address=' . urlencode($address) . '&sensor=false&output=json';
        if ($region!='') $url .= '&region=' . $region;
        if ($language!='') $url .= '&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->dbQuery("SELECT `id`,`lng`, `lat`, `data`, `parsed`, TIMESTAMPDIFF(SECOND,`timestamp`,CURRENT_TIMESTAMP()) as `diff` FROM google_geocode_data WHERE address=" . $this->dbQuote($address) . " AND region=" . $this->dbQuote($region) . " AND language=" . $this->dbQuote($language) );
        if ($query&&($row=$this->dbFetch($query))) 
        {
            $return = new stdClass;
            $return->address = $address;
            $return->lng = $row['lng'];
            $return->lat = $row['lat'];
            $return->data = json_decode($row['data']);
            $return->expired = ($row['diff']>$expired*24*60*60);
            return $return;
        }
        return false;
    }
    protected function setGoogleGeocodeData ($address,$region,$data='',$lng='',$lat='',$language='') {
        if (!($_data=json_decode($data)) || $_data->status!=='OK') return false;
        if (!is_numeric($lng) || !is_numeric($lat)) 
        {
            $lng=$_data->results[0]->geometry->location->lng;
            $lat=$_data->results[0]->geometry->location->lat;
        }
        $query=$this->dbQuery("SELECT `id` FROM google_geocode_data WHERE address=" . $this->dbQuote($address) . " AND region=" . $this->dbQuote($region) . " AND language=" . $this->dbQuote($language) );
        if ($query&&($row=$this->dbFetch($query))) 
        {
            $id=$row['id'];
            return !!$this->dbQuery("UPDATE `google_geocode_data` SET `lng`=" . $this->dbQuote($lng) 
                . ",`lat`=" . $this->dbQuote($lat) 
                . ",`data`=" . $this->dbQuote($data) 
                . ",`timestamp`=now() where `id`=" . $id);
        }
        else
            return !!$this->dbQuery("INSERT INTO `google_geocode_data` (`address`,`region`,`language`,`lng`,`lat`,`data`) VALUES (" 
                . $this->dbQuote($address) . "," 
                . $this->dbQuote($region) . "," 
                . $this->dbQuote($language) . "," 
                . $this->dbQuote($lng) . "," 
                . $this->dbQuote($lat) . "," 
                . $this->dbQuote($data) . ")");
    }
    public function getGeocode($address,$region='',$language='') {
        if (false===($data=$this->getGoogleGeocodeData($address,$region,$language)) || $data->expired) 
        {
            if (($data=$this->getGoogleGeocode($address,$language,$region))
                && ($geodata=json_decode($data)) && $geodata->status=='OK') 
                {
                if ($this->setGoogleGeocodeData($address,$region,$data,false,false,$language))
                    $data=$this->getGoogleGeocodeData($address,$region,$language);
                else 
                {
                    $data=new stdClass();
                    $data->address=$address;
                    $data->lat=$geodata->results[0]->geometry->location->lat;
                    $data->lng=$geodata->results[0]->geometry->location->lng;
                    $data->data=json_decode($data);
                    $data->expired=false; 
                }
            } 
            else $data=false;
            if (isset($geodata)&&$geodata&&$geodata->status=='OVER_QUERY_LIMIT')
            {
                $this->geocodeOverQueryLimit=true;
            }
        }
        return $data;
    }
}
class listings
{
    static $PDO;
    static function dbQuote($text) 
    {
        return static::$PDO->quote($text);
    }
    static function dbName($value) 
    {
        return '`'. $value . '`';
    }
    static function dbQuery($cmd) 
    {
        return static::$PDO->query($cmd);
    }
    static function dbExec($cmd) 
    {
        return static::$PDO->exec($cmd);
    }
    static function dbFetch($res) 
    {
        return $res->fetch(PDO::FETCH_ASSOC);
    }
    static function dbQuery1Row($cmd) 
    {
        if (($query=static::$PDO->query($cmd)))
            return $query->fetch(PDO::FETCH_ASSOC);
        return false;
    }
    static function initialize ()
    {

        $host='127.0.0.1';
        $database='forge';
        $charset='utf8';
        $username='root';
        $password='';       
        static::$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>'{$mlsid}' LIMIT 0,1";
        while (($row=static::dbQuery1Row($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->getGeocode($address,'ca','en')))
            {
                echo $data->lat."-".$data->lng;
                static::dbQuery("UPDATE commercials1 SET `lat`=" . static::dbQuote($data->lat) . ",`lng`=" . static::dbQuote($data->lng) . " WHERE mlsid='{$mlsid}'");
            }
            sleep(3);
            if ($geocode->geocodeOverQueryLimit)
            {
                echo "OverQueryLimitn";
                break;
            }
            //usleep (100);
        }
    }
}
listings::initialize ();
echo 'Done';
?>

Answer 1

"over_query_limit状态错误"表示您已达到地理编码配额。您已超过2500个空闲请求的限制。

Google Maps Geocoding API有以下限制:

-标准使用限制标准API的用户:

-每天2500个免费请求，计算为客户端和服务器端查询。每秒50个请求，计算为客户端和服务器端查询。

-如果您想超过2,500个配额，您必须获得许可保费计划客户。