<?php |
<?php |
function getPage($url) |
function getPage($url) |
{ |
{ |
$ch = curl_init($url); |
$ch = curl_init($url); |
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 ); |
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 ); |
curl_setopt( $ch, CURLOPT_HEADER, 0 ); |
curl_setopt( $ch, CURLOPT_HEADER, 0 ); |
$page = curl_exec($ch); |
$page = curl_exec($ch); |
curl_close($ch); |
curl_close($ch); |
return $page; |
return $page; |
} |
} |
// |
// |
// http://developers.cloudmade.com/wiki/geocoding-http-api/Documentation |
// http://developers.cloudmade.com/wiki/geocoding-http-api/Documentation |
$conn = pg_connect("dbname=bus user=postgres password=snmc"); |
$conn = pg_connect("dbname=bus user=postgres password=snmc"); |
if (!$conn) { |
if (!$conn) { |
echo "An error occured.\n"; |
echo "An error occured.\n"; |
exit; |
exit; |
} |
} |
$sql = "Select * from stops where name is null or suburb is null"; |
$sql = "Select * from stops where name is null or suburb is null"; |
$result_stops = pg_query($conn, $sql); |
$result_stops = pg_query($conn, $sql); |
if (!$result_stops) { |
if (!$result_stops) { |
echo("Error in SQL query: " . pg_last_error() ."<br>\n"); |
echo("Error in SQL query: " . pg_last_error() ."<br>\n"); |
} |
} |
while ($stop = pg_fetch_assoc($result_stops)) { |
while ($stop = pg_fetch_assoc($result_stops)) { |
if ($stop['name'] == "") { |
if ($stop['name'] == "") { |
echo "Processing ".$stop['geohash'] . " streetname ... "; |
echo "Processing ".$stop['geohash'] . " streetname ... "; |
$url = "http://geocoding.cloudmade.com/daa03470bb8740298d4b10e3f03d63e6/geocoding/v2/find.js?around=".($stop['lat']/10000000).",".($stop['lng']/10000000)."&distance=closest&object_type=road"; |
$url = "http://geocoding.cloudmade.com/daa03470bb8740298d4b10e3f03d63e6/geocoding/v2/find.js?around=".($stop['lat']/10000000).",".($stop['lng']/10000000)."&distance=closest&object_type=road"; |
$contents = json_decode(getPage($url)); |
$contents = json_decode(getPage($url)); |
print_r($contents); |
//print_r($contents); |
$name = $contents->features[0]->properties->name; |
$name = $contents->features[0]->properties->name; |
//todo suburb/locality select * from suburbs where the_geom @> 'POINT(149.075704592122 -35.21751569325)'::geometry |
|
echo "Saving $name ! <br>" ; |
echo "Saving $name ! <br>" ; |
$result_save = pg_query($conn, "UPDATE stops set name = '".pg_escape_string($name)."' where geohash = '{$stop['geohash']}' "); |
$result_save = pg_query($conn, "UPDATE stops set name = '".pg_escape_string($name)."' where geohash = '{$stop['geohash']}' "); |
if (!$result_save) { |
if (!$result_save) { |
echo("Error in SQL query: " . pg_last_error() ."<br>\n"); |
echo("Error in SQL query: " . pg_last_error() ."<br>\n"); |
} |
} |
} |
} |
if ($stop['suburb'] == "") { |
if ($stop['suburb'] == "") { |
echo "Processing ".$stop['geohash'] . " suburb ... "; |
echo "Processing ".$stop['geohash'] . " suburb ... "; |
$sql = "select * from suburbs where the_geom @> 'POINT(".($stop['lng']/10000000)." ".($stop['lat']/10000000).")'::geometry"; |
$sql = "select * from suburbs where the_geom @> 'POINT(".($stop['lng']/10000000)." ".($stop['lat']/10000000).")'::geometry"; |
$result_suburbs = pg_query($conn, $sql); |
$result_suburbs = pg_query($conn, $sql); |
if (!$result_suburbs) { |
if (!$result_suburbs) { |
echo("Error in SQL query: " . pg_last_error() ."<br>\n"); |
echo("Error in SQL query: " . pg_last_error() ."<br>\n"); |
} |
} |
$suburbs = ""; |
$suburbs = ""; |
while ($suburb = pg_fetch_assoc($result_suburbs)) { |
while ($suburb = pg_fetch_assoc($result_suburbs)) { |
$suburbs .= $suburb['name_2006'].";"; |
$suburbs .= $suburb['name_2006'].";"; |
} |
} |
echo "Saving $suburbs ! <br>" ; |
echo "Saving $suburbs ! <br>" ; |
$result_save = pg_query($conn, "UPDATE stops set suburb = '".pg_escape_string($suburbs)."' where geohash = '{$stop['geohash']}' "); |
$result_save = pg_query($conn, "UPDATE stops set suburb = '".pg_escape_string($suburbs)."' where geohash = '{$stop['geohash']}' "); |
if (!$result_save) { |
if (!$result_save) { |
echo("Error in SQL query: " . pg_last_error() ."<br>\n"); |
echo("Error in SQL query: " . pg_last_error() ."<br>\n"); |
} |
} |
} |
} |
flush(); |
flush(); |
} |
} |
|
|
?> |
?> |
|
|
|
|