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:
CREATE TABLE `addresses` ( `id` int(11) NOT NULL auto_increment, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `state` varchar(2) default NULL, `zip` varchar(9) default NULL, `lat` float(10,6) default NULL, `lon` float(10,6) default NULL, PRIMARY KEY (`id`) )
Here’s the script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | <?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 them if(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 message echo '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
- The Yahoo Geocoding API can return either XML or PHP serialized objects. I chose PHP serialized objects because it’s easier to deal with for what I’m trying to do.
- http_build_query does not exist in PHP4. To use this code with PHP4 you could use the PHP_COMPAT Pear Package or the http_build_query function from the CodeIgniter Compatibility Helper.
Categories: PHP