Script to geocode DB of postcodes and store in DB

[Derokorian]

//PSUEDO CODE
$res = mysql_query('SELECT DISTINCT `zip_code` FROM `users`');
while( $row = mysql_fetch_assoc($res) ) {
   $pc1 = 'http://maps.google.com/maps/geo?q='.$row['zip_code'].',+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2cuoGw6G6HnB4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g';
   $data1 = file_get_contents($pc1);
   $result1 = explode(",", $data1);
   $lat = $result1[2];
   $long = $result1[3];
   mysql_query('UPDATE `users` SET `longitude` = \''.$long.'\',` latitude = \''.$lat.'\' WHERE `zip_code` = '.$row['zip_code']);
}

exactly as brad stated, select the zip codes, loop thru them, get the longitude/latitude in that loop, and do an update in the loop to add the longitude/latitude to the zip code you just looked up.

[mackin]

looks great - I wasnt sure how to implement it with the mysql - I am not confident with general updates as I panic it will wipe everything!!

Ill make a back up and then edit the code to suit

thx for help - ill let u know if it works

stu

[mackin]

I adapted the code to fit - the result gives me this error

Warning: file_get_contents(http://maps.google.com/maps/geo?q=AB11 6DS,+UK&output=csv&sensor=false,+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2PAnHtB4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g) [function.file-get-contents]: failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request in /homepages/27/d374255998/htdocs/ship/get_lat_long.php on line 58
done

If i take the link from the get file - it works - and displays this
200,5,57.1427466,-2.1035241

So it seems to work - but summat is wrong - can u see it?

mysql_select_db($database_contractors, $contractors);
$query_Recordset1 = "SELECT est_postcode FROM hotels WHERE est_postcode = 'AB11 6DS'";
$res = mysql_query($query_Recordset1, $contractors) or die(mysql_error());


while( $row = mysql_fetch_assoc($res) ) {
  $pc1 = 'http://maps.google.com/maps/geo?q='.$row['est_postcode'].',+UK&output=csv&sensor=false,+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2P4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g';
  $data1 = file_get_contents($pc1);
  $result1 = explode(",", $data1);
  $lat = $result1[2];
  $long = $result1[3];
  mysql_query('UPDATE hotels SET longitude = \''.$long.'\', latitude = \''.$lat.'\' WHERE est_postcode = '.$row['est_postcode']);
}



echo "done";
?>

[leatherback]

Do an [man]urlencode[/man] on your url. The url seems to have spaces.

[Derokorian]

I was going to say roughly the same... Seems your postal codes haves spaces and their need url_encode()'d

[mackin]

leatherback;10994514 wrote:

Do an [man]urlencode[/man] on your url. The url seems to have spaces.

If I urlencode it changes error to no file or directory found.
The Url is http://maps.google.com/maps/geo?q=AB11 6DS,+UK&output=csv&sensor=false,+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2PAnHtRSLcuoGw6G6HnB4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g

which is formatted correctly and has no spaces/.

Cheers Leatherback

[mackin]

ah space in postcode - ok ill try that cheers

[leatherback]

Try it on just the postal code.

[mackin]

Ok - its doing its thing - but not updating the database with the longitude and latitude - does the update query look right??

<?php

function nowhitespace($data3) {
return preg_replace('/\s/', '', $data3);}

// $pc1 = 'http://maps.google.com/maps/geo?q'.urlencode($row['est_postcode']).',,+UK&output=csv&sensor=false,+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2PAnHtRSLcuoGw6G6HnB4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g';
while( $row = mysql_fetch_assoc($res) ) {

$pc1 = 'http://maps.google.com/maps/geo?q='.nowhitespace($row['est_postcode']).',+UK&output=csv&sensor=false,+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2PAnHtRSLcuoGw6G6HnB4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g';

$data1 = file_get_contents($pc1);
$result1 = explode(",", $data1);
$lat = $result1[2];
$long = $result1[3];
echo $lat;
mysql_query('UPDATE hotels SET longitude = \''.$long.'\', latitude = \''.$lat.'\' WHERE est_postcode = '.$row['est_postcode']);
}

echo "done";
?>

[Derokorian]

Please use the boards [NOPARSE]

[/NOPARSE] tags when posting php code, makes your code much easier to read and analyze.

As for the issue, same thing is happening in your query you need to quote the postal code:

mysql_query('UPDATE hotels SET longitude = \''.$long.'\', latitude = \''.$lat.'\' WHERE est_postcode = \''.$row['est_postcode'].'\'');

[mackin]

genius - works well - thank you all