search

hasadna / open-bus

:bus: Analysing Israel's public transport data
93 stars 29 forks source link

Adding siri_ride_id to siri_vehicle_location table #355

Closed EyalBerger closed 2 years ago

EyalBerger commented 2 years ago

Following the schema change (siri and gtfs separation), it's now not possible to join bus locations with siri ride directly. Could siri_ride_id be added to siri_vehicle_location table?

OriHoch commented 2 years ago

it can be joined via siri_ride_stop table, for example the following query gets all vehicle locations of a given journey_ref:

select *
from
    siri_vehicle_location, siri_ride_stop, siri_ride
where
    siri_vehicle_location.siri_ride_stop_id = siri_ride_stop.id
    and siri_ride_stop.siri_ride_id = siri_ride.id
    and siri_ride.journey_ref = '2021-11-10-39878369'
EyalBerger commented 2 years ago

Yes, I figured out it could be joined vie stops, but logically the locations are connected with the rides, and this it going to be one of the most common joins we will perform. The merge via stops is not straight forward, but yet - as you mentioned - possible.

OriHoch commented 2 years ago

we should keep the database structure normalized, we can add database views / API methods / Python functions to make using it easier

EyalBerger commented 2 years ago

Sure, so closing this issue.