Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
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
elif day_of_week == 6:
service_id = '3'
else:
raise Exception, "Not a day of week"
sql = """\
select *
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
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})
rows = cur.fetchall()
cur.close()
return map(dict,rows)
def depict_predinterval_calculation(rows=None,degsep=1,cond=60,alpha=0.05):
"""
Creates a plot explaining how the prediction interval calculations
work.
"""
if rows is None:
print "Selecting..."
cur = db.get_cursor()
db.SQLExec(cur,"""
select d2.lateness, trip_stop_weight
from datamining_table d2
natural join trip_stop_weights
inner join datamining_table d1
on d1.gps_segment_id = d2.gps_segment_id
and d2.stop_number-d1.stop_number=%(degsep)s
and d1.lateness = %(cond)s
and d2.lateness is not null and d1.lateness is not null
""", {'degsep':degsep,'cond':cond});
print "Retrieving..."
rows = cur.fetchall();
cur.close()
print len(rows),"rows retrieved."
def getMaxSegID():
"""
Returns the largest vehicle segment ID found in the "tracked_routes"
table (see the export_gps_route function below). This can be used to
construct a unique ID for further segments.
Note that there should be a 1-1 correspondence between segment and
trip IDs for any particular service day. Of course part of the point
of this is to eliminate any cases where this is not true in a meaningful
way.
"""
sql = """select max(gps_segment_id) from gps_segments"""
cur = get_cursor()
SQLExec(cur,sql);
ret = [r['max'] for r in cur];
cur.close();
if ret[0] is None:
return 0;
return ret[0];
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,
def get_routehops():
print "Selecting..."
cur = db.get_cursor()
db.SQLExec(cur,
"""select *
from datamining_table dm
natural join random_gps_segments rgs
inner join gps_segments gs on dm.gps_segment_id=gs.gps_segment_id
where lateness is not null
--and rgs.rand < 0.1
and gs.trip_date = '2009-03-27'
and dm.route_name in ('1','9','19')
and dm.service_id='1'
--and random() < 0.001""")
print "Retrieving..."
rows=cur.fetchall()
cur.close()
return rows
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 __init__(self,dbconn,autoFill=False,autoCommit=False):
"""
Creates a ServiceDateHandler using the database from dbconn.
If autoFill is True, then any missing service combinations
are added to the database. If autoCommit is True, these
changes will be committed immediately.
"""
cur = db.get_cursor()
## Prepare calendar data
db.SQLExec(cur,"""select monday,tuesday,wednesday,thursday,friday,saturday,
sunday, service_id, start_date, end_date from gtf_calendar""");
self.calendar_rows = cur.fetchall();
db.SQLExec(cur,"""select * from gtf_calendar_dates""");
self.calendar_date_rows = cur.fetchall();
## Load existing combos
db.SQLExec(cur,"""select * from service_combinations
order by combination_id, service_id""");
service_combo_rows = cur.fetchall();
self.combos = {}; # map from combo_id to combo
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();