Created an efficient database schema to store raw bus data into postgres/postgis to allow efficient queries. The new database uses foreign key indexes to improve performance and eliminates repeating groups to make it easier to investigate data with SQL queries. Bus stops were compared with passing routes to find routes with the most stops and stops with the most passing routes. Each route was analyzed to find which route had the highest amount of cumulative boardings, alightings, and stops, which may make it an ideal candidate to provide an express bus on the same route.
Interactive Maps indicating the busiest bus stops and longest bus routes were created.
Links:
Figure 1: Database schema design
Figure 2: Screenshot of Busiest Bus Stops Map (number of routes associated with each stop)
