/// <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);
}
