Geocoding with PHP

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:

  1. CREATE TABLE `addresses` (
  2. 	`id` int(11) NOT NULL auto_increment,
  3. 	`street` varchar(255) default NULL,
  4. 	`city` varchar(255) default NULL,
  5. 	`state` varchar(2) default NULL,
  6. 	`zip` varchar(9) default NULL,
  7. 	`lat` float(10,6) default NULL,
  8. 	`lon` float(10,6) default NULL,
  9. 	PRIMARY KEY  (`id`)
  10. )

Here's the script: 

  1. <?php
  2. // Geocode addresses using the Yahoo Geocoding API
  3.  
  4. // Turning on track_errors stores the latest PHP error in $php_errormsg.
  5. // This allows for more elegant display of the error messages without having to
  6. // code a php error handler, which would be overkill for such a simple script.
  7. ini_set('track_errors',TRUE);
  8.  
  9. mysql_connect('localhost', 'user', 'password');
  10. mysql_select_db('database_name');
  11.  
  12. // Setup variables to be used later
  13. $geocode_url = "http://local.yahooapis.com/MapsService/V1/geocode";
  14. // Enter your custom appid here
  15. $appid ='sample_appid';
  16.  
  17. // Get the addresses that have not been geocoded
  18. $address_result = mysql_query("SELECT id, street, city, state, zip FROM addresses WHERE lat IS NULL OR lon IS NULL");
  19.  
  20. // If the MySQL query returned any rows, loop through them
  21. if(mysql_num_rows($address_result) > 0){
  22.     while ($row = mysql_fetch_assoc($address_result)){
  23.         // appid is required for each call to the yahoo geocode API
  24.         $row['appid'] = $appid;
  25.         // output can be either xml or php
  26.         $row['output'] = 'php';
  27.  
  28.         // Store the row id for updating the database and remove from $row array
  29.         $id = $row['id'];
  30.         unset($row['id']);
  31.  
  32.         // Build the query string for sending the request to yahoo
  33.         $query_string = http_build_query($row, '', '&');
  34.  
  35.         // Get latitute / longitude
  36.         $response = @file_get_contents($geocode_url.'?'.$query_string);
  37.  
  38.         if($response == false){
  39.             // If the call to the yahoo api failed, display an error message
  40.             echo 'ERROR: '.$php_errormsg;
  41.         }
  42.         else{
  43.             // Get the results of the api call and update the database records
  44.             $response_array = unserialize($response);
  45.             $lat = mysql_real_escape_string($response_array['ResultSet']['Result']['Latitude']);
  46.             $lon = mysql_real_escape_string($response_array['ResultSet']['Result']['Longitude']);
  47.             $update_result = mysql_query("UPDATE addresses SET lat = '$lat', lon = $lon WHERE id = $id");
  48.  
  49.             // For each record, let the user know if the update was successful. If not, display the mysql error message generated.
  50.             if($update_result == TRUE){
  51.                 echo "Added lattitude and longitude for {$row['street']} {$row['city']}, {$row['state']} {$row['zip']}.";
  52.             }
  53.             else{
  54.                 echo "Unable to add lattitude and longitude for {$row['street']} {$row['city']}, {$row['state']} {$row['zip']}
  55.                       MySQL Error: ".mysql_error();
  56.             }
  57.         }
  58.         echo '<br />';
  59.     }
  60. }
  61. else{
  62.     echo "Nothing to do.";
  63. }
  64. ?>

The code is documented pretty well so I'll just point out a couple things