Bit of a techy post - I have a database of addresses with latitude and longitude. I had
geocoded these a few years ago using
Google maps api. (Geocoding means take an address and get the latitude and longitude)
The problem was that the old geocoding wasn't biased towards the country I was searching for. So for example, if I looked up Boston, it could take me to Boston in the US rather than the UK.
The latest version of the Google maps api has a
biased feature - so I can say search for Boston in the UK first. Brill!
So I've written some php code to geocode my addresses. Then had a strange error message "error 2006 - MySQL server has gone away". It took me 2 days to find an answer... Trouble is there are
lots of reasons for it.
I was using the
mysql functions in php, so thought I would try the more advanced
mysqli functions. Still the same error. Then came across the
mysqli->ping() function. This will report if the connection has been lost, so I can reconnect - a very simple solution and no more silly MySQL server has gone away messages
<?php
$key = "xxxxxxxxxxxxxxxx";
$base_url = "http://maps.googleapis.com/maps/api/geocode/json?key=" . $key . "&sensor=false";
// Initialize delay in geocode speed
$delay = (86400000000/2500); // microseconds in a day divided by maximum requests per day (2500)
echo "Reconnect : " . ini_get('mysqli.reconnect') . '<br/>';
if (ini_set('mysqli.reconnect', '1'))
{
echo 'Reconnection set<br/>';
}
else
{
echo 'reconnection not set <br/>';
}
require("phpsqlajax_dbinfo.php");
$mysqli = mysqli_init();
if (!$mysqli) {
die('mysqli_init failed');
}
if (!$mysqli->options(MYSQLI_INIT_COMMAND, "SET NAMES 'utf8'")) {
die('Setting MYSQLI_INIT_COMMAND failed');
}
// MYSQL_OPT_RECONNECT
// Google delay is 34 seconds
if (!$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 60)) {
die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}
if (!$mysqli->real_connect('localhost', $username, $password, $database)) {
die('Connect Error (' . mysqli_connect_errno() . ') '
. mysqli_connect_error());
}
//$mysqli = new mysqli('localhost', $username, $password, $database);
//if (mysqli_connect_errno()) {
// die("Invalid query: " .mysqli_connect_errno() . ' : ' .mysqli_connect_error());
//}
$mysqli->query("SET NAMES 'utf8'");
// Select all the rows in the markers table
$query = "SELECT * FROM mymarkers WHERE updated = 0 ORDER BY countryname LIMIT 1000;"; // AND SUBSTRING(field_countrycode,-2)='TN'
if (!$rows = $mysqli->query($query))
{
die("Invalid query: " .$mysqli->errno . ' : ' .$mysqli->error);
}
// Iterate through the rows, geocoding each address
while ($row = $rows->fetch_assoc())
{
$mycount = 0;
$finished = false;
$itemid = $row["itemid"];
$region = strtolower(substr($row["field_countrycode"],-2));
$fields = array();
$mycount = 0;
foreach( $row as $field )
{
if (($mycount>=2) && ($mycount<=7) && ($field!='') && ($field!=null))
{
$fields[] = html_entity_decode(unicodetohtml($field)); // convert \u to chars and &xxx; to the character
}
$mycount++;
}
$fields[] = $row['countryname'];
// print_r($row);
/// convert ampersands and \u?
$mycount = 0;
$maxfield = count($fields)-1;
while (!$finished && $mycount<=$maxfield)
{
$address = "";
for ($field=$mycount; $field<=$maxfield; $field++)
{
if ( ($field==0) || ($fields[$field]!=$fields[$field-1]) )
{
$address .= $fields[$field] . " ";
}
}
$mycount++;
echo "Search : " . $address ." region : " . $region . " Search attempt : " . $mycount . "<br/>";
$request_url = $base_url . "&address=" . urlencode($address) . "®ion=" . urlencode($region);
if ($address!='')
{
// Wait for it...
usleep($delay);
$json_contents = file_get_contents($request_url,0,null,null);
$json = json_decode($json_contents);
echo "Search status : " . $json->status . "<br/>";
switch ($json->status)
{
case "ZERO_RESULTS":
// try again
$finished=false;
break;
case "OVER_QUERY_LIMIT":
// try again
$finished=false;
$delay += 100000;
$mycount--;
break;
case "REQUEST_DENIED":
// try again
$finished=true;
break;
case "INVALID_REQUEST":
// try again
$finished=true;
break;
case "OK":
$finished=true;
break;
}
}
}
// echo $json_contents;
// echo $json;
$location = $json->results[0]->formatted_address;
$geo_lat = $json->results[0]->geometry->location->lat;
$geo_long = $json->results[0]->geometry->location->lng;
$accuracy = $json->results[0]->geometry->location_type; // Accuracy
// Select all the rows in the markers table
$query = sprintf( "UPDATE mymarkers SET location='%s', geo_lat='%s', geo_long='%s', accuracy='%s', updated=1 where itemid=%s limit 1",
addslashes($location),
addslashes($geo_lat),
addslashes($geo_long),
addslashes($accuracy),
$itemid );
echo $query . '<br/>';
if (!$mysqli->ping())
{
echo ("Ping Error: " . $mysqli->errorno . " : " . $mysqli->error . '<br/>');
if (!$mysqli->real_connect('localhost', $username, $password, $database)) {
die('Connect Error (' . mysqli_connect_errno() . ') '
. mysqli_connect_error());
}
else
{
echo 'Reconnected successfully!!<br/>';
}
}
if (! $mysqli->query($query) ) {
die("Invalid query: " .$mysqli->errno . ' : ' .$mysqli->error);
}
echo "Found : " . $location . " lat : " . $geo_lat . " lng : " . $geo_long . " accuracy : " . $accuracy . '<br/><br/>';
}
$rows->close();
mysqli_close($mysqli);
function unicodetohtml($str)
{
return (preg_replace("/\\\\u([0-9a-f]{3,4})/i", "&#x\\1;", $str));
}
?>
