I'm looking to get the following script working (cant post attachment so sorry for listing long code). I got it from wheresrhys.co.uk but I just can't
get it to work.
Basically it reads a list of UK postcodes from a database, geocodes it via Google LocalSearch (important),
then stores the returned longitude and latitude values back in the database.
I set it up, it runs but does nothing. I dont't understand how it enters the loop to start.
Looking to get this done pretty quickly.
Please see attached file for more details.
Thanks
Stew
Code: Select all
1. Create a html page geocode.html with the following content:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" >
<head>
<title></title>
<meta name="description" content="" />
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" href="" type="text/css" media="screen" />
<script type="text/javascript" src="1_jquery-1.3.2.js"></script>
<script src="http://www.google.com/jsapi" type="text/javascript"></script>
<script type="text/javascript" src="geocode.js"></script>
</head>
<body>
<div id="counter"></div>
</body>
</html>
2. Create a php file (in the same directory), geocode.php, with the following rough structure (it will only be accessed via ajax, so is very stripped down):
<?php
require_once ('mysqlConnect.php'); //or other database connection details
if($_GET){
update_record();
send_new_data();
}
//gets the next record without a geocode and sends the id and postcode to the browser
function send_new_data() {
$query = @mysql_query("SELECT int_id, str_postcode FROM geocodes_table WHERE lat = '' AND str_postcode != '' ORDER BY int_id LIMIT 1");
if(($query) &&mysql_num_rows($query)) {
$row = mysql_fetch_array($query, MYSQL_ASSOC);
echo $row['int_id'].','.$row['str_postcode'];
}
}
//updates the last record with data sent from browser
function update_record() {
$int_id = $_GET['int_id'];
$lat = $_GET['lat'];
$lng = $_GET['lng'];
if($int_id > 0)
{
$update = "UPDATE geocodes_table SET lat = '".$lat."', lng = '".$lng."' WHERE int_id = ".$int_id;
$result = @mysql_query($update);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
if(mysql_affected_rows() == 1){
}
}
}
?>
3. Create a javascript file geocode.js, saved in the same directory again
var localSearch = new google.search.LocalSearch();
var int_id = 0;
var counter;
$(function() {
counter = $('#counter');
send_and_get_next(0, 0, 0)
});
function send_and_get_next(id, lat, lng) {
$.ajax({
type: "GET",
url: "postcode.php",
data: "int_id="+id+"&lat="+lat+"&lng="+lng,
success: function(response) {
response = response.split(',');
int_id = response[0];
$(counter).html(int_id);
getPointFromPostcode(response[1]);
}
});
}
function getPointFromPostcode(postcode) {
localSearch.setSearchCompleteCallback(null,
function() {
if (localSearch.results[0]) {
var resultLat = localSearch.results[0].lat;
var resultLng = localSearch.results[0].lng;
send_and_get_next(int_id, resultLat, resultLng);
}else{
send_and_get_next(int_id, 'error', 'error');
}
});
localSearch.execute(postcode+', uk');
}
4. Running the code
Once you’ve altered the database connection details, and SQL query to suit your setup, simply open geocode.html in your browser. A counter will tell you which record you’re on. To stop the code simply close your browser/browser tab.
How it all works
In a nutshell (ignoring the special case of starting off the loop) the code repeatedly performs the following process:
….in geocode.php, send_new_data() finds a record which has no latitude value and sends it’s id number and postcode as an ajax response to set_and_get_next(). This keeps track of the id in a global variable and sends the postcode to getPointFromPostcode(), which uses google’s local search to get a geocode. Once it’s found a geocode it passes it to set_and_get_next(), which sends it to geocode.php in an ajax request. There update_record()… well… updates the record, and send_new_data() finds a record which has no la….