search

alan-turing-institute / urbanroute

Urban routing algorithms
MIT License
2 stars 1 forks source link

Create a materialized view of the AQ graph each day on a cron job. #11

Open PatrickOHara opened 4 years ago

PatrickOHara commented 4 years ago

Is your feature request related to a problem? Please describe. Each day the air quality predictions from our model will be updated. We should maintain a graph that is always upto date with the latest predictions.

Describe the solution you'd like To improve query efficiency, we should calculate the air pollution exposure on each edge and save it in a materialized PostgreSQL view.

The materialized view should be refreshed each day. This can be done on a cron job in Azure.

Describe alternatives you've considered

Alternatively we could re-calculate the AQ on each edge every time a new request comes in, but this is wasteful computation.

Or we could save the results to a table instead of a materialized view. But there are ~500k edges & 48 hours of predictions. This will very quickly become a big table. Recomputing historical values is not too expensive, so there is little value in storing historical AQ graphs.

Additional context Add any other context or screenshots about the feature request here.

OscartGiles commented 4 years ago

I have a pull request in to deal with materialized views (https://github.com/alan-turing-institute/clean-air-infrastructure/pull/373) which i hope to finish this week.