/// <summary> /// Gets all stop times for the given stop. /// </summary> /// <returns></returns> public IEnumerable <StopTime> GetForStop(string stopId) { string sql = "SELECT trip_id, arrival_time, departure_time, stop_id, stop_sequence, stop_headsign, pickup_type, drop_off_type, shape_dist_traveled, passenger_boarding, passenger_alighting, through_passengers, total_passengers, continuous_pickup, continuous_drop_off FROM stop_time WHERE FEED_ID = :id AND stop_id = :stop_id"; var parameters = new List <SQLiteParameter>(); parameters.Add(new SQLiteParameter(@"id", DbType.Int64)); parameters[0].Value = _id; parameters.Add(new SQLiteParameter(@"stop_id", DbType.String)); parameters[1].Value = stopId; return(new SQLiteEnumerable <StopTime>(_connection, sql, parameters.ToArray(), (x) => { return new StopTime() { TripId = x.GetString(0), ArrivalTime = TimeOfDay.FromTotalSeconds(x.GetInt32(1)), DepartureTime = TimeOfDay.FromTotalSeconds(x.GetInt32(2)), StopId = x.GetString(3), StopSequence = (uint)x.GetInt32(4), StopHeadsign = x.IsDBNull(5) ? null : x.GetString(5), PickupType = x.IsDBNull(6) ? null : (PickupType?)x.GetInt64(6), DropOffType = x.IsDBNull(7) ? null : (DropOffType?)x.GetInt64(7), ShapeDistTravelled = x.IsDBNull(8) ? null : x.GetString(8), PassengerBoarding = x.IsDBNull(9) ? null : (int?)x.GetInt32(9), PassengerAlighting = x.IsDBNull(10) ? null : (int?)x.GetInt32(10), ThroughPassengers = x.IsDBNull(11) ? null : (int?)x.GetInt32(11), TotalPassengers = x.IsDBNull(12) ? null : (int?)x.GetInt32(12), ContinuousPickup = x.IsDBNull(13) ? null : (ContinuousPickup?)x.GetInt64(13), ContinuousDropOff = x.IsDBNull(14) ? null : (ContinuousDropOff?)x.GetInt64(14) }; })); }
/// <summary> /// Gets all stop times for the given trips. /// </summary> /// <returns></returns> public IEnumerable <StopTime> GetForTrips(IEnumerable <string> tripIds) { if (tripIds.Count() == 0) { return(new List <StopTime>()); } var results = new List <StopTime>(); var groups = tripIds.SplitIntoGroupsByGroupIdx(); foreach (var group in groups) { var sql = new StringBuilder("SELECT trip_id, arrival_time, departure_time, stop_id, stop_sequence, stop_headsign, pickup_type, drop_off_type, shape_dist_traveled, passenger_boarding, passenger_alighting, through_passengers, total_passengers, continuous_pickup, continuous_drop_off FROM stop_time WHERE FEED_ID = :feed_id AND trip_id = :trip_id0"); var parameters = new List <NpgsqlParameter>(); parameters.Add(new NpgsqlParameter("feed_id", DbType.Int64)); parameters[0].Value = _id; int i = 0; foreach (var tripId in group.Value) { if (i > 0) { sql.Append($" OR trip_id = :trip_id{i}"); } parameters.Add(new NpgsqlParameter($"trip_id{i}", DbType.String)); parameters[1 + i].Value = tripId; i++; } sql.Append(" ORDER BY trip_id ASC, stop_sequence ASC;"); var result = new PostgreSQLEnumerable <StopTime>(_connection, sql.ToString(), parameters.ToArray(), (x) => { return(new StopTime() { TripId = x.GetString(0), ArrivalTime = TimeOfDay.FromTotalSeconds(x.GetInt32(1)), DepartureTime = TimeOfDay.FromTotalSeconds(x.GetInt32(2)), StopId = x.GetString(3), StopSequence = (uint)x.GetInt32(4), StopHeadsign = x.IsDBNull(5) ? null : x.GetString(5), PickupType = x.IsDBNull(6) ? null : (PickupType?)x.GetInt64(6), DropOffType = x.IsDBNull(7) ? null : (DropOffType?)x.GetInt64(7), ShapeDistTravelled = x.IsDBNull(8) ? null : x.GetString(8), PassengerBoarding = x.IsDBNull(9) ? null : (int?)x.GetInt32(9), PassengerAlighting = x.IsDBNull(10) ? null : (int?)x.GetInt32(10), ThroughPassengers = x.IsDBNull(11) ? null : (int?)x.GetInt32(11), TotalPassengers = x.IsDBNull(12) ? null : (int?)x.GetInt32(12), ContinuousPickup = x.IsDBNull(13) ? null : (ContinuousPickup?)x.GetInt64(13), ContinuousDropOff = x.IsDBNull(14) ? null : (ContinuousDropOff?)x.GetInt64(14) }); }); results.AddRange(result); } return(results); }
public void TestLoadFromOneConnection() { var transitDb = new TransitDb(); var feed = DummyGTFSFeedBuilder.OneConnection( TimeOfDay.FromTotalSeconds(0), TimeOfDay.FromTotalSeconds(3600)); transitDb.LoadFrom(feed); Assert.AreEqual(1, transitDb.TripsCount); var tripEnumerator = transitDb.GetTripsEnumerator(); Assert.IsTrue(tripEnumerator.MoveTo(0)); Assert.AreEqual(0, tripEnumerator.Id); Assert.AreEqual(0, tripEnumerator.ScheduleId); var tripMeta = new AttributeCollection(transitDb.TripAttributes.Get(tripEnumerator.MetaId)); Assert.IsTrue(tripMeta.Contains("id", "0")); Assert.IsTrue(tripMeta.Contains("route_id", "0")); Assert.IsTrue(tripMeta.Contains("service_id", "0")); var agencyMeta = new AttributeCollection(transitDb.AgencyAttributes.Get(tripEnumerator.AgencyId)); Assert.IsTrue(agencyMeta.Contains("id", "0")); Assert.AreEqual(2, transitDb.StopsCount); var stopEnumerator = transitDb.GetStopsEnumerator(); Assert.IsTrue(stopEnumerator.MoveTo(0)); Assert.AreEqual(0, stopEnumerator.Id); Assert.AreEqual(0, stopEnumerator.Latitude); Assert.AreEqual(0, stopEnumerator.Longitude); var stopMeta = new AttributeCollection(transitDb.StopAttributes.Get(stopEnumerator.MetaId)); Assert.IsTrue(stopMeta.Contains("id", "0")); Assert.IsTrue(stopEnumerator.MoveTo(1)); Assert.AreEqual(1, stopEnumerator.Id); Assert.AreEqual(1, stopEnumerator.Latitude); Assert.AreEqual(1, stopEnumerator.Longitude); stopMeta = new AttributeCollection(transitDb.StopAttributes.Get(stopEnumerator.MetaId)); Assert.IsTrue(stopMeta.Contains("id", "1")); Assert.AreEqual(1, transitDb.ConnectionsCount); transitDb.SortConnections(DefaultSorting.DepartureTime, null); var connectionEnumerator = transitDb.GetConnectionsEnumerator(DefaultSorting.DepartureTime); Assert.IsTrue(connectionEnumerator.MoveTo(0)); Assert.AreEqual(0, connectionEnumerator.Id); Assert.AreEqual(0, connectionEnumerator.DepartureStop); Assert.AreEqual(0, connectionEnumerator.DepartureTime); Assert.AreEqual(1, connectionEnumerator.ArrivalStop); Assert.AreEqual(3600, connectionEnumerator.ArrivalTime); Assert.AreEqual(0, connectionEnumerator.TripId); }
public void TestAddStopLinksDb() { // build a simple network and connections db. var routerDb = new RouterDb(); routerDb.LoadTestNetwork( System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream( "Itinero.Transit.Test.test_data.networks.network1.geojson")); var transitDb = new TransitDb(); var feed = DummyGTFSFeedBuilder.OneConnection( TimeOfDay.FromTotalSeconds(0), TimeOfDay.FromTotalSeconds(3600)); feed.Stops.Get(0).Latitude = 51.22965768754021f; feed.Stops.Get(0).Longitude = 4.460974931716918f; feed.Stops.Get(1).Latitude = 51.229617377118906f; feed.Stops.Get(1).Longitude = 4.463152885437011f; transitDb.LoadFrom(feed); var db = new MultimodalDb(routerDb, transitDb); // add stop links. var profile = VehicleMock.Car().Fastest(); db.AddStopLinksDb(profile); // check result. var stopLinksDb = db.GetStopLinksDb(profile); Assert.IsNotNull(stopLinksDb); var stop0 = db.TransitDb.SearchFirstStopsWithTags((t) => { return(t.Contains("id", "0")); }); var stop1 = db.TransitDb.SearchFirstStopsWithTags((t) => { return(t.Contains("id", "1")); }); var stopLinksDbEnumerator = stopLinksDb.GetEnumerator(); stopLinksDbEnumerator.MoveTo(stop0); Assert.AreEqual(1, stopLinksDbEnumerator.Count); Assert.IsTrue(stopLinksDbEnumerator.MoveNext()); Assert.AreEqual(0, stopLinksDbEnumerator.EdgeId); Assert.AreEqual(0, stopLinksDbEnumerator.Offset); stopLinksDbEnumerator.MoveTo(stop1); Assert.AreEqual(1, stopLinksDbEnumerator.Count); Assert.IsTrue(stopLinksDbEnumerator.MoveNext()); Assert.AreEqual(0, stopLinksDbEnumerator.EdgeId); Assert.AreEqual(ushort.MaxValue, stopLinksDbEnumerator.Offset); }
/// <summary> /// Returns all stop times. /// </summary> /// <returns></returns> public IEnumerable <StopTime> Get() { #if DEBUG var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); #endif var stopTimes = new List <StopTime>(); using (var reader = _connection.BeginBinaryExport("COPY stop_time TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() > 0) { var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer); stopTimes.Add(new StopTime() { TripId = reader.ReadStringSafe(), ArrivalTime = TimeOfDay.FromTotalSeconds(reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer)), DepartureTime = TimeOfDay.FromTotalSeconds(reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer)), StopId = reader.ReadStringSafe(), StopSequence = (uint)reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer), StopHeadsign = reader.ReadStringSafe(), PickupType = (PickupType?)reader.ReadIntSafe(), DropOffType = (DropOffType?)reader.ReadIntSafe(), ShapeDistTravelled = reader.ReadStringSafe(), PassengerBoarding = (int?)reader.ReadIntSafe(), PassengerAlighting = (int?)reader.ReadIntSafe(), ThroughPassengers = (int?)reader.ReadIntSafe(), TotalPassengers = (int?)reader.ReadIntSafe(), ContinuousPickup = (ContinuousPickup?)reader.ReadIntSafe(), ContinuousDropOff = (ContinuousDropOff?)reader.ReadIntSafe(), }); } } stopTimes = stopTimes.OrderBy(x => x.TripId).ThenBy(x => x.StopSequence).ToList(); #if DEBUG stopwatch.Stop(); Console.WriteLine($"Fetch stoptimes: {stopwatch.ElapsedMilliseconds} ms"); #endif return(stopTimes); }
/// <summary> /// Returns all stop times. /// </summary> /// <returns></returns> public IEnumerable <StopTime> Get() { string sql = "SELECT trip_id, arrival_time, departure_time, stop_id, stop_sequence, stop_headsign, pickup_type, drop_off_type, shape_dist_traveled FROM stop_time WHERE FEED_ID = :id"; var parameters = new List <SQLiteParameter>(); parameters.Add(new SQLiteParameter(@"id", DbType.Int64)); parameters[0].Value = _id; return(new SQLiteEnumerable <StopTime>(_connection, sql, parameters.ToArray(), (x) => { return new StopTime() { TripId = x.GetString(0), ArrivalTime = TimeOfDay.FromTotalSeconds(x.GetInt32(1)), DepartureTime = TimeOfDay.FromTotalSeconds(x.GetInt32(2)), StopId = x.GetString(3), StopSequence = (uint)x.GetInt32(4), StopHeadsign = x.IsDBNull(5) ? null : x.GetString(5), PickupType = x.IsDBNull(6) ? null : (PickupType?)x.GetInt64(6), DropOffType = x.IsDBNull(7) ? null : (DropOffType?)x.GetInt64(7), ShapeDistTravelled = x.IsDBNull(8) ? null : x.GetString(8) }; })); }
/// <summary> /// Builds a GTFS feed with two connections on two different trips, sharing no transfer stop but stops are close together geographically. /// The 'middle' stop has two outgoing connections with one 5 minutes later. /// </summary> /// <returns></returns> public static IGTFSFeed ThreeConnectionsThreeTripsCloseStops(TimeOfDay departureTime1, TimeOfDay arrivalTime1, TimeOfDay departureTime2, TimeOfDay arrivalTime2) { var feed = new GTFSFeed(); feed.Agencies.Add(new Agency() { Id = "0" }); feed.Routes.Add(new global::GTFS.Entities.Route() { Id = "0", AgencyId = "0" }); feed.Routes.Add(new global::GTFS.Entities.Route() { Id = "1", AgencyId = "0" }); feed.Routes.Add(new global::GTFS.Entities.Route() { Id = "2", AgencyId = "0" }); feed.Trips.Add(new Trip() { Id = "0", RouteId = "0" }); feed.Trips.Add(new Trip() { Id = "1", RouteId = "1" }); feed.Trips.Add(new Trip() { Id = "2", RouteId = "2" }); feed.Stops.Add(new Stop() { Id = "0", Longitude = 0, Latitude = 0 }); feed.Stops.Add(new Stop() { Id = "1", Longitude = 1, Latitude = 1 }); feed.Stops.Add(new Stop() { Id = "10", Longitude = 1.0001, Latitude = 1.0001 // approx 15m. }); feed.Stops.Add(new Stop() { Id = "2", Longitude = 2, Latitude = 2 }); feed.StopTimes.Add(new StopTime() { ArrivalTime = departureTime1, StopId = "0", TripId = "0", DepartureTime = departureTime1 }); feed.StopTimes.Add(new StopTime() { ArrivalTime = arrivalTime1, StopId = "1", TripId = "0", DepartureTime = arrivalTime1 }); feed.StopTimes.Add(new StopTime() { ArrivalTime = departureTime2, StopId = "10", TripId = "1", DepartureTime = departureTime2 }); feed.StopTimes.Add(new StopTime() { ArrivalTime = arrivalTime2, StopId = "2", TripId = "1", DepartureTime = arrivalTime2 }); feed.StopTimes.Add(new StopTime() { ArrivalTime = TimeOfDay.FromTotalSeconds(departureTime2.TotalSeconds + 60 * 5), StopId = "10", TripId = "2", DepartureTime = TimeOfDay.FromTotalSeconds(departureTime2.TotalSeconds + 60 * 5) }); feed.StopTimes.Add(new StopTime() { ArrivalTime = TimeOfDay.FromTotalSeconds(arrivalTime2.TotalSeconds + 60 * 5), StopId = "2", TripId = "2", DepartureTime = TimeOfDay.FromTotalSeconds(arrivalTime2.TotalSeconds + 60 * 5) }); return(feed); }