Somerset data is ready to be loaded into the database.

Acceptance Criteria

• Route and stop data copied into repository
• Database scripts updated
• Database load tested

The database should include local authority boundary data (from Ordnance Survey). This will allow for displaying the area that a mobile library organisation has jurisdiction over.

The connection between an organisation and a boundary should be optional, and should not be limited to UK.

Acceptance Criteria

• The boundary data is included in the database creation scripts
• A method is documented for updating this data

Shropshire mobile library data is ready to be loaded into the database. This data can be copied over to the database project and loading scripts updated to make use of it.

Acceptance Criteria

• Data loaded
• Refresh of database tested

We need a database view of all stops, that allows us to bring back a suitable amount of data about the stop. Primarily this will be from the stop table, but with associated IDs from Organisation, Mobile, and Route.

• ID
• Route ID
• Mobile ID
• Organisation ID
• Name
• Community
• Address
• Postcode
• Arrival
• Departure
• Timetable

Acceptance Criteria

• View created on database
• View tested against existing data (at least one organisation)
• View added to database creation scripts and create database run

In a few situations we generate from the stop frequency a set of visit dates. This is inefficient and we could alternatively do this once every X seconds (or even daily?), and reuse the results.

Running a script every time the mobile status is updated would be an option here. It would maybe need a simple status update table to keep track of when the statuses had been updated.

And to save the round trip of calling multiple procedures from code, this could be done under a single procedure along with the update of statuses.

Acceptance Criteria

• Table created to hold visit dates
• Table created to hold update times

Warwickshire data is ready to be loaded into the database.

Acceptance Criteria

• Route and stop data copied into repository
• Database scripts updated
• Database load tested

There are likely to be a lot of stops in the database, so displaying these on a map will need to use vector tiles, rather than downloading all in one go.

This can be achieved using the ST_AsMVT function. This ticket should create a function on the database that can be called from code, passing in a zoom level and map bounds.

Acceptance Criteria

• Function created on database fn_stops_mvt
• Function added to database creation scripts

The database should hold deprivation data.

This should be done in a way that could support deprivation of different types and different measures. The starting data point will be UK indices of deprivation, but potentially other datasets from International sources could be loaded.

The data will be linked to population stats (geospatial).

Acceptance Criteria

• Table schemas for deprivation
• UK indicies of deprivation loaded with permanent loading scripts

We need a database view of all organisations, that allows us to bring back a suitable amount of data about the organisation.

• Organisation name
• Organisation ID
• Timetable
• Website
• Email
• ONS Code
• Number of mobiles
• Number of routes
• Number of stops

Acceptance Criteria

• View created on database
• View tested against existing data (at least one organisation)
• View added to database creation scripts and create database run

The trip view needs to be extended to include associated information about the trip:

• Origin stop name
• Destination stop name
• Organisation name
• Mobile name
• Route name
• Estimated duration and distance
• Timetable duration (time available)

Acceptance Criteria

• View exists and has been tested
• Database creation tested

Perth and Kinross and Portsmouth data is ready to be loaded into the database.

Acceptance Criteria

• Route and stop data copied into repository
• Database scripts updated
• Database load tested

North Somerset data is ready to be loaded into the database.

Acceptance Criteria

• Route and stop data copied into repository
• Database scripts updated
• Database load tested

The database was not returning the stop name as part of the status view.

This is due to name not being in the timetable view, and not working it's way through.

Acceptance Criteria

• Stop name appears in dashboard
• Database creation tested

The database needs indexing for each table.

Acceptance Criteria

• Clustered index for each table
• Other indexes as appropriate

Views probably shouldn't have order by within them as we're going to be passing in sorting when calling the view.

Acceptance Criteria

• No order by clause in any of the views.

Libraries NI manages mobile libraries in Northern Ireland.

As the administrative geography is the whole of Northern Ireland, rather than use individual authorities, we should add Northern Ireland into the list at organisation level.

We also hold data for Northern Ireland so can load this in at the same time.

Acceptance Criteria

• Northern Ireland appears in select
• Northern Ireland mobiles appear
• Database creation script tested

We need a function to return a list of mobiles that have a stop within a certain distance of a location. The parameters will be:

• longitude
• latitude
• distance within

