Convert huge number of places into latitude & longitude and update MySQL database using PHP

Question

I'm new to PHP, and I want to get latitude and longitude of a place and then add them to MySQL database.

I'm using Google Geo-code API to get them, this is what I do right-row

for ($i = 0; $i<1000; $i++) {
        $sql = mysql_query("SELECT place_address FROM place_locator WHERE place_id =".$i, $this->db) or die('invalide request : ' . mysql_error());

        if (mysql_num_rows($sql)) {
            while ($place = mysql_fetch_assoc($sql)) {

                //Encode the place string I got, to get rid of space
                $encodePlace = str_replace(" ", "%20", $place["place_address"]);
                //Use Google API
                $url = 'http://maps.googleapis.com/maps/api/geocode/json?address='.$encodePlace.'&sensor=false';
                //Use Curl to send the request
                $ch = curl_init($url);

                curl_setopt($ch, CURLOPT_HEADER, 0);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

                $response = curl_exec($ch);
                $obj = json_decode($response, true);


                $updateSql = mysql_query("UPDATE  `place_locator`.`place_locator` SET
                `latitude` =  '".$obj["results"][0]["geometry"]["location"]["lat"]."',
                `longitude` =  '".$obj["results"][0]["geometry"]["location"]["lng"]."' WHERE  `place_locator`.`place_id` = ".$i, $this->db) or die('Invalide : ' . mysql_error());

                curl_close($ch);
            }
        }

It works for a loop of 10,when going to 1000, it will take a lot of time and many results didn't updated to the database.

I think may be multi thread should help, but I don't really know how it works, please help me.

Thanks in advance

The answers given to [this question](http://stackoverflow.com/questions/9308779/php-parallel-curl-requests) may be what you need: it talks about how to make and handle multiple requests "simultaneously" with `curl_multi_xxx` — Floris, Aug 04 '13 at 17:59
Google may place limits on how frequently you can make these requests. You might actually want to slow down the requests if some of them are coming back with empty data, perhaps by inserting a `sleep()` into it. That will definitely make it slower to run, but if that's the issue, it'll be necessary to get proper data for each request. — Nick Coons, Aug 04 '13 at 18:00

score 2 · Accepted Answer · answered Aug 04 '13 at 18:03

2

I had the same problem. Google limits the frequency of the requests! Try a sleep(1); in the loop and it will work but need much more time.

answered Aug 04 '13 at 18:03

Tim

99
7

Convert huge number of places into latitude & longitude and update MySQL database using PHP

1 Answers1