def import_trips(self, trips_file):
cur = self.db.cursor()
drop_table_if_exists(self.db, "gtfs_trips")
cur.execute("CREATE TABLE gtfs_trips (trip_id, route_id);")
reader = csv.DictReader(io.TextIOWrapper(trips_file, 'utf-8'))
to_db = [(i['trip_id'], i['route_id']) for i in reader]
cur.executemany(
"INSERT INTO gtfs_trips (trip_id,route_id) VALUES (?, ?);", to_db)
self.db.commit()
def add_prev_and_next_stop_names(self):
drop_table_if_exists(self.db, 'SUCCESSOR_NAME')
drop_table_if_exists(self.db, 'PREDECESSOR_NAME')
self.db.execute("""ALTER TABLE osm_stops ADD COLUMN next_stops TEXT""")
self.db.execute("""ALTER TABLE osm_stops ADD COLUMN prev_stops TEXT""")
self.db.execute("""CREATE TABLE PREDECESSOR_NAME AS
SELECT succ_id osm_id, group_concat(name,'/') pred_name FROM (
SELECT s.succ_id, o.name
FROM successor s, osm_stops o
WHERE s.pred_id = o.osm_id AND o.name IS NOT NULL
GROUP BY s.succ_id, o.name)
GROUP BY succ_id""")
self.db.execute("""CREATE TABLE SUCCESSOR_NAME AS
SELECT pred_id osm_id, group_concat(name,'/') succ_name FROM (
SELECT s.pred_id, o.name
FROM successor s, osm_stops o
WHERE s.succ_id = o.osm_id AND o.name IS NOT NULL
GROUP BY s.pred_id, o.name)
GROUP BY pred_id""")
self.db.execute(
"CREATE INDEX PRED_NAME_INDEX on PREDECESSOR_NAME(osm_id)")
self.db.execute(
"CREATE INDEX SUCC_NAME_INDEX on SUCCESSOR_NAME(osm_id)")
self.db.execute(
"UPDATE osm_stops AS g SET next_stops = (SELECT succ_name FROM SUCCESSOR_NAME s WHERE s.osm_id=g.osm_id)"
)
self.db.execute(
"UPDATE osm_stops AS g SET prev_stops = (SELECT pred_name FROM PREDECESSOR_NAME s WHERE s.osm_id=g.osm_id)"
)
drop_table_if_exists(self.db, 'SUCCESSOR_NAME')
drop_table_if_exists(self.db, 'PREDECESSOR_NAME')
self.db.commit()
def import_stops(self, stops_file):
cur = self.db.cursor()
drop_table_if_exists(self.db, "gtfs_stops")
cur.execute(
"CREATE TABLE gtfs_stops (stop_id,stop_name,stop_lat,stop_lon,location_type,parent_station);"
)
reader = csv.DictReader(io.TextIOWrapper(stops_file, 'utf-8'))
to_db = [(i['stop_id'], i['stop_name'], i['stop_lat'], i['stop_lon'],
i['location_type'], i['parent_station']) for i in reader]
cur.executemany(
"INSERT INTO gtfs_stops (stop_id,stop_name,stop_lat,stop_lon,location_type,parent_station) VALUES (?, ?, ?, ?, ?, ?);",
to_db)
self.db.commit()
def setup_osm_tables(self):
drop_table_if_exists(self.db, 'osm_stops')
self.db.execute('''CREATE TABLE osm_stops
(osm_id TEXT PRIMARY KEY, name TEXT, network TEXT, operator TEXT, railway TEXT, highway TEXT, public_transport TEXT, lat REAL, lon REAL,
mode TEXT, type TEXT, ref TEXT, ref_key TEXT, assumed_platform TEXT, empty_name INTEGER)'''
)
drop_table_if_exists(self.db, 'osm_stop_areas')
self.db.execute('''CREATE TABLE osm_stop_areas
(osm_id TEXT PRIMARY KEY, name TEXT, network TEXT, operator TEXT,
mode TEXT, ref TEXT, ref_key TEXT)''')
drop_table_if_exists(self.db, 'osm_stop_area_members')
self.db.execute('''CREATE TABLE osm_stop_area_members
(stop_area_id TEXT, member_id TEXT)''')
drop_table_if_exists(self.db, 'successor')
self.db.execute('''CREATE TABLE successor
(pred_id TEXT, succ_id TEXT)''')
drop_table_if_exists(self.db, 'platform_nodes')
self.db.execute('''CREATE TABLE platform_nodes
(way_id TEXT, node_id TEXT)''')
def export_match_candidates(self):
drop_table_if_exists(self.db, "candidates")
self.db.execute('''CREATE TABLE candidates
(ifopt_id text, osm_id text, rating real, distance real, name_distance real, platform_matches integer, successor_rating INTEGER, mode_rating real)'''
)
for stop_id in self.official_matches:
matches = self.official_matches[stop_id]
rows = []
for match in matches:
rows.append((
match["globalID"],
match["match"]["id"],
match["rating"],
match['distance'],
match['name_distance'],
match['platform_matches'],
match['successor_rating'],
match['mode_rating'],
))
self.logger.debug("export match candidates ", rows)
self.db.executemany(
'INSERT INTO candidates VALUES (?,?,?,?,?,?,?,?)', rows)
self.db.commit()
self.db.execute(
'''CREATE INDEX osm_index ON candidates(osm_id, rating DESC)''')
self.db.execute(
'''CREATE INDEX ifopt_index ON candidates(ifopt_id, rating DESC)'''
)
backup_table_if_exists(self.db, "matches", "matches_backup")
drop_table_if_exists(self.db, "matches")
self.db.execute("""CREATE TABLE matches AS
SELECT * FROM candidates WHERE ifopt_id='Non existant'""")
# Add Spatial columns
try:
self.db.execute("SELECT InitSpatialMetaData()")
self.db.execute(
"SELECT AddGeometryColumn('osm_stops', 'the_geom', 4326, 'POINT','XY')"
)
self.db.execute(
"SELECT AddGeometryColumn('matches', 'the_geom', 4326, 'LINESTRING','XY')"
)
self.db.execute(
"SELECT AddGeometryColumn('candidates', 'the_geom', 4326, 'LINESTRING','XY')"
)
except:
pass
self.db.execute(
"UPDATE osm_stops SET the_geom = MakePoint(lon,lat, 4326)")
self.db.execute("""UPDATE matches SET the_geom = (
SELECT LineFromText('LINESTRING('||o.lon||' '||o.lat||', '||n.lon||' '||n.lat||')', 4326)
FROM osm_stops o, haltestellen_unified n
WHERE o.osm_id = matches.osm_id AND matches.ifopt_id = n.globaleID AND n.lat IS NOT NULL)"""
)
self.db.execute("""UPDATE candidates SET the_geom = (
SELECT LineFromText('LINESTRING('||o.lon||' '||o.lat||', '||n.lon||' '||n.lat||')', 4326)
FROM osm_stops o, haltestellen_unified n
WHERE o.osm_id = candidates.osm_id AND candidates.ifopt_id = n.globaleID AND n.lat IS NOT NULL)"""
)
self.db.commit()