def create_observation_id( trip_id, stop_id, day_of_week, stop_sequence ):
"""
Inserts an entry with new observed_stop_id into observation_attributes.
Returns the new ID.
"""
## observed_stop_id is not necessarily unique, across service intervals
cur = get_cursor()
SQLExec(cur,"select max(observed_stop_id) from observation_attributes")
r = list(cur)
if len(r) == 0 or r[0][0] is None:
newid = 0
else:
newid = r[0][0] + 1
sql = """\
insert into observation_attributes
(observed_stop_id, trip_id, stop_id, stop_sequence, day_of_week)
values
( %(osid)s, %(tid)s, %(sid)s, %(seq)s, %(dow)s )
"""
SQLExec(cur, sql, {'osid':newid, 'tid':trip_id, 'sid':stop_id,
'seq':stop_sequence, 'dow':day_of_week})
cur.close()
return newid
def get_route_for_dirtag(dirtag,routetag=None):
"""
Given a nextbus dirtag, returns the GTFS route ID if known.
If routetag is provided, and the GTFS route ID is not known,
attempts to make a match based off the routetag alone.
"""
cur = get_cursor();
SQLExec(cur,
"""Select route_id from routeid_dirtag where dirtag=%(dirtag)s""",
{'dirtag':dirtag});
ret = [r[0] for r in cur];
if not ret and routetag:
SQLExec(cur,
"""select route_id from gtf_routes gr
where gr.route_short_name=%(routetag)s""",
{'routetag':routetag})
ret = [r[0] for r in cur]
cur.close()
if len(ret) > 1:
print "MORE THAN ONE ROUTE PER DIRTAG"
print " dirtag:",dirtag
print " routes:",ret
if len(ret) == 0:
print "No routes mapped for dirtag",dirtag
return None
return ret[0]
def export_gps_route( trip_id, trip_date, vehicle_id,
gtfs_error, offset_seconds,
gps_data ):
"""
Writes the given entry to the "tracked_routes" table. This table is used
to cache the results of finding and filtering only the valid routes as
represented in the GPS dataset.
Returns segment_id, a unique identifier for this GPS segment
trip_id: the GTFS trip id
trip_date: the date of the trip
vehicle_id: as reported in the GPS data
gtfs_error: The distance from the matched GTFS trip as measured by
the GPSBusTrack metric
offset_seconds: Number of seconds to subtract from GTFS trip to normalize.
gps_data: A list of (lat, lon, reported_update_time) values, exactly as
reported in the GPS dat. Note that reported_update_time should
be a timestamp.
WARNING: No effort is made to prevent duplicate entries! If you do this
more than once for the same route then YOU MUST DELETE IT FIRST!
"""
sql1 = """insert into gps_segments (
trip_id, trip_date, vehicle_id,
schedule_error, schedule_offset_seconds
) VALUES (
%(trip_id)s,%(trip_date)s,%(vehicle_id)s,
%(gtfs_error)s, %(offset)s
) RETURNING gps_segment_id"""
sql2 = """insert into tracked_routes (
gps_segment_id, lat, lon, reported_update_time
) VALUES (
%(seg_id)s,%(lat)s,%(lon)s,%(reported_update_time)s
)"""
cur = get_cursor()
SQLExec(cur,sql1,
{'trip_id':trip_id,'trip_date':trip_date,'vehicle_id':vehicle_id,
'gtfs_error':str(gtfs_error),'offset':offset_seconds});
segment_id = list(cur.fetchall())[0][0];
for lat,lon,reported_update_time in gps_data:
SQLExec(cur,sql2,
{'lat':lat,'lon':lon,
'reported_update_time':reported_update_time,
'seg_id':str(segment_id)});
cur.close()
return segment_id
def correct_gps_schedule( segment_id, trip_id, gtfs_error, offset_seconds,
gps_data ):
sql1="""update gps_segments set trip_id=%(tid)s,schedule_error=%(gerr)s,
schedule_offset_seconds=%(os)s
where gps_segment_id=%(gid)s"""
sql2="""delete from gps_stop_times where gps_segment_id=%(gid)s"""
cur = get_cursor()
SQLExec(cur,sql1,{'tid':trip_id,'gerr':gtfs_error,'os':offset_seconds,
'gid':segment_id});
SQLExec(cur,sql2,{'gid':segment_id});
cur.close()
export_gps_schedule( segment_id, gps_data )
def get_previous_trip_ID(trip_id, start_date, offset, numtrips=10):
"""
Given GTFS trip ID, the date it ran on, and the schedule's offset in seconds,
finds the immediately previous GTFS trip ID with the same direction and
route. The start_date is necessary in cases close to midnight.
"""
cur = get_cursor();
SQLExec(cur,"select route_id,direction_id from gtf_trips where trip_id=%(tid)s",
{'tid':trip_id});
routeinfo = list(cur)[0];
route_id,dir_id = map(lambda s:routeinfo[s],
"route_id,direction_id".split(","));
SQLExec(cur,"""select first_departure as mintime
from gtf_trip_information where trip_id=%(tid)s""",
{'tid':trip_id});
# start_time is the time the bus started for the date start_date
start_time = list(cur)[0]['mintime'] - offset;
yesterday_ids = map(lambda sid: "'"+str(sid)+"'",
get_serviceIDs_for_date(start_date -
datetime.timedelta(days=1)));
today_ids = map(lambda sid: "'"+str(sid)+"'",
get_serviceIDs_for_date(start_date));
sql = """(select trip_id, 0 as offset,
abs(first_departure- %(start_time)s) as diff
from gtf_trips natural join gtf_trip_information
where direction_id=%(dir_id)s and route_id=%(route_id)s
and service_id in (""" + ','.join(today_ids) + """)
and first_departure < %(start_time)s
union
select trip_id, 86400 as offset,
abs(first_departure-86400- %(start_time)s ) as diff
from gtf_trips natural join gtf_trip_information
where direction_id=%(dir_id)s and route_id=%(route_id)s
and service_id in (""" + ','.join(yesterday_ids) + """)
and first_departure-86400 < %(start_time)s
) order by diff limit """ + str(numtrips)
SQLExec(cur,sql,
{'start_time':start_time,'dir_id':dir_id,'route_id':route_id});
ret = [(r['trip_id'],r['offset']) for r in cur]
cur.close()
if len(ret) == 0:
return None
return ret
def get_shapes_for_route(route_short_name):
"""
Given a route short name, returns a list of dictlike rows
containing the shapes associated with that route, sorted in
order of ascending shape ID then ascending shape point sequence.
Keys:
'shape_id',
'shape_pt_lat',
'shape_pt_lon',
'shape_pt_sequence',
'shape_dist_traveled',
'dirtag'
"""
cur = get_cursor();
SQLExec(cur, """SELECT gtf_shapes.*,shape_dirtag.dirtag
FROM gtf_shapes,shape_dirtag
WHERE gtf_shapes.shape_id = shape_dirtag.shape_id
and gtf_shapes.shape_id IN
(select distinct(shape_id) from gtf_trips
where route_id IN
(select route_id from gtf_routes
where route_short_name = %(route_short_name)s
)
)
ORDER BY gtf_shapes.shape_id asc,
gtf_shapes.shape_pt_sequence asc""",
{'route_short_name':route_short_name});
ret = [r for r in cur];
cur.close();
return ret;
def get_vehicle_reports(dirtags,tzdiff=0):
"""
Given a list of dirtags, returns a list of dictlike rows of
vehicle tracking reports, sorted in ascending order of update time.
Keys:
'id',
'lat',
'lon',
'routetag',
'dirtag',
'reported_update_time'
"""
if len(dirtags) == 0:
return []
p = {}
for i,d in enumerate(dirtags):
p['k'+str(i)] = d;
sql = """SELECT id,lat,lon,routetag,dirtag,
reported_update_time + interval '%d hours'
from vehicle_track
where dirtag IN ( %s )
order by reported_update_time asc""" \
% (int(tzdiff), ','.join(map(lambda k: "%("+k+")s", p.keys())) )
cur = get_cursor();
print "Executing..."
SQLExec(cur, sql, p);
print "Retrieving..."
ret = cur.fetchall();
print "...done."
cur.close();
return ret;
def lateness_observed( trip_id, stop_id, day_of_week, stop_sequence,
lateness_seconds, auto_create=True ):
"""
Increments the count for the indicated observation and returns True.
If the entry does not exist, will create the entry if auto_create is
True, otherwise returns False.
"""
sql = """\
update simplified_lateness_observations
set num_observations = num_observations+1
where minutes_late=%(lateness)s
and observed_stop_id=%(osid)s
"""
osid = get_observation_stop_id( trip_id,stop_id,day_of_week,stop_sequence,
auto_create = False )
# round to the nearest minute
ret = True
if lateness_seconds is not None:
lateness_minutes = int( (lateness_seconds/60.0) + 0.5 )
else:
lateness_minutes = None
cur = get_cursor()
SQLExec(cur,sql, {'osid':osid,'lateness':lateness_minutes})
if cur.rowcount == 0:
if auto_create:
create_observation_row(trip_id,stop_id,day_of_week,stop_sequence,
lateness_minutes,initial_num_obs=1)
else:
ret = False
elif cur.rowcount > 1:
raise Exception, "Redundant rows in observations table"
cur.close()
return ret
def load_gps_schedule(segment_id):
"""
Given a segment_id, loads the corresponding arrival schedule from the
gps_stop_times table in the database.
Returns a list of dictlike rows, each with the following keys:
'stop_id'
'stop_sequence'
'stop_headsign'
'pickup_type'
'drop_off_type',
'shape_dist_traveled',
'timepoint',
'arrival_time_seconds',
'departure_time_seconds',
'actual_arrival_time_seconds'
'seconds_since_last_stop'
The rows will be in order of increasing stop sequence.
"""
sql = """select * from gps_stop_times
where gps_segment_id=%(segid)s
order by stop_sequence asc"""
cur = get_cursor();
SQLExec(cur,sql,{'segid':segment_id});
ret = list(cur);
cur.close();
return ret;
def measure_prob_mass( trip_id, stop_id, day_of_week, stop_sequence,
lateness_bounds ):
sql = """\
select num_observations, minutes_late
from simplified_lateness_observations slo
inner join observation_attributes oa
on slo.observed_stop_id = oa.observed_stop_id
and oa.trip_id=%(tid)s
and oa.stop_sequence=%(seq)s
and oa.day_of_week=%(dow)s
"""
cur = get_cursor()
SQLExec(cur, sql, {'tid':trip_id,'seq':stop_sequence,'dow':day_of_week})
rows = map( lambda r: (r['num_observations'],r['minutes_late']),
cur.fetchall() );
cur.close()
reducer = lambda l,r: l+r[0]
total = reduce( reducer, rows, 0 )
sums = [0] * len(lateness_bounds)
for i,(min,max) in enumerate(lateness_bounds):
sums[i] = reduce(reducer,
filter( lambda r: min<=r[1]<=max, rows ),
0)
if total == 0:
return None
return map(lambda i: float(sums[i])/total,
range(len(lateness_bounds)))
def get_stop_info( stop_id, day_of_week ):
## SF hack here, for now.
## Need to define a way of handling the "day of week"
## problem in terms of service IDs.
if 0 <= day_of_week <= 4:
service_id = '1'
elif day_of_week == 5:
service_id = '2'
elif day_of_week == 6:
service_id = '3'
else:
raise Exception, "Not a day of week"
sql = """\
select gst.*, gt.*, gr.*, oa.observed_stop_id
from gtf_stop_times gst
inner join gtf_trips gt on gst.trip_id = gt.trip_id
inner join gtf_routes gr on gt.route_id = gr.route_id
left outer join observation_attributes oa
on oa.trip_id = gst.trip_id
and oa.stop_sequence = gst.stop_sequence
and oa.day_of_week=%(dow)s
where gst.stop_id=%(stopid)s
and gt.service_id=%(sid)s
order by gr.route_short_name, gst.arrival_time_seconds
"""
cur = get_cursor()
SQLExec(cur,sql,{'stopid':stop_id,'sid':service_id,'dow':day_of_week})
rows = cur.fetchall()
cur.close()
return map(dict,rows)
def load_gps_route(segment_id):
"""
Given a segment ID, loads the associated trip from the tracked_routes table
in order of increasing report time.
Returns (trip_id, trip_date, vehicle_id, schedule_error, offset, route)
where trip_id is the gtfs trip ID, trip_date is the date on whose schedule
the trip took place, vehicle_id is the gps vehicle's ID, schedule_error
is the measured error between the GPS route and the GTFS schedule, offset
is the number of seconds to substract from any GTFS schedule times, and
route is a list of [lat,lon,reported_update_time] triples.
"""
sql = """select lat, lon, reported_update_time
from tracked_routes
where gps_segment_id=%(segID)s
order by reported_update_time"""
cur = get_cursor()
SQLExec(cur,sql,{'segID':segment_id});
res = [r for r in cur];
cur.close();
trip_id,trip_date,veh_id,sched_err,sched_off= load_gps_segment_header(segment_id);
rows = [[r['lat'],r['lon'],r['reported_update_time']] for r in res];
return trip_id,trip_date,veh_id,sched_err,sched_off,rows
def get_observation_stop_id( trip_id, stop_id, day_of_week, stop_sequence,
auto_create = True):
sql="""\
select observed_stop_id
from observation_attributes oa
where oa.trip_id=%(tid)s
and oa.stop_sequence=%(seq)s
and oa.day_of_week=%(dow)s
"""
cur = get_cursor()
SQLExec(cur, sql, {'tid':trip_id,'seq':stop_sequence,'dow':day_of_week})
rows = [r[0] for r in cur]
if len(rows) == 0:
if auto_create:
ret = create_observation_id(trip_id,stop_id,day_of_week,stop_sequence)
else:
ret = None
elif len(rows) > 1:
raise Exception, "Redundant observation IDs"
else:
ret = rows[0]
return ret
def simplified_lateness_counts():
"""
This is a one-time function to translate all data from datamining_table
into simplified_lateness_observations.
"""
sql = """
select dm.lateness, dm.gtfs_trip_id, dm.stop_id, dm.stop_sequence,
((EXTRACT(DOW FROM gs.trip_date) + 6)::integer % 7) as dow
from datamining_table dm
inner join gps_segments gs on gs.gps_segment_id = dm.gps_segment_id
"""
cur = get_cursor()
SQLExec(cur,sql);
tot = cur.rowcount
i=1
for row in cur:
if row['lateness'] is None:
continue
if i%1000 == 0:
print i,"/",tot
i+=1
lateness_observed( row['gtfs_trip_id'], row['stop_id'],
row['dow'],
row['stop_sequence'], row['lateness'],
auto_create = True );
cur.close()
def get_segment_IDs(scheduled_only=True):
cur = get_cursor();
if scheduled_only:
sql = "select gps_segment_id from gps_segments where trip_id is not null"
else:
sql = "select gps_segment_id from gps_segments"
SQLExec(cur,sql)
seg_ids = [s['gps_segment_id'] for s in cur]
cur.close()
return seg_ids
def populate_routeid_dirtag(deletefirst=False):
"""
Populates routeid_dirtag table with all distinct instances of
(routeid,dirtag) from the vehicle tracking data table joined with
the gtfs routes table.
If deletefirst is true, then the routeid_dirtag table is truncated
first.
"""
cur = get_cursor()
if deletefirst:
SQLExec(cur,"""truncate routeid_dirtag""")
SQLExec(cur,"""
insert into routeid_dirtag
(select distinct route_id,dirtag from vehicle_track vt inner join gtf_routes gr
on vt.routetag = gr.route_short_name
where vt.dirtag != 'null' and vt.dirtag is not null)
""")
cur.close()
def export_lateness_data( gpssched, sched_error ):
"""
Given a GPSBusSchedule gpssched, adds entries into the datamining_table
which records observations of lateness along with their attributes.
"""
sql = """
insert into datamining_table dt
( gps_segment_id, gtfs_trip_id, rms_schedule_error, vehicle_id,
route_name, vehicle_type, service_id, direction_id,
stop_lat, stop_lon, stop_id, stop_sequence,
scheduled_arrival_time, scheduled_departure_time,
actual_arrival_time, lateness, prev_stop_id )
values
( null, %(trip_id)s, %(sched_err)s, %(vehid)s,
%(routename)s, %(vehtype)s, %(service_id)s, %(dir_id)s,
%(stoplat)s,%(stoplon)s,%(stopid)s,%(stopseq)s,
%(sched_arr)s,%(sched_dep)s,%(actual_arr)s,%(lateness)s,
%(prev_stop_id)s )
"""
gtfs = gpssched.getGTFSSchedule()
basedict = { 'trip_id' : gtfs.trip_id,
'sched_err' : sched_error,
'vehid' : gpssched.segment.vehicle_id,
'routename' : gtfs.route_short_name,
'vehtype' : gtfs.route_type,
'service_id' : gtfs.service_id,
'dir_id' : gtfs.direction_id
}
cur = get_cursor()
for arrival in gpssched.getGPSSchedule():
print dict(arrival)
stopdict = dict(basedict)
stopdict.update ( { 'stoplat' : arrival['stop_lat'],
'stoplon' : arrival['stop_lon'],
'stopid' : arrival['stop_id'],
'stopseq' : arrival['stop_sequence'],
'sched_arr' : arrival['arrival_time_seconds'],
'sched_dep' : arrival['departure_time_seconds'],
'actual_arr' : arrival['actual_arrival_time_seconds'],
'lateness' : arrival['actual_arrival_time_seconds'] \
- arrival['departure_time_seconds'],
'prev_stop_id' : arrival['prev_stop_id']
} )
SQLExec(cur, sql, stopdict)
cur.close()
def create_observation_row( trip_id, stop_id, day_of_week, stop_sequence,
lateness_minutes, initial_num_obs=1 ):
obs_id = get_observation_stop_id(trip_id,stop_id,day_of_week,stop_sequence,
auto_create = True)
sql = """\
insert into simplified_lateness_observations
( minutes_late, num_observations, observed_stop_id )
values
( %(minutes)s, %(init)s, %(obsid)s )
"""
cur = get_cursor()
SQLExec(cur, sql, {'minutes':lateness_minutes,'init':initial_num_obs,
'obsid':obs_id})
cur.close()
def get_route_dirtags(route_short_name):
"""
Given the short name for a route, returns a list of dirtags
which are associated with that route.
"""
cur = get_cursor();
SQLExec(cur,
"""SELECT dirtag FROM routeid_dirtag
WHERE route_id IN (select route_id from gtf_routes
where route_short_name = %(rsn)s)""",
{'rsn':route_short_name});
ret = map(lambda r: r[0], cur);
cur.close()
return ret;
def get_stops( min_lat, max_lat, min_lon, max_lon ):
sql = """\
select * from gtf_stops
where stop_lat >= %(min_lat)s
and stop_lat <= %(max_lat)s
and stop_lon >= %(min_lon)s
and stop_lon <= %(max_lon)s
"""
cur = get_cursor()
SQLExec(cur,sql,{'min_lat':min_lat,
'max_lat':max_lat,
'min_lon':min_lon,
'max_lon':max_lon})
rows = cur.fetchall()
cur.close()
return map(dict,rows)
def get_routes_for_stop( stop_id ):
sql = """\
select distinct(route_short_name)
from gtf_routes gr
inner join gtf_trips gt
on gr.route_id = gt.route_id
inner join gtf_stop_times gst
on gst.trip_id = gt.trip_id
where gst.stop_id = %(stopid)s
"""
cur = get_cursor()
SQLExec(cur,sql,{'stopid':stop_id})
rows = cur.fetchall()
cur.close()
return [r[0] for r in rows]
def get_direction_for_dirtag(dirtag):
"""
Given a nextbus dirtag, returns the GTFS direction ID
"""
dir = dirtag.find("OB");
if dir < 0: dir = "Inbound"
else: dir = "Outbound"
cur = get_cursor();
SQLExec(cur,
"""Select direction_id from gtfs_directions
where description=%(dir)s""",
{'dir':dir});
ret = cur.next()[0];
cur.close();
return ret;
def export_trip_information(trip_id,first_arrive,first_depart,
trip_length,trip_duration,total_stops):
"""
Given a GTFS trip ID, the time (in seconds) of its first arrival and
departure, the length of the whole trip in meters, and the duration
of the trip in seconds, pushes it to the database.
"""
sql="""insert into gtf_trip_information (trip_id,first_arrival,
first_departure,trip_length_meters,trip_duration_seconds,
total_num_stops)
values
(%(tid)s,%(farr)s,%(fdep)s,%(lenm)s,%(durs)s,%(tns)s)"""
cur = get_cursor()
SQLExec(cur,sql,{'tid':trip_id,'farr':first_arrive,'fdep':first_depart,
'lenm':str(trip_length),'durs':trip_duration,
'tns':total_stops});
cur.close()
def getGTFSRouteData(route_id):
"""
Given a route ID, returns a row from the GTFS table as a dict-like type.
Keys:
'route_long_name',
'route_type',
'route_text_color',
'route_color',
'agency_id',
'route_id',
'route_url',
'route_desc',
'route_short_name'
"""
cur = get_cursor();
SQLExec(cur,"select * from gtf_routes where route_id=%(id)s",{'id':str(route_id)});
ret = cur.next();
cur.close();
return ret;
def export_gps_schedule(segment_id,schedule):
"""
Given a gps segment ID and a list of dictlike rows having the keys:
'stop_id', GTFS stop ID
'stop_sequence', GTFS trip stop sequence
'stop_headsign',
'pickup_type',
'drop_off_type',
'shape_dist_traveled',
'timepoint',
'arrival_time_seconds', time in seconds into day
'departure_time_seconds', time in seconds into day
'actual_arrival_time_seconds', actual arrival in seconds into day
'actual_departure_time_seconds' actual departure in seconds into day
'seconds_since_last_stop' time elapsed since arrival at previous stop
exports the rows appropriately into the database.
"""
keystr = \
"""stop_id,stop_sequence,stop_headsign,pickup_type,"""\
"""drop_off_type,shape_dist_traveled,timepoint,"""\
"""arrival_time_seconds,departure_time_seconds,"""\
"""actual_arrival_time_seconds,actual_departure_time_seconds,"""\
"""seconds_since_last_stop,prev_stop_id"""
keys = keystr.split(",")
sql = """insert into gps_stop_times (gps_segment_id,"""+keystr+""")
values
(%(gps_seg_id)s,""" \
+",".join(map(lambda k: "%("+k+")s", keys)) + """)"""
cur = get_cursor()
for row in schedule:
params = {}
params['gps_seg_id']=segment_id
for key in keys:
params[key]=row[key]
SQLExec(cur,sql,params);
cur.close()
def update_routes(vehicle_data):
"""
Inserts GPS tracking data into the database.
Vehicle data is a list of dicts mapping attributes to values.
Attributes can be the following:
id -- the vehicle id (string)
routeTag -- the route id (string)
dirTag -- identifier for the route direction (string)
lat -- latitude of the vehicle (float)
lon -- longitude of the vehicle (float)
secsSinceReport -- how old the data was upon recording time (integer)
predictable -- i have no idea what this field means, ask nextbus (string)
heading -- the direction the bus is moving (integer -- degrees?)
update_time -- the recording time (when this data was retrieved) (timestamp)
Of the above, update_time and secsSinceReport are required. If you
don't have any value for secsSinceReport then put in a 0. If you don't
have any value for update_time then what exactly are you trying to
accomplish?
"""
if len(vehicle_data) == 0:
print "empty list"
return
query_start = "insert into vehicle_track (" \
+ ",".join(vehicle_data[0].keys()) + ", reported_update_time) values (" \
+ ",".join(map(lambda attr: "%(" + attr + ")s",vehicle_data[0].keys())) \
+ ","
query_end = ")"
cur = get_cursor();
for vdata in vehicle_data:
q = ("date_trunc('second',TIMESTAMP '%(update_time)s') - " \
+ "interval '%(secsSinceReport)s seconds'") \
% vdata
q = query_start + q + query_end
#print q
SQLExec(cur,q,vdata);
cur.close();
def export_trip_stop_information(trip_id,stop_sequence,stop_number,
prev_stop_distance,cumulative_distance,
prev_stop_travel_time):
"""
Given a GTFS trip ID and stop sequence number, the number of this stop
along the route, the distance from the previous stop to this stop in meters,
the cumulative distance from the beginning of the trip to this stop in
meters, and the time in seconds it takes (according to the schedule) to
get to this stop from the previous stop, pushes it to the database.
"""
sql="""insert into gtf_stoptimes_information (trip_id, stop_sequence,
trip_stop_number,prev_stop_distance_meters,
cumulative_distance_meters,travel_time_seconds)
values
(%(tid)s,%(seq)s,%(num)s,%(dist)s,%(cdist)s,%(time)s)"""
cur = get_cursor()
if prev_stop_distance is not None:
prev_stop_distance = str(prev_stop_distance)
SQLExec(cur,sql,{'tid':trip_id,'seq':stop_sequence,'num':stop_number,
'dist':prev_stop_distance,
'cdist':str(cumulative_distance),
'time':prev_stop_travel_time});
cur.close()
def load_gps_segment_header(segment_id):
"""
Given a segment ID, returns:
(trip_id,trip_date,vehicle_id,schedule_error,offset)
where trip_id is the gtfs trip ID, trip_date is the date on whose schedule
the trip took place, vehicle_id is the gps vehicle's ID, schedule_error
is the measured error between the GPS route and the GTFS schedule, and
offset is the number of seconds to substract from any GTFS schedule times.
"""
sql_header = """select trip_id, trip_date, vehicle_id, schedule_error,
schedule_offset_seconds
from gps_segments
where gps_segment_id=%(segID)s"""
cur = get_cursor()
SQLExec(cur,sql_header,{'segID':segment_id});
header = [r for r in cur][0];
cur.close()
trip_id,trip_date,veh_id,sched_err,sched_off= \
map(lambda c:header[c],
('trip_id','trip_date','vehicle_id','schedule_error',
'schedule_offset_seconds'));
return trip_id,trip_date,veh_id,sched_err,sched_off
def get_best_matching_trip_ID(route_id, dir_id, start_date, start_time,
num_results=1):
"""
Given GTFS route and direction IDs, a datetime.date object representing
the day of the route, and start_time an integer representing the begin
time of the route as seconds into the day, returns a list of the
num_results best matching GTFS trips, in order of best matching start
datetime.
Each item in the list is of the form:
(trip_id, offset_seconds)
where trip_id is the GTFS trip ID, and offset_seconds represents the
offset (in seconds) that need be applied to the trip for any times
in the format of "seconds into day." This is necessary because
sometimes the best matching trip will come from the previous day's
service schedule, and so you will need to subtract 1 day = 86400 seconds
from the times for that bus.
For example, a bus running at 3am might be from today's schedule at
03:00, or it might be from yesterday's schedule at 27:00. If a match
is found for 03:00 today, then the offset returned is 0. If a match
is found for 27:00 yesterday, then the offset returned is 86400.
"""
yesterday_ids = map(lambda sid: "'"+str(sid)+"'",
get_serviceIDs_for_date(start_date -
datetime.timedelta(days=1)));
print " Yesterday's IDs:",yesterday_ids
today_ids = map(lambda sid: "'"+str(sid)+"'",
get_serviceIDs_for_date(start_date));
print " Today's IDs:",today_ids
print " (today is",start_date,")"
tomorrow_ids = map(lambda sid: "'"+str(sid)+"'",
get_serviceIDs_for_date(start_date +
datetime.timedelta(days=1)));
print " Tomorrow's IDs:",tomorrow_ids
sql = """(select trip_id, 0 as offset,
abs(first_departure-%(start_time)s) as diff
from gtf_trips natural join gtf_trip_information
where direction_id=%(dir_id)s and route_id=%(route_id)s
and service_id in (""" + ','.join(today_ids) + """)
union
select trip_id, 86400 as offset,
abs(first_departure-86400-%(start_time)s) as diff
from gtf_trips natural join gtf_trip_information
where direction_id=%(dir_id)s and route_id=%(route_id)s
and service_id in (""" + ','.join(yesterday_ids) + """)
union
select trip_id, -86400 as offset,
abs(first_departure+86400-%(start_time)s) as diff
from gtf_trips natural join gtf_trip_information
where direction_id=%(dir_id)s and route_id=%(route_id)s
and service_id in (""" + ','.join(tomorrow_ids) + """)
) order by diff limit """ + str(num_results)
cur = get_cursor()
SQLExec(cur,sql,
{'start_time':start_time,'dir_id':dir_id,'route_id':route_id});
ret = [(r['trip_id'],r['offset']) for r in cur]
if len(ret) == 0:
ret = None
cur.close()
return ret
def get_route_names(): cur = get_cursor() SQLExec(cur,"select route_short_name from gtf_routes"); ret = [s['route_short_name'] for s in cur] cur.close() return ret