Skip to content

Update database so that route data is stored against individual flights, not as reference data. #112

@tomcarman

Description

@tomcarman

Issue

  • Currently route data is stored in a route_data table, which is refreshed periodically from the routing API, at a maximum interval of 60min per callsign.
  • This data is then joined (via callsign as a key) with data in the aircraft_data table to generate route statistics. (Note: It is also used in the AboveTimeline component to display route data for aircraft in close proximity - this will need some minor modifications as part of this update)
  • This approach is flawed, as callsigns are not consistently bound to the same route; their associated route may change over time - hence the current periodic refreshes from the routing API.
  • This means that currently, when route statistics are generated, and rows in aircraft_data are joined with route_data, the "most recent known route" for that callsign is attributed to the flight - but at the time of the flight that callsign could have had a different route associated.

Fix

  • Expand the aircraft_data table to store the route information inline against the flight.
  • Continue to maintain the route_data table, but treat it as a "cache" of recently seen routes (to avoiding always calling the routing API).
  • Update the existing implementation to store route data into the new columns in aircraft_data.
  • Decrease the frequency which callsigns are re-fetched from the routing API from 60min to 120min to reduce load.
  • Create a migration script for existing users, so that route data is copied for existing flights, from the route_data table to the newly added columns in the aircraft_data table. This accepts that historic flights will just receive the most recent route assigned to that callsign - if this was different at the time the flight was originally captured, that is now lost data, as it is in the existing implementation.

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions