65 lines
2.7 KiB
Python
65 lines
2.7 KiB
Python
#!/usr/bin/env python3
|
|
"""build_gtfs_db.py — GTFS-Datei in SQLite laden (einmalig).
|
|
Aufruf: python3 build_gtfs_db.py <gtfs_zip> <out_db> [gtfs_dir]
|
|
Legt Tabellen stops/trips/st an (Schema siehe gtfs-timetable-routing/references).
|
|
"""
|
|
import zipfile, sqlite3, os, sys, time
|
|
|
|
def main():
|
|
gtfs_zip = sys.argv[1]
|
|
out_db = sys.argv[2]
|
|
work = sys.argv[3] if len(sys.argv) > 3 else "/tmp/gtfs_x"
|
|
os.makedirs(work, exist_ok=True)
|
|
# extrahieren
|
|
with zipfile.ZipFile(gtfs_zip) as z:
|
|
z.extractall(work)
|
|
t0 = time.time()
|
|
con = sqlite3.connect(out_db)
|
|
con.execute("PRAGMA journal_mode=MEMORY"); con.execute("PRAGMA temp_store=MEMORY")
|
|
con.executescript("""
|
|
CREATE TABLE stops(stop_id TEXT PRIMARY KEY, stop_name TEXT, stop_lat REAL, stop_lon REAL);
|
|
CREATE TABLE trips(trip_id TEXT PRIMARY KEY, route_id TEXT, service_id TEXT);
|
|
CREATE TABLE st(stop_id TEXT, trip_id TEXT, seq INT, arr TEXT, dep TEXT);
|
|
CREATE INDEX idx_st_stop ON st(stop_id);
|
|
CREATE INDEX idx_st_trip ON st(trip_id);
|
|
CREATE INDEX idx_st_seq ON st(trip_id, seq);
|
|
""")
|
|
# stops.txt: stop_name,parent_station,stop_id,stop_lat,stop_lon,...
|
|
n=0
|
|
with open(f"{work}/stops.txt", encoding="utf-8") as f:
|
|
f.readline()
|
|
for line in f:
|
|
p = line.strip().split(",")
|
|
if len(p) >= 5 and p[3] and p[4]:
|
|
sid = p[2] if p[2] else p[1] # bevorzugt stop_id, fallback parent_station
|
|
con.execute("INSERT OR IGNORE INTO stops VALUES(?,?,?,?)", (sid, p[0], p[3], p[4]))
|
|
n += 1
|
|
print("stops:", n)
|
|
# trips.txt: route_id,service_id,trip_id
|
|
n=0
|
|
with open(f"{work}/trips.txt", encoding="utf-8") as f:
|
|
f.readline()
|
|
for line in f:
|
|
p = line.strip().split(",")
|
|
if len(p) >= 3 and p[0]:
|
|
con.execute("INSERT OR IGNORE INTO trips VALUES(?,?,?)", (p[2], p[0], p[1]))
|
|
n += 1
|
|
print("trips:", n)
|
|
# stop_times.txt: trip_id,arrival_time,departure_time,stop_id,stop_sequence,...
|
|
n=0; batch=[]
|
|
with open(f"{work}/stop_times.txt", encoding="utf-8") as f:
|
|
f.readline()
|
|
for line in f:
|
|
p = line.strip().split(",")
|
|
if len(p) >= 5 and p[0] and p[3]:
|
|
batch.append((p[3], p[0], int(p[4]) if p[4].isdigit() else 0, p[1], p[2]))
|
|
if len(batch) >= 50000:
|
|
con.executemany("INSERT INTO st VALUES(?,?,?,?,?)", batch); n += len(batch); batch = []
|
|
if batch:
|
|
con.executemany("INSERT INTO st VALUES(?,?,?,?,?)", batch); n += len(batch)
|
|
con.commit(); con.close()
|
|
print("stop_times:", n, "| DB gebaut in %.1fs" % (time.time()-t0))
|
|
|
|
if __name__ == "__main__":
|
|
main()
|