projects / busui.git / blobdiff
search:
summary | shortlog | log | commit | commitdiff | tree
side by side | plain
Refactor to use PostGIS database instead of gtfs tools
[busui.git] / include / db / trip-dao.inc.php
blob:a/include/db/trip-dao.inc.php -> blob:b/include/db/trip-dao.inc.php 
--- a/include/db/trip-dao.inc.php
+++ b/include/db/trip-dao.inc.php
@@ -1,132 +1,288 @@
 <?php
+
+/*
+ *    Copyright 2010,2011 Alexander Sadleir 
+
+  Licensed under the Apache License, Version 2.0 (the "License");
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+  http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+ */
+
 function getTrip($tripID) {
-    /* def handle_json_GET_triprows(self, params):
-    """Return a list of rows from the feed file that are related to this
-    trip."""
-    schedule = self.server.schedule
-    try:
-      trip = schedule.GetTrip(params.get('trip', None))
-    except KeyError:
-      # if a non-existent trip is searched for, the return nothing
-      return
-    route = schedule.GetRoute(trip.route_id)
-    trip_row = dict(trip.iteritems())
-    route_row = dict(route.iteritems())
-    return [['trips.txt', trip_row], ['routes.txt', route_row]]
-    */
-}
-function getTripShape() {
-    /* def handle_json_GET_tripstoptimes(self, params):
-    schedule = self.server.schedule
-    try:
-      trip = schedule.GetTrip(params.get('trip'))
-    except KeyError:
-       # if a non-existent trip is searched for, the return nothing
-      return
-    time_stops = trip.GetTimeInterpolatedStops()
-    stops = []
-    times = []
-    for arr,ts,is_timingpoint in time_stops:
-      stops.append(StopToTuple(ts.stop))
-      times.append(arr)
-    return [stops, times]
-
-  def handle_json_GET_tripshape(self, params):
-    schedule = self.server.schedule
-    try:
-      trip = schedule.GetTrip(params.get('trip'))
-    except KeyError:
-       # if a non-existent trip is searched for, the return nothing
-      return
-    points = []
-    if trip.shape_id:
-      shape = schedule.GetShape(trip.shape_id)
-      for (lat, lon, dist) in shape.points:
-        points.append((lat, lon))
-    else:
-      time_stops = trip.GetTimeStops()
-      for arr,dep,stop in time_stops:
-        points.append((stop.stop_lat, stop.stop_lon))
-    return points*/
-}
-function tripStopTimes($tripID, $after_time, $limit) {
-    /*     rv = []
-
-    stoptimes = self.GetStopTimes()
-    # If there are no stoptimes [] is the correct return value but if the start
-    # or end are missing times there is no correct return value.
-    if not stoptimes:
-      return []
-    if (stoptimes[0].GetTimeSecs() is None or
-        stoptimes[-1].GetTimeSecs() is None):
-      raise ValueError("%s must have time at first and last stop" % (self))
-
-    cur_timepoint = None
-    next_timepoint = None
-    distance_between_timepoints = 0
-    distance_traveled_between_timepoints = 0
-
-    for i, st in enumerate(stoptimes):
-      if st.GetTimeSecs() != None:
-        cur_timepoint = st
-        distance_between_timepoints = 0
-        distance_traveled_between_timepoints = 0
-        if i + 1 < len(stoptimes):
-          k = i + 1
-          distance_between_timepoints += util.ApproximateDistanceBetweenStops(stoptimes[k-1].stop, stoptimes[k].stop)
-          while stoptimes[k].GetTimeSecs() == None:
-            k += 1
-            distance_between_timepoints += util.ApproximateDistanceBetweenStops(stoptimes[k-1].stop, stoptimes[k].stop)
-          next_timepoint = stoptimes[k]
-        rv.append( (st.GetTimeSecs(), st, True) )
-      else:
-        distance_traveled_between_timepoints += util.ApproximateDistanceBetweenStops(stoptimes[i-1].stop, st.stop)
-        distance_percent = distance_traveled_between_timepoints / distance_between_timepoints
-        total_time = next_timepoint.GetTimeSecs() - cur_timepoint.GetTimeSecs()
-        time_estimate = distance_percent * total_time + cur_timepoint.GetTimeSecs()
-        rv.append( (int(round(time_estimate)), st, False) )
-
-    return rv*/
-}
-
-function tripStartTime($tripID) {
-    $query = 'SELECT arrival_secs,departure_secs FROM stop_times WHERE trip_id=? ORDER BY stop_sequence LIMIT 1';
-    
-}
-
-function viaPoints($tripid, $stopid, $timingPointsOnly = false)
-{
-	global $APIurl;
-	$url = $APIurl . "/json/tripstoptimes?trip=" . $tripid;
-	$json = json_decode(getPage($url));
-	debug(print_r($json, true));
-	$stops = $json[0];
-	$times = $json[1];
-	$foundStop = false;
-	$viaPoints = Array();
-	foreach ($stops as $key => $row) {
-		if ($foundStop) {
-			if (!$timingPointsOnly || !startsWith($row[5], "Wj")) {
-				$viaPoints[] = Array(
-					"id" => $row[0],
-					"name" => $row[1],
-					"time" => $times[$key]
-				);
-			}
-		}
-		else {
-			if ($row[0] == $stopid) $foundStop = true;
-		}
-	}
-	return $viaPoints;
-}
-function viaPointNames($tripid, $stopid)
-{
-	$points = viaPoints($tripid, $stopid, true);
-	$pointNames = Array();
-	foreach ($points as $point) {
-		$pointNames[] = $point['name'];
-	}
-	return implode(", ", $pointNames);
-}
+    global $conn;
+    $query = "Select * from trips
+	join routes on trips.route_id = routes.route_id
+	where trip_id =	:tripID
+	LIMIT 1";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":tripID", $tripID);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    return $query->fetch(PDO :: FETCH_ASSOC);
+}
+
+function getTripShape($tripID) {
+    // todo, use shapes table if shape_id specified
+    global $conn;
+    $query = "SELECT ST_AsKML(ST_MakeLine(geometry(a.position))) as the_route
+FROM (SELECT position,
+	stop_sequence, trips.trip_id
+FROM stop_times
+join trips on trips.trip_id = stop_times.trip_id
+join stops on stops.stop_id = stop_times.stop_id
+WHERE trips.trip_id = :tripID ORDER BY stop_sequence) as a group by a.trip_id";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":tripID", $tripID);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    return $query->fetchColumn(0);
+}
+function getTripStopTimes($tripID) {
+    global $conn;
+    $query = "SELECT stop_times.trip_id,arrival_time,stop_times.stop_id,stop_lat,stop_lon,stop_name,stop_code,
+	stop_sequence,service_id,trips.route_id,route_short_name,route_long_name
+FROM stop_times
+join trips on trips.trip_id = stop_times.trip_id
+join routes on trips.route_id = routes.route_id
+join stops on stops.stop_id = stop_times.stop_id
+WHERE trips.trip_id = :tripID $range ORDER BY stop_sequence";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":tripID", $tripID);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    $stopTimes = $query->fetchAll();
+     return $stopTimes;
+
+}
+function getTripAtStop($tripID, $stop_sequence) {
+    global $conn;
+     foreach (getTripStopTimes($tripID) as $tripStop) {
+        if ($tripStop['stop_sequence'] == $stop_sequence)
+            return $tripStop;
+    }
+    return Array();
+}
+/* DEPRECIATED 
+function getTimeInterpolatedTrip($tripID, $range = "") {
+    global $conn;
+    $query = "SELECT stop_times.trip_id,arrival_time,stop_times.stop_id,stop_lat,stop_lon,stop_name,stop_code,
+	stop_sequence,service_id,trips.route_id,route_short_name,route_long_name
+FROM stop_times
+join trips on trips.trip_id = stop_times.trip_id
+join routes on trips.route_id = routes.route_id
+join stops on stops.stop_id = stop_times.stop_id
+WHERE trips.trip_id = :tripID $range ORDER BY stop_sequence";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":tripID", $tripID);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    $stopTimes = $query->fetchAll();
+    $cur_timepoint = Array();
+    $next_timepoint = Array();
+    $distance_between_timepoints = 0.0;
+    $distance_traveled_between_timepoints = 0.0;
+    $rv = Array();
+    foreach ($stopTimes as $i => $stopTime) {
+        if ($stopTime['arrival_time'] != "") {
+            // is timepoint
+            $cur_timepoint = $stopTime;
+            $distance_between_timepoints = 0.0;
+            $distance_traveled_between_timepoints = 0.0;
+            if ($i + 1 < sizeof($stopTimes)) {
+                $k = $i + 1;
+                $distance_between_timepoints += distance($stopTimes[$k - 1]["stop_lat"], $stopTimes[$k - 1]["stop_lon"], $stopTimes[$k]["stop_lat"], $stopTimes[$k]["stop_lon"]);
+                while ($stopTimes[$k]["arrival_time"] == "" && $k + 1 < sizeof($stopTimes)) {
+                    $k += 1;
+                    // echo "k".$k;
+                    $distance_between_timepoints += distance($stopTimes[$k - 1]["stop_lat"], $stopTimes[$k - 1]["stop_lon"], $stopTimes[$k]["stop_lat"], $stopTimes[$k]["stop_lon"]);
+                }
+                $next_timepoint = $stopTimes[$k];
+            }
+            $rv[] = $stopTime;
+        } else {
+            // is untimed point
+            // echo "i".$i;
+            $distance_traveled_between_timepoints += distance($stopTimes[$i - 1]["stop_lat"], $stopTimes[$i - 1]["stop_lon"], $stopTimes[$i]["stop_lat"], $stopTimes[$i]["stop_lon"]);
+            // echo "$distance_traveled_between_timepoints / $distance_between_timepoints<br>";
+            $distance_percent = $distance_traveled_between_timepoints / $distance_between_timepoints;
+            if ($next_timepoint["arrival_time"] != "") {
+                $total_time = strtotime($next_timepoint["arrival_time"]) - strtotime($cur_timepoint["arrival_time"]);
+                // echo strtotime($next_timepoint["arrival_time"])." - ".strtotime($cur_timepoint["arrival_time"])."<br>";
+                $time_estimate = ($distance_percent * $total_time) + strtotime($cur_timepoint["arrival_time"]);
+                $stopTime["arrival_time"] = date("H:i:s", $time_estimate);
+            } else {
+                $stopTime["arrival_time"] = $cur_timepoint["arrival_time"];
+            }
+            $rv[] = $stopTime;
+        }
+    }
+    // var_dump($rv);
+    return $rv;
+}
+
+function getTripPreviousTimePoint($tripID, $stop_sequence) {
+    global $conn;
+    $query = " SELECT trip_id,stop_id,
+	stop_sequence
+FROM stop_times
+WHERE trip_id = :tripID and stop_sequence < :stop_sequence
+and stop_times.arrival_time IS NOT NULL ORDER BY stop_sequence DESC LIMIT 1";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":tripID", $tripID);
+    $query->bindParam(":stop_sequence", $stop_sequence);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    return $query->fetch(PDO :: FETCH_ASSOC);
+}
+
+function getTripNextTimePoint($tripID, $stop_sequence) {
+    global $conn;
+    $query = " SELECT trip_id,stop_id,
+	stop_sequence
+FROM stop_times
+WHERE trip_id = :tripID and stop_sequence > :stop_sequence
+and stop_times.arrival_time IS NOT NULL ORDER BY stop_sequence LIMIT 1";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":tripID", $tripID);
+    $query->bindParam(":stop_sequence", $stop_sequence);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    return $query->fetch(PDO :: FETCH_ASSOC);
+}
+
+
+
+function getTimeInterpolatedTripAtStop($tripID, $stop_sequence) {
+    global $conn;
+    // limit interpolation to between nearest actual points.
+    $prevTimePoint = getTripPreviousTimePoint($tripID, $stop_sequence);
+    $nextTimePoint = getTripNextTimePoint($tripID, $stop_sequence);
+    // echo " prev {$lowestDelta['stop_sequence']} next {$nextTimePoint['stop_sequence']} ";
+    $range = "";
+    if ($prevTimePoint != "")
+        $range .= " AND stop_sequence >= '{$prevTimePoint['stop_sequence']}'";
+    if ($nextTimePoint != "")
+        $range .= " AND stop_sequence <= '{$nextTimePoint['stop_sequence']}'";
+    foreach (getTimeInterpolatedTrip($tripID, $range) as $tripStop) {
+        if ($tripStop['stop_sequence'] == $stop_sequence)
+            return $tripStop;
+    }
+    return Array();
+}*/
+
+function getTripStartTime($tripID) {
+    global $conn;
+    $query = "Select * from stop_times
+	where trip_id = :tripID
+	AND arrival_time IS NOT NULL
+	AND stop_sequence = '1'";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":tripID", $tripID);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    $r = $query->fetch(PDO :: FETCH_ASSOC);
+    return $r['arrival_time'];
+}
+
+function getTripEndTime($tripID) {
+    global $conn;
+    $query = "SELECT trip_id,max(arrival_time) as arrival_time from stop_times
+	WHERE stop_times.arrival_time IS NOT NULL and trip_id = :tripID group by trip_id";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":tripID", $tripID);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    $r = $query->fetch(PDO :: FETCH_ASSOC);
+    return $r['arrival_time'];
+}
+
+function getActiveTrips($time) {
+    global $conn;
+    if ($time == "")
+        $time = current_time();
+    $query = "Select distinct stop_times.trip_id, start_times.arrival_time as start_time, end_times.arrival_time as end_time from stop_times, (SELECT trip_id,arrival_time from stop_times WHERE stop_times.arrival_time IS NOT NULL
+AND stop_sequence = '1') as start_times, (SELECT trip_id,max(arrival_time) as arrival_time from stop_times WHERE stop_times.arrival_time IS NOT NULL group by trip_id) as end_times
+WHERE start_times.trip_id = end_times.trip_id AND stop_times.trip_id = end_times.trip_id AND :time > start_times.arrival_time  AND :time < end_times.arrival_time";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":time", $time);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    return $query->fetchAll();
+}
+
+function viaPoints($tripID, $stop_sequence = "") {
+    global $conn;
+    $query = "SELECT stops.stop_id, stop_name, arrival_time
+FROM stop_times join stops on stops.stop_id = stop_times.stop_id
+WHERE stop_times.trip_id = :tripID
+" . ($stop_sequence != "" ? " AND stop_sequence > :stop_sequence " : "") ." ORDER BY stop_sequence";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    if ($stop_sequence != "")
+        $query->bindParam(":stop_sequence", $stop_sequence);
+    $query->bindParam(":tripID", $tripID);
+    $query->execute();
+    if (!$query) {
+        databaseError($conn->errorInfo());
+        return Array();
+    }
+    return $query->fetchAll();
+}
+
+function viaPointNames($tripid, $stop_sequence = "") {
+    $viaPointNames = Array();
+    foreach (viaPoints($tripid, $stop_sequence) as $point) {
+        $viaPointNames[] = $point['stop_name'];
+    }
+    if (sizeof($viaPointNames) > 0) {
+        return r_implode(", ", $viaPointNames);
+    } else {
+        return "";
+    }
+}
+
 ?>
Unnamed repository; edit this file to name it for gitweb.
GitPHP by Chris Han