Split geocoding task

Question

I'm currently working on a project which needs to geocode locations stored in a mySQL database.

I wrote a small script which imports a CSV into the mySQL database and automatically fills the longitude and latitude fields in each row with a "0.000000".

Now I found a script that checks the database for missing geocodes and updates those. Since this script is a few years old and just worked with Google I fitted it to my needs (should work with Wordpress and Nominatim instead of Google Maps).

Importing the CSV works just fine but when running this script to update about 5200 locations I get a timeout. Unfortunately I can't increase any timeout settings so I need to split task into steps.

How can I accomplish this?

I'm still a bit green behind the ears when it comes to PHP so any help would be appreciated. I already tried splitting the loop by increasing a counter variable which I squared with the number of mySQL rows I need to update. It should refresh the page now every 100 rows but unfortunately it didn't really work. It works for about 500 or so locations but after that - timeout.

Does anybody know a solution?

// Opens a connection to a MySQL server
$connection = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if (!$connection) {
die("Not connected : " . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db(DB_NAME, $connection);
if (!$db_selected) {
die("Can\'t use db : " . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM locations WHERE 1";
$result = mysql_query($query);

if (!$result) {
die("Invalid query: " . mysql_error());
}

// Initialize delay in geocode speed
$delay = 0;
function getLnt($address){
    $request_url = "http://nominatim.openstreetmap.org/search?q=".urlencode($address)."&format=json&addressdetails=1";
    $result_string = file_get_contents($request_url);
    $result_json = json_decode($result_string, true);
    return $result_json[0];
}


// Iterate through the rows, geocoding each address


    while ($row = @mysql_fetch_assoc($result)) {

        $geocode_pending = true;

        while ($geocode_pending) {

            $street = $row["address"];
            $zip = $row["zip"];
            $city = $row["city"];
            $id = $row["id"];

            $address = $street.', '.$zip.' '.$city;

            $details = getLnt($address);
            $lat = $details[lat];
            $lng = $details[lon];
            $state = $details[address][state];
            $country_iso = $details[address][country_code];

            if (http_response_code() == 200) {     

                // successful geocode
                $geocode_pending = false;

                $query = sprintf("
                    UPDATE locations SET lat = '%s', lng = '%s', state = '%s', country_iso = '%s' WHERE lat = '0.000000' AND lng = '0.000000' AND id = '%s' LIMIT 1;",
                    mysql_real_escape_string($lat), mysql_real_escape_string($lng), mysql_real_escape_string(utf8_decode($state)),mysql_real_escape_string($country_iso),mysql_real_escape_string($id)
                );

                $update_result = mysql_query($query);

                if (!$update_result) {
                   die("Invalid query: " . mysql_error());
                }

        } else if(http_response_code() == 620) {

                // sent geocodes too fast
                $delay += 100000;

            } else if($details == NULL) {

                // failure to geocode
                $geocode_pending = false;
                echo "Address " . $address . " failed to geocoded. ";
                echo "Received status " . $status . "
                \n";

            }
            usleep($delay);

        }

}

Answer 1

You have to add pagination to your location query:

// Select all the rows in the markers table
$offset = intval($_GET['offset'];
$query = "SELECT * FROM locations WHERE 1 LIMIT $offset, 100";

and then iterate through pages in your browser. You can do it manually, or by using javascript/jquery on another page which will query destination:

var locations_count = 1000000;
var offset = 0;
var func = function(){
    $.get('update-location.php', {offset: offset}, function(){
            document.write('<br/>Processed ' + offset + ' records');
            offset += 100;
            if( offset < locations_count )
                setTimeout(func, 100);
            else
                document.write('<br/>Update finished');
    });
};
func();

(dont forget to update locations_count to your actual number of locations).

And other solution:

Dump the table to your local machine and then update it locally and then update the resulting table on server.