Suburb geocoder for stop locations
[bus.git] / maxious-canberra-transit-feed / 04-locatebetweenpoints.reversegeocode.php
blob:a/maxious-canberra-transit-feed/04-locatebetweenpoints.reversegeocode.php -> blob:b/maxious-canberra-transit-feed/04-locatebetweenpoints.reversegeocode.php
--- a/maxious-canberra-transit-feed/04-locatebetweenpoints.reversegeocode.php
+++ b/maxious-canberra-transit-feed/04-locatebetweenpoints.reversegeocode.php
@@ -21,7 +21,8 @@
 	echo("Error in SQL query: " . pg_last_error() ."<br>\n");
      }
      while ($stop = pg_fetch_assoc($result_stops)) {
-      echo "Processing ".$stop['geohash'] . " ... ";
+	if ($stop['name'] == "") {
+      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";
       $contents = json_decode(getPage($url));
       print_r($contents);
@@ -31,8 +32,25 @@
       $result_save = pg_query($conn, "UPDATE stops set name = '".pg_escape_string($name)."' where geohash = '{$stop['geohash']}' ");
 			      if (!$result_save) {
 	echo("Error in SQL query: " . pg_last_error() ."<br>\n");
+			      }
+	}
+	if ($stop['suburb'] == "") {
+      echo "Processing ".$stop['geohash'] . " suburb ... ";
+	$sql = "select * from suburbs where the_geom @> 'POINT(".($stop['lng']/10000000)." ".($stop['lat']/10000000).")'::geometry";
+     $result_suburbs = pg_query($conn, $sql);
+     if (!$result_suburbs) {
+	echo("Error in SQL query: " . pg_last_error() ."<br>\n");
      }
-     die();
+     $suburbs = "";
+     while ($suburb = pg_fetch_assoc($result_suburbs)) {
+	$suburbs .= $suburb['name_2006'].";";
+     }
+      echo "Saving $suburbs ! <br>" ;
+      $result_save = pg_query($conn, "UPDATE stops set suburb = '".pg_escape_string($suburbs)."' where geohash = '{$stop['geohash']}' ");
+			      if (!$result_save) {
+	echo("Error in SQL query: " . pg_last_error() ."<br>\n");
+			      }
+     }
      flush();
      }