The return output will be a set of mobiles and the details of the nearest stop.

Acceptance Criteria

• Function or view created
• Database creation tested

Go through all views, tables, and functions again attempting to improve performance. As more mobiles are added, the performance is affected, so need to make this as performant as possible to deal with future additions.

Acceptance Criteria

• Script changes made
• Database creation script tested

Times are currently displaying wrong because they are stored in a time with time zone column.

Acceptance Criteria

• Stop times displaying correctly

The database will need to hold data on mobile libraries organisations.

We need:

• Website
• Contact email
• Domain for authentication
• ONS code

Acceptance Criteria

• All organisations are held in the database
• There is an email domain for every mobile library service
• There is a column for contact email
• The email domain is held in a separate table for authentication
• Creation scripts are updated and tested

The following data is available to load:

• Edinburgh
• Angus
• Wrexham

These can be loaded and added to the permanent loading scripts.

Acceptance Criteria

• Data added to loading scripts
• Recreate of the database tested

The stops view should return the stop coordinates so that these can be used in geospatial operations, and so that they can be filtered.

Acceptance Criteria

• Longitude returned in stop view
• Latitude returned in stop view
• Refresh of database tested

The database will need a staging table in order to host uploaded data that can be refined before making permanent and putting into the main database tables.

The staging table should be created, as well as a function to import the staged data into the database, once it has been accepted from staging.

Acceptance criteria

• Database table scripts created
• Database creation script updated and a fresh version tested
• Sample data tested (manual import)

Bath and North East Somerset data is ready to be loaded into the database.

Acceptance Criteria

• Route and stop data copied into repository
• Database scripts updated
• Database load tested

Cambridgeshire mobile library data is ready to be loaded into the database. This data can be copied over to the database project and loading scripts updated to make use of it.

Acceptance Criteria

• Data loaded
• Refresh of database tested

Norfolk mobile library data is ready to be loaded into the database. This data can be copied over to the database project and loading scripts updated to make use of it.

Acceptance Criteria

• Data loaded
• Refresh of database tested

The initial database needs to be set up in order to provide the basic tables and views required, confirming to relational standards and appropriate data types.

Acceptance Criteria

• Database creation script works against a fresh PostgreSQL server instance

We need a table to store the estimated location of each mobile library.

This will be updated on calls to get mobile library locations. This will be a separate call to the call to get all mobile library data, and will just return the current location of the mobile library.

With the routes, stops, and trip data, it should be possible to estimate a location for the mobile within the database. The location data will then be updated with that location, if it hasn't been updated in the last 1 minute.

Location table will include

• Location Id
• Mobile Id
• Date updated
• Type of update (estimated, real)

Acceptance Criteria

• Location table created
• Location view created
• Update function created
• Database script created
• Database creation tested

York data is ready to be loaded into the database.

Acceptance Criteria

• Route and stop data copied into repository
• Database scripts updated
• Database load tested

Milton Keynes mobile library data is ready to be loaded into the database. This data can be copied over to the database project and loading scripts updated to make use of it.

Acceptance Criteria

• Data loaded
• Refresh of database tested

There is a problem with the live location estimation.

This seems to be a problem with code setting a numeric value to an integer in the estimation function.

Acceptance Criteria

• Code fixed
• Live map tested
• Database creation scripts run

In order to have smooth mobile library movements we need to return the route for a given period so that the website can use it to move the move library until it next gets a location update.

Acceptance Criteria

• Add two additional parameters to the location function: update interval (integer: seconds) and smoothness (numeric: multiple)
• Array of coordinates returned by the view

The staging table should have the same column names as in the data schema.

Currently start and end are different (start_date, end_date).

Acceptance Criteria

• Table creation corrected
• Database creation tested

Essex mobile library data is ready to be loaded into the database. This data can be copied over to the database project and loading scripts updated to make use of it.

Acceptance Criteria

• Data loaded
• Refresh of database tested

West Dunbartonshire mobile library data is ready to be loaded into the database. This data can be copied over to the database project and loading scripts updated to make use of it.

Acceptance Criteria

• Data loaded
• Refresh of database tested

Add start and end columns to the routes view.

Acceptance Criteria

• Start and end returned from the view

