/* Returns the expected arrival times given vehicleId and naptanId pairs */
func SelectNextBusesByVehicleStopPairs(vehiclesStopsMap map[string]string) []NextBus {
var nextBuses []NextBus
innerQueries := make([]string, 0)
for vehicleId, naptanId := range vehiclesStopsMap {
innerQueries = append(innerQueries,
fmt.Sprintf("SELECT line, bound, naptan_id, vehicle_id, expected_arrival_time, already_departed "+
"FROM next_buses "+
"WHERE vehicle_id='%v' AND naptan_id='%v'",
vehicleId, naptanId))
}
dbconn.AcquireLock()
err := db.Select(&nextBuses, strings.Join(innerQueries, " UNION ALL "))
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
/* Set time zone to UTC */
for i, v := range nextBuses {
nextBuses[i].ExpectedArrivalTime = v.ExpectedArrivalTime.In(timezone.Get())
}
return nextBuses
}
/* Returns the naptan ids of the terminuses of given line bound combinations,
as two separate maps */
func SelectTerminuses(lineBoundCombinations map[string]map[string]bool) map[string]map[string]string {
var stopPoints []StopPoint
innerQueries := make([]string, 0)
for line, details := range lineBoundCombinations {
for bound, _ := range details {
innerQueries = append(innerQueries,
fmt.Sprintf(
"(SELECT line_id, bound, naptan_id FROM line_stop_points WHERE line_id='%v' AND bound='%v' ORDER BY stop_seq DESC LIMIT 1)",
line, bound))
}
}
dbconn.AcquireLock()
err := db.Select(&stopPoints, strings.Join(innerQueries, " UNION ALL "))
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make(map[string]map[string]string)
for _, stopPoint := range stopPoints {
_, exists := result[stopPoint.LineId]
if !exists {
result[stopPoint.LineId] = make(map[string]string)
}
result[stopPoint.LineId][stopPoint.Bound] = stopPoint.NaptanId
}
return result
}
/* Returns intermediate stops between (end points inclusive) fromId and toId of line and direction*/
func SelectIntermediateStops(line, bound, fromId, toId string) []string {
var stopPoints []StopPoint
dbconn.AcquireLock()
err := db.Select(&stopPoints,
"SELECT naptan_id FROM line_stop_points "+
"WHERE line_id=$1 AND bound=$2 AND stop_seq >="+
"(SELECT stop_seq FROM line_stop_points WHERE "+
"line_id=$1 AND bound=$2 AND naptan_id=$3 "+
"ORDER BY stop_seq LIMIT 1) "+
"AND stop_seq <= "+
"(SELECT stop_seq FROM line_stop_points WHERE "+
"line_id=$1 AND bound=$2 AND naptan_id=$4 "+
"ORDER BY stop_seq DESC LIMIT 1) "+
"ORDER BY stop_seq ASC",
line, bound, fromId, toId)
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
if len(stopPoints) <= 1 {
logger.GetLogger().Panicf("No intermediate stops between %v and %v for line %v (%v)",
fromId, toId, line, bound)
}
result := make([]string, len(stopPoints))
for i, v := range stopPoints {
result[i] = v.NaptanId
}
logger.GetLogger().Info("Get intermediate stops (inclusive) from line_stop_points returned: %v", result)
return result
}
/* Returns the next buses that are approaching a stop given a line and bound after a certain time */
func SelectNextBuses(lineBoundCombination map[string]string, naptanId string, arrivingAfter time.Time) []NextBus {
var nextBuses []NextBus
innerQueries := make([]string, 0)
for line, bound := range lineBoundCombination {
innerQueries = append(innerQueries,
fmt.Sprintf("SELECT line, bound, naptan_id, vehicle_id, expected_arrival_time, already_departed "+
"FROM next_buses "+
"WHERE naptan_id='%v' AND line='%v' AND bound='%v' AND expected_arrival_time>=TIMESTAMP '%v'",
naptanId, line, bound, toPsqlTimeStamp(arrivingAfter)))
}
sql := fmt.Sprintf("SELECT line, bound, naptan_id, vehicle_id, expected_arrival_time, already_departed "+
"FROM (%v) AS all_next_buses ORDER BY expected_arrival_time",
strings.Join(innerQueries, " UNION ALL "))
logger.GetLogger().Info(sql)
dbconn.AcquireLock()
err := db.Select(&nextBuses, sql)
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
/* Set time zone to UTC */
for i, v := range nextBuses {
nextBuses[i].ExpectedArrivalTime = v.ExpectedArrivalTime.In(timezone.Get())
}
return nextBuses
}
/* Returns intermediate stops between (end points inclusive) fromIcs and toIcs of line and direction
NOTE requires the use of table "stops" */
func SelectIntermediateStopsWithIcsCode(line, bound, fromIcs, toIcs string) ([]string, error) {
var stopPoints []StopPoint
dbconn.AcquireLock()
err := db.Select(&stopPoints,
"SELECT naptan_id FROM line_stop_points "+
"WHERE line_id=$1 AND bound=$2 AND stop_seq >="+
"(SELECT stop_seq FROM line_stop_points JOIN stops ON line_stop_points.naptan_id=stops.id "+
"WHERE line_id=$1 AND bound=$2 AND icscode=$3 "+
"ORDER BY stop_seq LIMIT 1) "+
"AND stop_seq <= "+
"(SELECT stop_seq FROM line_stop_points JOIN stops ON line_stop_points.naptan_id=stops.id "+
"WHERE line_id=$1 AND bound=$2 AND icscode=$4 "+
"ORDER BY stop_seq DESC LIMIT 1) "+
"ORDER BY stop_seq ASC",
line, bound, fromIcs, toIcs)
dbconn.ReleaseLock()
if err != nil {
return nil, err
}
if len(stopPoints) <= 1 {
return nil, errors.New(
fmt.Sprintf("No intermediate stops between %v and %v for line %v (%v)",
fromIcs, toIcs, line, bound))
}
result := make([]string, len(stopPoints))
for i, v := range stopPoints {
result[i] = v.NaptanId
}
logger.GetLogger().Info("Get intermediate stops (inclusive) with icscode from line_stop_points returned: %v", result)
return result, nil
}
func insertRouteDurationsIntoDB(routeDurations map[string]map[string]map[string]time.Duration,
tflEstimates map[string]map[string]time.Duration) {
if len(routeDurations) == 0 {
logger.GetLogger().Error("Lack of route durations. Cycle skipped...")
return
}
values := make([]string, 0)
for line, lineDetails := range routeDurations {
for bound, boundDetails := range lineDetails {
duration := boundDetails["duration"]
historicDuration := boundDetails["historicDuration"]
realTimeDuration := boundDetails["realTimeDuration"]
tflDuration := tflEstimates[line][bound]
values = append(values, fmt.Sprintf("('%v', '%v', INTERVAL '%v seconds', INTERVAL '%v seconds', INTERVAL '%v seconds', INTERVAL '%v seconds')",
line, bound, duration.Seconds(), historicDuration.Seconds(), realTimeDuration.Seconds(), tflDuration.Seconds()))
}
}
valuesCommaSep := strings.Join(values, ",")
dbconn.AcquireLock()
db.MustExec("DELETE FROM delayed_routes")
db.MustExec(
fmt.Sprintf("INSERT INTO delayed_routes (line, bound, estimate, historic_estimate, real_time_estimate, tfl_estimate) "+
"VALUES %v", valuesCommaSep))
dbconn.ReleaseLock()
}
/* Returns a map of current delays
fromStop => (toStop =>
("historicEstimate" => seconds (int64)
"realTimeEstimate" => seconds (int64)
"lastUpdated" => millis in Unix Time (int64))) */
func SelectDelays() map[string]map[string]map[string]int64 {
var delays []CurrentEstimate
dbconn.AcquireLock()
err := db.Select(&delays,
"SELECT from_id, to_id, last_updated, delay_count, "+
"EXTRACT (epoch FROM historic_estimate) AS historic_estimate, "+
"EXTRACT (epoch FROM real_time_estimate) AS real_time_estimate "+
"FROM current_estimates "+
"WHERE delay_count >= 3 "+
"AND last_updated=(SELECT MAX(last_updated) FROM current_estimates)")
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
results := make(map[string]map[string]map[string]int64)
for _, record := range delays {
fromStopDetails, exists := results[record.FromStop]
if !exists {
fromStopDetails = make(map[string]map[string]int64)
}
fromStopDetails[record.ToStop] = map[string]int64{
"historicEstimate": record.HistoricEstimate,
"realTimeEstimate": record.RealTimeEstimate,
"delayCount": record.DelayCount,
"lastUpdated": record.LastUpdated.Unix(),
}
results[record.FromStop] = fromStopDetails
}
return results
}
/* Returns the stop sequence number, given the line, direction and naptanId of the stop
Should there be ties, an error is returned */
func SelectStopSeq(line, bound, naptanId string) (int, error) {
var stopPoints []StopPoint
dbconn.AcquireLock()
err := db.Select(&stopPoints,
"SELECT stop_seq FROM line_stop_points WHERE line_id=$1 AND bound=$2 AND naptan_id=$3",
line, bound, naptanId)
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
if len(stopPoints) == 0 {
logger.GetLogger().Error("Failed to find stop sequence number for stop %v of line %v (%v)",
naptanId, line, bound)
return -1, errors.New(
fmt.Sprintf("Failed to find stop sequence number for stop %v of line %v (%v)",
naptanId, line, bound))
}
if len(stopPoints) > 1 {
logger.GetLogger().Error("Expected unique stop sequence number for stop %v of line %v (%v), but got: %v",
naptanId, line, bound, stopPoints)
return -1, errors.New(
fmt.Sprintf("Expected unique stop sequence number for stop %v of line %v (%v), but got: %v",
naptanId, line, bound, stopPoints))
}
result := stopPoints[0].StopSeq
return result, nil
}
func createMedian() {
createFinalMedianSql := "CREATE OR REPLACE FUNCTION _final_median(NUMERIC[]) " +
"RETURNS NUMERIC AS " +
"$$ " +
"SELECT AVG(val) " +
"FROM ( " +
"SELECT val " +
"FROM unnest($1) val " +
"ORDER BY 1 " +
"LIMIT 2 - MOD(array_upper($1, 1), 2) " +
"OFFSET CEIL(array_upper($1, 1) / 2.0) - 1 " +
") sub; " +
"$$ " +
"LANGUAGE 'sql' IMMUTABLE"
createMedianSql := "CREATE AGGREGATE median(NUMERIC) ( " +
"SFUNC=array_append, " +
"STYPE=NUMERIC[], " +
"FINALFUNC=_final_median, " +
"INITCOND='{}' " +
")"
dbconn.AcquireLock()
tx, err := db.Begin()
tx.Exec(createFinalMedianSql)
tx.Exec(createMedianSql)
err = tx.Commit()
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Warning(err.Error())
return
}
logger.GetLogger().Info("Function 'median' created successfully")
}
/* Returns all current estimates of bus travel times
CATCH: Not all stop pairs may be present, especially if estimate is not recently generated
e.g. routes served by night buses in the day*/
func SelectAllCurrentEstimates() map[string]map[string]time.Duration {
var currentEstimates []CurrentEstimate
dbconn.AcquireLock()
err := db.Select(¤tEstimates,
"SELECT from_id, to_id, last_updated, "+
"EXTRACT(epoch FROM estimate) AS estimate "+
"FROM current_estimates "+
"WHERE last_updated=(SELECT MAX(last_updated) FROM current_estimates)")
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make(map[string]map[string]time.Duration)
for _, currentEstimate := range currentEstimates {
fromStop := currentEstimate.FromStop
toStop := currentEstimate.ToStop
estimate := time.Duration(currentEstimate.Estimate) * time.Second
/* Depending on whether from stop exists or not, decide whether there
is need to create a new map in results */
toStopMap, exists := result[fromStop]
if !exists {
toStopMap = make(map[string]time.Duration)
}
toStopMap[toStop] = estimate
result[fromStop] = toStopMap
}
return result
}
func replaceTimetable(line, bound, origin string, newTimetable map[time.Weekday]map[selectdb.LocalTime]bool) {
values := make([]string, 0)
for weekday, departureTimes := range newTimetable {
for departureTime, _ := range departureTimes {
values = append(values, fmt.Sprintf("('%v', '%v', '%v', %v, TIME '%02d:%02d:00')",
line, bound, origin, int(weekday), departureTime.Hour, departureTime.Minute))
}
}
dbconn.AcquireLock()
tx := db.MustBegin()
tx.MustExec(fmt.Sprintf("DELETE FROM timetable WHERE line_id='%v' AND bound='%v' AND naptan_id='%v'", line, bound, origin))
logger.GetLogger().Info(fmt.Sprintf("INSERT INTO timetable (line_id, bound, naptan_id, weekday, departure_time) VALUES %s",
strings.Join(values, ",")))
tx.MustExec(
fmt.Sprintf("INSERT INTO timetable (line_id, bound, naptan_id, weekday, departure_time) VALUES %s",
strings.Join(values, ",")))
err := tx.Commit()
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Error(err.Error())
}
logger.GetLogger().Info("Replaced timetable for line %v (%v), stop %v", line, bound, origin)
}
/* Returns sequences of all naptanIds, grouped by line and bound
map (line => (bound => []naptanIds)) */
func SelectAllLineStopPoints() map[string]map[string][]string {
var stopPoints []StopPoint
dbconn.AcquireLock()
err := db.Select(&stopPoints,
"SELECT line_id, bound, stop_seq, naptan_id "+
"FROM line_stop_points "+
"ORDER BY line_id, bound, stop_seq")
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make(map[string]map[string][]string)
for _, v := range stopPoints {
lineDetails, exists := result[v.LineId]
if !exists {
lineDetails = make(map[string][]string)
}
orderedStopPoints, exists := lineDetails[v.Bound]
if !exists {
orderedStopPoints = make([]string, 0)
}
orderedStopPoints = append(orderedStopPoints, v.NaptanId)
lineDetails[v.Bound] = orderedStopPoints
result[v.LineId] = lineDetails
}
logger.GetLogger().Info("Get existing line stop points returned: %v", result)
return result
}
/* Returns timetable for specified line, bound and stop in the form of a map
map (weekday => []("hour" => hour
"minute" => minute)) */
func SelectTimetable(line, bound, naptanId string) map[time.Weekday]map[LocalTime]bool {
var timetable []TimetableEntry
dbconn.AcquireLock()
err := db.Select(&timetable,
"SELECT weekday, departure_time FROM timetable WHERE line_id=$1 AND bound=$2 AND naptan_id=$3 ORDER BY weekday,departure_time",
line, bound, naptanId)
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make(map[time.Weekday]map[LocalTime]bool)
for _, entry := range timetable {
weekday := time.Weekday(entry.Weekday)
weekdayMap, exists := result[weekday]
if !exists {
weekdayMap = make(map[LocalTime]bool, 0)
result[weekday] = weekdayMap
}
weekdayMap[LocalTime{Hour: entry.DepartureTime.Hour(), Minute: entry.DepartureTime.Minute()}] = true
}
logger.GetLogger().Info("Get timetable for line %v (%v), stop %v returned: %v", line, bound, naptanId, result)
return result
}
/* Returns all available routes stored in tfl_predictions_history
[]map ("line" => line,
"bound" => bound,
"fromStop" => fromStop,
"toStop" => toStop)
*/
func SelectAvailableRoutesTflPredictions() []map[string]string {
type AvailableRoute struct {
Line string `db:"line"`
Bound string `db:"bound"`
FromStop string `db:"from_id"`
ToStop string `db:"to_id"`
}
var availableRoutes []AvailableRoute
dbconn.AcquireLock()
err := db.Select(&availableRoutes, "SELECT DISTINCT line, bound, from_id, to_id FROM tfl_predictions_history")
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make([]map[string]string, len(availableRoutes))
for i, v := range availableRoutes {
result[i] = map[string]string{
"line": v.Line,
"bound": v.Bound,
"fromStop": v.FromStop,
"toStop": v.ToStop,
}
}
return result
}
/* Inserts expected departures found above into table "expected_departures".
To supply map of expected departures
map (vehicleId =>
("line" => line,
"bound" => bound,
"naptanId" => naptanId of terminus,
"expectedDeparture" => expectedDeparture in TfL format,
))
*/
func insertExpectedDeparturesIntoDB(expectedDepartures map[string]map[string]string) {
values := make([]string, 0)
for vehicleId, details := range expectedDepartures {
values = append(values,
fmt.Sprintf("('%v', '%v', '%v', '%v', TIMESTAMP '%v')",
details["line"], details["bound"], details["naptanId"], vehicleId,
toPsqlTimeStamp(details["expectedDeparture"])))
}
valuesCommaSep := strings.Join(values, ",")
dbconn.AcquireLock()
db.MustExec(
fmt.Sprintf("UPDATE terminus_departures AS entry "+
"SET line=data.line, bound=data.bound, naptan_id=data.naptan_id, expected_departure_time=data.expected_departure_time, last_updated=DEFAULT "+
"FROM (VALUES %s) AS data(line, bound, naptan_id, vehicle_id, expected_departure_time) "+
"WHERE data.vehicle_id=entry.vehicle_id",
valuesCommaSep))
db.MustExec(
fmt.Sprintf("WITH data(line, bound, naptan_id, vehicle_id, expected_departure_time) AS (VALUES %s) "+
"INSERT INTO terminus_departures(line, bound, naptan_id, vehicle_id, expected_departure_time) "+
"SELECT data.line, data.bound, data.naptan_id, data.vehicle_id, data.expected_departure_time FROM data WHERE NOT EXISTS "+
"(SELECT 1 FROM terminus_departures entry "+
"WHERE data.vehicle_id=entry.vehicle_id)",
valuesCommaSep))
dbconn.ReleaseLock()
}
func DeleteOldHistory(days int) {
/* Remove data older than days old from journey_history to save space */
dbconn.AcquireLock()
_, err := db.Exec(fmt.Sprintf("DELETE FROM journey_history WHERE to_timestamp < NOW() - INTERVAL '%v days'", days))
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
/* Remove data older than days old from skipped_stops_history to save space */
dbconn.AcquireLock()
_, err = db.Exec(fmt.Sprintf("DELETE FROM skipped_stops_history WHERE to_timestamp < NOW() - INTERVAL '%v days'", days))
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
}
/* Returns the tfl predictions at the times specified for a given line, bound, origin and destination
map time.Time => time.Duration */
func SelectTflPredictions(line, bound, fromStop, toStop string, times []time.Time) map[time.Time]time.Duration {
tflPredictions := make([]int64, len(times))
dbconn.AcquireLock()
tx, err := db.Begin()
for i, targetTime := range times {
upperBound := targetTime.Add(10 * time.Minute)
lowerBound := targetTime.Add(-10 * time.Minute)
logger.GetLogger().Info(fmt.Sprintf("SELECT EXTRACT(epoch FROM predicted_time) FROM ("+
"(SELECT predicted_time, departure_time FROM tfl_predictions_history "+
"WHERE line='%v' AND bound='%v' AND from_id='%v' AND to_id='%v' AND departure_time>='%v' AND departure_time <= '%v' ORDER BY departure_time LIMIT 1) "+
"UNION ALL "+
"(SELECT predicted_time, departure_time FROM tfl_predictions_history "+
"WHERE line='%v' AND bound='%v' AND from_id='%v' AND to_id='%v' AND departure_time<'%v' AND departure_time >= '%v' ORDER BY departure_time DESC LIMIT 1) "+
") AS result ORDER BY abs(EXTRACT(epoch FROM departure_time-'%v')) LIMIT 1",
line, bound, fromStop, toStop, toPsqlTimeStamp(targetTime), toPsqlTimeStamp(upperBound),
line, bound, fromStop, toStop, toPsqlTimeStamp(targetTime), toPsqlTimeStamp(lowerBound),
toPsqlTimeStamp(targetTime)))
err = tx.QueryRow(
fmt.Sprintf("SELECT EXTRACT(epoch FROM predicted_time) FROM ("+
"(SELECT predicted_time, departure_time FROM tfl_predictions_history "+
"WHERE line='%v' AND bound='%v' AND from_id='%v' AND to_id='%v' AND departure_time>='%v' AND departure_time <= '%v' ORDER BY departure_time LIMIT 1) "+
"UNION ALL "+
"(SELECT predicted_time, departure_time FROM tfl_predictions_history "+
"WHERE line='%v' AND bound='%v' AND from_id='%v' AND to_id='%v' AND departure_time<'%v' AND departure_time >= '%v' ORDER BY departure_time DESC LIMIT 1) "+
") AS result ORDER BY abs(EXTRACT(epoch FROM departure_time-'%v')) LIMIT 1",
line, bound, fromStop, toStop, toPsqlTimeStamp(targetTime), toPsqlTimeStamp(upperBound),
line, bound, fromStop, toStop, toPsqlTimeStamp(targetTime), toPsqlTimeStamp(lowerBound),
toPsqlTimeStamp(targetTime))).Scan(&tflPredictions[i])
if err != nil {
logger.GetLogger().Error(err.Error())
tflPredictions[i] = 0
}
}
err = tx.Commit()
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make(map[time.Time]time.Duration)
for i, t := range times {
/* Skip if no recent prediction found (within 20 minute window) */
tflPrediction := tflPredictions[i]
if tflPrediction == 0 {
continue
}
result[t] = time.Duration(tflPredictions[i]) * time.Second
}
return result
}
/* Returns the time required for each cycle of update process, so that script can compensate for time difference*/
func SelectCurrentEstimatesCompensation() (time.Duration, error) {
var compensationInSeconds int64
dbconn.AcquireLock()
err := db.QueryRow("SELECT EXTRACT(epoch FROM MAX(last_updated)-MIN(last_updated)) " +
"FROM (SELECT last_updated FROM current_estimates ORDER BY last_updated DESC LIMIT 2) AS lastest").Scan(&compensationInSeconds)
dbconn.ReleaseLock()
if err != nil {
return 0, err
}
return time.Duration(compensationInSeconds) * time.Second, nil
}
func SelectPerformanceMeasure2(startTime time.Time) (map[string]map[string][]map[string]string, error) {
var performanceMeasures []PerformanceMeasure
dbconn.AcquireLock()
err := db.Select(&performanceMeasures, "SELECT line, bound, vehicle_id, from_id, to_id, departure_time, arrival_time, "+
"EXTRACT(epoch FROM tfl_expected_time) AS tfl_expected_time, "+
"EXTRACT(epoch FROM predicted_time) AS predicted_time, "+
"EXTRACT(epoch FROM actual_time) AS actual_time "+
"FROM performance_measure_2 "+
"WHERE db_inserted_timestamp>=$1", startTime)
dbconn.ReleaseLock()
if err != nil {
return nil, err
}
result := make(map[string]map[string][]map[string]string)
for _, performanceMeasure := range performanceMeasures {
/* Insert into results, careful not to overwrite map if already exists */
boundDetails, boundDetailsExists := result[performanceMeasure.Line]
if !boundDetailsExists {
boundDetails = make(map[string][]map[string]string)
}
innerSlice, innerSliceExists := boundDetails[performanceMeasure.Bound]
if !innerSliceExists {
innerSlice = make([]map[string]string, 0)
}
details := make(map[string]string)
details["fromStop"] = performanceMeasure.FromStop
details["toStop"] = performanceMeasure.ToStop
details["tflExpectedTime"] = toPsqlFormatDuration(performanceMeasure.TflExpectedTime)
details["predictedTime"] = toPsqlFormatDuration(performanceMeasure.PredictedTime)
details["actualTime"] = toPsqlFormatDuration(performanceMeasure.ActualTime)
details["departureTime"] = toPsqlFormatTime(performanceMeasure.DepartureTime)
details["arrivalTime"] = toPsqlFormatTime(performanceMeasure.ArrivalTime)
details["vehicleId"] = performanceMeasure.VehicleId
innerSlice = append(innerSlice, details)
boundDetails[performanceMeasure.Bound] = innerSlice
result[performanceMeasure.Line] = boundDetails
}
return result, nil
}
/* Returns the naptan id of a stop given its line, bound and stop sequence */
func SelectStopWithStopSeq(line, bound string, stopSeq int) string {
var naptanId string
dbconn.AcquireLock()
err := db.QueryRow("SELECT naptan_id "+
"FROM line_stop_points "+
"WHERE line_id=$1 "+
"AND bound=$2 "+
"AND stop_seq=$3 ", line, bound, stopSeq).Scan(&naptanId)
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
return naptanId
}
/* Returns whether the given line is outbound only */
func SelectIsOutboundOnly(line string) bool {
var isOutboundOnly bool
dbconn.AcquireLock()
err := db.QueryRow("SELECT NOT EXISTS "+
"(SELECT 1 FROM line_stop_points "+
"WHERE line_id=$1 "+
"AND bound='inbound')", line).Scan(&isOutboundOnly)
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
return isOutboundOnly
}
/* Returns the station naptan conrresponding to a naptan id*/
func SelectStationNaptan(id string) (string, error) {
var stop []Stop
dbconn.AcquireLock()
err := db.Select(&stop, "SELECT station_naptan FROM stops WHERE id=$1", id)
dbconn.ReleaseLock()
if err != nil {
return "", err
}
if len(stop) != 1 {
return "", errors.New(fmt.Sprintf("Expected exactly 1 name for naptan id: %v, but got: %v", id, stop))
}
return stop[0].StationNaptan, nil
}
/* Return stop name given its naptan id */
func SelectStop(id string) string {
var stop []Stop
dbconn.AcquireLock()
err := db.Select(&stop, "SELECT name FROM stops WHERE id=$1", id)
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
if len(stop) != 1 {
logger.GetLogger().Panicf("Expected exactly 1 name for naptan id: %v, but got: %v", id, stop)
}
return stop[0].Name
}
/* Inserts the three times into database for comparison */
func insertIntoPerformanceMeasureDB(line, bound, vehicleId, origin, destination string, tflExpectedTime, predictedTime, actualTime time.Duration,
departureTime, arrivalTime time.Time) {
dbconn.AcquireLock()
_, err := db.Exec(
fmt.Sprintf("INSERT INTO performance_measure(line, bound, vehicle_id, from_id, to_id, "+
"tfl_expected_time, predicted_time, actual_time, departure_time, arrival_time, db_inserted_timestamp) "+
"VALUES ('%v', '%v', '%v', '%v', '%v', INTERVAL '%v', INTERVAL '%v', INTERVAL '%v', "+
"TIMESTAMP '%v', TIMESTAMP '%v', TIMESTAMP '%v')",
line, bound, vehicleId, origin, destination,
int(tflExpectedTime.Seconds()), int(predictedTime.Seconds()), int(actualTime.Seconds()),
toPsqlTimeStamp(departureTime), toPsqlTimeStamp(arrivalTime), getCurrentPsqlTimeStamp()))
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
}
func SelectLines() []string {
var lines []Line
dbconn.AcquireLock()
err := db.Select(&lines, "SELECT id FROM lines")
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make([]string, len(lines))
for i, v := range lines {
result[i] = v.Id
}
logger.GetLogger().Info("Get existing lines returned: %v", result)
return result
}
/* Returns slice of naptan ids */
func SelectStops() []string {
var stops []Stop
dbconn.AcquireLock()
err := db.Select(&stops, "SELECT id FROM stops")
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make([]string, len(stops))
for i, v := range stops {
result[i] = v.Id
}
logger.GetLogger().Info("Get existing stops returned: %v", result)
return result
}
/* Returns map of adjacent stops (from_id => to_id) serving given lines and bounds,
also individual adjacent stops grouped by line and bound */
func SelectMultipleAdjacentStops(lineBoundCombinations map[string]map[string]bool) (map[string]map[string]bool, map[string]map[string]map[string]string) {
var adjacentStops []AdjacentStop
innerQueries := make([]string, 0)
for line, details := range lineBoundCombinations {
for bound, _ := range details {
innerQueries = append(innerQueries,
fmt.Sprintf("SELECT line_id, bound, from_id, to_id FROM adjacent_stops WHERE line_id='%v' AND bound='%v'",
line, bound))
}
}
dbconn.AcquireLock()
err := db.Select(&adjacentStops, strings.Join(innerQueries, " UNION ALL "))
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make(map[string]map[string]bool)
resultsByLineBound := make(map[string]map[string]map[string]string)
for _, v := range adjacentStops {
/* Handle result */
_, exists := result[v.FromId]
if !exists {
result[v.FromId] = make(map[string]bool)
}
result[v.FromId][v.ToId] = true
/* Handle result by line bound */
_, exists = resultsByLineBound[v.LineId]
if !exists {
resultsByLineBound[v.LineId] = make(map[string]map[string]string)
}
_, exists = resultsByLineBound[v.LineId][v.Bound]
if !exists {
resultsByLineBound[v.LineId][v.Bound] = make(map[string]string)
}
resultsByLineBound[v.LineId][v.Bound][v.FromId] = v.ToId
}
logger.GetLogger().Info("Get existing adjacent stops for lines %v returned: %v", lineBoundCombinations, result)
return result, resultsByLineBound
}
/*
Returns the longest lasting time satisfying supplied constraints
*/
func SelectDelayedRoutes() []DelayedRoute {
var delayedRoutes []DelayedRoute
dbconn.AcquireLock()
err := db.Select(&delayedRoutes,
"SELECT line, bound, "+
"EXTRACT(epoch FROM estimate) AS estimate, "+
"EXTRACT(epoch FROM historic_estimate) AS historic_estimate, "+
"EXTRACT(epoch FROM real_time_estimate) AS real_time_estimate, "+
"EXTRACT(epoch FROM tfl_estimate) AS tfl_estimate "+
"FROM delayed_routes")
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
return delayedRoutes
}
/* Returns terminus given line and direction */
func SelectTerminus(line, bound string) string {
var stopPoints []StopPoint
dbconn.AcquireLock()
err := db.Select(&stopPoints,
"SELECT naptan_id FROM line_stop_points WHERE line_id=$1 AND bound=$2 ORDER BY stop_seq DESC LIMIT 1",
line, bound)
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
if len(stopPoints) == 0 {
logger.GetLogger().Panicf("Failed to find terminus for line %v (%v)", line, bound)
}
result := stopPoints[0].NaptanId
return result
}
/* Returns slice of all naptanIds from table line_stop_points */
func SelectAllStopsFromLineStopPoints() []string {
var stopPoints []StopPoint
dbconn.AcquireLock()
err := db.Select(&stopPoints,
"SELECT DISTINCT naptan_id FROM line_stop_points")
dbconn.ReleaseLock()
if err != nil {
logger.GetLogger().Panic(err)
}
result := make([]string, len(stopPoints))
for i, v := range stopPoints {
result[i] = v.NaptanId
}
logger.GetLogger().Info("Get all stop points from line_stop_points returned: %v", result)
return result
}