Add and Remove operations for between stops
[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
@@ -15,6 +15,7 @@
   echo "An error occured.\n";
   exit;
 }
+echo "reverse geocode stops<br>";
 $sql = "Select * from stops where name is null or suburb is null";
      $result_stops = pg_query($conn, $sql);
      if (!$result_stops) {
@@ -52,6 +53,32 @@
      }
      flush();
      }
+echo "reverse geocode timing points<br>";
+$sql = "Select * from timing_point where suburb is null";
+     $result_timingpoints = pg_query($conn, $sql);
+     if (!$result_timingpoints) {
+	echo("Error in SQL query: " . pg_last_error() ."<br>\n");
+     }
+     while ($timingpoint = pg_fetch_assoc($result_timingpoints)) {
+	if ($timingpoint['suburb'] == "") {
+      echo "Processing ".$timingpoint['name'] . " suburb ... ";
+	$sql = "select * from suburbs where the_geom @> 'POINT(".($timingpoint['lng']/10000000)." ".($timingpoint['lat']/10000000).")'::geometry";
+     $result_suburbs = pg_query($conn, $sql);
+     if (!$result_suburbs) {
+	echo("Error in SQL query: " . pg_last_error() ."<br>\n");
+     }
+     $suburbs = "";
+     while ($suburb = pg_fetch_assoc($result_suburbs)) {
+	$suburbs .= $suburb['name_2006'].";";
+     }
+      echo "Saving $suburbs ! <br>" ;
+      $result_save = pg_query($conn, "UPDATE timing_point set suburb = '".pg_escape_string($suburbs)."' where name = '".pg_escape_string($timingpoint['name'])."'");
+			      if (!$result_save) {
+	echo("Error in SQL query: " . pg_last_error() ."<br>\n");
+			      }
+     }
+     flush();
+     }
 
 ?>