class WMTSettings(): """ Class representing a settings read/write handler (for remembering data between sessions) for When's My Transport """ def __init__(self, instance_name): self.instance_name = instance_name self.settingsdb = WMTDatabase('%s.settings.db' % self.instance_name) self.settingsdb.write_query("create table if not exists %s_settings (setting_name unique, setting_value)" % self.instance_name) def get_setting(self, setting_name): """ Fetch value of setting from settings database """ # pylint: disable=W0703 setting_value = self.settingsdb.get_value("select setting_value from %s_settings where setting_name = ?" % self.instance_name, (setting_name,)) # Try unpickling, if this doesn't work then return the raw value (to deal with legacy databases) if setting_value is not None: try: setting_value = pickle.loads(setting_value.encode('utf-8')) except Exception: # Pickle can throw loads of weird exceptions, gotta catch them all! pass return setting_value def update_setting(self, setting_name, setting_value): """ Set value of named setting in settings database """ setting_value = pickle.dumps(setting_value) self.settingsdb.write_query("insert or replace into %s_settings (setting_name, setting_value) values (?, ?)" % self.instance_name, (setting_name, setting_value))
def scrape_odd_platform_directions(write_file=False): """ Check Tfl Tube API for Underground platforms that are not designated with a *-bound direction, and (optionally) generates a blank CSV template for those stations with Inner/Outer Rail designations """ database = WMTDatabase("whensmytrain.geodata.db") print "Platforms without a Inner/Outer Rail specification:" station_platforms = {} all_train_data = get_tfl_prediction_summaries() for (line_code, train_data) in all_train_data.items(): for station in train_data.findall('S'): station_code = station.attrib['Code'][:3] station_name = station.attrib['N'][:-1] station_name = station_name.replace(" Circle", "") for platform in station.findall('P'): platform_name = platform.attrib['N'] direction = re.search("(North|East|South|West)bound", platform_name, re.I) if direction is None: rail = re.search("(Inner|Outer) Rail", platform_name, re.I) if rail: if (station_name, station_code) not in station_platforms: station_platforms[(station_name, station_code)] = [] if line_code not in station_platforms[(station_name, station_code)]: station_platforms[(station_name, station_code)].append(line_code) if direction is None and rail is None: print "%s %s" % (station_name, platform_name) print "" if write_file: outputfile = open('./sourcedata/circle_platform_data.csv', 'w') else: outputfile = sys.stdout writer = csv.writer(outputfile) writer.writerow(['Station Code', 'Station Name', 'Line Code', 'Inner Rail', 'Outer Rail']) errors = [] for (station_name, station_code) in sorted(station_platforms.keys()): for line_code in sorted(station_platforms[(station_name, station_code)]): writer.writerow([station_code, station_name, line_code, '', '']) if not database.get_value("SELECT name FROM locations WHERE name=?", (station_name,)): errors.append("%s is not in the station database" % station_name) outputfile.flush() print "" for error in errors: print error outputfile.close()
def scrape_tfl_destination_codes(): """ Scrape codes from TfL's TrackerNet and save to a database """ database = WMTDatabase("whensmytube.destinationcodes.db") destination_summary = {} all_train_data = get_tfl_prediction_summaries() for (line_code, train_data) in all_train_data.items(): for train in train_data.findall('.//T'): destination = train.attrib['DE'] destination_code = train.attrib['D'] if destination_summary.get(destination_code, destination) != destination and destination_code != '0': print "Error - mismatching destinations: %s (existing) and %s (new) with code %s" \ % (destination_summary[destination_code], destination, destination_code) database.write_query("INSERT OR IGNORE INTO destination_codes VALUES (?, ?, ?)", (destination_code, line_code, destination)) destination_summary[destination_code] = destination pprint(destination_summary)
def check_tfl_destination_codes(): """ Audit codes we have recorded and make sure that they are all fine """ # Check to see if destination is in our database geodata = RailStationLocations() database = WMTDatabase("whensmytube.destinationcodes.db") rows = database.get_rows("SELECT destination_name, destination_code, line_code FROM destination_codes") for (destination_name, destination_code, line_code) in rows: # Hack: Fake a ElementTree object to use the XML parser's tube train filter function fake_tag = lambda x: 1 fake_tag.attrib = {'Destination': destination_name, 'DestCode': str(destination_code)} if not filter_tube_train(fake_tag): continue train = TubeTrain(destination_name, "Northbound", "1200", "C", "001") destination = train.get_destination_no_via() if not destination.endswith("Train") and not geodata.find_fuzzy_match(destination, {}): print "Destination %s (%s) on %s not found in locations database" % (destination, destination_code, line_code) via = train.get_via() if via and not geodata.find_fuzzy_match(via, {}): print "Via %s (%s) on %s not found in locations database" % (via, destination_code, line_code)
def import_network_data_to_graph(): """ Import data from a file describing the edges of the Tube network and turn it into a graph object which we pickle and save """ database = WMTDatabase("whensmytrain.geodata.db") # Adapted from https://github.com/smly/hubigraph/blob/fa23adc07c87dd2a310a20d04f428f819d43cbdb/test/LondonUnderground.txt # which is a CSV of all edges in the network reader = csv.reader(open('./sourcedata/tube-connections.csv')) reader.next() # First we organise our data so that each station knows which lines it is on, and which stations it connects to stations_neighbours = {} interchanges_by_foot = [] for (station1, station2, line) in reader: if line in ("National Rail", "East London"): continue if line == "Walk": interchanges_by_foot.append((station1, station2)) else: # When a line splits into two branches, we don't want people being able to travel from one branch to another without # changing. So for these special cases, we mark the transitions as being in a particular direction in the CSV, with the # direction coming after a colon (e.g. "Leytonstone:Northbound","Wanstead","Central" and "Snaresbrook","Leytonstone:Southbound","Central" # Effectively the Central Line station has become two nodes, and now you cannot go directly from Snaresbrook to Wanstead. direction = station1.partition(':')[2] # Blank for most station1 = station1.partition(':')[0] # So station name becomes just e.g. Leytonstone station_data = stations_neighbours.get(station1, []) if (station2, direction, line) not in station_data: station_data += [(station2, direction, line)] stations_neighbours[station1] = station_data # Sanity-check our data and make sure it matches database canonical_data = database.get_rows("SELECT * FROM locations") canonical_station_names = unique_values([canonical['name'] for canonical in canonical_data]) for station in sorted(stations_neighbours.keys()): if station not in canonical_station_names: print "Error! %s is not in the canonical database of station names" % station for (neighbour, direction, line) in stations_neighbours[station]: line_code = get_line_code(line) if not database.get_value("SELECT name FROM locations WHERE name=? AND line=?", (station, line_code)): print "Error! %s is mistakenly labelled as being on the %s line in list of nodes" % (station, line) for station in sorted(canonical_station_names): if station not in stations_neighbours.keys(): print "Error! %s is not in the list of station nodes" % station continue database_lines = database.get_rows("SELECT line FROM locations WHERE name=?", (station,)) for row in database_lines: if row['line'] not in [get_line_code(line) for (neighbour, direction, line) in stations_neighbours[station]]: print "Error! %s is not shown as being on the %s line in the list of nodes" % (station, row['line']) # Produce versions of the graphs for unique lines graphs = {} lines = unique_values([line for station in stations_neighbours.values() for (neighbour, direction, line) in station]) for line in lines: this_line_only = {} for (station_name, neighbours) in stations_neighbours.items(): neighbours_for_this_line = [neighbour for neighbour in neighbours if neighbour[2] == line] if neighbours_for_this_line: this_line_only[station_name] = neighbours_for_this_line graphs[get_line_code(line)] = create_graph_from_dict(this_line_only, database, interchanges_by_foot) graphs['All'] = create_graph_from_dict(stations_neighbours, database, interchanges_by_foot) pickle.dump(graphs, open("./db/whensmytrain.network.gr", "w"))
def __init__(self, instance_name): self.instance_name = instance_name self.settingsdb = WMTDatabase('%s.settings.db' % self.instance_name) self.settingsdb.write_query("create table if not exists %s_settings (setting_name unique, setting_value)" % self.instance_name)
def __init__(self, instance_name): self.database = WMTDatabase('%s.geodata.db' % instance_name) self.network = None self.returned_object = Location
class WMTLocations(): """ Service object used to find stops or stations (locations) - given a position, exact match or fuzzy match, will return the best matching stop. Subclassed and not called directly """ def __init__(self, instance_name): self.database = WMTDatabase('%s.geodata.db' % instance_name) self.network = None self.returned_object = Location def find_closest(self, position, params): """ Find the closest location to the (lat, long) position specified, querying the database with dictionary params, of the format { Column Name : value }. Returns an object of class returned_object, or None if none found nearby """ # GPSes use WGS84 model of Globe, but Easting/Northing based on OSGB36, so convert to an easting/northing logging.debug("Position in WGS84 determined as lat/long: %s %s", position[0], position[1]) easting, northing = convertWGS84toOSEastingNorthing(*position) logging.debug("Translated into OS Easting %s, Northing %s", easting, northing) # Do a funny bit of Pythagoras to work out closest stop. We can't find square root of a number in sqlite # but then again, we don't need to, the smallest square will do. Sort by this column in ascending order # and find the first row (where_statement, where_values) = self.database.make_where_statement('locations', params) query = """ SELECT (location_easting - %d)*(location_easting - %d) + (location_northing - %d)*(location_northing - %d) AS dist_squared, * FROM locations WHERE %s ORDER BY dist_squared LIMIT 1 """ % (easting, easting, northing, northing, where_statement) row = self.database.get_row(query, where_values) if row: obj = self.returned_object(Distance=sqrt(row['dist_squared']), **row) logging.debug("Have found nearest location %s", obj) return obj else: logging.debug("No location found near %s, sorry", position) return None def find_fuzzy_match(self, stop_or_station_name, params): """ Find the best fuzzy match to the query_string, querying the database with dictionary params, of the format { Column Name : value, }. Returns an object of class returned_object, or None if no fuzzy match found """ if not stop_or_station_name or stop_or_station_name == "Unknown": return None # Try to get an exact match first against station names in database exact_params = params.copy() exact_params.update({'name': stop_or_station_name}) exact_match = self.find_exact_match(exact_params) if exact_match: return exact_match # Users may not give exact details, so we try to match fuzzily (where_statement, where_values) = self.database.make_where_statement('locations', params) rows = self.database.get_rows("SELECT * FROM locations WHERE %s" % where_statement, where_values) possible_matches = [self.returned_object(**row) for row in rows] best_match = get_best_fuzzy_match(stop_or_station_name, possible_matches) if best_match: return best_match else: return None def find_exact_match(self, params): """ Find the exact match for an item matching params. Returns an object of class returned_object, or None if no fuzzy match found """ (where_statement, where_values) = self.database.make_where_statement('locations', params) row = self.database.get_row("SELECT * FROM locations WHERE %s LIMIT 1" % where_statement, where_values) if row: return self.returned_object(**row) else: return None