Geocoding Lab Results

Posted on the September 27th, 2008 under Databases by cwebster

Based on articles published on:

Initial testing using the google maps API was only able to get postcode to latitude conversions at a level of the 1st three figures of the postcode e.g. B10 this is good enough for plotting of data vs post code areas. For street level coding the Yahoo geocoding gives much better accuracy.

The following code searches through a database of post codes and gets the longitude and latitude of the location and then updates the record in the database.


function request_cache($url, $dest_file, $timeout=43200) {
if(!file_exists($dest_file) || filemtime($dest_file) < (time()-$timeout)) {
$stream = fopen($url,'r');
$tmpf = tempnam('/tmp','YWS');
file_put_contents($tmpf, $stream);
fclose($stream);
rename($tmpf, $dest_file);
}
}

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

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

// Select all the rows in the markers table
$query = "SELECT * FROM markers m where lat =0";
$result = mysql_query($query);
if (!$result) {
die("Invalid query: " . mysql_error());
}

// Initialize delay in geocode speed
$delay = 0;

// Iterate through the rows, geocoding each address
while ($row = @mysql_fetch_assoc($result)) {
$geocode_pending = true;

while ($geocode_pending) {
$address = $row["address"];
$id = $row["id"];

echo $address;

$q = 'http://api.local.yahoo.com/MapsService/V1/geocode';
$q .= '?appid=rlerdorf&location='.rawurlencode($address);

// request_cache($q, $tmp, 43200);
libxml_use_internal_errors(true);

$xml = simplexml_load_file($q) or die("url not loading");

// Successful geocode
$geocode_pending = false;

$lat = $xml->Result->Latitude;
$lng = $xml->Result->Longitude;

$query = sprintf("UPDATE markers " .
" SET lat = '%s', lng = '%s' " .
" WHERE id = '%s' LIMIT 1;",
mysql_real_escape_string($lat),
mysql_real_escape_string($lng),
mysql_real_escape_string($id));
$update_result = mysql_query($query);

usleep($delay);
}
}
?>

You must be logged in to post a comment.