Script to geocode DB of postcodes and store in DB

[mackin]

I am using Googlemaps to show a group of businesses around a central point. I can dynamically generate the map based on the location. I have a DB of postcodes that I need to geocode into LAT and LONG - and store them in the same field as postcode - so I can use lat&long to plot points on the map.

Does anyone have a script for this or know where I should begin?
Cheers

[bradgrafelman]

mackin;10994466 wrote:

I have a DB of postcodes that I need to geocode into LAT and LONG - and store them in the same field as postcode

I certainly hope not - that would violate the first normal form (1NF) of database design. No column should contain multiple values.

Perhaps you meant store them in the same 'table' instead?

mackin;10994466 wrote:

Does anyone have a script for this or know where I should begin?

Well I guess you should begin by clarifying your question; are you asking how to actually convert a given postal code into a latitude and longitude, or are you asking how to apply that process to a database of such codes?

[mackin]

ok to clarify what I want is to take many postcodes - and geocode them into lat & long - and store those values in the relevant table columns.

So at the moment I have around 1100 postcodes - I wish to use a script to take these postcodes, geocode them with google - and update each entry adding the latitude and longitude for each one.

[bradgrafelman]

mackin;10994470 wrote:

geocode them with google

And how is that done?

If you can already answer that, then it sounds like you simply need to SELECT the relevant rows in your DB, loop through the result set, and for each row simply perform the above lookup and execute an UPDATE query to update the row in the DB with the lat/lon data.

[mackin]

I have only done it with single entries before using the following

$pc1 = 'http://maps.google.com/maps/geo?q='.$postcode1.',+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2cuoGw6G6HnB4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g';
$data1 = file_get_contents($pc1);
$result1 = explode(",", $data1);
$custlat1 = $result1[2];
$custlong1 = $result1[3];

I dont know how to marry that up with a loop and updating the scraped data into the database

[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