The stop tiles could do with showing more detail. On a map it would be good to show the date and time for the stop.

Acceptance Criteria

• Label for the next date and time is returned
• Organisation colour

North Lincolnshire data is ready to be loaded into the database.

Acceptance Criteria

• Route and stop data copied into repository
• Database scripts updated
• Database load tested

The adding of the 1269 Norfolk has brought issues in performance. This has led to locking and period of downtime.

This task will be to go through the whole database looking at areas where this can be addressed. There is also an issue with Angus data where each stop is applied to a distinct route. This should be fixed.

In future more tickets may be needed to review how the locations table is updated, and how caching is applied.

Acceptance Criteria

• Angus has the correct number of routes
• Performance has improved.

There are likely to be a lot of routes in the database, so displaying these on a map will need to use vector tiles, rather than downloading all in one go.

This can be achieved using the ST_AsMVT function. This ticket should create a function on the database that can be called from code, passing in a zoom level and map bounds.

Acceptance Criteria

• Function created on database fn_routes_mvt
• Function added to database creation scripts

Organisations will be able to add certain options and information about themselves. This could include.

• Colour
• Logo URL
• Contact email

Acceptance Criteria

• Organisation CSV updated
• Database tables created and views
• Database creation tested

We need a database view of all mobile libraries, that allows us to bring back a suitable amount of data about the mobile to show in a dashboard. Including:

• Mobile name
• Mobile ID
• Organisation name
• Organisation ID
• Timetable
• Website
• Email
• Number of routes
• Number of stops

Acceptance Criteria

• View created on database
• View tested against existing data (at least one mobile)
• View added to database creation scripts and create database run

It will be useful for the stop view to have a greater number of derived fields and fields joined from other tables. Particularly this would be of use in situations such as the PDF stop generator, as it will need to have associated information.

Fields to add:

• Service name
• Mobile name
• Route name
• Day
• Frequency
• Dates

Acceptance Criteria

• Fields added to stop view
• Database creation script tested

A trip will be a link between two stops to represent the travel geometry between those two points. This will be the driving (HGV) line.

The tables should include:

• Route id
• Start stop id
• Destination stop id
• Duration (seconds)
• Distance (metres)

Acceptance Criteria

• Trip table created
• Trips view created
• Database scripts updated
• Database creation tested

It looks like there are a considerable number of trips in the system that do not have origin or destination stop set.

This is likely caused by the import job not correctly matching stop names. Route data also needs updating.

Acceptance Criteria

• Latest route and stop data loaded
• Not null constraints on trip table
• Corrected loading job
• Database creation tested

The database should hold population data in order to estimate the catchment area of a stop and of a route.

Initially this can be done with the UK Output Area population stats. But the data should be held in such a way that it would allow any population data to be loaded that would be usable. Suggest that simply holding polygons of population stats would allow for a geospatial query to be run that returned population for each stop.

Acceptance Criteria

• View to bring back population data against each stop
• View to bring back population data against each route (no double counting)
• View to bring back population data against each mobile
• View to bring back population data against each organisation
• UK Output area data loaded into the database

The README and Licence file should be fully completed.

Acceptance Criteria

• Fill out all sections of README
• Ensure Licence file exists and is linked in to README

We need a database view of all routes, that allows us to bring back a suitable amount of data about the route and its stops.

• ID
• Route name
• Mobile ID
• Organisation ID
• Frequency
• Timetable
• Start date
• End date
• Number of stops

Acceptance Criteria

• View created on database
• View tested against existing data (at least one organisation)
• View added to database creation scripts and create database run

Look at performance of the stops MVT functions.

This may involve:

• Creating a view for the function to pull from the table
• Removing fields that we don't need

Acceptance Criteria

• Changes made
• Database creation tested
• Map tested

The staging table holds data in the same format as the set flat-file data schema for mobile libraries.

This is useful for staging draft data and working on it. But is not useful in an efficient relational database where reference names (such as mobile name, organisation name) can be easily updated.

A function needs to be written which will convert the staging data into the database tables, and then empty it from staging. This should be done for a single organisation, as the table could hold many staging entries.

Acceptance Criteria

• Function is written which takes an organisation name argument
• Function is tested with test data (Aberdeenshire)
• Database creation scripts are updated and tested