In my last post I mentioned geocoding using the Yahoo Geocoding API, so I thought I'd post some code to do exactly that.
For the purposes of this example, assume that we have a very simple MySQL database table with this structure:
Here's the script:
<?php// Geocode addresses using the Yahoo Geocoding API// Turning on track_errors stores the latest PHP error in $php_errormsg.// This allows for more elegant display of the error messages without having to// code a php error handler, which would be overkill for such a simple script.ini_set('track_errors',TRUE);
mysql_connect('localhost', 'user', 'password');
mysql_select_db('database_name');
// Setup variables to be used later$geocode_url = "http://local.yahooapis.com/MapsService/V1/geocode";
// Enter your custom appid here$appid ='sample_appid';
// Get the addresses that have not been geocoded$address_result = mysql_query("SELECT id, street, city, state, zip FROM addresses WHERE lat IS NULL OR lon IS NULL");
// If the MySQL query returned any rows, loop through themif(mysql_num_rows($address_result) > 0){
while ($row = mysql_fetch_assoc($address_result)){
// appid is required for each call to the yahoo geocode API$row['appid'] = $appid;
// output can be either xml or php$row['output'] = 'php';
// Store the row id for updating the database and remove from $row array$id = $row['id'];
unset($row['id']);
// Build the query string for sending the request to yahoo$query_string = http_build_query($row, '', '&');
// Get latitute / longitude$response = @file_get_contents($geocode_url.'?'.$query_string);
if($response == false){
// If the call to the yahoo api failed, display an error messageecho 'ERROR: '.$php_errormsg;
}else{
// Get the results of the api call and update the database records$response_array = unserialize($response);
$lat = mysql_real_escape_string($response_array['ResultSet']['Result']['Latitude']);
$lon = mysql_real_escape_string($response_array['ResultSet']['Result']['Longitude']);
$update_result = mysql_query("UPDATE addresses SET lat = '$lat', lon = $lon WHERE id = $id");
// For each record, let the user know if the update was successful. If not, display the mysql error message generated.if($update_result == TRUE){
echo "Added lattitude and longitude for {$row['street']} {$row['city']}, {$row['state']} {$row['zip']}.";
}else{
echo "Unable to add lattitude and longitude for {$row['street']} {$row['city']}, {$row['state']} {$row['zip']}
MySQL Error: ".mysql_error();
} }echo '<br />';
}}else{
echo "Nothing to do.";
}?>The code is documented pretty well so I'll just point out a couple things