Better Handle Centreline Cleaning

[cczhu]

Traffic Prophet reads from prj_volume.tp_daily_volumes to generate CountMatch predictions. The source table is prj_volume.centreline_volumes, which uses an arterycode to centreline geo_id mapping to map FLOW counts to the centreline. A recent refresh of this table uses a mapping with centreline segments not defined in gis.centreline, so we generated the following temporary matview:

CREATE MATERIALIZED VIEW prj_volume.tp_daily_volumes_clean
TABLESPACE pg_default
AS
 WITH distinct_centrelines AS (
         SELECT DISTINCT tp_daily_volumes_1.centreline_id
           FROM prj_volume.tp_daily_volumes tp_daily_volumes_1
        ), available_centrelines AS (
         SELECT distinct_centrelines.centreline_id
           FROM distinct_centrelines
             JOIN gis.centreline ON distinct_centrelines.centreline_id::numeric = centreline.geo_id
        )
 SELECT available_centrelines.centreline_id,
    tp_daily_volumes.direction,
    tp_daily_volumes.count_year,
    tp_daily_volumes.count_date,
    tp_daily_volumes.daily_count
   FROM available_centrelines
     JOIN prj_volume.tp_daily_volumes USING (centreline_id)
  ORDER BY available_centrelines.centreline_id, tp_daily_volumes.direction, tp_daily_volumes.count_year, tp_daily_volumes.count_date
WITH DATA;

ALTER TABLE prj_volume.tp_daily_volumes_clean
    OWNER TO prj_volume_admins;

COMMENT ON MATERIALIZED VIEW prj_volume.tp_daily_volumes_clean
    IS 'Version of tp_daily_volumes_clean without centreline IDs that do not exist in gis.centreline.';

to read data into Traffic Prophet. Annoyingly I didn't manage to document this entire process during my offboarding.

If the problem can be corrected quickly, this issue is to remove tp_daily_volumes_clean and ensure all documentation in docs uses tp_daily_volumes rather than _clean. If the problem cannot be corrected quickly, this issue is to add the script above to input_data/flow.